Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX Newbie: How to combine some members to create a new member at same level (but not a calc. member) Expand / Collapse
Author
Message
Posted Saturday, July 20, 2013 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 21, 2013 2:56 AM
Points: 4, Visits: 6
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.





Post #1475752
Posted Saturday, July 20, 2013 10:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Today @ 11:49 AM
Points: 1,815, Visits: 3,467
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.
Post #1475757
Posted Saturday, July 20, 2013 8:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 21, 2013 2:56 AM
Points: 4, Visits: 6
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
Post #1475794
Posted Saturday, July 20, 2013 10:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Today @ 11:49 AM
Points: 1,815, Visits: 3,467
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.
Post #1475801
Posted Saturday, July 20, 2013 11:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 21, 2013 2:56 AM
Points: 4, Visits: 6
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.
Post #1475803
Posted Sunday, July 21, 2013 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 21, 2013 2:56 AM
Points: 4, Visits: 6
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]
Post #1475805
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse