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.
