Obtaining Current Fiscal Year from Hierarchy with MDX
- by Robert Iver
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.
