Dimension Override based on Property

[former_member398742]

Hi People,

I'm having one issue with Dimension Override based on Property.

I have Time dimension in Column Axis, and

ZKM_PRODUCT - Product and ZKM_OWNER - Owner dimensions in row axis , ZKM_OWNER is the property in ZKM_PRODUCT Dimension.

and I have Hierarchy for ZKM_OWNER Dimension like below

So if i use =EPMDimensionOverride("000","ZKM_PRODUCT","ZKM_OWNER="&B9) then my report works fine,Currently B9 has "P1" it is a base level of P Hierarchy in ZKM_OWNER Dimension.

But If i choose B9 cell as "P" then it should display three Products (See above first picture - Master data of Product) but it is not happening.It is working very wired.

Could you please let me know, how i can do Dimension override based on Property, if property has hierarchy.

Thanks,

Vishu

[Former Member]

Dear Vishu,

You can achive it by using BAS(). But you should difine the BAS() using "Concatination" of the cells.And you have to refer the concatinated cell in the Dimension override.......as shown below.....

Kindly refer the below screenshot for detailed explination...

Aravind

[former_member186338]

Hi Aravind,

Your proposal is not related to the question It's Override by property, not by member!

Vadim

[Former Member]

Hi vadim,

Yes, I agree with you vadim,It was my understanding mistake. By seeing the requirement at first glance i thought that the property is calling in the cell & based on that member owner dimension need to be override. But now i got it, It was totally reverse.

As vadim said, by adding another hirerchy in the product based on owner will fulfil the requirment.

Aravind

[david_ings]

Hi Vishu,

If you try something like this..

=EPMDimensionOverride("000","ZKM_PRODUCT","ZKM_OWNER="BAS("&B9&")")

..then it will always return the base value. So if you pick a base member such as P1, it will still return P1, but if you pick the parent "P" then it will return the three base members of P.

David

[former_member186338]

Hi David,

Why do you think that BAS will work in this case at all???

Vadim

[david_ings]

Hmm yes, I've misread the dimensionality.

Still, I think there could be ways around this. A couple of examples below, by all means not perfect at all but might get you what you need.

For example:

- Create another property e.g. ZKM_OWNER_PARENT or something

- Populate with P for those that relate to P1-3 (Cosmos etc)

- Then some logic in the worksheet that says, if owner selection Calc=y (or something similar to identify when user selects the parent), then override based on the ZKM_OWNER_PARENT property, otherwise if calc=n then look at the ZKM_OWNER property which will just return the one base member

Only trouble with this is if someone picks "All_Owner" whereby the calc=y logic might fall down.

An alternative would be to identify if the CHAR length of the user selection is 1, then pick the ZKM_OWNER_PARENT property, otherwise look at the ZKM_OWNER property.

Just some ideas to play around with. Please ignore my earlier post.

David

[former_member186338]

It will not work for multilevel hierarchy (or you will have to create additional properties for each level)...

May it's better to create additional hierarchy in product dimension - by owner and then simply select parent in product dimension. The owner dimension member can be linked by property of product dimension.

Vadim

[Former Member]

Vishu,

I don't think you could use the hierarchy the way your thinking in a single step.  You would need to make it a two step process: first determine the base members from the input owner hierarchy, and then apply that result to the epmdimensionoverride formula with proper and/or logic.

Thanks,

Ryan

[former_member398742]

Hi Ryan,

Thanks for the replay.

Could you please explain me in more details, how i can do with two steps ?

Thanks,

Vishu

[former_member186338]

Hi Ryan,

Unfortunately with 2 reports (first to get base members of owner and perfom concatenation of the override string in the local member, second - actual report with override based on first report) you will have to refresh twice Like here:

[former_member186338]

Sorry, but it will never work like you described!

Override by property will override by exact property match and hierarchy parent is not related to this match.

Vadim