Return items from one dimension based on another dimension

New Contributor III


I have a CubeView with 2 nested rows.

Row 1 - |!varEntity!|.Base (say this will return 5 entities - this is a drop down in a Dashboard)

Row 2 - XFBR(myModule_ParamHelper, GetWBS_by_Entity, Entity=|!varEntity!|)

This rule checks for WBSs where Text2 = Entity (i.e. WBS1 - Text2 = Entity1, WBS2 - Text2 = Entity2)

When I execute the CubeView, I see all the 5 Entities, and in the nested Row, I get the WBSs where Text2 = the first Entity only).

I understand why it is doing this, as the variable gets the same value and passes to the rule.

I also tried |PovEntity| and |CVEntity|. 

PovEntity returns blank, and CVEntity return what is in the Cube Pov section, which I tried with a value or blank.

I also tried WBSDim.Base.Where(Text2 = |CVEntity|) --- or PovEntity - comes blank

Is there a way to pass the value of each evaluated Entity row to the WBS row?

The result should be something like:

Entity1 WBS1
Entity2 WBS3





New Contributor III

I've done something similar in the past using the XFMemberProperty. If I'm understanding the desired outcome correctly, try something like WBSDim.Base.Where(Text2 = XFMemberProperty(DimType=Entity, Member = |MFEntity|, Property=Name). I want to say MFEntity works in this situation, but it's been a minute since I did this.

New Contributor III


I tried this now but it complains about ","

U3#WBSDim.Base.Where(Text2 = XFMemberProperty(DimType=Entity, Member=|MFEntity|, Property=Name))


I believe I can't use it since, based on the documentation, it should be used with Name and GetDataCell.

"Use this function with the Name() and GetDataCell() portion of a Member Filter."

Thank you

New Contributor III

Went back and reviewed and you are correct. I was using a param to pass it along...


U3#WBSDim.Base.Where(Text2 = XFMemberProperty(DimType=Entity, Member=|!varEntity!|, Property=Name))

But because you are pulling the E# at .Base it makes it a bit more tricky.

New Contributor III

Yeah, that's what I figured.

But all good, thanks for trying and sending some solution to test. At least I will save the XFMemberProperty reference for the future, as I think I will need it in another scenario.

I am suggesting a differente solution to the client, i.e. showing the property in a data column (using UD8) - it won't be in the exact right order but at least they will get the right information.



New Contributor III

It wouldn't be quite as elegant, but if you don't have a lot of entities, you could create a literal param like...

E#Entity1:U3#WBSDim.Base.Where(Text2 = Entity1), E#Entity2:U3#WBSDim.Base.Where(Text2 = Entity2), etc...

Then on your CV set Use Default Row Headers to False and make Header 1 Entity and Header 2 UD3.

That should give you the report in the style you are looking for, but it would require maintenance as entities are created.

New Contributor III

2000+ entities 🙂

But it gives me an idea - I may be able to write a rule to return the member selection as you suggested. If the client did not change their mind I will try this.

thank you