Cubeset / Cuberankedmemember question

[kingcong]

Hi Everyone. I was hoping someone could help me figure out a solution to a problem I am having.
I have a hierarchy setup that goes down multiple levels: L1,L2,L3.

Operating Expenses (L1)=> Programming (L2) => Internal Programming (L3) / External Programming (L3)

Other Expenses (L1) => Miscellaneous (L2) => Misc #1 (L3) / Misc #2 L3

This is a very simplified version but I just wanted to illustrate the hierarchy levels. I am trying to create a list using the cuberankedmemeber function to give me the L3 members for a specific L2 Group but also filtered for a specific line of business.

LOB #1 would have Internal programming (L3), but not External Programming (L3)
LOB #2 would have both Internal and External (L3)


Simplified report setup.

LOB: LOB #1 (input)

Operating Expenses (L1) - $100
Programming Expenses (L2) - $100
Internal Programming (L3) - $100
External Programming (L3) - $0.00

Problem: I want Internal Programming & External Programming to dynamically change depending on the LOB input. I could just hardcode those lines in and have a cubevalue calculate the amounts, but I wanted to see if it could be done dynamically. The reason for that is because a LOB could have 10 Level 3s and another LOB could have 5 different ones. If I hardcoded them, I would then have 15 lines. I'm trying to keep the lines down to the bare minimum by having the lines dynamically change on the spreadsheet.

*If I use cuberankedmember formula that pulls all children under L3, I would get the Misc ones as well, but I only want the Misc ones to show up in the Other Expenses (L1) section, not the Operating Expenses (L1) section.

THANKS IN ADVANCE FOR ANY HELP!!

 

[Tianbas]

If your L1-L3 structure is always the same for LOB1 you could build a Cubeset of all LOB1 L3 members , one for all LOB2 L3 members and so on and then use this set in a cuberankendmember formula. But this would be a lot of maintenance in case the L3 change for a LOB

I would use good old Excel to solve this.

First create a table with Cubeformulas showing all the 15 L3 members (column B) and the amount (column C). Column A gets following formula A2=IF(C2="";A1;A1+1). This should count from 1 to 15 but only if there is a value in column C. In your report table add a column with numbers 1 to 15 (hide it later) and with a VLOOKUP you can now pull the cubemembers that are in use for the LOB

To split between OPEX and Other use two tables for the L3