Key figure selection in variable screen
If there is a requirement to restrict the key figures in the variable screen of a BEx Query, you have to work with some sort of workarounds ( formula variables, zero suppression and so on). With BW 7.4 SP8 another option is now available. It is called Visibility of Structure Members. Once activated a new selection in the variable screen of a BEx query is visible.
How does it look like?
Following example is based on a sales query from business content. If the query is called in the usual way, the variable screen will look like this:
Once the Visibility of Structure Members is activated, you’ll find a new key figure section where you can select the required key figures:
How does it work?
The new option can be found under the properties of the Key figures (Structure), second Tab Display:
If this is set to “Switchable via Default Variable” the key figure selection is automatically being added to the variable screen. The logic for displaying key figures is explained in the corresponding SAP Help section Visibility of structure elements.
There it is also mentioned that the technical name of the query element is specified which can be found under the key figure properties:
So far I have not seen many systems where the technical name has been used for reporting. But for this feature it is pretty important as otherwise only the generated query element id (UID) would be displayed which might be confusing for the users.
I think this is a useful alternative where selection of key figures is required. Compared with other solutions it is very easy to use. The only thing which I personally do not think is optimal at this point, is the fact, that the order of new variable can’t be influenced as with the other variables (Query Properties / Variable Sequence).
As mentioned before this is only available from 7.4 SP8 onwards. If you are on this release you should also check that a recent version of the query designer is being used (BI Addon for 7.3 SP8 or BI Addon for 7.4 SP1).
Is there also a possibility to not show all key figures initially in the prompt, but only some of them?
I didn't try so far, but I guess if you have the individual KF on hide (but can be shown) it should dissapear from the variable entry.
This is very useful however if I add technical names to key figures that are nested CKF or RKF saved on the server (not tested completely yet) it will display No Applicable Data Found at runtime. Whereas if I remove technical names and use UID with same query I get data.
There's a possibility of using a Exit Variable to hide columns depending on user input variables ? Automatic ?
Did you have any answer? I'm looking for it.
It would be great to get this working with Exit Variables. This would enable scenarios where different measures are shown depending on the Company Code selected in the prompts.
But I do not see that this is possible. In fact, the prompt to select the elements of the structure does not seem to be a variable at all. Or am I mistaken?
The name of the variable consists of '!' + the UID of the key figure structure, for example: