1
votes

Hello I have a simple dataset in SQL that counts meter readings... There are meter readings every day and I would like to SUM the count, display that in a column chart at a daily interval. What I would also like to do is display a cumulative count as a line so 4 orders Monday, 3 orders Tuesday, 5 orders Wednesday 4+3+5 etc. Apparently there is a running total property I read somewhere in SSRS? Does anyone know how to do this? I'm stumped! I currently have a SUM of count as my total value with category group of date read but it does not seem to be displaying properly. Instead of saying 5 orders on tuesday, it is just showing 1,1,1,1,1 etc...

SELECT     COUNT(readingId), dateRead
FROM         Readings
WHERE     (dateRead BETWEEN @StarDate AND @EndDate) AND (webcontactid IS NOT NULL) AND (meter = 1)
GROUP BY Readings.dateRead

as you can see the 19 March is missing as I have found that there is no data... Is there any way I can get it to display a 0 or something to show there is nothing?

1

1 Answers

3
votes

I created a bar chart, put dt as Category group, added a fake Series group called g1 (Group by "A"), and used two data fields, one with the expression

=Sum(Fields!NReadings.Value)

and one with the expression

=RunningValue(Fields!NReadings.Value, Sum, "g1")

and set this to appear as a line.

I got this on SSRS 2005, but I hope it helps for 2008. If anyone has a way to do this without creating a fake Series, then please let me know.

enter image description here