Wednesday, August 3, 2011

How to combine a bar chart with a line graph and running totals in SSRS.

In of my previous blogs a wrote about dashboards design rules and the do's and don'ts. Easy readable charts are essential in the success of your reports. When building reports you have a lot of different chart types you can choose for your reports. By default you can use a bar chart of a line chart. When comparing figures for instance current year with prevoius year of current year with the budget. You can use a standard bar chart like this one:

However the graph is a little bit busy to read. Using the combination of a bar for the current year and line graph for the comparing previous year will make the chart easier to read. Like this:

How to do this:
  • Open the report in Business Intelligent Development Studio (BIDS).
  • Double click on the bars in the chart.

  • Select the Bar of previous year in the Chart data window. In my example RevenueYTD_1.
  • Change chart type
  • Select the line type you want. That's all you need to do.
How to create running totals?
  • Double click on the bar in the chart.
  • Select the Bar of current year in the Chart data window.
  • Series properties.
  • Press on the expression button for the value field.
  • Use next expression: =Runningvalue(Sum(Fields!Previous_Year.Value),SUM,"RevenueYTD_YTDPY")
  • RevenueYTD_YTDPY is the name of my dataset. The column name used is 'Previous_Year'
  • Do the same for the line graph.
  • That's all you have to do.
Enjoy it to build nice and easy readable reports.


Zac Chernik said...

Hi....I have a question for your regarding running would you handle a dynamic range of instead of months 1 though 12 I wanted to display only 3, 4 and 5 but still have the correct cumulative totals for month 3 which would also include the data from months 1 and 2 in my Running Total?

sochau said...

"RevenueYTD_YTDPY" can be nothing