Skip to Content

Creating report filters with multiple hierarchies

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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Dec 06, 2016 at 06:35 PM

    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.

    merge.png (87.1 kB)
    Add a comment
    10|10000 characters needed characters exceeded