SSRS Reporting - Multiple Selections Defining Multiple Queries

Question

An SSRS dataset has two T-SQL queries. One query will only run if a certain parameter has two or more values selected. The other query will only run if the parameter has only one value selected. How can I let the dataset know whether one value or multiple values were selected from the parameter?

Answer 1

You can create an addional integer parameter called NumberOfValues, set it to be hidden and use an expression like the below in the Default Value:

=Parameter!YourMultipleValueParam.Value.Count

Then in the dataset you can use the NumberOfValues parameter to determine the number of values selected in YourMultipleValueParam parameter.

IF @NumberOfValues > 1 BEGIN ....

Answer 2

Good question. Here's one way I've found around this.

Step 1: In your dataset's properties, go to the "Parameters" tab. Add a parameter with the following expression, replacing MyParameterName with your parameter's name.

=join(Parameters!MyParameterName.Value,",")

Step 2: If you don't already have one, add a splitting function to your SQL Server functions. Simple google search would direct you to something like this: Split function equivalent in T-SQL?

Step 3: Select a count of parameters used in your SSRS report. Based on the result, do what is desired: (My splitting function is simply called Split)

IF (select count(*) from Devl.dbo.Split(@MyParameterName,',')) = 1
BEGIN
  /* Do your second query here, when one parameter is selected */
END
IF (select count(*) from Devl.dbo.Split(@MyParameterName,',')) > 1 
BEGIN
  /* Do your first query here, when more than one parameter is selected */
END

---

EDIT: Alternatives to steps 2 and 3 if you don't want to (or can't) create/utilize a split function.

Step 2: In your main query at the top, include the following (basically an in-line version of a split function)

declare @string varchar(max), @delimiter char(1)
declare @temptable table (items varchar(max))

set @string = @MyParameterName
set @delimiter = ','
declare @idx int, @slice varchar(max)
select @idx = 1     
if len(@String)<1 or @String is null  return     

while @idx!= 0     
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0     
        set @slice = left(@String,@idx - 1)     
    else     
        set @slice = @String     

    if(len(@slice)>0)
        insert into @temptable(Items) values(@slice)     

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break     
end

Step 3: Select a count of parameters used in your SSRS report. Based on the result, do what is desired: (My splitting function is simply called Split)

IF (select count(*) from @temptable) = 1
BEGIN
  /* Do your second query here, when one parameter is selected */
END
IF (select count(*) from @temptable) > 1 
BEGIN
  /* Do your first query here, when more than one parameter is selected */
END