SSRS avoid WHERE clause if select all is selected

Question

I am working on an SSRS report and a part of my sql query is like

WHERE SuperVisorId IN (@SupervisorIDs) AND CreatedDate> @StartDate

where the @SupervisorIDs is a dropdown with option of "select all" and individual supervisors.

So if the supervisors "all" option is selected , then I don't need to include that in the where clause and my where clause is only this

  WHERE CreatedDate> @StartDate

So how can I make the WHERE clause looks different according to Selection of dropdown?

Answer 1

This only applies if you are using a single valued parameter with a manually added All option to the list of available values. Multi-value parameters do not know when all options are selected.

SQL Server doesn't always execute the conditions in a where clause in the order you write them, so if you are using where (@p = 'all' or col = @p) and ... you may still be comparing your values.

If performance is a concern, you can avoid this by using a short circuiting case, that only progresses to the actual data comparison if it is necessary:

where case when @SupervisorIDs = 'All' then 1
           else case when SuperVisorId = @SupervisorIDs then 1
                     else 0
                     end
           end = 1
  and CreatedDate > @StartDate

Answer 2

Assuming that you are using a dataset query to populate the supervisor parameter dropdown, then you can try this.

Create an additional hidden parameter of a boolean type. For this example, I'll call it @AllSupsSelected. Set the default value of the parameter to: =COUNT(Parameters!SupervisorIds.Label)=COUNT(Fields!SupervisorIdLabel.Value,"SupervisorDataset")

Replace the field and dataset names accordingly. If the dataset is returning non-distinct values, you may have to tinker further to get this working.

Now your query can read:

WHERE @AllSupsSelected OR SupervisorId IN (@SupervisorIds)

Answer 3

Make your where clause like below

WHERE (
        (SuperVisorId IN (@SupervisorIDs))
        OR (
            @SupervisorIDs = 0
            AND COLUMN_WITH_NULL IS NOT NULL
            )
        )
    AND CreatedDate > @StartDate

And pass 0 when selected "select all"

Answer 4

As an actual answer to your particular problem, set your multi-valued parameter dataset up similar to this to return all Supervisors as well as a value at the bottom of the list for No Supervisor:

select distinct SupervisorID as Value
               ,SupervisorName as Label
               ,1 as Sort
from Suppliers

union all

select <Uniquely identifiable value with the same data type as SupervisorID> as Value
      ,'No Supervisor' as Label
      ,2 as Sort

order by Sort
        ,Label

Then in your dataset set up your filtering similar to the below. I have structured it in this manner to avoid using the isnull function on your SupervisorID column, which will hurt the query performance:

select cols
from tables
where SupervisorID in(@SupervisorID)
   or (SupervisorID is null
       and <Unique value from above> in (@SupervisorID)
      )

Answer 5

which version of ssrs ? in 2016, you don't need to alter your query. when you click "select all" by default it pass all the values. so your query works good without changing anything.

thanks, SK