Generate Level 1 and Level 2 Members on Fly in Cubeview
- 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_Zso 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.