SQL SSRS-several conditions in one report

问题:

I am creating a tabular report with several conditions. As a case in point, the conditions could be either of these:

X is NULL

X is not NULL

X like '%Y%'

I want to create a kind of drill through report with the capability of selecting one of the above conditions.

Now I am creating three tables and use IIF for the visibility of each. I add the parameter to visibility and by selecting one condition the related table becomes visible and the other two becomes hidden.
But I am thinking of just a single table with a parameter for selecting desired condition.

回答1:

Instead of using a visibility expression, add it as a filter. This will only show rows that match the expression. You can do something like this:

Expression: ="TRUE"

Operator: =

Value: IIF(Parameters!Status.Label = "NULL", "TRUE", "FALSE")

回答2:

Just add a case statement to your query and use a parameter that returns a number for the different report types. Something like this:

WHERE CASE WHEN @ReportType = 1 AND x IS NULL THEN 'Y'
           WHEN @ReportType = 2 AND x IS NOT NULL THEN 'Y'
           WHEN @ReportType = 3 AND x LIKE '%y%' THEN 'Y'
           ELSE 'N' END = 'Y'