Add time functions like YTD to SSAS using the “Add business intelligence” wizard

I’ve been using SSAS in combination with Reporting Services for a few years now but i have always found it very cumbersome to deal with YTD and other time intelligence functions in combination with SSRS. A few days ago i read this whitepaper: Translating Cognos PowerPlay Transformer Relative Date Functionality into SQL Server Analysis Services. In this paper they use the “Add business intelligence” wizard to add time intelligent members to the time dimension, I’ve seen the button but never tested it out.

This whitepaper made me do some testing. To do so i’ve created a cube based on the adventureworks database. I’ve created 2 dimension, and used the sales amount in the fact table.

In the date dimension I’ve created a hierarchy:

Now for the magic bit:

We run the Business Intelligence wizard to add a time intelligence functions

We can pick multiple calculations to be created

You have to make sure you pick the date hierarchy so the date calculation can determine the relationship between the different levels.

Last step is picking the measure we want to use the functions with

Now we can click finish, the time intelligent functions will be implemented. This is done by adding a calculated member to the time dimension as we can see here:

This will add a attribute with 2 members to my date dimension, one which shows the measure value for the current month selected and member that shows the year to date to the month selected:

we now can use this member in a query, in for example a SSRS report:

As you can see we now have the sales amount of the selected month from the selected month and the sales amount of the current month year to date. Putting this in a tablix makes it into the following report:

This is great stuff, makes YTD very easy to implement !

I don’t know how i could have missed this great wizard for all this time. I’ll use it for sure very soon at the next project.