Grouping by a matrix depending on a value found via combination of three string columns in ssrs 2008

Question

I have a matrix in ssrs 2008 like:

Id type1    type2    type3
1  low     normal    normal
2  high     low      normal
3  normal  normal    normal

What I would like to do is to group by this table in ssrs 2008, but not any of these columns. I will need to add an additional hidden column called "Total". The rule of this column will be (I dont know how to write it and where in ssrs):

int total = 0;
if(type1<>normal) total++;
if(type2<>normal) total++;
if(type3<>normal) total++;
return total;

And the group by need to be based on this column. So an example below:

Id type1    type2    type3   total(visibility:false)
2  high     low      normal   2
1  low     normal    normal   1
3  normal  normal    normal   0

How can I provide it in ssrs 2008. Any help would be appreciated

Is this actually a matrix table based on a TYPE column or are there separate Type columns (1, 2, 3) in a regular table? Does your data look like the first example (ID, type1, type2, type3, value)? — Hannover Fist
they are type columns in a regular table. data currently is looking like (id,type1,type2,type3), and I want to sort this table by a total column that we need to assign as the examples in the question. the total column is something temprorary, noone will see it, it is there for only order by process — Eray Balkanli

Hannover Fist Hannover Fist · Accepted Answer · 2016-04-07T17:36:36

Your Total column expression should be:

=IIF(type1 <> "normal", 1, 0) + IIF(type2 <> "normal", 1, 0) + IIF(type3 <> "normal", 1, 0)

The IFF checks the expression in argument 1 and returns 1 if true (second argument) else 0 (third argument).

I think you also want to use this as the SORTING expression (in reverse [Z-A] order).

Grouping by a matrix depending on a value found via combination of three string columns in ssrs 2008

1 Answers