SSRS matrix and dynamic grouping from user parameters

0
votes

I'm using SQL Server 2014 Reporting Services to display matrix with dynamic data in the following manner:

  1. Data for report resides in a database view, where one record has >20 id/name attributes (e.g. CityID, CityName, CountryID, CountryName, SalesmanID, SalesmanName, ProductID, ProductName, Index1ID, Index1Name, Index2ID, Index2Name...) and 2 decimal values (Value1, Value2)
  2. SSRS report has exactly one matrix (tablix)
  3. User specifies what data to put in the tablix columns "domain" and what data to put on the rows "domain" (e.g. user selects parameters columns: Product, and rows: City)
  4. Tablix value shows aggregated Value1 and Value2

This dynamic feature works by using expressions in the group definition. In the "Group on" field I used an expression like this:

=Switch(
Parameters!XAxisDimensionParameter.Value=1, Fields!CityId.Value,
Parameters!XAxisDimensionParameter.Value=2, Fields!CodeId.Value,
Parameters!XAxisDimensionParameter.Value=3, Fields!NameId.Value,
Parameters!XAxisDimensionParameter.Value=4, ...,
..., ...,
)

What I would now like to do is to add a grouping functionality to rows. User would enter e.g. "Country,Region,City" in the "Group by" parameter and the tablix would group Products data by CountryID, RegionID and CityID. Pay attention to the detail that there can be an arbitrary number of grouping specified by the user. No grouping is also a valid option.

I can do this statically in design-time,but it's not the solution for the user requirement.

It looks like it's impossible to dynamically add grouping in the runtime.

Is it really?

1
dynamicreporting-servicesgrouping

1 Answers

0
votes

I would do the dynamic grouping in your SQL. You can use the a CASE expression with the parameter. Then in the report you would group by the [dynamic_group] column.

SQL example

;WITH
example_data
AS
(
    SELECT tbl.* FROM (VALUES
      ( 'City1', 'Code1', 'Name1')
    , ( 'City2', 'Code2', 'Name2')
    , ( 'City3', 'Code3', 'Name3')
    , ( 'City4', 'Code4', 'Name4')
    , ( 'City5', 'Code5', 'Name5')
    , ( 'City6', 'Code6', 'Name6')
    ) tbl ([CityId], [CodeId], [NameId]) 
)
SELECT 
    [dynamic_group] = 
        CASE 
            WHEN @XAxisDimensionParameter IN(1) THEN [CityId]
            WHEN @XAxisDimensionParameter IN(2) THEN [CodeId]
            WHEN @XAxisDimensionParameter IN(3) THEN [NameId]
            WHEN @XAxisDimensionParameter IN(1,2,3) THEN [CityId] + ':' + [CodeId] + ':' +  [NameId]
            ELSE NULL
        END 
FROM 
    example_data

Query results

query results