CODEWITHSUNDEEP
sqlsql-serversql-server-2008reporting-services
WendyA
WendyA

Reputation: 45

SSRS - Group By Multiple Values

I am trying to group by specific values of a field. I have project information data populating, and I would like to group on electoral area field.

However, I would like to group the values together in a specific way. For example:

If the values are:

District of Invermere, City of Cranbrook, Town of Creston

Then group those projects in a group called "EAST"

If the values are:

Village of Valemount, Village of New Denver, Village of Kaslo

Then group those projects in a group called "WEST"

Everything else group in "BASIN".

So, far I have created a group and in the group properties I have put an expression in the "Group on" field:

**=iif(Fields!cbt_electoralareaidname.Value = "District of Invermere" OR "City of Cranbrook" , "EAST" ,(iif(Fields!cbt_electoralareaidname.Value = "RDCK Area F" OR "Town of Creston", "WEST", "BASINWIDE")))**

But nothing is working... I know I am overthinking this, please HELP!

UPDATE

The following is sample data:

It is retrieved and the layout is currently like this:

Project Details

I also have the field Electoral Area, and that is the field I want to SORT/GROUP the data on but not using the SQL query because I do not want to aggregate, I want the details as they are and create Row Groups. For the example below, I just want it to look like below:

Sorted By

Upvotes: 4

Views: 3201

Answers (1)

Tim3880
Tim3880

Reputation: 2583

You can try something like this:

Select ...
From ..
GROUP by 
case 
when city in ("city1","city2",...) then "west" 
When city in (...) Then "east"
Else "other"
End

If you want to generate an extra field so you can group by later:

Select ..., 
case 
when city in ("city1","city2",...) then "west" 
When city in (...) Then "east"
Else "Basin"
End AS region
From ..

If you have many citys to define and the case ... end is out of control, you can create a table mapping each city to some region and do a join:

Upvotes: 4

Related Questions