Use Google to find help fast. For example, search on "xpressdox choosefromlist".

Handling calculations and Regional Settings in XpressDox

Handling calculations and Regional Settings in XpressDox

The contents of this article apply to Version 4 (and later) of XpressDox.

Enhanced calculation support has been added in Version 9. If you have that version installed and are licensed for it, rather read Arithmetic in XpressDox

XpressDox uses a technology called XSLT at the core of its assembly engine.  This is a very powerful technology and is part of what makes XpressDox able to deliver such a comprehensive set of document automation features.

There is, however, one issue that needs to be taken into account with XSLT, and that is with regard to its number handling.  If you want XpressDox (and that means XSLT) to perform calculations, then the numbers presented to it have to be in a very restricted format: they must consist of digits only with an optional leading minus sign and an optional decimal point.

Let’s look at the example «(UnitPrice * Quantity)».

This is a syntactically correct fillpoint, but it will only work correctly if UnitPrice and Quantity are in the correct format.  For example, a value of “1234.56” for UnitPrice and “12” for Quantity are correct, but “1,234.56” is invalid (because thousands separators are not permitted) as is “1234,56” (because, although it may conform with the regional settings in some areas, the comma is not permitted by XSLT).

Values entered in the XpressDox interview

There are a number of ways to ensure that the two data elements in the calculation have valid XSLT values.  Firstly, the IsNumber rule can be applied to the data elements, so that when they are captured in the interview, they conform to the correct format.

The following template snippet does this:

«Rule(UnitPrice,hard,IsNumber,,,'The UnitPrice must be a numeric value, i.e. don!'t include the currency symbol, and don!'t include a comma between thousands.')»
«Rule(Quantity,hard,IsNumber,1,100,'The Quantity must be a number between 1 and 100.')»

The extended price is: «(UnitPrice * Quantity)»

A nicely formatted extended price is «FormatNumber(UnitPrice * Quantity)»

Values included from data sources

In general, the template author is not in control of how data are formatted in data sources.  For example, using an Excel spreadsheet as a data source, numeric values which are formatted in Excel with decimal separators and 1000 separators will be formatted according to the regional settings, and not as required for XSLT.

So, in the above example, if the UnitPrice and Quantity data elements were imported from a data source, they would be in a format complying with the regional settings, and would need to be converted to “calculation compliant” format.  This is done as follows:

The extended price is: «(RegionToX(UnitPrice) * RegionToX(Quantity))»

If you were to run a template with the above line in it, and the values 1,234.56 and 12 for the two data elements, the result would be:

The extended price is: 14814.72

Notice the output format: it is calculation compliant format.  The line:

A nicely formatted extended price is «FormatNumber(RegionToX(UnitPrice) * RegionToX(Quantity))»

would render as:

A nicely formatted extended price is 14,814.72

Regional Settings

The last example of FormatNumber quoted above would work in a situation where the regional settings were the default for the US or UK (where the regional settings specify a number format which is compatible with the XSLT format), but would be incorrect where the regional settings indicated a decimal separator as ‘,’ and the 1000 separator as ‘.’.

The following would be required where the data are formatted according to regional settings, and those regional settings are not the same as US/UK:

The extended price is: «(RegionToX(UnitPrice) * RegionToX(Quantity))»

A nicely formatted extended price is «FormatNumber(XToRegion(RegionToX(UnitPrice) * RegionToX(Quantity)))»

The XToRegion function takes the result of a calculation and converts it back to regional settings-compliant format, so that it can be used as an argument to the FormatNumber function.

Note that the RegionToX function and RenderAsNumeric function in exactly the same way.  RegionToX was introduced to be symmetrical with XToRegion.

Because FormatNumber (and IncrementV, see below) needs the number to be in regional settings format, when XpressDox encounters a FormatNumber function, it applies the IsRegionalNumber validation rule in the interview to the data element being formatted. This ensures that data captured by the interview user will be in the correct format.

Other XpressDox functions

Just as the FormatNumber function above needed to have XToRegion applied to the result of the calculation, so too all other XpressDox functions that take a number as a parameter need to have that number supplied in regional settings-compliant format.

Take the IncrementV function as an example.  Here follows some XpressDox markup that calculates a total:

«SetVr('Total',0)»
«ForEach(InvoiceLine)»

«SetVr('ExtendedPrice',RegionToX(UnitPrice) * RegionToX(Quantity))»

«IncrementV('Total',XToRegion(GetV('ExtendedPrice')))»

«End(ForEach)» 
Total is: «FormatNumber(GetV('Total'))»

Notice that the second argument passed to IncrementV is converted from XSLT-compliant format to regional settings-compliant.

Leave a Reply