Adding a parameter in SSRS Report Builder with string/varchar values but field in SQL is numeric

Question

I have a report I am trying to add a parameter for in Report Builder but am having issues with exactly what I need to do to pass through the selected parameter value upon report run into the report SQL given that the parameter selection differs from the value stored in the table.

What I mean by that is this:

There is a field in the table that the report queries called ErrorLevel that stores numeric values (1, 2, 3, 4). However, each of these values correspond with a text string.

1 is Warning;

2 is Override;

3 is Restriction;

and 4 is Pending.

These text strings are what is displayed when actually running the report. I am able to create a parameter just fine by using the following query.

Parameter no. 1

SET NOCOUNT ON
(Select '' as errorlevel
union
select distinct errorlevel from [TABLE] 
where errorlevel is not null and errorlevel != ''

Report SQL

Select distinct 
case errorlevel
When 1 then 'Warning'
When 2 then 'Override'
When 3 then 'Restriction'
When 4 then 'Pending'
Else '' as errorlevel
from [TABLE] 
where ('<ALL>' in (@errorlevel) or errorlevel in (@errorlevel))

But the issue is that when the end-user runs the report and selects the report, they see the numeric values (1, 2, 3, or 4) but they would like to see/select the string values (Warning, Override, Restriction, Pending). I tried using the following query.

Parameter no. 2

SET NOCOUNT ON
(Select '<ALL>' as errorlevel
union
select distinct 
CASE ErrorLevel 
When 1 then 'Warning'
When 2 then 'Override'
When 3 then 'Restriction'
When 4 then 'Pending'
Else '' as errorlevel
from [TABLE] 
where errorlevel is not null and errorlevel != ''

When I use this, the parameter passes the string values to the SQL in the report as string instead of numeric and then the report is blank. I know I have to do something in the SQL to take what the parameter is passing (e.g. Warning, Override, etc.) and convert that to the corresponding numeric values but not sure how to do that. Any guidance is appreciated.

Answer 1

I would add a field to the query in SQL or the Dataset in SSRS to convert the number into the desired text.

You can use your same CASE statement for the SQL

Select <CURRENT FIELDS>, 
CASE ErrorLevel When 1 then 'Warning' When 2 then 'Override' When 3 then 'Restriction' When 4 then 'Pending' Else '' END as ErrorLabel
from <REST OF QUERY>

If you can't do it in the query (due to using a stored proc or other issue), you can add a Calculated Field to the Dataset in SSRS.

• Open (double-click) your dataset and click on the Fields tab.

• Click Add and select Calculated Field.

• Give your new field a name (ErrorLabel)

• for the expression, use the SWITCH statement (which is like a lame CASE statement)

  =SWITCH(Fields!ErrorLevel.Value = 1, "Warning", 
      Fields!ErrorLevel.Value = 2, "Override", 
      Fields!ErrorLevel.Value = 3, "Restriction", 
      Fields!ErrorLevel.Value = 4, "Pending", 
      1 = 1, "" )