RunningTotal (for the birds)

How do I solve this RDL chart problem?

A bird watcher records the date each time he spots a new type of bird ('NewBird'). Likewise, he records the date of each time he spots a previously known type of bird ('OldBird'). Like this:

SpotDate Event
01/01/07 NewBird
01/01/07 NewBird
01/01/07 NewBird
01/02/07 OldBird
01/02/07 NewBird
01/02/07 OldBird
01/06/07 NewBird
01/06/07 OldBird
01/06/07 OldBird

I want to make an RDL chart that shows a running total of 'NewBird' to 'OldBird' along the date timeline. I am using SQL suite 2K5. I figure my "Category" field should be 'SpotDate', my series field should be 'Event,' and my "Data" field should be some kind of "=RunningTotal(Event, ?)". Is this correct?

And for that matter, how do I convince the RDL chart to include the elapsed time (the missing days between Jan 1, and Jan 6 in the plot)?

Ok, it seems the answer (mostly) to my own question is this:

1) Create a select statement that provides the daily total (e.g. called "Tally") grouped by SpotDate and Event.

2) In the chart, add the SpotDate to the category, the Event to the series, and the "Tally" as the value

3) Edit the "Tally" properties so that its 'value' field shows '=RunningValue(Fields!Tally.Value, Sum, "chart1_SeriesGroup1")

Thats it! The name "chart1_SeriesGroup1" was chosen simply to match the auto-generated name Visual Studio gave to the "Event" series (you can find or edit this name by selecting the 'data' tab on chart properties. One final piece of advice: do not "add" a new dataset field that contains a "RunningValue" calculated value...it causes Visual Studio to crash every time you view a chart (whether or not you used it on the chart).

So now my only remaining issue, is the need to convince the RDL chart to include the elapsed time (the missing days between Jan 1, and Jan 6 in the plot) that I showed as example data earlier.

|||

> need to convince the RDL chart to include the elapsed time (the missing days between Jan 1, and Jan 6 in the plot)

Set the x-axis to use scalar mode and make sure the category grouping expression returns DateTime objects (not data values as strings).

For more information, you may want to read the following section of a technical article on SSRS charts: http://msdn2.microsoft.com/en-us/library/aa964128.aspx#moressrscharts_topic3

-- Robert