Conditional Column Hiding in BO 3.1 Webi Report Followup Post
Since my last post, I have been asked by people about how would I be hiding a column conditionally when it is not the rightmost column of the table, rather is in the middle.
I will take a similar example as the previous one to illustrate this. However, this time I will base the condition on an input control rather than a query filter.
Once again, I am using the standard efashion universe. I have developed a report with Store Name, Lines, Margin and Sales Revenue without any filters in the query panel. The report will look as below.
In addition, I will have created an Input Control on the Lines object as follows:
Now, I want that the Margin column will only be displayed, when the ‘Accessories’ value is selected in the Input Control. For all other selections(even ‘All Values’), this column will be hidden. To achieve this, the steps will be:
- Click on the Margin column and modify the formula for the column(=[Margin] initially) in the function panel as
= If( ReportFilter( [Lines] ) = "Accessories" ; [Margin] ; [Sales revenue] )
- This formula will strip the Margin values from its formatting. So now select the column, right click and choose Format Number. In the Format Type, select Currency as $ as below. Then click ok.
- Next click on the header of the <b>Margin</b> column and modify the formula for the cell(=NameOf([Margin]) initially) as
= If( ReportFilter ( [Lines] ) = "Accessories" ; NameOf( [Margin] ) ; NameOf( [Sales revenue] ) )
- Now click on the Sales Revenue column and modify the formula for the column(=[Sales revenue] initially) as
= If( ReportFilter( [Lines] ) = "Accessories" ; [Sales revenue] ; "hide" )
- Now click on its header and modify the formula of the cell(=NameOf([Sales revenue]) initially) as
= If( ReportFilter( [Lines] ) = "Accessories"; NameOf( [Sales revenue] ) ; "hide" )
- Now create an alerter and put the condition as Cell Contents Equal to hide
- Click on Format and set the font color, background color as colorless and border as none
- Save the Alerter, click on the Sales Revenue Column and apply this alerter. Similarly apply on its header
- Now select any other value from the Input Control. The Margin column will be hidden
- Now select ‘Accessories’ again. The Margin column will appear
So what I have effectively done is moved the Sales Revenue column left and hidden the original Sales Revenue column based on the same condition. As a result, the Margin column appears to vanish and reappear based on Input Control selection
Note: ReportFilter() function is used to capture the selections in an Input Control or Report Filter used in a WebI Report. The reason I have used this function instead of the actual value, as in the previous case is, there is an option for selecting All Values. If I had based the logic on values of Lines, the selection of ‘All values’ would have looked like
If you are using the condition based on a query filter on Lines, you will be able to use the values in the formula as in the previous example.