Re: Impossible MDX?

#1

You could put the calcs in a random dimension (not measures).

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote

Quote:

Using foodmart and the MDX below I get the following Unit Sales figures
for 1997 Q2 (so, last period=1997 Q1):

Current Period Last Period % Change
Q2 62,610 66,291 -5.55%

I really want several measures in the output, not just Unit Sales, like:
Current Period Last Period
% Change
Q2 Unit Sales 62,610
66,291 -5.55%
Sales Count #### ####
##%
Profit ### ###
##%

(where ### is the actual amounts). I know this means I'm trying to put
measures on both axis which you probably can't do. Can anybody help?

Thanks.

Quote:

WITH MEMBER [Measures].[Current Period] As
'[Measures].[Unit Sales]', solve_order=1, FORMAT_STRING = '###,###'
member [Measures].[Last Period] As '(0)+(Measures.[Unit Sales],
[Time].[1997].[Q2].lag(1))', solve_order=2, FORMAT_STRING = '###,###'
MEMBER [Measures].[% Change] AS '([Measures].[Current
Period]-[Measures].[Last Period])/[Last Period]',
FORMAT_STRING = '#.00%' SELECT {[Measures].[Current
Period],[Measures].[Last Period],[Measures].[% Change]}
on columns, {[Time].[1997].[Q2]} on rows FROM Sales

#2

Here you go Andrea. (no data in 1998, the assumed cur yr.) Solve Order is
so that it will play well with calc measures.

WITH
MEMBER [Time].[ThisYR] AS ' Tail(Year.Members,1).Item(0).Item(0) '
MEMBER [Time].[LastYR] AS '
Tail(Year.Members,1).Item(0).Item(0).PrevMember '
MEMBER [Time].[%Change] AS
'([Time].[ThisYR]-[Time].[LastYR]) / Time.[LastYR] ',
Format_String = '0%', SOLVE_ORDER = 2
SELECT
{Time.[ThisYR], Time.[LastYr], [Time].[%Change]} on columns,
Measures.Members on rows
FROM Sales

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote

Quote:

I tried that and I can make it work for one measure (MDX is below), but I
can't figure out how to do it for more than one. I don't see how to add,

say, Profit, because Unit Sales is already defined in Current Period and
Last Period.

Quote:

Current Period Last Period %
Change
Q2 Unit Sales 62,610
66,291 -5.55%

WITH MEMBER [Gender].[Unit Sales] As 'SUM({[Gender].[F],[Gender].[M]})'
MEMBER [Measures].[Current Period] As
'[Measures].[Unit Sales]', solve_order=1, FORMAT_STRING = '###,###'
member [Measures].[Last Period] As '(0)+(Measures.[Unit Sales],
[Time].[1997].[Q2].lag(1))', solve_order=2, FORMAT_STRING = '###,###'
MEMBER [Measures].[% Change] AS
'([Measures].[Current Period]-[Measures].[Last Period])/[Last Period]',

Quote:

FORMAT_STRING = '#.00%' SELECT {[Measures].[Current
Period],[Measures].[Last Period],[Measures].[% Change]}
on columns, NON EMPTY CROSSJOIN({[Time].[1997].[Q2]},{[Gender].[Unit
Sales]}) on rows FROM Sales

----- Tom Chester wrote: -----

You could put the calcs in a random dimension (not measures).

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote in message
news:BFDED6B8-3555-4232-8D02-46A5B9A1BA1F (AT) microsoft (DOT) com...
Using foodmart and the MDX below I get the following Unit Sales
figures
for 1997 Q2 (so, last period=1997 Q1):
Current Period Last Period %
Change
Q2 62,610
66,291 -5.55%
I really want several measures in the output, not just Unit Sales,
like:
Current Period Last
Period
% Change
Q2 Unit Sales 62,610
66,291 -5.55%
Sales Count #### ####
##%
Profit ### ###
##%
(where ### is the actual amounts). I know this means I'm trying
to put
measures on both axis which you probably can't do. Can anybody help?
Thanks.
WITH MEMBER [Measures].[Current Period] As
'[Measures].[Unit Sales]', solve_order=1, FORMAT_STRING = '###,###'
member [Measures].[Last Period] As '(0)+(Measures.[Unit Sales],
[Time].[1997].[Q2].lag(1))', solve_order=2, FORMAT_STRING =
'###,###'
MEMBER [Measures].[% Change] AS '([Measures].[Current
Period]-[Measures].[Last Period])/[Last Period]',
FORMAT_STRING = '#.00%' SELECT {[Measures].[Current
Period],[Measures].[Last Period],[Measures].[% Change]}
on columns, {[Time].[1997].[Q2]} on rows FROM Sales

#3

That's perfect! Thank you so much. You make it look so easy...

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

Re: Impossible MDX?

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

Re: Impossible MDX? - 01-19-2004 , 03:37 PM

Re: Impossible MDX? - 01-19-2004 , 07:15 PM

Re: Impossible MDX? - 01-21-2004 , 03:16 PM