MDX for calculated members in SSAS 2000

Question

Post reply

MDX for calculated members in SSAS 2000

Sam Peascod

SSCrazy

Points: 2497
More actions
May 16, 2007 at 12:53 am

#117311

I have a calculated member which sums three types of channels, which I call "KeyAccounts". I have a shared "Channel" dimension. My "Sales" cube holds the calculated members and uses the shared dimension.
I have the following MDX:
SELECT
{p_Channel} ON 0,
{[Channel].[All Channel].Children} ON 1
FROM Sales
This only returns the children of the shared Channel dimension, and not my calculated member which sits under [Channel].[AllChannel]. How can I get this to include the KeyAccounts calculated member?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Try using the AddCalculatedMembers function, maybe like...

SELECT

{p_Channel} ON 0,

ADDCALCULATEDMEMBERS({[Channel].[All Channel].Children}) ON 1

FROM

Sales

Steve.

Sam Peascod SSCrazy Points: 2497 More actions · Answer 2

Sam Peascod

SSCrazy

Points: 2497

May 16, 2007 at 4:54 pm

#706960

Perfect, and so logical! Thanks for that

Sam Peascod SSCrazy Points: 2497 More actions · Answer 3

Hmmm, I spoke too soon. While that function does what I asked for, I realise I asked for the wrong thing.

The MDX for my KeyAccounts calculated member is as follows:

[Channel].[All Channel].[1] + [Channel].[All Channel].[2] + [Channel].[All Channel].[17]

When I choose the KeyAccounts option in the Channel parameter, I get no data returned (since the Channel parameter is wrapped in a DESCENDANTS() function). Taking out the DESCENDANTS(), I get the sum of all customers in those three channels, without seeing the customers individually. What I really need to see is all the customers of channels 1, 2 and 17, and have this selection hidden behind some kind of group called "KeyAccounts", selectable as a parameter.

Any ideas people?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Not quite sure I'm up with the parameters you're using (I'm guessing this is in Reporting Services) but it sounds like you may want a Named Set rather than a Calculated Member. A Named Set is literally a pre-determined set of members, and selecting it on rows/cols will display the set contents (ie the members). A calc member will always create just that single (new) member which when returned on rows/cols is simply the calculated total (be that sum, divide etc).

Again, not sure on how (or where) you're implementing parameters but if you have the flexability, I would:

i) create the named set in the cube;

ii) have my parm be effectively a boolean;

iii) if KeyAccts = True then MDX will look like...

SELECT

{p_Channel} ON 0,

{[MySetOfKeyAccounts]} ON 1

FROM Sales

iv) if KeyAccts = False then MDX will look like...

SELECT

{p_Channel} ON 0,

{[Channel].[All Channel].Children} ON 1

FROM Sales

Assumptions here are that your KeyAccounts parm determines whether 'all' accounts are displayed or simply the key accounts.

You may need to mod the first MDX to include [NamedSet].MEMBERS , but i think the named set alone would/should work.

HTH,

Steve.

Sam Peascod SSCrazy Points: 2497 More actions · Answer 5

Hi Steve,

I am using SSRS and created a named set in SSAS. I created a non-queried list of values for my parameter and pointed one of them to my named set. I'm happy with this now.

Thanks,

Sam