Why is my matrix row group sort not working in SSRS

Question

I have a matrix where the rows are being pulled from a DB and they are showing as follows:

Critical
High
Informational
Low
Medium

I need them to show like this:

Critical
High
Medium
Low
Informational

Currently, I am going to the row group properties, and I am choosing sort, and entering in the following expression.

=IIF(Fields!Risk.Value = "Critical", 1, IIF(Fields!Risk.Value = "High", 2, IIF(Fields!Risk.Value = "Medium", 3, IIF(Fields!Risk.Value = "Low", 4, 5))))

When I generate a report, it now only has 1 row. Just the "critical" row. All other rows are missing.

Does anyone have any idea what I am doing wrong?

When I use this:

=iif(Fields!Risk.Value = "Critical", "1", iif(Fields!Risk.Value = "High", "2", iif(Fields!Risk.Value = "Medium", "3", iif(Fields!Risk.Value = "Low", "4", iif(Fields!Risk.Value = "Informational", "5", "")))))

all the rows show again but in the incorrect order as if no sort is being done at all.

Answer 1

It may be cleaner and more precise to make the risk identifier available through the actual dataset rather than at runtime of the report. In your original dataset you could include a case statement within your select sql statement:

CASE [Risk] WHEN 'Critical' THEN '1' WHEN 'High' THEN '2'
WHEN 'Medium' THEN '3' WHEN 'Low' THEN '4' ELSE '5' END as risk_identifier

You would then include "risk_identifier" in your tablix/matrix and would be available to group by or sort by.

Answer 2

You have the following options:

Try to generate an identifier for this data using Row_Number() function in your database. Then you can specify it un your row group. Try a switch ssrs expression instead of a nested iif as follows:

=SWITCH(Fields!CurrentRiskLevel.Value = "Low", "Green",
Fields!CurrentRiskLevel.Value = "Moderate", "Blue",
Fields!CurrentRiskLevel.Value = "Medium", "Yellow",
Fields!CurrentRiskLevel.Value = "High", "Orange",
Fields!CurrentRiskLevel.Value = "Very High", "Red"
)