Hide all middle columns in SSRS

0
votes

I have a matrix report in SSRS 2008 displaying information about a Company and the total points earned by each company for a selected Quarter.

Example

Company  | Current | Previous | Diff |
--------------------------------------
         | Q3 2008 | Q2 2008  |      |
--------------------------------------
Comp 1   | 2000    | 1500     | 500  |
--------------------------------------
Comp 2   | 1200    | 1400     | -200 |
--------------------------------------

In my SSRS report I have a RowGroup based on my Company field from the data set ([Company]). And I have a ColumnGroup based on my Quarter field from the data set. Sorting in the group is reversed because I want to start with the latest quarter.

I have parameter for selecting companies and 2 parameters for selecting quarters (QuarterCurrent and QuarterPrevious).

As a side effect if I select 2 quarters that are not consequent I get more than 2 columns which is something that I do not like.

But then I thought that if I could hide the columns in the middle and display on the First (QuarterPrevious) and the Last (QuarterCurrent) I will be able to compare any quarter to any other quarter.

My question is about hiding the columns in the "middle" of the matrix. I am trying to

achieve something like this:

Company  | Current  | *(HIDDEN)* | Previous | Diff |
----------------------------------------------------
         | Q3 2008  | *Q2 2008*  | Q1 2008  |      |
---------------------------------------------------- 
Comp 1   | 2000     | *1500*     | 1300     | 700  |
---------------------------------------------------- 
Comp 2   | 1200     | *1400*     | 1250     | -50  |
----------------------------------------------------

So in the end I will have only the Current and the Previous columns visible.

I tried the following approaches:

  • Setting the Column Visibility property to:

    =IIF(StrComp(Fields!Quarter.Value,First(Fields!Quarter.Value))=0 OR StrComp(Fields!Quarter.Value,Last(Fields!Quarter.Value))=0,False,True)

In my understanding this should set the property Hide to False to each column that is equal to the First and the Last values in the sequence, and to True to all the others. However it does not happen like this, but all columns were hidden.

  • I tried setting the same expression to the Visibility property in Group Properties. Still has the same effect - all columns were hiding.

Edit : format code

2
reporting-services

2 Answers

0
votes

Instead of two parameters for your quarter selection, have you tried a multi-select dropdown parameter ?

0
votes

I solved the issue in the following way: Instead of using parameter with range (From and To) I used a parameter with two values and my queries is not BETWEEN From and To, but rather IN [From, To]. The values of From and To can be any 2 quarters (they can even be switched - From does not have to be before To).