Show multiple line chart in SSRS

Question

I am developing report in SSRS where i have to show data over line chart. Line chart vary on parameter selected by user.

Now my issue is how to produce line chart based on parameter selected. Sometimes line chart may be 2 and sometime it may be 4, based on parameter selected by user.

Please advise how to do so.

Answer 1

In the following example, I used some basic population data, it returns

Country, Year and Population.

here is the SQL to generate the sample data

DECLARE @t TABLE(Country varchar(20), [Year] int, Population float)

INSERT INTO @t VALUES
('France', 2000, 59.0),
('France', 2005, 61.1),
('France', 2010, 62.9),
('France', 2015, 64.5),
('France', 2020, 65.3),
('Germany', 2000, 81.4),
('Germany', 2005, 81.6),
('Germany', 2010, 80.8),
('Germany', 2015, 81.8),
('Germany', 2020, 83.8),
('UK', 2000, 58.9),
('UK', 2005, 60.3),
('UK', 2010, 63.5),
('UK', 2015, 65.9),
('UK', 2020, 67.9)

SELECT * FROM @t
    WHERE Country = @Country

The final WHERE clause uses a parameter @Country which will get passed in from the report.

The approach

We will create a report that shows a single country's population, this will be used as a sub report. We will then create a master report that calls the sub report as many times as is required.

Create the sub report

I created a new report, added a dataset using the query above and called the dataset dsPopulation. This automatically generates the parameter @Country. There is no need to do anything with this parameter as the user will not use it directly.

I then added a line chart to the report and dragged Population to the Values section, Year to the Category Groups section and Country to the Series Groups section as shown below.

Save the report, call it something like _sub_PopulationReport

.

Now create the master report

Create a new report.

Add a new dataset 'dsCountries' that contains a list of distinct countries. You'll have to decide how best to do this. You might be able to do something like SELECT DISTINCT Country FROM myDataTable ORDER BY Country. For this example I will hardcode the list.

The datatset query looks like this,

DECLARE @t TABLE(Country varchar(20))
INSERT INTO @t VALUES
('France'), ('Germany'), ('UK')

SELECT * FROM @t
    WHERE Country IN (@Country)

This will automatically create a Country parameter. Edit the parameter and set the available values list to a list of countries. You can do this either by pointing it to a query of just adding them by hand.

Set the parameter to be Multi-Value

Now add a Table to the report design.

Next, right-click on any of the row or column headers (any one will work) and choose "Tablix Properties". Set the "Dataset Name" option to the dataset you just created (dsCountries).

Next, remove the header row and two of the columns so you are left with one 'cell'.

Make the table wide enough to fit your chart in (the height it not important).

Inside the remaining cell, right-click and choose "Insert => Subreport"

Right-click the <Subreport> placeholder and choose "Subreport properties". Under the option "Use this report as a subreport" choose the report we created earlier (_sub_PopulationReport).

Click the "Parameters" tab on the left then "Add"

Under "Name" choose or type Country and under "Value" choose or type [Country]

That's it!

The report design should look something like this... (note my subreport name is different than above, ignore this.)

If I run the report for and select 1 country I get this..

If I select more countries (all of them in this case) I get this...