SSRS Dynamic Running Total

Question

I am trying to include a cumulative total column in a matrix on a report. This is the expression I am using at the moment:

=RunningValue(Ceiling((SUM(Fields!MINUTES.Value)/60)*4)/4,Sum,"RowGroup")

The problem is the total seems to reset on each day.

I have 3 groups on the report: Parent is Day of month, type, then sub type. I want it so that the sub types will accumulate across the days:

So 1st june/holiday/annual = 2 - cumulative = 2 .. 2nd june/holiday/annual = 1 - cumulative = 3

rowgroup in the expression above is the 3rd level sub type group in the matrix.

Whatever I try I cannot get it to total up over the days, like i said above it seems to not carry over to the next day.

Below is a sample dataset:

+------+------------+---------+----------+---------+
| User |    Date    |  Type   | Subtype  | Minutes |
+------+------------+---------+----------+---------+
|  158 | 13/02/2015 | Holiday | Annual   |      90 |
|  158 | 13/02/2015 | Meeting | Training |     300 |
|  158 | 13/02/2015 | Lunch   | Lunch    |      60 |
|  158 | 03/06/2015 | Holiday | Annual   |     120 |
|  158 | 03/06/2015 | Meeting | Meeting  |     285 |
|  158 | 04/06/2015 | Holiday | Sick     |     120 |
|  158 | 04/06/2015 | Holiday | Annual   |     200 |
+------+------------+---------+----------+---------+

My matrix column group is the user. The row groups are date, then type, then subtype.

I then have a total column outside the column grouping.

I'm now trying to add in to the report a cumulative total based on the type and sub-type columns, that sums up the minutes.

As an example (looking at the holiday, annual entries):

03/06/2015 - 120 minutes
13/02/2015 - 90 minutes = 210 minutes 
04/06/2015 - 200 minutes = 410 minutes.

KidCode KidCode · Accepted Answer · 2015-06-07T20:16:44

Taken from the MSDN page for RunningValue, adding a scope to your expression will have this effect.

The value for RunningValue resets to 0 for each new instance of the scope. If a group is specified, the running value is reset when the group expression changes. If a data region is specified, the running value is reset for each new instance of the data region. If a dataset is specified, the running value is not reset throughout the entire dataset.

To fix the issue, simply remove the scope:

=RunningValue(Ceiling((SUM(Fields!MINUTES.Value)/60)*4)/4,Sum)

This will have the total run across all days (As days are your top-most level). If this is incorrect could you perhaps give us an example of what the report is currently doing, and what you want it to do?

SSRS Dynamic Running Total

1 Answers