RE: Using MDX to retrieve sales by month for a 2 year period of time

#1

There is a really neat way of doing this with MDX - but it only works under
certain conditions, namely that your Time dimension only contains leaf-level
members up to and including today. [N.B. This prohibits you from having data
in your cube for dates in the future (e.g. budgetry data).]

An easy way to do this is build a view similar to the following:
CREATE VIEW vTime AS
SELECT * FROM <time_dim_table>
WHERE <date_natural_key> <= GETDATE()
and base your Time dimension on this view. You will need to incrementally
update your time dimension every day.

Your MDX statement will become similar to the following:
SELECT { [Measures].[Amt Sold], [Measures].[Num Sold] } ON COLUMNS,
{YTD([TimeDimension].LastChild.[December]),YTD([TimeDimension].LastChild.PrevMember.[December]) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS
FROM [Sales]

If your Time dimension cannot be built like this then don't worry...there is
more than 1 way to skin this one.

Let me know.

cheers
Jamie

"BobT" wrote:

Quote:

I'm building a report in Reporting Services that will compare sales over a 2
year period of time. I need to retrieve 2 years of data, grouped by month.
I have this in my Sales OLAP cube, which is fine, except that I'm having
trouble getting the MDX query right.

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]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: Using MDX to retrieve sales by month for a 2 year period of time

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

RE: Using MDX to retrieve sales by month for a 2 year period of time - 09-13-2004 , 05:02 PM