CODEWITHSUNDEEP
reporting-servicesfilteroption-typessrs-tablix
Roman
Roman

Reputation: 1711

How to apply sometimes a filter in a tablix on SQL Report Services, and sometimes don't?

I am doing a report with Visual Studio, and I am newbie on this kind of reports. I have a parameter (StringID), and in its properties I allow multiple values. I also have a tablix with the following filter:

Expression: [@ExternalID] -- Operator: In -- Value: [@StringID]

It works well, but I also want to allow the user to select all the registries (ie, not to filter by StringID), and if this field is blank it shows all the registries with ExternalID empty, and that is not what I want. So the question is:

Can I have an optional filter or something with this functionality?

Upvotes: 0

Views: 1245

Answers (2)

thomasswilliams
thomasswilliams

Reputation: 388

I agree with Mark.

Going a bit further - you might need to alter your query to cover the cases: all ExternalIDs, ExternalID is in the passed @StringID, or ExternalID is empty. One way to just get the empties is to use a "magic value" that is not in the data, for example: 

...
AND (
    --get all 
    @StringID IS NULL OR 
    --get in passed string 
    ExternalID IN @StringID OR 
    --"magic value" - get empty "ExternalID"
    (
     @StringID = '_EMPTY_' AND 
     ISNULL(ExternalID, '') = '' 
    )
...

Upvotes: 1

user359040
user359040

Reputation:

The simplest solution would probably be to amend the query to select based on the value of the parameter - something like:

...
and (ExternalID in @StringID or @StringID = '')
...

Upvotes: 1

Related Questions