Creating YTD Calculation in SSRS

Recently I wrote on a method to create YTD calculations using MDX without making modification to the SSAS cube. However, there will often be scenarios where you need an YTD calculation and you are not using a cube as a data source. In these cases you are at the mercy of making an YTD calculation using SQL. For those that have done this before, you know how difficult that can be and how much code you have to write to get that working.
You can creating this using a combination of a Tablix, Matrix, or a Chart object. The required SSRS Expression needed is RUNNINGVALUE. The example of this expression is as follows…

= RunningValue(Fields!Total_Cost.Value,Sum,"Chart3")

Here is an example of using RunningValue to accumulate YTD values…

 

  Using this example, I specific the following formula in the Tablix for the YTD…

 

 The same could be done for SSRS Matrix and Chart objects as well.

YTD Calculation in Query Designer

One of the most common requested aggregations in MDX is YTD. There are many sources that highlight how to do this in the Cube development environment, but sometimes you don’t have access to the SQL Data Tools / BIDs.

I find using the native YTD calculation problematic when attempting to make YTD calculation outside of the Cube development environment. The YTD function can be extremely slow to process outside of the Cube development environment.  Also, if you’re like me many times you only have access to read the cube but not develop against it. You should then you use the Query Builder to create an YTD calculation.

Basically use the Calculated Member Builder in the Query Designer (whether in Report Builder or Visual Studio).

In my example, I use the following data to create the YTD calculation against the Date Hierarchy PostingPeriod.Fiscal and the Measure.LaborCost.

Aggregate

 (
 PeriodsToDate
 (
[Posting Period].[Fiscal].[Fiscal Year]
 , [Posting Period].[Fiscal].CurrentMember
 )
 ,[Measures].[Labor Cost]
 )

 This how looks in the Calculated Member in Query Designer…

  This is one of the most useful features of the Calculated Member tool – allowing you to create dynamic aggregations without cube authoring..