SSRS Report using SSAS cube - time calculations

Question

I am looking for optimized solution for the below problem -

I have a cube which contains Date dimension which has one hierarchy month->quarter->year.

I have created a cube, all good. Now my ssrs report requires all my dimension customer sales attributes along with the calculations like last month profit, current month profit, last 6 months profit (listing every month), last month transactions, last year profit vs current year profit.

As i have created a hierarchy all my calucations for every month and every year and every quarter already pre-calculated in the CUBE. Now to retrieve the above listed calculations, what is the best way to do these time calculations? Where to put these calculations in CUBE or in the SSRS DATASET?

Could you please suggest some good approach and how to do it?

Answer 1

Although it's certainly possible - if you're comfortable with MDX - to write your query to get all these calculations into your dataset, it's usually better for reusablity across many reports to put these types of calculations in the cube. That way, other tools can also use the time calculations if you decide to use Excel or a 3rd party application.

You can use the Business Intelligence Wizard from the Calculations tab to do this - here's an article for SSAS 2005 that's still applicable to later versions: http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

And here's another approach to do something similar: http://www.bidn.com/articles/mdx-and-dmx/169/mdx-time-calculations-done-the-right-way

UPDATE: For named sets to handle date ranges like the last 6 months from now, see this article: http://my.opera.com/duncans/blog/using-custom-sets-in-analysis-services-to-deliver-special-date-ranges-to-end-use

Answer 2

not sure if I get you, but the problem is that you need to get dynamic values and you have everything hard-coded?

You can get the previous member in a hierarchy with the prevmemebr function:

([Date].[Calendar].PrevMember, <your measure here>)

where date is the dimension and calendar is the hierarchy. So if you are browsing by month, it will get the last months data for example

Answer 3

You have to calculate last month profit, current month profit, last 6 months profit as measures in the cube 'calculations' using mdx query. I recently did this for my 'revenue comparison report' where I calculatedlast month's revenue, last2monthsrevenue and priorquarterrevenue and used them. It worked!