Skip to Content

Creating report filters with multiple hierarchies

Dec 06, 2016 at 04:04 PM


avatar image
Former Member

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?

10 |10000 characters needed characters left characters exceeded

Please select correct primary tag!

EPM Add-In for Excel?

* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Vadim Kalinin 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)
Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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.


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