July 20, 2013 at 6:49 am
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.
July 20, 2013 at 10:15 am
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.
July 20, 2013 at 8:58 pm
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
July 20, 2013 at 10:24 pm
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.
July 20, 2013 at 11:33 pm
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.
July 21, 2013 at 2:59 am
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]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply