Need to select all the months for the current year and prior year on the rows axis -
08-26-2004
, 02:48 PM
I'm going crazy. The MDX query below gives me all the months and
their corresponding sales for a two-year period, which is what I want.
However, I have no idea how to substitute the hard-coded year values
with the current year for [2004] and the prior year for [2003]. Is
there some kind of variable I can use to set "CurrentYear =
Year(Current_Timestamp)" and "PriorYear = Year(Current_Timestamp) -1"
or something?
I need to build a report with Reporting Services that will display
this data. If I leave the hard-coded dates, the report will be
invalid next year, which is why the current and prior years need to be
variables rather than hard-coded values.
My TimeDimension levels are set up as Year, Quarter, Month, and Day.
SELECT { [Measures].[Amt Sold], [Measures].[Num Sold] } ON COLUMNS,
{YTD([TimeDimension].[2003].[Quarter
4].[December]),YTD([TimeDimension].[2004].[Quarter 4].[December]) }
DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS
FROM [Sales] |