In my previous post I showed how to display “all” in a WebI report when the “all” value is selected in a drill filter. This blog will discuss almost the same. The only difference now is that the “all” value is selected in an input control.
Let’s create a simple report on top of the eFashion universe, selecting Year, Lines and Sales revenue objects. I will include also a query filter applied on the Lines dimension selecting only first 4 values so there are not too many records to play with:
I will create a simple table with all 3 objects as well as a new input control – a multi-values check box assigned to the Lines dimension:
Since an input control is not a drill filter, the DrillFilter() function cannot be used to show selected values in a report. An input control works as a report filter so the ReportFilter() function is needed instead.
However, the ReportFilter() function returns all selected values separated by a semicolon. If an input control contains many items (because there are many distinct values of a dimension where the input control was assigned) then the result can be a very long string:
To replace the above long string with an “All Lines” value, a formula needs to check what values are selected in the input control and whether those values that are selected are all that can be selected. The easiest way how to achieve it is to count the number of items in the Lines dimension and compare it to the same count when no filter is applied. So the formula will look like this:
=If NoFilter(Count([Lines])) = Count([Lines]) Then "All Lines" Else ReportFilter([Lines])
The formula works as expected when all values are selected in the input control. But it can still provides a long result when many (but not all) values are selected. To fix this, the semicolons in the string can be replaced with the Line Feed character so every value is displayed in a new row:
=If NoFilter(Count([Lines])) = Count([Lines]) Then "All Lines" Else Replace(ReportFilter([Lines]);";";Char(10))
Various WebI functions were combined in a formula that shows in a cell values selected in an input control:
• NoFilter() – ignores all filters when values are calculated
• Count() – gives the number of items/values in a dimension
• Replace() – replaces a part of a string with another string
• ReportFilter() – gives values of a report filter applied to a dimension