Conditional Column Hiding in BO 4.0
I had earlier posted about column hiding in BO 3.1 using Alerters. BO 4.0 offers a more flexible means of column hiding. This is actually a component hiding feature that works for table/chart etc.
Let us take the previous example, where we have Store Name, Lines, Quantity Sold and Sales Revenue with a prompt for Product
And Like Previous time, I want to display Sales Revenue only when Lines is “Accessories”
To achieve this
- First we need to create another table with Store Name, Lines and Quantity Sold in it
- Next, we need to overlap these two tables manually or using relative positioning
- Right click on the table containing Sales Revenue and select Format Table
- Under General, Check the Hide When Following Formula is True and enter
Lines <> ”Accessories”
This would mean that this table will be hidden when Lines is anything else than Accessories
- Similarly right click on the other table(containing only Quantity Sold), go to Format Table and enter the Hiding condition as
Lines = ”Accessories”
- Now refresh the report and enter the products which fall under Lines, other than accessories. The table containing only Quantity Sold will be displayed while the other table will remain hidden
- Again refresh the report with products belonging to Accessories
The table with both Quantity Sold and Sales Revenue will be displayed while the other table will be hidden
Thus, we are achieving the column hiding by means of two tables which are dynamically hidden based on condition. The advantage of this method is, no matter where the column to be hidden exists(at the end or in the middle), one does not need any additional formula to achieve it. The other table simply needs to be created with all the columns except the one to be hidden, in the desired format.
This conditional hiding can be applied to any report component, for e.g charts, which was not possible in BO 3.1
However handling multiple tables when there are a number of hiding conditions may be confusing. In that case a combination of the alerter method and conditional hiding method may be advisable.