Passing multiple values to subreport parameter - SSRS

Question

I have a report that has 4 parameters:

Year - accepts a single value for a year - ex: 2020
Carrier Group - allows the user to select a single carrier grouping
Segment - allows the end user to select one or multiple business segments
Loss Cause - allows the end user to select one or multiple loss causes

And when I execute this report:

The report generates as expected - and I have the SSRS report set up so it creates a new tab in Excel when exported to separate the monthly totals vs the cumulative totals.

But the end users would like to be able to execute this report for multiple years to compare - so I have created a parent or main report - then I created a tablix and added the sub-report to be called - and created a row group based on the Year(s) selected.

My subreport parameters are as follows:

What I am ultimately hoping to accomplish is to pass in a single year plus the carrier group, segments and loss causes that were selected - and run the subreport for each of the years the user may select.

For example, if the user selects 2016, 2017, 2018 - the sub-report would need to be run 3 times to generate the totals for each of those years using the same parameters for carrier group, segment and loss cause.

I'm not sure what is happening but with the Year parameter as it: =Parameters!Year.Value(0) - the report looks like it keeps generating one year over and over:

I also tried using =JOIN(Parameters!Year.Value,",") but that did not seem to help either.

Anyone have experience on how to solve this type of issue? Thanks,

=Parameters!Year.Value(0) would mean the first value that was selected for the parameter Year. You want =Parameters!Year.Value within the grouping. — Larnu
I appreciate the reply but after making that update - running the report for 2 years results in the main report only showing data for the first year selected - but creating 14 worksheets each for the monthly and cumulative totals - so 28 total - instead of just the two that are required for each year. — MISNole
I feel like we're missing a lot of information here; as in you are giving us but a small part of the true picture. The assumption was that you were filtering already to the years you wanted; clearly you are not. So you need to add those filters. — Larnu

Alan Schofield Alan Schofield · Accepted Answer · 2020-10-27T00:11:56

The easiest way to do this is to add a dataset to your main report that returns one row per year.

If you have a dates table or similar in your database then you could do something like

SELECT DISTINCT Year(myDateColumn) as [myYear]
FROM myDatesTable
WHERE Year(myDateColumn) IN (@Year)

If you don't have a date table then (other than suggesting you add one...) you could create one on the fly with something like

SELECT * FROM (
    SELECT top 20 
        ROW_NUMBER() OVER(ORDER BY name) + 2000 as [myYear]
        FROM sysobjects) o 
    WHERE myYear IN (@Year)

(adjust the top 20 and +2000 as required to get a range of years that covers all your potential data)

Now set the dataset property of the tablix in your main report to point to this new dataset.

In your subreport object's parameters, set the value for the Year parameter to the [myYear] field in your dataset by selecting it from the drop down or using =Fields!myYear.Value as the expression.

Now that the tablix is bound to the dataset, it will create one row for each record returned from the "dates" dataset, each row will have a different year which is passed to the subreport, so the subreport is called once for each row/year.

Passing multiple values to subreport parameter - SSRS

1 Answers