Wednesday, April 3, 2013

SSRS - How to Change Chart Type Dynamically

This article shows you how to change an SSRS chart type dynamically. In order to change the SSRS chart type dynamically there are a few steps. SSRS does not support this natively, but there are a few things you can do to dynamically change a chart type.

The first step to dynamically change your SSRS chart type is to add a parameter called "chartType" and give it a default value of "Bar".

Next we add a bar chart and style it the way we want:

Next we add a table that will help us to dynamically hide our charts. Once you have added your table, delete all of the columns except for the first one, and delete the bottom row so all you have left is just one row / column table. After you do this, resize the table box so it is the desired chart size.

The next step in creating a dynamically changing chart type is to cut and paste our chart inside of the table

The next step is to add rows to our table (we will have six, so add five rows to the table)

Now copy and paste your chart into the rows below and then resize each so they are the exact same size. How you can do this is by clicking on your chart and opening properties (F4) and going down to the Position area and copy the size of your chart and pasting that value in all of your rows.

Here is what your report will look like:

The next step is to change the chart type of each one (You can also have different charts in each table row if you would like)

So now that we have this table with several different chart types, we are going to do some conditional hiding on our rows. Right click on the first row and click Row Visibility.... Click Show or hide based on an expression. Open the function (fx button) and insert this expression:
=iif(Parameters!chartType.Value = "Bar", false, true). 
Click OK and OK to apply the visibility expression. This is a critical step in changing a chart type dynamically.

Repeat this step for every chart, changing the chartType.Value="Bar" to each chart type value (Line, Stacked Column, Stacked Bar, etc). What this will do is whenever we change our parameter to a different value, it will hide every other row and only show the row we want making it look like it is a dynamically changing chart type!

Steps to dynamically change an SSRS chart type (Condensed version):
1. Add a parameter called chartType and give it a default value of "Bar"
2. Add a parameter called DrillAction and give it a default value of "swap"
3. Add a bar chart with the data that you would like displayed and style it how you would like
4. Add a table
5. Delete all the columns except for the first
6. Add six rows to the table
7. Cut and paste the Bar chart into the first row
8. Paste the Bar chart into the second, third, fourth, fifth, and sixth row
9. Change the size of each row to be: 5.5in, 3.74261in
10. Right click on the second chart and click Change Chart type and choose another chart type (Line, scatter, etc)
11. Repeat for each chart so each chart is a different Chart type
12. Right click on the first row and click Row Visibility and click Show or Hide based on an expression and enter the expression:
=iif(Parameters!chartType.Value = "Bar", false, true)
13. Repeat for each row, changing the value to the chart type ("Line", "Scatter", etc)

1 comment:

  1. This is by far the best way I've seen to manage multiple charts in SSRS.
    It also simplifies the sizing and by inserting a row above, I was able to use this for the chart title.
    So now I have a title that'll accept HTML tags for formatting. Unlike the default chart titles.
    Thank you.