Better way of repeating charts based on parameters

Ok, so this is not your everyday run-of-the-mill post which talks about how to repeat the same chart for each value selected in the report parameters. I know you have been listening to countless posts instructing how you can do the same with Lists in SSRS (in case you haven’t, this would be a nice place to start). But the subtle issue with just using a list is that they grow only downwards. Now this would mean that in the case of small charts, we would not be using the page efficiently as there would be lots of horizontal space left. So the ideal situation would be that the list grows horizontally and at the end of the page, it just breaks to the next line, just as we would expect our typewriter to behave. Actually speaking, this is not that hard as it looks to be. All it needs is a little tweak in your dataset query and some changes to your layout as I am going to show you in this post. To illustrate this, I am using the following scenario from AdventureWorks database:- a) Report parameter for subcategory b) The report should display charts (Products of the selected subcategory v/s Reseller Amount) for each of the subcategory selected Now that the requirement is crystal clear, follow the steps detailed below:- 1) Edit the dataset query to include a rank for each subcategory. For e.g., in my query, I have the Subcategory, Products and Reseller amount. Create a calculated member using the rank() which would return the rank of the subcategory. Query Designer - Add calculated member for rank 2) Once you have the rank, proceed to the Layout. Calculate the max no of charts that can be displayed horizontally. This is done by using the formula Integer part of (Page Width/Chart Width) In this example, I am taking 3 as the max number of charts displayed in one row. 3) Make the chart with Product on Category and measure on Data Value. Make the title of the chart as [First(Subcategory)] Making the Chart 4) Now create a matrix and add Subcategory on the rows and embed the above chart in the data section of the matrix. This will ensure that the chart will appear once for each subcategory appearing in the matrix. Make the column containing subcategory as hidden and borders of the matrix as none, so that only the chart is visible. Copy this 2 more times so that we have the layout as shown below in the figure Final Layout Notice that the column for subcategory has been made very small. The reason is because anyways they are hidden and we would not want to waste precious real estate (read it as the space in the layout) on hidden things. 5) Now right click on the first chart and select tablix properties. Tablix properties 6) Go to filters tab and click Add button. Enter =Fields!Prod_Rank.Value mod 3 for Expression. Operator should be = and Value should be 1. Also make sure that the data type is integer. Adding the filter This would make sure that only subcategories whose Rank mod 3 is 1 would be displayed in the first matrix. E.g. subcategories with rank 1,4,7,10, etc 7) Repeat step 5 and 6 for second matrix with Value=2 8) Repeat step 5 and 6 for third matrix with Value=0 9) You can preview the report and see your chart growing horizontally first and then vertically as required based on your selections. Final Result This technique would work beautifully on both the 2005 as well as the 2008 versions of SSRS.

Posted by SQLJason

8 comments

Pretty Cool. Nicely done Jason.

Katam Arunamma

Hi Jason,

I developed a similar report and but when viewed on a report viewer on an aspx page the charts gets duplicated. whats your thoughts on that.. ?

@Katam: Did you check if the report was displaying correctly when deployed directly to the Report Manager? If yes, I would expect it to be working in the same manner in report viewer too

Jose Barreto

Thank you. It was very useful for me. You gave me the idea for developing a similar approach with dynamics gauge charts associated with Kpis.

Hello Jason,

Even I developed a similar report and but when viewed on a report viewer the charts gets duplicated. Any idea?

Ideally, it should show the same in reportviewer as in BIDS (apart from some alignments). Make sure you are passing the right parameters. If you are still facing the issue, could you send a detailed summary of the issue to jason143@gmail.com?

Hi Jason,
I facing few problem, I has already email you. Can you please have a look on it.
Thanks in advance.

The article helped to solve my problem. Thank you !

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.