SSRS query report parameter visibility when there is no value

Question

I have one report with 4 multi valued parameters where the values populates based on which value is chosen from the first parameter:

Example: @Param1 = Forest, @Param2 = Lake , @Param3 = Fish

Depending on the values selected, the last parameter might not have a value.

Example: @Param1 = Desert, @Param2 = Sand, @Param3 = Null (Empty)

In this case, having @param3 visible in Reporting Services just displays an empty parameter box, and the user starts to wonder if there is missing data and so on.

How can I have the empty @Param3 get some kind of disabled in t-sql code?

This is the query for @param3:

SELECT DISTINCT Column3
FROM TABLE
WHERE Column1 = (@param1) AND
Column2 = (@param2)
ORDER BY Column3

The main dataset query has this predicate:

FROM TABLE
WHERE (Column1 = (@param1) or (@param1) is not null) AND
       Column2 = (@param2) or (@param2) is not null)
      AND (Column3 IN (@Param3) OR (@Param3) !='') AND 
(Column4 IN (@param4)) OR (@param4 !='')
ORDER BY Month

.

Dont forget, not blind and not funny...however there is other forums for this kind of comments dont forget that either. — Hankman3000
Format your posting without those #, and it will be much easier to read it. Don't make it difficult to help you. — jarlh
Post the code you are using to populate the parameter options - it should be possible to include a 'No Value Needed' option when there is no data to return. — AHiggins
Possible duplicate of How to handle a parameter that has no value in SSRS — AHiggins

AHiggins AHiggins · Accepted Answer · 2016-01-20T13:55:52

Try adding a 'dummy' value to your script, and only returning it when there are no other values available:

SELECT DISTINCT Column3
FROM TABLE
WHERE 
    Column1 = (@param1) AND
    Column2 = (@param2)
UNION ALL 
SELECT 'No Value Needed'
WHERE NOT EXISTS 
  (
    SELECT 1
    FROM TABLE 
    WHERE 
        Column1 = (@param1) AND
        Column2 = (@param2)
  )
ORDER BY Column3

SSRS query report parameter visibility when there is no value

1 Answers