Handling the ALL level in Xcelsius selectors

Handling the ALL level in Xcelsius selectors is always a problematic proposition. If you tried using a selector to pass a value to your BusinessInteligence prompt, in order to retrieve filtered data from the database, you must have run into this issue. While selector components are generally highly configurable and provide loads of useful functionality, they lack the inherent capabilities to handle an “all” selection, which would apply everything is selected. When using BusinessObjects as a data source for an Xcelsius dashboard, you can use one of the following simple techniques to address this problem, and resolve it with relative ease.

First approach: leverage webi optional prompt option for a single list of values

Since the release of XI 3.0 webi was enhanced with the capability to create optional prompts. If you only need to handle a single list of values in a single selector, and you are looking to retrieve new database values each time the selection changes, you can create an optional prompt in your query, and then bind the prompt value to a cell in your excel model. The value that the webi  web service (LO/QaaWS/BI Service) will be looking for to ignore the selected prompt would be <Not Specified> (including the greater then less then brackets). You can use a formula in your model to convert this value for presentation purposes, and display ALL or anything else for that matter.

Second approach: when dealing with multiple lists of values

The second use case addresses a situation in which multiple selectors are needed and you do not plan to retrieve data from the database every time a selection is made. In this scenario, you will need not just one ALL row, but multiple ALL rows, one for each dimensional value in your multi-selectors. In this example, you need to produce data for states and product lines (from eFashion) with revenues, and allow users to filter the data for any combination of states and lines.

 

In this case, you can create an ALL object in your universe (for two dimensions, you will need two) and then use union queries in your webi report to artificially manufacture an ALL summary row for each level in your data. Once you have your data setup this way, you will be able to easily filter it based on the user selection in the selectors. 

The second use case addresses a situation in which multiple selectors are needed and you do not plan to retrieve data from the database every time a selection is made. In this scenario, you will need not just one ALL row, but multiple ALL rows, one for each dimensional value in your multi-selectors. In this example, you need to produce data for states and product lines (from eFashion) with revenues, and allow users to filter the data for any combination of states and lines.

In this case, you can create an ALL object in your universe (for two dimensions, you will need two) and then use union queries in your webi report to artificially manufacture an ALL summary row for each level in your data. Once you have your data setup this way, you will be able to easily filter it based on the user selection in the selectors.

This entry was posted in Xcelsius and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *