SSRS Grouping Data Together

Question

I have a report that lists securities that are held and there security types. I want to some business logic that says security types 1, 2 and 3 are Equities, while security types 4, 5 and 6 are Bonds and then I want to group the report by these. Any idea how to do that? Right now the report lists each individual security type.

Answer 1

A good way to do this is to add a calculated field to your dataset that evaluates this logic. Then you can group on this new column in the report.

1. Go to your dataset properties
2. Add a calculated field

3. Name it and enter the expression for it. It could be something like this:

      =Switch(Fields!SecurityType.Value = 1 OR Fields!SecurityType.Value = 2 OR Fields!SecurityType.Value = 3, 'Equity'
   ,Fields!SecurityType.Value = 4 OR Fields!SecurityType.Value = 5 OR Fields!SecurityType.Value = 6, 'Bond', true, 'Other')    
   

4. Add a grouping to your table/matrix using this new column

Answer 2

amend your dataset: within your select sql statement , lastly add the following:

CASE WHEN [security types] IN ('1', '2', '3') THEN 'Equities' 
     WHEN [security types] IN ('4', '5', '6') THEN 'Bonds' 
  ELSE 'others' 
END AS securitiestype

Then with in your SSRS report you can now use securitiestype as a group filter.