MDX Newbie: How to combine some members to create a new member at same level (but not a calc. member)

Question

MDX Newbie: How to combine some members to create a new member at same level (but not a calc. member)

tim-1151818

SSC-Addicted

Points: 466
More actions
July 20, 2013 at 6:49 am

#277626

I have a Dimension Items with first level ItemType.
I want to create a member which combines some of the existing members.
For example, I have members [Items].[ItemType].Deposit and [Items].[ItemType].GiftVouchers
and I want to "pretend" that I actually have a member [Items].[ItemType].DepositsAndVouchers
It sounds like Aggregate would do what I want:
With
Member [Items].[ItemType].[DepositsAndVouchers] as '
Aggregate({[Items].[ItemType].[Deposit], [Items].[ItemType].[GiftVouchers]})'
select [Items].[ItemType].[DepositsAndVouchers] on columns,
[Measures].[AmtReceived] on rows
Logically I thought this would work ... but I get nothing (using Mondrian actually)
So I have a conceptual problem with how MDX works, perhaps.

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

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

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

Aggregate only works on measures. Take the aggregate function out of your calc member definition and simply make a new member that is (A , B) then refernece that member on rows of columns with a measure.

HTH,

Steve.

tim-1151818 SSC-Addicted Points: 466 More actions · Answer 2

But I can't use a set to define a member ... I have to have some function.

So doing this

with Member [Items].[ItemType].[DepositsAndVouchers] as '{[Items].[ItemType].[Deposit],[Items].[ItemType].[GiftVouchers]}'

give an error

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

Yep, I misread what you are doing. So when you select just those two members on rows with the same measure, they return values but when you use them in the calc member, it doesn't?

Steve.

tim-1151818 SSC-Addicted Points: 466 More actions · Answer 4

stevefromOZ (7/20/2013)
Yep, I misread what you are doing. So when you select just those two members on rows with the same measure, they return values but when you use them in the calc member, it doesn't?

Yes, when I use the two members on rows, I get the total of sales for each of those two members.

When I use aggregate, I get no output for that member.

I thought this would have been a common requirement: to simulate new members that are the sum of siblings.

tim-1151818 SSC-Addicted Points: 466 More actions · Answer 5

My problem is that I do not understand how to write member names. I am confused about when you need to use [Dimension].[Level].[Member] and when [Dimension].[Member] is correct.

I used a demo cube and got it to work.

In this cube, there is a dimension Department with a single level also called Department.

This query does what I want: it creates a new member SF which adds the two real members (Sales and Finance). So I need to learn about how to write member names in multi-level hierarchies.

with member [Department].SF as

[Department].Sales + [Department].Finance

SELECT {[Department].[Sales], [Department].[SF]} on columns,

[Measures].[Actual] on rows

FROM [Quadrant Analysis]