Dynamic groupings using IIF with multiple datasources

1
votes

I'm using report viewer and trying to create dynamic groupings on my table. Before the report is generated, I have a popup that ask if the report generated should be grouped by category or not.

I have two datasets, one called ReportParameterDTO and the second is called LoanDTO

The tablix is connected to the dataset LoanDTO. I want to create a parent grouping expression for that tablix such that if the first value of ReportParameterDTO.GroupByCategory is true, then it should group, otherwise do nothing.

What I tried

=IIF(First(Fields!GroupByCategory.Value, "ReportParameterDTO") = true, Fields!Category.Value, ""))

It gives me back and error around Fields!GroupByCategory.Value and the error within the error list states that A group expression for tablix includes an aggregate function.

The IIF Expression will compile if I use Field Values from LoanDTO but I don't want to do that.

2
reporting-servicesssrs-2012

2 Answers

1
votes
  1. Add a new parameter.
  2. Set it to Internal.
  3. Set the Available Values to pull from the ReportParameterDTO dataset and the Value field will be GroupByCategory.
  4. Also set the Default Values to pull from the ReportParameterDTO query.

  5. Now you can refer to this internal parameter in your grouping expression like so:

    =IIf(Parameters!GroupByCategory.Value = True, Fields!Category.Value, Nothing)
-1
votes

You can create both table and whit your parameter show or hide the table visibility. So like this I think is going to be more easy for you to set up your table and groups.