Exago Logo
Search
Generic filters
Exact matches only
Exago Logo

Custom Function Case Study #1: Formatting Large Numbers

by | Maintaining BI, Reporting

Custom functions and other extensibility features are routinely underrated in the BI software world. Like insurance, the ability to alter third-party software with custom code doesn’t seem valuable until, one day, you suddenly need to be able to do something the application doesn’t natively do. 

That’s when you deploy the custom-code safety net — if your BI solution has one.

And not all of them do. They’re actually surprisingly rare, given that no embedded business intelligence solution is capable of anticipating all data manipulation and calculation needs across all verticals. When your average embedded BI customer encounters a need for new functionality, their only option is to submit an enhancement ticket to the vendor. 

And wait. 

Sometimes indefinitely.

But custom code extensions give those customers a second, DIY option. Not only do they get to the desired outcome, but they get there in a matter of hours instead of weeks, months, or years. 

This case study series tells their stories. First up: a fintech SaaS vendor finds a way to format large numbers to user specifications.

The Challenge: Really Large Numbers

A fintech SaaS vendor came to our Services team with a messy-looking report they wanted to simplify. The output looked something like this:

Financial report before the use of custom functions

They were fine with displaying currency in whole-pound amounts if there weren’t too many digits involved, but the really large numbers were making the output difficult to read.

To achieve the desired output, they needed to be able to:  

  1. Round numbers to the nearest multiple of 1K, 1M, or 1B, depending on the size of the number being rounded. 
  2. Conditionally define the rounding threshold for a field (e.g., only numbers greater than 100K are rounded).
  3. Round numbers between 100,000 and 999,999 to zero decimal places and numbers at or greater than 1M to three decimal places.
  4. Specify whether the currency is in GBP (£) or USD ($).
  5. Round both positive and negative numbers in this way.
  6. Display blank values as zeros.

Exago BI comes with seven different rounding functions, but none of them meet all the requirements of this vendor’s use case. The Ceiling() function only rounds up, and the Floor() function only rounds down, so they’re out. Even() and Odd() only round to even and odd integers, respectively, so they don’t apply. Fixed() and Round() only deal in decimal places, and Int() rounds to the nearest whole number but not to the nearest multiple of significance.

Together, these seven functions meet 95% of rounding use cases. For everything else, there are custom functions.

Solution

This SaaS provider decided to spend a few of their complimentary service hours and have an Exago BI consultant write the custom function for them. The resulting function, NumberSymbolSuffix(), rounds the input number to the nearest multiple of interest and appends both a currency symbol and number symbol suffix. The function takes three arguments:

  1. The input number. This could be a field, static text, or the result of a calculation.
  2. The rounding threshold. The five input options for this argument are K, 100K, M, 100M, and B. Numbers greater than or equal to this argument will be rounded. Numbers in the thousands will be rounded to the nearest thousand, numbers in the millions will be rounded to the nearest million, and numbers in the billions will be rounded to the nearest billion. Rounded values of 1M and above will be shown to three decimal places.
  3. Currency symbol. (Optional.) Appends the currency symbol of choice to the rounded number.

Result

Now, when the SaaS provider runs their reports using the NumberSymbolSuffix() function, which appears in the UI alongside the application’s native functions, the output looks tidy and readable:

Financial report with custom function applied

In this report, values above and including 100,000 are rounded to the nearest 100K with all smaller numbers rounded to the nearest pound. Blank values display as zeros, and currency symbols appear to specification. 

Thanks to custom function extensibility, the SaaS provider was able to declutter its cramped tables and delight fintech customers with clean, legible reports.

BI Newsletter
Sign Up For The Exago Newsletter

Stay up-to-date on all things SaaS and analytics with fresh content each month.

Ready to see Exago BI in action?

Request a Demo

Please fill out the form and we’ll be in touch to arrange a personalized demo.

Just want a quick overview? Check out our webinar.

Share This