Thursday, January 26, 2012

Dynamic Grouping in SSRS

To achieve dynamic grouping in SSRS , i.e you group by a parameter chosen by the user.

1) Send the group by parameter to the stored procedure/sql statement and let SQL handle the grouping by case statement
 Ex :
    SELECT   case(@groupby)
when 'gender' then dummy.Gender
when 'race' then dummy.Race
when 'district' then dummy.District
end "groupbyfield",
count(*)"Count",
 FROM  dbo.dummy
WHERE DummyDate BETWEEN @startdate and @enddate
     GROUP BYcase(@groupby)
when 'gender' then dummy.Gender
when 'race' then dummy.Race
when 'district' then dummy.District
end "groupbyfield"

2) Sort by the "groupbyfield"  in SSRS.

No comments:

Post a Comment