dbTalk Databases Forums  

Need to select all the months for the current year and prior year on the rows axis

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap


Discuss Need to select all the months for the current year and prior year on the rows axis in the microsoft.public.sqlserver.olap forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Steve Pantazis
 
Posts: n/a

Default 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]

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.