CODEWITHSUNDEEP
sqlsql-servert-sqlreporting-servicesssrs-2012
Hankman3000
Hankman3000

Reputation: 115

SSRS query report parameter visibility when there is no value

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

.

Upvotes: 0

Views: 365

Answers (1)

AHiggins
AHiggins

Reputation: 7219

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

Upvotes: 1

Related Questions