CODEWITHSUNDEEP
reporting-servicesssrs-2012ssrs-tablixssrs-grouping
OmGanesh
OmGanesh

Reputation: 1062

Conditionally grouping tablix in SSRS reporting

My tablix has following columns

Country, City, College, totalstudent,passedstudent,failedstudent

I have a parameter "GroupBy" with values "country/city/college". So, when i select one of the parameter, my tablix should be grouped by that parameter and only so that parameter column + total,passed,failed columns

For eg. If user selects GroupBy = "City" then, the tablix will show 

City, totalstudent, passedstudent, failedstudent

The values in total, passed and failed is total sum grouped in that city.

The similar logic should be applied to country and college also.

So, far i am able to show the all columns with grouping applied by country, then city and then college. (which is one of simple ssrs grouping)

(My need is to only apply the one grouping based on parameter and only show that particular columns)

Note: I have a raw dataset that has all these values retrieved using inline sql from my sql database. (Dataset1: Country,City,College,totalstudent,passedstudent,failedstudent)

Upvotes: 1

Views: 5482

Answers (3)

OmGanesh
OmGanesh

Reputation: 1062

I tried the different approaches (I went through the last/3rd approach)

  1. Used multiple tablix for the different grouping conditions and used the parameter to hide/show the correct tablix in the report Pitfall: It is awfully slow since I have 4 different grouping conditions and putting in 4 different tablix.

  2. Using the dynamic grouping concept as illustrated at ( http://www.advancedssrs.com/2014/01/how-can-i-use-dynamic-grouping.html) which is good. But It was not suitable for my situation as it was also comparatively slow while i try to sort the columns in SSRS. (I need the sorting feature in all columns)

  3. Created a new parameter (groupby) which is used to mimic the scenario of tabs. So, when the user selects one of the value ( Country/City/College), I am grouping my SQL result based on this groupby parameter value using conditional grouping statements and returning result back to SSRS.


    SELECT
    CASE WHEN @GroupBy = 'Country' THEN CountryName
    WHEN @GroupBy = 'City' THEN CityName
    WHEN @GroupBy = 'College' THEN CollegeName
    END AS GroupTitle,
    SUM(totals) AS totalstudent,
    SUM(passes) AS passedstudent,
    SUM(fails) AS failedstudent,
    FROM #temp
    GROUP BY CASE WHEN @GroupBy = 'Country' THEN CountryName
    WHEN @GroupBy = 'City' THEN CityName
    WHEN @GroupBy = 'College' THEN CollegeName
    END
    ORDER BY 1
    DROP TABLE #temp

Now, I use the GroupTitle in my first column and rest of the aggregated values in the remaining columns in a single tablix. The output looks like: SSRS Report sample output

Upvotes: 0

William Xu
William Xu

Reputation: 261

When creating your dataset, don't directly write your query in the box --- instead, use expression. 

="SELECT " + Parameters!GroupBy.Value + ", passedstudent, failedstudent
  FROM Table GROUP BY " + Parameters!GroupBy.Value

Your "passedstudent" and "failedstudent" will be some aggregation, either COUNT or SUM, depends on your case.

Upvotes: -1

StevenWhite
StevenWhite

Reputation: 6024

You can group your table by an expression. In your group properties you would write something like this:

=Switch(Parameters!GroupBy.Value = "City", Fields!City.Value
    , Parameters!GroupBy.Value = "College", Fields!College.Value
    , ...)

The other columns should be able to just use regular Sum functions and work with any grouping.

Upvotes: 2

Related Questions