How to create drop-down list in SSRS based on WHERE clause below?

Question

At my organization we using UI third party tool that communicates with SQL database. I have access to a database, but not to the tool.

Based on a stored procedure below user able to choose from drop-down list DairyStatus "Open", "Closed", or "Both"

ALTER Procedure
AS
@ShowOpen bit = 0,
@ShowClosed bit = 0
 SELECT
 FROM 
 WHERE 
              AND
                        (
                        (CASE WHEN (@ShowOpen = 1) THEN
                              CASE WHEN (tblNoteRecipients.CompletedDate IS NULL and tblNoteRecipients.IsDiary = 1) or tblNoteRecipients.UserGUID is null THEN 1 ELSE 0 END

                        ELSE
                              1
                        END = 1)
                  AND
                        (CASE WHEN (@ShowClosed = 1) THEN
                              CASE WHEN (tblNoteRecipients.CompletedDate IS NULL) THEN 0 ELSE 1 END
                        ELSE
                              1
                        END = 1)
                  OR    ((@ShowOpen = 1) AND (@ShowClosed = 1))
                        )

So my question is how can I make same drop-down list in SSRS? What would be the data-set in order to populate this drop-down list?

Kostya Kostya · Accepted Answer · 2017-02-10T17:45:30

create a parameter in ssrs with 3 static values(open closed both) in where clause it should be something like :

( @DairyStatus = 'open' and ((tblNoteRecipients.CompletedDate IS NULL and tblNoteRecipients.IsDiary = 1) or tblNoteRecipients.UserGUID is null)) or
( @DairyStatus = 'closed' and tblNoteRecipients.CompletedDate IS not NULL) or
  @DairyStatus = 'both'

How to create drop-down list in SSRS based on WHERE clause below?

1 Answers