StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGetting YTD figure in SSRS report with filtered column group on year/month
text
Body
copied!<p>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:</p> <pre><code>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 </code></pre> <p>The problem I'm seeing is that the <em>RunningValue</em> 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).</p> <p>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:</p> <pre><code>=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")) </code></pre> <p>Any help much appreciated</p> <p><strong>Update:</strong> 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:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload