While working with time series charts in SQL Server Reporting Services, I ran across a particular need to show cumulative aggregation of income over time by fiscal year. Yup, that’s a mouthful eh? I initially thought great, I’ll just make the Y-axis value be the running total of the !Fields:Total.Value for the fiscal year series grouping using the RunningValue() function. This worked great with the exception of the current fiscal year.
So just to set the foundation for this scenario, we are using SSRS 2008. The chart is being created via Dundas Chart for Reporting Services, not the included Microsoft Chart control. Our Y value expression is:
=RunningValue(Fields!Total.Value,sum, "DundasChartControl1_DRG_PMTFiscalYear_agg")
As you can see in the chart to the left, for the current fiscal year we are currently in March. There are still a number of months left in the fiscal year and in our dataset, there are no values for those future months. Unfortunately, using the RunningValues() function means the Y value just remains where it was for March through the rest of the fiscal year. Not the behavior we want.
What we are looking for is that if there is no value we do not want to show a line or marker. In the Dundas Chart properties you have the option to set how empty data points should be handled. We ensured that this was set to the “zero” setting rather than “average”. Of course this did not resolve our issue because the Y value is not empty as it contains the running value. So how can we ensure that the empty datapoints are seen by the Dundas custom chart control in order to be properly handled? Custom code is the answer.
First we changed the Y value expression to simply be:
=Sum(Fields!Total.Value)
This sets each datapoint to the individual year/month income value rather than the RunningValue expression we were using. Next in the Advanced –> View Code area we found and selected the PostApplySeriesData event. Finally we added custom code that modifies the Y value for the chart to essentially be the running total.
' Parameter: chartObj - represents the chart object ' Parameter: series - the series that is currently being populated ' Parameter: codeParams - user defined code parameters Dim runningTotal As Double = 0 For Each point As DataPoint In series.Points point.YValues(0) = point.YValues(0) + runningTotal runningTotal = point.YValues(0) Next
Why does this work? It is actually quite simple.
At the time the series data is applied to the chart, the charting component is seeing the actual Y series datapoint value. For those future months, the datapoints are empty and the chart property for handling empty points works. After that is out of the way, we then come back and adjust the Y values for the existing datapoints. (Our empty datapoints aren’t there.)
Voila, our problem is fixed.
Popularity: 18% [?]
Have you ever looked at Style Chart at
If you are referring about InetSoft StyleChart, no I haven’t. Of course I’m not sure how that product would address the use of SQL Server Reporting Services nor the time series data issue either. However it is neat to see a free, AJAX charting component that could be used in other contexts.
How can I add normal columns to this chart?
Columns which are not affected by the running total code?
Thanks.
RE: PatrickPK’s post. This capability would be helpful to me as well. We need to show incremental values (e.g. weekly counts) as columns and cumulative counts (to date) with a cumulative line that increases (left to right).
We also need to contruct Pareto charts (i.e. columns representing the largest number of defects to the smallest number of defects (left to right) with a cumulative (aggregate) count line that increases (left to right) to show the total number of defects.
Thank you for your assistance.