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.

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

Answer 3

I know it is a "bit" too late but for people reaching this question page this might help:

IIF(Month(Now()) > MonthOffSetNumber, Year(Now()) + 1,Year(Now())) 

This is for getting the current fiscal year. This will be applied in something like this:

SET CurrentFiscalYear AS 
(
    StrToSet("[Dim Date Name].[Fiscal Year].&[" + Format(IIF(Month(Now()) > MonthOffSetNumber, Year(Now()) + 1,Year(Now()))) + "]")
)

This will help for later cross join in the query.

Answer 4

WE find an easy way to calaculate fiscal period to date date in mdx by using parameters. Imagine that we have BeginDate (01/04/2014) and EndDate (31/03/2015). Here are the formulars. Click on Parameter "beginDate" in Report Data - right click parameters - Specify values - add expression value:

=DATEADD
("M"
,IIF(Month(Today())<4
 ,-Month(Today())-8
 ,-Month(Today())+4
 )
,DATEADD("D",0-DATEPART("D",Today())+1,Today()))

Do the same for the second parameter "EndDate" and set the Defualt values - Specify values and add expression value:

=DATEADD("D",-1,DATEADD("M",12,DATEADD
("M"
,IIF(Month(Today())<4
 ,-Month(Today())-8
 ,-Month(Today())+4
 )
,DATEADD("D",0-DATEPART("D",Today())+1,Today()))))

Now your ssrs report will have the fiscal period as default period. Moise Kabongo