Lets say I have 5 filters on a dashboard. They are all database columns. I would like to have “All” keyword on top for all the filters. Lets say if Filter1 value is selected then filter2 should only show values pertaining to Filter1 selection including “All” keyword and the values needs to be sorted too. I have tried bringing all column values in one query and then use multiple combo boxes but combo box doesn’t sort values in order. If I select “All” keyword in the first filter, the 2nd filter shows All the values but they are not sorted.
What is the best way of having ‘All’ keyword in filters without using too many queries?
The best way to add “All” to a combo box is to just have it as a cell on the spreadsheet, and load your other values below it. Anything else requires excessive monkeying with the queries, mostly doing unions and whatnot.
The downside to that, though, is that you can’t really take advantage of Filtered Rows. And for what you are doing, Filtered Rows is going to help a LOT.
So here’s a different approach. Build out a query that adds “**ALL VALUES” to each level of your hierarchy. This is kind of tricky, and I have to leave it up to you, as I don’t know your data. But union queries are likely to be your friend. If the possible values are fairly static, you can gain a lot of mileage by making this a materialized view or even a table.
(I put the asterisks at the front of “**ALL VALUES” to make sure it sorts to the top.)
Now you are bringing in your sorted data. Use Filtered Rows on the first combo box to push the relevant values of the next four columns out to their own section. Repeat until you have all the combo boxes filled.
Currently, I am doing Union approach in order to add “All” keyword. We have many dashboards and they all have at least 3 filters from database columns. For 3 filters I have to add 4 union queries as shown below and All for the last column I had to put it on a cell in excel.
Queries
“All” Column A - “All” Column B - Column C (No All)
Union
“All” Column A - Column B - Column C
Union
Column A - “All” Column B - Column C
Union
Column A - Column B - Column C
I also have a filter for Monthly (Last 12 mths) and Quarterly (Last 6 Qtrs) view. Since Monthly and Quarterly view time differs, I have to create separate labels for each view. I think this wont be an efficient way as there will be a lot of universe queries.
From a performance perspective, are you running QaaWS directly against the universe, or are you publishing blocks from WebI reports? I highly recommend the latter, particularly for scenarios like this. You can schedule the reports to run as often as the data changes (for most DW set-ups, daily). Then simply connect to the latest instance instead of refreshing. It is so much faster. Do note that you also have to switch from using prompts to using filters, though.
We are using Dashboard design 4.0 and I can directly connect to Universe and create queries. Unfortunately, our BIWS server setting is not working hence I cannot leverage BIWS to be able to use scheduled Webi report data. If there are 7-8 filters in the dashboard then its difficult to implement this approach.
