Removing shared members in MDX

Tim Faitsch
Tim Faitsch

November 18th, 2013, 5:31 pm #1

Let's say I want to do an MDX pull and include all members in a dimension. The shared members show up as duplicate rows. How do I remove those? It seems really simple so I must be missing something simple. I tried Distinct() but I've got too many tuples for that to work (limit is 107374182). Any thoughts?
Thanks,
Tim
Quote
Share

Joined: November 28th, 2012, 1:51 pm

November 18th, 2013, 6:54 pm #2

Descendants([hierarchy],[dimension].levels(0)

Quote
Like
Share

Timothy Faitsch
Timothy Faitsch

November 18th, 2013, 7:15 pm #3

That doesn't work if the second hierarchy is a mix of shared and non-shared members. I miss all the non-shared members in the second hierarchy.
Quote
Share

Joe W
Joe W

November 18th, 2013, 7:17 pm #4

oh poop eom
Quote
Share

Joe
Joe

November 18th, 2013, 7:18 pm #5

That doesn't work if the second hierarchy is a mix of shared and non-shared members. I miss all the non-shared members in the second hierarchy.
I don't think I've ever built a hierarchy that has both shared and stored leafs..

Quote
Share

Joe
Joe

November 18th, 2013, 7:36 pm #6

That doesn't work if the second hierarchy is a mix of shared and non-shared members. I miss all the non-shared members in the second hierarchy.
This applies to shared members apperantly

SELECT
{Sales}
ON COLUMNS,
{[[Diet]].[100-20]]]} PROPERTIES MEMBER_UNIQUE_NAME
ON ROWS
FROM Sample.Basic;
Quote
Share

Joe
Joe

November 18th, 2013, 7:46 pm #7

Let's say I want to do an MDX pull and include all members in a dimension. The shared members show up as duplicate rows. How do I remove those? It seems really simple so I must be missing something simple. I tried Distinct() but I've got too many tuples for that to work (limit is 107374182). Any thoughts?
Thanks,
Tim
I think all the shared members are explicity called with '[]' around them.. you could test for those brackets and not pull those members..


WHEN RIGHT([product].CURRENTMEMBER.MEMBER_NAME,1) <> "[" THEN (product].currentmember)
Quote
Share

Tim Faitsch
Tim Faitsch

November 18th, 2013, 8:02 pm #8

This applies to shared members apperantly

SELECT
{Sales}
ON COLUMNS,
{[[Diet]].[100-20]]]} PROPERTIES MEMBER_UNIQUE_NAME
ON ROWS
FROM Sample.Basic;
Adding that just gives a column with all the member names and unique ones for any shared members. It also puts brackets around the original members.

It shouldn't be this difficult, right?
Quote
Share

Joe Watkins
Joe Watkins

November 18th, 2013, 8:47 pm #9

I think all the shared members are explicity called with '[]' around them.. you could test for those brackets and not pull those members..


WHEN RIGHT([product].CURRENTMEMBER.MEMBER_NAME,1) <> "[" THEN (product].currentmember)
SELECT
{Sales}
ON COLUMNS,
FILTER({product.LEVELS(0).MEMBERS}, LEFT(PRODUCT.CURRENTMEMBER.MEMBER_UNIQUE_NAME,1) <> "[" )
ON ROWS
FROM Sample.Basic;
Quote
Share

Joe
Joe

November 18th, 2013, 8:50 pm #10

There is another way to do this - I just need to figure it out.. <eom>
Quote
Share

Confirmation of reply: