Solved

SSRS Dataset with multi value Parameters in query - ignore where clause if all options selected

Posted on 2014-03-26
5
4,626 Views
Last Modified: 2014-05-11
I'm creating an SSRS report with a multi-value parameter "FilterColours" set for the size of the product.   The available sizes are SMALL, MEDIIUM and LARGE.  I am expecting to create my query as follows:

SELECT * FROM PRODUCT
WHERE Size IN (@FilterColours)

For performance reasons I would like to ignore the parameters if all three available sizes are selected.  Is there a way to do this?
0
Comment
Question by:canuckconsulting
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39958536
I would add an extra parameter value "select all". Then in your query it's a simple IF statement.

IF @FiltetColours = 'Select all'
Begin

Select *
From product
End

Else
Begin

Select *
From product
Where size in (@FilterColours)
End

You probably also need to use some kind of split function to split your parameter in separate values recognizable for the IN clause.
0
 

Author Comment

by:canuckconsulting
ID: 39958831
Ah, ok so I can put logic statements into the query?  I didn't realise that.  Beauty.  Is that if statement a SSRS thing or a TSQL supported?  Can I reference expressions in there or just parameters?

I didn't realise the multi-valued parameter wouldn't just slot into the IN statement.  Thanks for the heads up on that.
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39958863
The example I gave is TSql. You want to put that in the dataset query. So it only works for your parameters.

I don't have the split function handy on my phone here, will check if I can add it to this question in a bit from my laptop.
0
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 39959025
Here's the code for the split function:

CREATE FUNCTION [dbo].[fn_Split] (@sList AS VARCHAR(MAX))
RETURNS  @retTable TABLE(Val VARCHAR(MAX))  AS  

BEGIN 

 IF @sList IS NULL RETURN

 --preparing the input list, removing IN keyword and parenthesis
 SET @sList = REPLACE(@sList, 'IN(','')
 SET @sList = REPLACE(@sList, 'IN (','')
 SET @sList = REPLACE(@sList, '(','')
 SET @sList = REPLACE(@sList, ')','')
-- SET @sList = LTRIM(RTRIM(@sList))
 SET @sList = @sList


 --scrolling thru values in @sList and populating temporary table
 DECLARE @Index INT, @Delimiter CHAR(1)
 DECLARE @Result VARCHAR(MAX)


 SET @Delimiter = ','

 WHILE @sList <> ''
 BEGIN
  SET @Index = CHARINDEX(@Delimiter, @sList)
  IF @Index <> 0
  BEGIN
   SET @Result = LEFT(@sList, @Index - 1)
   SET @sList = SUBSTRING(@sList, @Index + 1, LEN(@sList))
  END
  ELSE
  BEGIN
   SET @Result = @sList
   SET @sList = ''
  END

  INSERT @retTable SELECT LTRIM(RTRIM(REPLACE(@Result,'''''','''')))
 END

 RETURN 

END

GO

Open in new window


Run this on your main database in the SQL Management studio. It will create a table function which takes your parameter as input, and splits it into a list of values accepted by an IN clause.
For your report, the syntax would then become:

IF @FiltetColours = 'Select all'
Begin

Select *
From product
End

Else
Begin

Select * 
From product
Where size in (select val from fn_split(@FilterColours))
End

Open in new window

0
 

Author Closing Comment

by:canuckconsulting
ID: 40057395
Thanks!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to increase the row limit in Jasper Server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question