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]

MDX in Analysis Server 2000 does not support parameters
but you can do this programmatically by generating the
MDX dynamically.

Abhinav Kumar
akumar (AT) itmag (DOT) com.au

Using Reporting Services you'll have to set up parameters for your time
periods you want. Use these parameter values in your dynamic MDX statement.

You can also write some custom code to get the current year and from that
the previous year also. See vb.net date functions.


"Abhinav Kumar" <akumar (AT) itmag (DOT) com.au> wrote

Thanks for the info. This is what I did as a solution, which worked.

I created a parameter called CurrentYear and set its default value to
the expression =Year(Today).

I then converted the MDX query to an expression and substituted
"(YTD([TimeDimension].[2004].[Quarter 4].[December])" with
"(YTD([TimeDimension].[" & Parameters!CurrentYear.Value & "].[Quarter
4].[December])"

The whole MDX query appears below.

="SELECT { [Measures].[Amt Sold zero-filled], [Measures].[Num Sold
zero-filled] } ON COLUMNS, { (YTD([TimeDimension].[" &
Parameters!CurrentYear.Value & "].[Quarter 4].[December]) *
[ProdHierarchy].[Level 04].ALLMEMBERS * [Prod
Description].[Description].ALLMEMBERS ) } DIMENSION PROPERTIES
MEMBER_CAPTION ON ROWS FROM [Sales] WHERE [INum].[INum].[111111] CELL
PROPERTIES VALUE"

"mike" <mike_no_spam (AT) no_spam (DOT) com> wrote