SSRS report each page based on values on parameter

0
votes

I have SSRS report having a chart based on multi-valued parameter. Now, I need to repeat these chart on each page.

Example - If I select year 2016, 2017 then Page1 would show a chart for 2016 and 2017. Page 2 would show a chart for 2016 and Page 3 would show a chart for 2011.

How can I do that using SSRS?

1
reporting-servicesssrs-2012

1 Answers

0
votes

One way to tackle this is would be to: 1) Add a dataset that uses the CTE way to create an on the fly numbers table and limit the table by your years selected. So now you have a dataset that returns a row per year

2) Drop in a list, bind the list to that dataset and put your chart into that list and pass the year as a parameter.

Here's the CTE where @YearVal is ties to the multivalue parameter for years:

    WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536 
    ,Tally (yearVal) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv4)
SELECT TOP (5000) yearVal
FROM Tally
WHERE yearVal in (@YearVal)
ORDER BY YearVal;