SSRS - Multiple Value Group
I am trying to group by specific field values. I have project information data and I would like to group in the electoral area.
However, I would like to group the values together in a specific way. For example:
If the values are:
Invermere District, Cranbrook City, Creston City
Then group these projects into a group called "EAST"
If the values are:
Valemount Village, New Denver Village, Castle Village
Then group these projects in a group called "WEST"
All other bands are in "BASIN".
So, I created a group, and in the group properties, I placed an expression in the "Group" group :
**=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 thinking too much about this, please HELP!
UPDATE
Below are examples of data:
- the name of the project
- Project Description
- Location
- Electoral zone
It is retrieved and the layout currently looks like this:
I also have a Select Scope field and this is the field where I want to display SORT / GROUP data, but not using SQL query because I don't want to aggregate the data, I want it to be the same as these, and create groups of rows.In the example below, I just want it to look like this:
source to share
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 create an additional field so you can group 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 cities to define, and in case ... the end is out of control, you can create a table mapping each city to some region and do the join:
source to share