Obtaining Current Fiscal Year from Hierarchy with MDX

Question

I'm building a report in Reporting Services 2005 based on a SSAS 2005 cube. The basic idea of the report is that they want to see sales for this fiscal year to date vs. last year sales year to date. It sounds simple, but being that this is my first "real" report based on SSAS, I'm having a hell of a time.

First, how would one calculate the current fiscal year, quarter, or month. I have a Fiscal Date Hierarchy with all that information in it, but I can't figure out how to say: "Based on today's date, find the current fiscal year, quarter, and month."

My second, but slightly smaller problem, is getting last years sales vs. this years sales. I have seen MANY examples on how to do this, but they all assume that you select the date manually. Since this is a report and will run pretty much on it's own, I need a way to insert the "current" fiscal year, quarter, and month into the PERIODSTODATE or PARALLELPERIOD functions to get what I want.

So, I'm begging for your help on this one. I have to have this report done by tomorrow monring and I'm beginning to freak out a bit. Thanks in advance.

Answer 1

You'll probably need to modify the SSRS MDX by hand to do this. It is possible to get SSAS to use "Today", it is usually done as something like this:

WITH 
MEMBER [Today]
 AS {
StrToMember("[Date].[Date Key].&[" + Format(now(), "yyyyMMdd") + "]")
}


SELECT
    [Measures].[Some Measure]
ON COLUMNS
FROM    
    [Cube]
WHERE
    {[Today]}

You'll need to change that to fit your own cube structure of course.

So, given that you have fiscal year, and you want to plug values in, modify the above to fit? Put together a string like I showed you that equates to the values you want to use. It sounds like you're OK after that?

Answer 2

You should be able to figure this out using various functions which can tell you 'where you are in the hierarchy'

e.g.

http://www.sqldev.org/sql-server-analysis-services/find-parent-of-current-day-10080.shtml