ElementParent rule in Hierarchies

[mvaspal]

Hi,
I am trying to write a "% of Parent" -type of rule in an alternative/virtual (not sure what's the official term...) Hierarchy. Here below is a simplified code of this

The rule for the primary/base dimension looks like this, and works fine:

Code: Select all

[Measures: '% of Parent'] = [Measures: 'Gross Sales'] \ DB('Sales',!Version,!Time,ELPAR('Products',!Products,1),'Gross Sales');

Then I have a Hierarchy, called "By Bucket1", here is my try:

Code: Select all

[Measures: '% of Parent in Bucket1'] = [Measures: 'Gross Sales'] \ DB('Sales',!version,!Time,ElementParent('Products','By Bucket1',!Products,1),'Gross Sales');

The rule returns only 0s. Then I created a test S measure, called TestParent:

Code: Select all

[Measures: 'TestParent'] = S:ElementParent('Products','By Bucket1',!Products,1);

This measure correctly displays the element parents in the By Bucket1 hierarchy, and so I am a bit puzzled on what I'm doing wrong in my % of parent rule?

Any help appreciated,
Thank you
Matyas

[PavoGa]

In the DB statement, the argument for the hierarchy element has to be fully qualified:

'dimname:By Bucket1:' | ElementParent('Products','By Bucket1',!Products,1)

[MarenC]

I was reading that you don't need the dimension qualifier in a DB.

I also read that you shouldn't use the pipe.

'By Bucket1': ElementParent('Products','By Bucket1',!Products,1)

Is this incorrect?

Maren

[mvaspal]

Excellent, that's it, thank you for your help!!

[mvaspal]

@Maren - your syntax worked too
I guess because in a DB() function, the dimension order is fixed, so no need to qualify it

[PavoGa]

I was reading that you don't need the dimension qualifier in a DB.

I also read that you shouldn't use the pipe.

'By Bucket1': ElementParent('Products','By Bucket1',!Products,1)

Is this incorrect?

Maren

I've not read that on the pipe, but we do use it without any problems. However, I am UP with not using it. I try to avoid it anyway possible. Did not know this particular syntax would work, so thank you for that!