Conditional formatting can be extended to dimensions, as it is done for measures. Consider a table having dimension values – ‘Overdue’ and ‘On Time’. We may want to conditionally format the dimension, so that ‘Overdue’ is displayed in red and ‘On Time’ is displayed in green. These kinds of requirements often come up in scenarios like in Accounts Payable/Receivable KPIs, where we would quickly want to identify which of them are overdue.

VIsual BI Extensions (VBX) plugins for Lumira Designer, offer a variety of 100+ components which can be leveraged to create powerful visualizations. They are feature rich, and they can be customized using custom theme codes to make them even more powerful.

VBX Advanced Table is one such component from the suite. In addition to displaying data in a tabular/crosstab format, it offers a variety of functionalities such as Pivoting, run-time filters, and aggregation type change. ScriptBox is another component, which provides the flexibility to customize components by writing Javascript code. The example mentioned above (Overdue/Ontime), can be achieved by using a combination of VBX Scriptbox and Advanced table.

 

Initial Setup

Launch Lumira Designer and open the application containing the table and the dimension for which conditional formatting needs to be applied to.

Add VBX Advanced Table to the application, and feed the dimensions and measures that are required. Also, add the VBX ScriptBox to the application. The ScriptBox component does not occupy any space during run-time.

conditionally-format-dimension-vbx-advanced-table-using-vbx-scriptbox-lumira-designer

 

Adding the code

Use the following code after removing all spaces, comments and modify it to suit your needs. Place it in an event such as onStartup or onClick of a button, ensuring the table gets displayed when the event is called.

conditionally-format-dimension-vbx-advanced-table-using-vbx-scriptbox-lumira-designer

 

Code Explanation

The code being used here is jQuery, for the purpose of simplicity. We create an anonymous JS function, which contains the crux of our formatting implementation. We get the column ID([col-id=””]) for which we need to conditionally format the data.

From the column ID, we get its children and set its background to red or green based on the condition. The Trimfunction removes any spaces before and after the text. $(this).css(property,value) sets the CSS property with the new value passed to the function. We set it to be called every second so that it gets updated even after changing its state (like passing filters, scrolling).

 

Result

conditionally-format-dimension-vbx-advanced-table-using-vbx-scriptbox-lumira-designer

This works every time since we are applying the change at the DOM level of the application. The if-else part can be altered based on requirements. Keep in mind to call the function only once by creating a Boolean variable, and checking to see if it has already been run.

 

Conclusion

Thus by combining VBX Advanced table with VBX ScriptBox, we have added a custom capability that ensures that business users can quickly identify the significant rows.

Subscribe to our Newsletter

5920 Windhaven Pkwy
Plano TX 75093.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com