IBM Planning Analytics has the ability to export data to flat files with the use of the ASCIIOUTPUT and TEXTOUTPUT functions. However, these two functions will only accept string variables. That means that numeric data will first need to be converted to string before exporting. Most developers know about the NumberToString function, which converts numbers to string, but there’s another function that is more powerful.

NumberToString does not apply any formatting to the numbers. This will work fine if the export is meant to be consumed by another system. However, if your export has an audience, you may want your report to have consistent and pleasing formatting. Here’s where the NumberToStringEx function can come in handy.

The NumberToStringEx function has four parameters: Value, Number Format, Decimal Separator, and Thousands Separator. The number format uses the same structures as the custom formatting in Excel.  Here’s an example of the function with the results:

NumberToStringEx( 1750.34892, ‘$#,0.00’, ‘.’, ‘,’)

$1,750.35

As you can see, the NumberToStringEx function eliminates the need to round your numbers prior to conversion as well as concatenating special characters after conversion.  

NumberToStringEx also allows for dual formatting of positive and negative numbers. Just like in Excel, you will need to separate the two using a semicolon. This eliminates any IF conditions you’ll need to use to format numbers if you had used the NumberToString function.

NumberToStringEx( -1750.34892, ‘$#,0.00;($#,0.00)‘, ‘.’, ‘,’)

($1,750.35) 

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Process Editor Function Help

IBM Planning Analytics Tips & Tricks: Excel OFFSET Function

IBM Planning Analytics Tips & Tricks: The SCAN Function