Templates
The location where standard Excel templates are stored for assignment to Global Defaults and Reconciliation Definitions.
NOTE: If you upgrade using Uninstall Full, custom templates are removed and replaced with default templates. To save custom templates for future use, save the Excel files outside of OneStream Financial Close and upload them after you upgrade. If you upgrade using Uninstall UI, custom templates are retained.
IMPORTANT: If using multiple instances [rcm].[ReconItem] will reflect the instance in use. For example, [rcm1].[ReconItem].
If you are using any of the Templates or Exports listed below from a prior release version, please update the file to reflect the new table names in the following areas:
IMPORTANT: If you are using a Template or Export from a prior release version, you will need to unhide certain rows within the template to update to the new table names.
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 and 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. The following example is the design view.
NOTE: In this template, the rows typically 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 you download 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.
The following examples are the MultiPeriodTemplate.xlsx and MultiCurrencyMultiPeriodTemplate.xlsx. Note that the Named Range of xftRecon is selected and rows are shown that are typically hidden (rows 16–19 in the MultiPeriod template and rows 19–22 in the MultiCurrencyMultiPeriod template). 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.
MultiPeriod Template
MultiCurrencyMultiPeriod Template
Note the following information for the highlighted items:
Reconciliation ID: A Reconciliation ID column with a drop-down menu is included in the following templates: Basic, MultiCurrencyMultiPeriod, MultiCurrency, and MultiPeriod. For account groups, the drop-down menu displays the related child reconciliations associated with the group as well as (Group); for individual reconciliations, the drop-down menu is blank. When you import an item with the template, the Reconciliation ID from the template will display in the Detail Items grid on the Reconciliations page.
Child Reconciliations tab: The Basic, MultiCurrency, MultiPeriod, and MultiCurrencyMultiPeriod templates include a Child Reconciliations tab that displays related balances for each child reconciliation in an account group.
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 when imported. Include as many rows as periods of data are necessary, even if spanning years.
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.
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 when imported into OneStream. Further, only current period balances will be translated on the template.
Multi-currency Solutions: Templates that support multiple currencies are included for solutions that have multi-currency enabled. In the MultiCurrency template and MultiCurrencyMultiPeriod 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 uses 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 MultiPeriod templates, informational translated balances only display for the current month because future rates are not available at the time of template creation.
