Difficult MDX-Statement

I have a cube with a dimension DATE (Year, Quarter, Month) and a
measure "turnover".

I need data to create a chart of the course of the turnover for each
month of the last 3 years.

My result table should look like this.

Jan Feb Mar ...
2003
2004
2005

Is this possible ?

Thanks for your help
aaapaul

Paul -

Unfortunately you can't do it with a single dimension. There's no MDX you
can write to do what you want. You'll either have to create a new physical
time dimension with just year, a year hierarchy off of the existing time
dimension (which for all intents and purposes creates the same storage as a
seperate physical dimension), or a virtual dimension off of a member property
for year. (Depending on your front end, you may not be able to put different
hierarchies of the same dimension on different axes. If that's the case, you
can't use the extra hierarchy off of an existing dimension option.)

If you don't have a huge cube, I'd recommend using a virtual dimension.
However, with a really large cube, the lack of aggregations from a virtual
will make queries using the virtual dim very slow.

Good luck.

- Phil


"lvpaul (AT) gmx (DOT) net" wrote:

What you want is the measure in each month of each year. You just need to
write a simple MDX query and then treat the results in the way you want.

select
[dim_time].[year].[quarter].[month] on columns,
[measures].[turnover] on rows
from Cube_a;

this should work?

Guangming



"SQL McOLAP" wrote:

Not quite right, I have some that can do it

While you cannot have the same dimension on both axis at the same time
you can create calculated measure that return only the value for a given
month. This approach is kind of like what you have to do in SQL to pivot
data.

I have provided an example that works in Foodmart below. (I have only
done the first 6 months as it involves a lot of repetitive typing)

===========================

WITH

MEMBER Measures.[Jan] as 'sum(filter(descendants(time.currentmember,
[time].[month]),Time.currentMember.name ="1"),Measures.[Unit Sales])'
MEMBER Measures.[Feb] as 'sum(filter(descendants(time.currentmember,
[time].[month]),Time.currentMember.name ="2"),Measures.[Unit Sales])'
MEMBER Measures.[Mar] as 'sum(filter(descendants(time.currentmember,
[time].[month]),Time.currentMember.name ="3"),Measures.[Unit Sales])'
MEMBER Measures.[Apr] as 'sum(filter(descendants(time.currentmember,
[time].[month]),Time.currentMember.name ="4"),Measures.[Unit Sales])'
MEMBER Measures.[May] as 'sum(filter(descendants(time.currentmember,
[time].[month]),Time.currentMember.name ="5"),Measures.[Unit Sales])'
MEMBER Measures.[Jun] as 'sum(filter(descendants(time.currentmember,
[time].[month]),Time.currentMember.name ="6"),Measures.[Unit Sales])'

SELECT
{Measures.[Jan]
,Measures.[Feb]
,Measures.[Mar]
,Measures.[Apr]
,Measures.[May]
,Measures.[Jun]
} ON COLUMNS,
time.year.members ON ROWS
FROM [Sales]

==============================

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Thanks Darren. It works. Another question:

I only want the TOP 20 of [PCENTER].[KT] not of all customers. How can
I integrate this condition in the TOP-20 Selection ?

This is my MDX-Statement.

With
MEMBER Measures.[Jan] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="Januar"),measures.[Menge])'

MEMBER Measures.[Feb] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="Februar"),measures.[Menge])'

MEMBER Measures.[Mär] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="März"),measures.[Menge])'

MEMBER Measures.[Apr] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="April"),measures.[Menge])'

MEMBER Measures.[Mai] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="Mai"),measures.[Menge])'

MEMBER Measures.[Jun] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="Juni"),measures.[Menge])'

MEMBER Measures.[Jul] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="Juli"),measures.[Menge])'

MEMBER Measures.[Aug] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="August"),measures.[Menge])'

MEMBER Measures.[Sep] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="September"),measures. [Menge])'

MEMBER Measures.[Okt] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="Oktober"),measures.[Menge])'

MEMBER Measures.[Nov] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="November"),measures.[Menge])'

MEMBER Measures.[Dez] as
'SUM(Filter(descendants(rdatum.currentmember,[rdatum].[monat]),rdatum.currentmember.name="Dezember"),measures.[Menge])'

SET Top20 AS
'{TOPCOUNT([Kunde].[kunde].members,20,[Measures].[Umsatz])}'
select
{
Measures.[Jan],
Measures.[Feb],
Measures.[Mär],
Measures.[Apr],
Measures.[Mai],
Measures.[Jun],
Measures.[Jul],
Measures.[Aug],
Measures.[Sep],
Measures.[Okt],
Measures.[Nov],
Measures.[Dez]
} ON COLUMNS,
crossjoin(TOP20,{rdatum.[2003],rdatum.[2004],rdatum.[2005]}) ON ROWS
from faktura
where ([inex].[extern],[PCENTER].[KT])

Use a tuple in the topcount expression. To only return the value of the
measure where it intersects with the [PCENTER].[KT] member. I think the
example below should work for you.

SET Top20 AS
'{TOPCOUNT([Kunde].[kunde].members,20,([Measures].[Umsatz],[inex].
[extern],[PCENTER].[KT]))}'

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Paul and Darren -

Forgive my misinformation. Darren's code, although having to be hardcoded
for each existing member, does indeed work. A virtual dim will still be
more dynamic and require no coding, but I'm still impressed with the solution.

My apologies.

- Phil


"Darren Gosbell" wrote:

No Problems Phil, your advice regarding using a virtual dimension
definite more flexible and a better long term solution.

The technique I showed does not really "scale" well and is really only
good for "one offs" or short term fixes. It comes in handy though when
you need to get a report out quick and don't have time to restructure
your cube

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell