SSRS drop down list displays no data

Question

I have a SSRS report with drop down list as active,inactive and all. when i select active and inactive it displays the data but when i select 'all' it displays no data. I have tried many possibilities but did not worked to me.

Here is my where condition in main procedure

WHERE 
     ( (@Status = 'ALL') OR
     (DisabledDate IS NULL AND @Status = 'ACTIVE') OR 
     (DisabledDate IS NOT NULL AND @Status = 'INACTIVE')

     )

and my parameter query is

SELECT 'ALL' AS Status
UNION ALL

SELECT DISTINCT  (case when DisabledDate IS NULL then 'ACTIVE' else 'INACTIVE' END) AS Status
FROM            Table

Aditya Aditya · Accepted Answer · 2014-03-21T04:39:15

Change your,

parameter query to:

SELECT 'ALL' AS STATUS
UNION ALL 
SELECT 'ACTIVE' AS STATUS
UNION ALL 
SELECT 'INACTIVE' AS STATUS

where condition in main procedure to:

WHERE
@Status = 
COALESCE(
CASE WHEN STATUS = 'ALL' THEN 'ALL' END,
CASE WHEN STATUS = 'ACTIVE' THEN 'ACTIVE' END
CASE WHEN STATUS = 'INACTIVE' THEN 'INACTIVE' END
)

SSRS drop down list displays no data

1 Answers