SSRS - Min, Max and Average Percentage Values for Each Row in a Matrix

1
votes

Using SSRS 2008R2

I have a matrix set up displaying % values in each cell:

         ColGrp1  ColGrp2 ColGrp3

RowGrp1     5%      80%     50%

RowGrp2     ..      ..       ..

RowGrp3     ..      ..       ..

The expression deriving the percentage value is as follows:

=Sum(Fields!FieldX.Value)
/
Count(Fields!FieldX.Value)

Field X contains either a 0 or a 1 in the dataset, so I'm seeing the percentage of the 1's in the data above - this is fine.

My issue is that I need to include the min, max and avg values for each row group:

Col1  Col2 Col3  Min  Max Avg

5%    80%  50%  ..   ..   ..

..    ..   ..   ..   ..   ..  

..    ..   ..   ..   ..   ..

The avg value is fine as I have just created a column outside of the column group and used the same expression as above.

However, I'm unable to find a way to get the Min and Max % values.

Any ideas?

2
sqlsql-serverreporting-servicespercentage
Thanks for the answers guys. It may not have been clear in my post, but the Col1/2/3 are based upon a matrix column grouping, so there can be any number of these... – qwerty12
I suspect there is no universal solution in this case. Any chance of moving grouping to SQL part and leaving SSRS to do presentation bit? – Pavel Nefyodov

2 Answers

0
votes
SELECT Col1, Col2, Col3, CASE WHEN Col1 < Col2 AND Col1 < Col3 
                               THEN Col1
                               WHEN Col2 < Col1 AND Col2 < Col3 THEN Col2 
                               ELSE Col3
                               END AS Min, 
                          CASE WHEN Col1 > Col2 AND Col1 > Col3 
                               THEN Col1
                               WHEN Col2 > Col1 AND Col2 > Col3 THEN Col2 
                               ELSE Col3
                               END AS Max, 
       ((Col1 + col2 + col3) / 3 ) AS Avg
FROM table
0
votes

Try this :

SELECT
    *,
    (select MIN(col) from (VALUES(Col1),(Col2),(Col3),......) x([col])) [MIN],
    (select MAX(col) from (VALUES(Col1),(Col2),(Col3),......) x([col])) [MAX]
FROM
    (
    -- your query --
    ) AS T

Reference : Get the minimum value between several columns