Microsoft SSRS 2016 Report Variable to Group data: Variable values cannot be used in group expressions

0
votes

I am building a report in Microsoft SSRS 2016; I have multiple groups in my report, all these groupings are done via (the same) set of parameters. You can see the screen shot below:

1) I have 3 groups in SSRS 2016 Report Designer.

enter image description here

2) All 3 groups use the same expression (using parameters) to group report.

enter image description here

In a nutshell, if the parameter value is X, it is grouped by X; if that parameter value is Y, it is grouped by Y; I do this for many groups, creating many parameter.

I will have many more groups in the future, and hence many more parameter options,as the report grows. I am trying to figure out a way to optimize this SSRS 2016 report by writing this code JUST ONCE.

I tried to add a Report Variable as shown below:

enter image description here

Now I have a new Report Variable (GV2) that stores this code with Switch operator.

I now try to use this Report Variable across many different groups.

enter image description here

I get this error:

Variable values cannot be used in group expressions

Is there any other way to do this? I need to optimize this sort of parameterized grouping.

May be writing some custom codes in SSRS; if so, can you provide the code (my knowledge in .Net is zero!)

Thanks

1
variablesreporting-servicesgroupingssrs-2016custom-code
It may keep your report simpler if you do this in your dataset. Pass the parameter to your SQL query if you are not doing already and add a 'groupby' column. You could then change what appears in that column based on the parameters passed in Your report would only then need to group on a fixed column. – Alan Schofield
The issue here is that the data is fixed; its not any aggregation issue (GROUP BY in T-SQL); its more to do with the presentation in SSRS (Row Groups); our client wants to club many existing reports into one; so we write one stored procedure/query, and then add Row Groups in SSRS, and parameterize these Row Groups – user3812887
@AlanSchofield In a sense I cannot modify anything in the dataset ! – user3812887
I wasn't suggesting you group the data in SQL. I was suggesting you add an extra column to the data returned from SQL that you could populate based on the parameters. It's the same process you are trying to do in your SWITCH statement, but just done in SQL with a case statement. Sorry, I've just seen your additional comment. – Alan Schofield

1 Answers

0
votes

You can use a parameter to do this.

  • Set up a parameter for your group by options
  • In the Available Values, add the things you want to group by
  • For the value section of these, put the name of the field you want to group by. For example - if you want to group by Fields!Region_Name.Value, you set up your parameter with Region_Name in the value field. Parameter
  • In your group expression on the tablix, use =Fields(Parameter!GroupBy.Value).Value

Now when you select the value from the parameter, it will group by that. You simply have to add new values to the GroupBy parameter in the future to add more options.