CODEWITHSUNDEEP
reporting-servicesparametersmultivalue
ajoe
ajoe

Reputation: 1

How do I make the multi value parameter work in SSRS?

I am trying to have multiple values in a parameter (qtr_name) in SSRS as shown in this picture:

enter image description here

In the query, I am concatenating 2 parameters (year and quarter name) and trying to show the values of the parameters.

--declare @year varchar(15)='2021-2022'
--declare @qtrname  varchar(5)='Q1'

declare @period varchar(25)
set @period =ltrim(rtrim(@year))+' '+ltrim(rtrim(@qtrname));

select @period

But when I preview, I get the error shown in the picture.

When I set the parameter property to 'not have multiple values' for the quarter, this report works.

So basically, when I choose year = 2021-2022, and quarter = Q1 & Q2, I would like to see the result as given below.

2021-2022 Q1, 
2021-2022 Q2

Is this possible?

Please let me know if you need more clarification.

Thank you!

Upvotes: 0

Views: 532

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

You have to process the selected qtrname values in some way depending on your needs.

You could join them in SSRS and pass that result to your SQL statement, but then you would have to split the string back out again in order to process it.

As you only need a very small set of data, you can take advantage of an SSRS 'feature' that will automatically replace multivalue parameter names that are used in an IN() with the split out values. Typically you would use this to select from a table, so we can just create a table variable with all our available values in.

Change your dataset query to this ...

DECLARE @q TABLE (qtr varchar(2))
INSERT INTO @q VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')

SELECT 
    CONCAT(@year, ' ', qtr) as Result
    FROM @q 
    WHERE qtr IN (@qtrname)

This assumes that the parameter Values and Labels are both Q1 etc. Here's my basic report design with the parameter values visible. As you can see both the label and value are the same.

I added a simple table to show the results.

enter image description here

When I execute the report and select 2 quarters I get the following results.

enter image description here

Upvotes: 1

Related Questions