SSRS – Aggregate LAST YTD or LAST CHILD Value in an SSAS query

Trying to aggregate an aggregate is not possible in SSRS.  You can write custom code to capture the running total of values in a report, which works, but can have unexpected behaviour when groups in your report change. It also prevents you from using some other SSRS functionality, for example repeating column headers on a page.

Here is an example of a solution that works using math, which doesn’t prohibit you from using any functionality, since SSRS has no trouble aggregating math equations.  This solution has many applications in filtering out unwanted values in a row.  I will show you an example where only the most recent (LAST) value of YTD is wanted for each group of rows in a query.

1.  In my cube, the YTD calculation requires a member of the “Week Period To Date” hierarchy to be selected.

Aggregate
(
  PeriodsToDate
  (
     [TIME].[Week Period To Date].[Fiscal Year],
     [TIME].[Week Period To Date].CurrentMember
  ),
[Measures].[Sales Dollars]
)

2. In my report, the user can select a range of weeks.  So I include the Week ID from the “Week Period To Date” hierarchy in the query in order to return results for YTD.  Notice that the YTD Sales grow incrementally each week. 

image

I can sum the Sales Units in the report, and my report Groups will capture the correct value when using Sum.  But to aggregate YTD across groups I want to capture only the LAST value.  In my detail I can select the last value for the detail group, but now I want to SUM these LAST values for the parent group.  SSRS gives an error when specifying SUM (LAST(Sls Un YTD)).  IIF statements can’t be aggregated either, not even from a calculated field in the query.

3.  The solution is so simple, it only took me 3 days to figure out.

First, I built the query against the cube as above, including the weeks and the YTD field from the cube.  Then I added a Calculated Field to the Dataset.  Right click on the report Dataset, select Dataset Properties, Select Fields, click on the Add button and select Calculated Field.

image

4. I added a calculated field for the last week in the range selected by the user.  The calc pulls the integer value of the Week Number out of the parameter select by the user.  ***Note: this only works if the weeks in your cube are numbered uniquely and consecutively over time.  So the first week in your Date table should be week one (or whatever starting point you choose), and each week number thereafter should be greater than the last.

=Replace(Mid(Parameters!ToTIMEFiscalWeekIDName.Value,InStr(Parameters!ToTIMEFiscalWeekIDName.Value,”].&[“)+4,10),”]”,””)

5.  Now the magic.  I added a calculated field called Sls_Un_YTD_calc.  I always name my calculations whatever the altered measure name is plus “_calc”.  This makes it very easy to use/replace in fields and calculations in the report.  Here is the calculation.

=Floor(Fields!Fiscal_Week_ID.Value/Fields!ToWeek.Value)*Fields!Sls_Un_YTD.Value

Simple, right?  This basically says, whatever week you are looking at, if it’s the last week in the selected range then muliply the YTD by One otherwise multiply it by Zero.  By dividing the week in the row, but the maximum week in the range, you will either get One for the last week, or less than one for any other week.  Taking the Floor of this value makes anything less than one into zero.  Ta da!  You have successfully zeroed out any unwanted values from your dataset, and in your report you can sum away to your hearts content.

Leave a Reply

Your email address will not be published. Required fields are marked *