Accumilative graph plotting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Accumilative graph plotting

Author

Topic

lappy
Starting Member

6 Posts

Posted - 2009-03-04 : 11:44:33

This could be tricky to explain...

I am trying to create a chart that shows date on the x and amount on the y. The data to plot is coming from a dataset that has only the date (yyyy mm) and the total amount of entries created in the table in that month. When a graph is plotted with this data is produces a nice bar chart that shows the creations by month, as one would expect.

However, what I want is to show the total amount of entries in the table by that month. This means that for month a there would be a entries plotted, and for month b there would be a + b entries plotted, etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 13:07:17

then rewrite query to return running count rather than absolute count and plot by it.

something like


SELECT datefield as date,t1.MonthCount
FROM table t
CROSS APPLY (SELECT COUNT(*) AS MonthCount
             FROM Table
             WHERE date<=t.date
             )t1

i assume the date valued field is of datetime or int type

lappy
Starting Member

6 Posts

Posted - 2009-03-05 : 05:04:59

Thanks for the suggestion, it looks like it would work. Unfortunately the SQL was slightly more complicated than that. It was a couple of DATEPARTs used to get the month by year then converted to VARCHAR, with the count for each month returned, which led into using GROUP BY and the restrictions that come with it. This needed to be done for other reasons I won't go into. Next time I'll probably break this down so that something that simple could be used instead though. :)

However, a colleague returned to the office today and he instantly gave me the solution. What I have done is within the properties of the chart used the date as the category field, the total as the data field, then created a series field that =1. In the value property I used the RunningValue function as

=RunningValue(total, Sum, "<spoofed series field>")

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources