Drill filters are an excellent way how to make WebI reports more interactive to users. However, the “All” value in a drill filter can sometimes cause problems.
If a WebI report with drill filters was used only by a WebI user then everything would be almost without a problem. However, reports need to be either printed or exported into XLS/PDF format very often. In such case it’s nice to print also values of drill filters so it’s obvious from a simple look at the printed/exported report what the report is actually showing.
Drill filters and their values are not printed nor exported. Their values need to be added into the report. For that reason there is the DrillFilters(object) function. It returns a value selected in a drill filter:
In the picture above, the drill filter is applied on the [Lines] dimension and the value “Jackets” is selected. The cell that shows “Jackets” in bold is the result of the formula shown in the rectangle.
When the value of the drill filter is changed to anything but “All lines”, the value in the cell reflects the selection. However, it stops working when All lines is selected. The cell with the formula becomes blank:
There is an easy way how to fix the formula though. The fix utilizes a fact that when All is selected in a drill filter then DrillFilters() function returns a string with a zero length. This can be tested in an IF statement and the result of the formula returned accordingly. So the final formula would look like this:
=If Length(DrillFilters([Lines])) = 0 Then "All Lines" Else DrillFilters([Lines])
I will describe in the next blog how to achieve the same when all values are selected in an input control. Stay tuned 🙂