Templates

The location where standard Excel templates are stored for assignment to Global Defaults and Reconciliation Definitions.

Account Reconciliations comes with a few example templates. You can create custom templates and upload them here.

Upload: Add a new Excel template to this solution.

View: Opens a read-only copy of the selected template.

Delete: Deletes a template if it is not assigned to any reconciliations.

Replace: To change the template assigned to any existing Reconciliation Definition, select the Original Template, the New Template, and then click Replace. This change occurs in all Reconciliation Definitions. When viewing the Basic Template that comes with Account Reconciliations, there are Substitution Variables replaced with values from the reconciliation being processed. This is the design view below.

NOTE: In this template, the rows normally hidden are shown along with the Excel Named Range being imported.

The references to Substitution Variables in the top part of the Excel Template relate to Substitution Variables available in Account Reconciliations. These settings are replaced when the user downloads the template at runtime to substitute with values from Account Reconciliations. Other Substitution Variables can be used here, making templates flexible.

For details on designing this type of Excel template to load into OneStream , see Data Collections in the Design and Reference Guide.

Here is how this appears as a multi-currency S-Doc Template, with the MultiPeriodTemplate.xlsx being the example template provided. Note that the Named Range of xftRecon is selected and rows 16-20 are unhidden, but are normally hidden. This Named Range would need to be extended if additional rows are needed for import. The top left cell of such a Named Range must be the cell with the word Application in it and the bottom right cell of the range should be the last column of the last row to be imported. It is acceptable to include additional empty rows in this range. OneStream can read in multiple ‘xft’ Named Ranges on one or more sheets of the same Excel workbook. Save this file in Excel xlsx format.

A few column tokens of note:

Wtk: Workflow Time Key – These OneStream time periods contain an exclamation point as the first character (for example, !2018M1). This helps OneStream look up the time key for this period and store that value in the database upon import. Include as many rows as periods of data are necessary, even if spanning years.

Item Amounts (Multi-currency Solutions): These columns are automatically translated within the template. The template is pulling FX rates for the period in order to translate the Detail Amount balances. The translated amounts are for display purposes only, because translation of the Detail Amounts takes place upon import into OneStream. Further, only current period balances will be translated on the template.

BookedPeriod: The |WFTime| Substitution Variable is used, which means that every row imported lists the period it was booked as the same period that was processed at the time.

TimeStamp: The date and time this template was imported, which will be the same for each time period.

NOTE: If a template is used to create a supporting Detail Item, that support type, either T-Doc or S-Doc, must be attached to the reconciliation or the reconciliations may not be completed.

Multi-currency Solutions: Templates that support multiple currencies are included for solutions that have multi-currency enabled. In the Multi-currency Basic Template and Multi-currency Multi Period Template, columns are included to allow for Detail Amount, Detail Currency Type, Account Balance, and Reporting Balance.

For Single Currency reconciliations, only Local balances are loaded and as such, if a reconciliation is set to Single Currency, the templates that do not include Multi-Currency functionality must be used. The currency type for each currency level appears within the template and FX rates are automatically pulled in order to show the conversion that will occur on import. Note that OneStream still utilizes the translation functionality within the system to translate Detail Amounts.

The translated balances of Account, Local, and Reporting for the Detail Items are for informational purposes only. However, if an override is necessary for any of the levels, OneStream accepts the overridden amount upon upload of the template if overrides are permitted on the reconciliation. To identify balances that were overridden within the template, amounts overridden display bold and the override alert appears on the face of the reconciliation.

For Multi-Period templates, informational translated balances only display for the current month because future rates are not available at the time of template creation.