0

In my SSRS report layout, there is a parameter @GroupBy with four values: A, B, C and D.

In the table, there are four columns: Column AA, Column BB, Column CC and Column DD. I want to make this four columns dynamic. For examples,

  1. when I select B in @GroupBy, the table shall be rearranged to this seq: BB, AA, CC, DD.
  2. when I select C in @GroupBy, the table shall be rearranged to this seq: CC, DD, AA, BB. (special condition: CC must always be next to DD)
  3. when I select D in @GroupBy, the table shall be rearranged to this seq: DD, CC, AA, BB. (special condition: CC must always be next to DD)
  4. when I select A in @GroupBy, the table shall be rearranged to this seq: AA, BB, CC, DD. (back to the original seq)

Can someone help me?

Chris Wong
  • 31
  • 1
  • 1
  • 6
  • I think the only way to do that in SSRS would be to create all of the combinations of columns in and hide each dependant on the parameter selected. Something like http://stackoverflow.com/questions/27555339/show-or-hide-ssrs-column-based-on-specific-parameter-value or http://stackoverflow.com/questions/726783/reporting-services-hide-table-column-based-upon-report-parameter – Matt Aug 14 '15 at 08:53
  • Are you implementing all four columns in a single group? i.e. as a single column in the report design? – SQLDiver Aug 14 '15 at 12:28

1 Answers1

0

First I would reorganise the dataset (hopefully it is SQL) so that there are 4 rows for each current row, with a GroupBy column containing A, B, C or D. In SQL this would need 3 UNION ALL clauses.

Then in the Tablix I would create a Column group on the GroupBy column.

Finally in the Sort for that Column Group, I would add an expression to test if the dataset value matches the parameter.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40