Getting YTD figure in SSRS report with filtered column group on year/month

Question

I have a Reporting Services report which shows a bunch of KPI scores. Each KPI has it's own Row on the report and for each row we show the SUM or AVG (depending on KPI type) score for last 3 years, and then a SUM, AVG or (ideally) total YTD figure per month in the current year:

KPI                     2009 2010 2011  Jan Feb Mar Apr May etc.
Bagels Eaten (Total)    100   90    70  10  20  9   13  14
Sandwiches (Cumm. YTD)  90    75    86  13  23  46  65  76

The problem I'm seeing is that the RunningValue function appears to be working across rows, instead of within the filtered column group and current row that makes up the Jan, Feb, Mar columns (monthly data, grouped by year and month, filtered on current year). So the output is not as above, instead its a fairly random figure based on all the values in the rows (I'm sure it's not random it just looks like it is).

The Column Group is called "MonthNum" and the Expression for the cell value decides which aggregation to apply based on an "AggregationType" value in the source data:

=Switch(Fields!AggregationType.Value="SUM", Sum(Fields!ScoreValue.Value), 
       Fields!AggregationType.Value="AVG", Avg(Fields!ScoreValue.Value), 
       Fields!AggregationType.Value="YTD", 
       RunningValue(Fields!ScoreValue.Value,Sum,"MonthNum"))

Any help much appreciated

Update: Here is an example few rows (scores per month, per kpi) of the data set being used (formatting is a bit tricky!). Items with aggragation type 'YTD' would have the cummulative YTD expression applied, everything else would be SUM for the current month:

KpiID   Title   KPIOwner    ScoreValue  YearNum MonthNum    ReportingGroupTitle ScoreType   DisplayOrder    DisplayPrecision    UnitOfMeasure   AggregationType
5   Donuts Served   Donut Manager   35  2007    1   Catering Services   Actual  10020   0   Number  YTD
5   Donuts Served   Donut Manager   42  2007    2   Catering Services   Actual  10020   0   Number  YTD
5   Donuts Served   Donut Manager   86  2007    3   Catering Services   Actual  10020   0   Number  YTD
5   Donuts Served   Donut Manager   14  2007    4   Catering Services   Actual  10020   0   Number  YTD
6   Donuts Cooked   Donut Manager   45  2007    1   Catering Services   Actual  10020   0   Number  SUM
6   Donuts Cooked   Donut Manager   48  2007    2   Catering Services   Actual  10020   0   Number  SUM
6   Donuts Cooked   Donut Manager   93  2007    3   Catering Services   Actual  10020   0   Number  SUM
6   Donuts Cooked   Donut Manager   32  2007    4   Catering Services   Actual  10020   0   Number  SUM
6   Donuts Cooked   Donut Manager   18  2007    5   Catering Services   Actual  10020   0   Number  SUM

Joao Leal Joao Leal · Accepted Answer · 2012-10-02T17:49:15

From what I understand from your question this is what I would do:

Assuming that when the aggregation type YTD returns the acumulated totals until (and including) the MonthNum.

Create a Matrix with two a groups on KpiID and a child group on AggregationType, and two column groups (adjacent) one on the year and one on the month (on the year group, create a filter so that you don't include the current year and on the month group exclude the values where the year is not the current one).

Then you can use the following expression, for both the year and the month group (the last will give you the YTD value for the last month in each year)

=Switch(Fields!AggregationType.Value="SUM", Sum(Fields!ScoreValue.Value), 
       Fields!AggregationType.Value="AVG", Avg(Fields!ScoreValue.Value), 
       Fields!AggregationType.Value="YTD", Last(Fields!ScoreValue.Value))

Getting YTD figure in SSRS report with filtered column group on year/month

1 Answers