Filter on multiple values using virtual hierarchies

[CRP0021]

Hello all,
Just looking for some guidance to clear up what is possible with virtual hierarchies in PAW.

I have created a hierarchy on my orders dimension called Sales Rep.

Each sales rep is assigned to an order number but in many cases there are more than one sales rep assigned to an order number.

When creating the hierarchy from an attribute it does not create a unique list of Sales Reps which is causing a problem when filtering cube views for a specific Rep.

When I pull the Order Number dimension out as a filter and switch to the Sales Rep hierarchy it functions as the screenshot below.

I'd like to know if it's possible to be able to "search" for any instance of Sales Rep using the virtual hierarchy?

I have a few workarounds with MDX statements but I'd like to know what's possible with the hierarchies before making a decision one way or another.

I've attached an image for additional details.

Thanks in advance!

[Paul Segal]

You can do this. See below for results from creating a hierarchy from a string attribute and applying filters. I think this is what you're trying to do?
I've added an attribute for each sales rep and then an All attribute which puts them together (I've just done this manually for this, but it could be a rule).

Screenshot 2024-11-21 091602.png (17.85 KiB) Viewed 1793 times

[CRP0021]

Thanks Paul for your response.

The behaviour I am seeing is that when I create a hierarchy from the string attribute called Sales Rep I get the hierarchy below:
Bob
Jane
Sue
Bob; Sue
John
Jane; Bob
Jane; John

The parent node Bob now has only one order # attached to it (Order #001).

If I wanted to use the Sales Rep hierarchy to filter on all of Bob's order #'s I'm not able to do that.

I guess the trick in your example is that I would need to add a hierarchy per Sales Rep, which could work but then each hierarchy would need to be added to the book as a filter, which could become tedious.

I appreciate the feedback and attention!

[Steve Rowe]

You probably need to drop down and use a TI to create the hierarchy using functions such as HierarchyElementInsert and HierarchyElementComponentAdd

You would then use this to create a hierarchy that has double counting in it (which is odd and a little scary)

Then you could have Bob = 001 + 004 + 007 and Jane = 002+ 007 + 010

This would give you the filter you want, but you would need to take care to never report on the consolidation of All Sales Reps as it would not give the right number (probably safest not to consolidate the sales people actually)