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

0
votes

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?

2
reporting-servicesfilteroptionalssrs-tablix
Are you accessing a SQL data source, and if so is there a reason you are filtering in the Tablix rather than in the query? - user359040
Because I don't know how to filter optionally on a query. I mean, how can I use the "where" sometimes, and sometimes don't? - DarthRoman

2 Answers

1
votes

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 = '')
...
1
votes

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, '') = '' 
    )
...