Creating report filters with multiple hierarchies

[former_member196865]

Hi gurus,

I am trying to create a report where the user wants to filter out members of one hierarchy based on the members of the other hierarchy.

For Example:

• ID Desc ParentH1 ParentH2 ParentH3
• 1000 desc1 company1 OP1 Dep1
• 2000 desc2 company1 OP2 Dep2
• 3000 desc3 company3 OP3 Dep1
• 4000 desc4 company3 OP2 Dep1

The user wants to create adhoc reports where she wants to filter based on two hierarchies. The user wants to select the record 4000 based on filters. If I give H2=OP2 & H3=Dep1, it says "your dynamic selection has no result". If one of hierarchies is a property then we can see the filtered member. The user wants to use only hierarchies though. I have suggested using EPM functions on the sheet but she doesn't want to do that either. Is there a way around this?

[former_member186338]

Please select correct primary tag!

EPM Add-In for Excel?

[former_member186338 · Accepted solution]

Unfortunately it will not work, just tested:

Selection of 2 parents in different hierarchies will result in empty report.

Same effect with formula:

=EPMDimensionOverride("000";"TITLES";"BAS(1HIMP) AND BAS(2MAGAZINES)")

But formula with "OR" work correctly.

[former_member196865 · Accepted solution]

Thanks Vadim.

Yeah looks like it. Cannot filter based on multiple hierarchies. At the adhoc level it is impossible to derive the common data. A design change may be needed for this.

[former_member186338 · Accepted solution]

Theoretically it's possible to create VBA code to generate selection based on multiple hierarchies, but it's a complex job!