Forum Discussion

Sudhakar's avatar
Sudhakar
New Contributor II
2 years ago

Generate Level 1 and Level 2 Members on Fly in Cubeview

Hi Team,

We have a requirement to generate Level 1 member list (Department Member List) based on POV (Entity Dimension) selected so we have developed XFBR script to generate the Level 1 Member List. Now we have to generate Level 2 Member List (Product Dimension Member List) based on Level 1 (Department). I am not sure how to reference or Pass Level 1 information as a parameter to XFBR script?

 

POV Entity=>Level 1 Member List: XFBR(POV Entity) =>Level 2 Member List: XFBR(Level 1).

Not Sure how to refer or Pass Level 1 information as a parameter to XFBR script.

Thanks,

Sudhakar S

Reports
  • ChrisLoran's avatar
    ChrisLoran
    2 years ago

    Unfortunately you cannot use XFBR rules on more than the first level of row/column expansion in a Cube View.
    This is because the XFBR is only executed for each row/column that have XFBR expression in its *pre-expanded* form. 

    There is no multi-level processing of XFBR expressions.

    You don't explain what the association is between products and departments, in other words how do you determine what products are relevant for a particular department?  Is this driven by a Text property on Level2?
    Also there isn't any context here on what you are trying to do. Is the idea to show a cube view that is enabled for input, and show all products that are relevant for a certain department?   Would 'allow sparse row suppression' automatically do this, which would make the rows to display driven by data?  Or is it because you want to show all valid combinations regardless of data?

    One theoretical solution could be to have an XFBR on the first level, as you already have, then on the 2nd level just have an expansion to show all products, and then filter them by using a DynamicCalc expression which returns either 'nothing' or an empty cell if this product is not valid for the level1 dimension, or return a real number (1) if it is a valid combination.  Then you can use row suppression driven from that DynamicCalc.  However this will be very very slow and use a lot of memory because it has to run a DynamicCalc test on every possible combination of department+product.

    Otherwise you could have a single XFBR rule that returns a string like this:
     U1#Dept01:U2#Prod_A, U1#Dept01:U2#Prod_B,  U1#Dept01:U2#Prod_C
      ... .. etc. ..
     U1#Dept99:U2#Prod_X, U1#Dept99:U2#Prod_Y, U1#Dept99:U2#Prod_Z

    so it returns a single long string full of member *pairs*.  Then override the Row Header settings in the Cube View to always show UD1 / UD2 (in this example) , put the XFBR expression into the first row in the Cube View design, and you will get a list of every valid/relevant Level1/Level2 combination.  This should be OK for performance, and allow data input, however it will have lost the visual structure, for example you won't be able to show it as a tree-like expansion.

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    2 years ago

    Unfortunately you cannot use XFBR rules on more than the first level of row/column expansion in a Cube View.
    This is because the XFBR is only executed for each row/column that have XFBR expression in its *pre-expanded* form. 

    There is no multi-level processing of XFBR expressions.

    You don't explain what the association is between products and departments, in other words how do you determine what products are relevant for a particular department?  Is this driven by a Text property on Level2?
    Also there isn't any context here on what you are trying to do. Is the idea to show a cube view that is enabled for input, and show all products that are relevant for a certain department?   Would 'allow sparse row suppression' automatically do this, which would make the rows to display driven by data?  Or is it because you want to show all valid combinations regardless of data?

    One theoretical solution could be to have an XFBR on the first level, as you already have, then on the 2nd level just have an expansion to show all products, and then filter them by using a DynamicCalc expression which returns either 'nothing' or an empty cell if this product is not valid for the level1 dimension, or return a real number (1) if it is a valid combination.  Then you can use row suppression driven from that DynamicCalc.  However this will be very very slow and use a lot of memory because it has to run a DynamicCalc test on every possible combination of department+product.

    Otherwise you could have a single XFBR rule that returns a string like this:
     U1#Dept01:U2#Prod_A, U1#Dept01:U2#Prod_B,  U1#Dept01:U2#Prod_C
      ... .. etc. ..
     U1#Dept99:U2#Prod_X, U1#Dept99:U2#Prod_Y, U1#Dept99:U2#Prod_Z

    so it returns a single long string full of member *pairs*.  Then override the Row Header settings in the Cube View to always show UD1 / UD2 (in this example) , put the XFBR expression into the first row in the Cube View design, and you will get a list of every valid/relevant Level1/Level2 combination.  This should be OK for performance, and allow data input, however it will have lost the visual structure, for example you won't be able to show it as a tree-like expansion.

  • Sudhakar's avatar
    Sudhakar
    New Contributor II
    2 years ago

    Hi Chris,

    Thanks for the reply!

    Purpose is to show only the valid Intersection or relevant product to the users.

    The association between Entity, Product and Department using Text 2 Property

    Passing Entity as Parameter to XFBR Function to Generate the Department List attached this in Level 1 in the Cube View

    We have another XBR Function to Generate the Product Member based on the Department Member. Wanted to place this function into the Level 2 Cube View Row. 

    I am not sure how to pass the Level 1 row information as parameter into Level 2.  From your answer i clearly understand it is not possible to pass Level 1 information to any other levels in row. We can have another cube view to summary by Department.

    Thanks for the solution, we will generate XFBR with Combination of valid Department and Product but we will loss the visual structure. I believe that should be fine. 

    Note: Other Alternative

    1) when i have the Entity, Department and Product in POV as RTP Solution.

    2) When i have the Entity and Department in POV as RTP and Product in Level 1 as XBR Function.

  • Sudhakar's avatar
    Sudhakar
    New Contributor II
    2 years ago

    Hi Chris,

    I am able to generate valid intersection list and able to assign it to CubeView 

    Logs

    Cube View - Row Header Settings

    Calling XFBR Script which will return Department and Product combinations

    Output

    Thanks,

    Sudhakar S