Formatting Label Parameters with Thousand Separators Using Expressions

Published:

When presenting numerical data in Bold BI dashboards, it’s often necessary to format numbers with thousand separators for better readability. Although the Text widget in Bold BI may not offer a direct formatting option, there is a workaround that can be used to achieve this. Below are the steps to format label parameters with thousand separators in Bold BI dashboards.

Steps to Format Label Parameters with Thousand Separators

Open Your Dashboard: Start by opening the dashboard where you want to apply the number formatting.
Add or Select a Text Widget: Choose an existing Text widget or add a new Text Widget to your dashboard.
Create a Formatting Expression: Depending on your data source, create an expression to format the numbers with thousand separators.

SQL Server Formatting

For SQL Server, you can use the following expression to format a field as a money type, then convert it to a string without the decimal part:

REPLACE(CONVERT(VARCHAR, CONVERT(MONEY, SUM([YourField])), 1), '.00', '')

Replace [YourField] with the actual field name you want to format. The REPLACE function removes the ‘.00’ from whole numbers.

PostgreSQL Formatting

In PostgreSQL, use the TO_CHAR function with a number format to include a thousand separators:

TO_CHAR(SUM([YourField]), 'FM999,999,999,990.00')

Replace [YourField] with the actual field you want to format. The pattern ensures the correct placement of a thousand separators.

MySQL Formatting

For MySQL databases, the FORMAT function can be used:

FORMAT(SUM(`YourField`), 2)

Replace [YourField] with the actual field you want to format. This will format the sum of the field with a thousand separators and two decimal places.

Apply the Expression: Input the created expression into the Text widget as a label parameter. Refer to the following documentation for more details about label parameter.
Publish and Visualize the Results: After publishing the dashboard, ensure the Text widget displays the numeric value with a thousand separators.