CODEWITHSUNDEEP
sql-serverreporting-servicesexpression
Serdia
Serdia

Reputation: 4428

how to write an expression in SSRS to filter data

I need to filter my result based on Parameter value. If it says "Open" then DueDate column should be = NULL, if says 'Closed' that DueDate = NOT NULL, and if it says "Both" then it should grab all DueDates

I have created query parameter "Status" that gives me 3 possible values:

enter image description here

Next I created report parameter "Status" and Allow Multiple Values

enter image description here

Now I go to my main query and go to Filters:

enter image description here

And here I cant understand how can I write an expression saying:

If report "status" value = "Open" then show me the result where DueDate IS NULL, If report "Status" value = "Closed" then DueDate IS NOT NULL , And If report "Status" value = "Both" then show me all DueDates ( null and not null)

Another thing is I already have case statement in my query:

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
                             -- CASE WHEN (tblNoteRecipients.CompletedDate 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))
                        )

Is any way out of it in SSRS I can create parameter that accepts values Open, Closed and Both?

Added test1

enter image description here

Upvotes: 0

Views: 6598

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Add a Filter in the tablix like this:

enter image description here

In Expression use:

=IIF(
(Array.IndexOf(Parameters!Status.Value,"Open")>-1 and 
Isnothing(Fields!DueDate.Value)) or
(Array.IndexOf(Parameters!Status.Value,"Closed")>-1 and not 
Isnothing(Fields!DueDate.Value)) or
(Array.IndexOf(Parameters!Status.Value,"Both")>-1),
 "Include","Exclude")

For Value use:

="Include"

Upvotes: 1

Related Questions