Recently, we have a requirement in our Web Intelligence reports, where we need to provide the option of user to prompt for a measure selection. Based on selection the report table should display only the selected measure in the table.
For example, in the island resort universe, we have 2 measures i.e Revenue and Number of Guests. So the user should be prompted to select only one measure and then the report should show the table for that measure only.
My table with both the measure will look like as shown below:
On selection of Revenue, my table should display like
and on selection of Number of Guests it should display like
We can hide table blocks on user selection based on block filters and table properties. The steps involved are:
1. Create a custom list of values object and prompt in the universe. It is being demonstrated in my earlier Web Intelligence: Dynamic Ranking based on User Selection
2. It will result in prompt as shown below:
3. In Web Intelligence document include both the tables:
4. Create a report variable Show Block as shown below:
5. Now for Revenue table apply the block filter as shown below:
6. Similarly apply the block filter for Number of Guests table as shown below:
7. For both the table block we have to uncheck the property Show When Empty:
8. Then the tables should be align on top of each other. As only one table will be visible at one selection so it will appear as if the table columns are changing to show appropriate measure columns.
9. If there are any chart blocks then same can be done to them also.
First we need to apply the block filter as done above for the tables.
Then for the chart blocks we need to uncheck the Show When Empty property.
Then align the charts on top of each other.
Below are the screen shot showing, how the report table and graph will be displayed in the Web Intelligence document:
a) If user selects Revenue from the Show By prompt:
The table and graph will be visible only for Revenue.
b) If user selects Number of Guests from Show By Prompt:
The table and graph will be visible only for Number of Guests.