Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5026
  • Last Modified:

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

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
canuckconsulting
Asked:
canuckconsulting
  • 3
  • 2
1 Solution
 
Koen Van WielinkIT ConsultantCommented:
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
 
canuckconsultingAuthor Commented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
 
canuckconsultingAuthor Commented:
Thanks!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now