Loading Journal Data

Journal Excel Template

When loading journal entries via Excel a specific template must be created to determine the journal properties, the Dimensions to which the entries are being made, and the debit and credit amounts. 

OneStream reads this template using a specific Named Range which is explained later in this section. Ensure the following information is included in the Named Range.

Property Tokens

The first eleven rows of the Named Range in the Excel template must include the following token definitions:

Template Name

Enter a template name is applicable. If this is a free form journal, leave this blank.

Name

Enter the name of the journal.

Description

If desired, enter a description for the journal.

Journal Type*

Standard or Auto-reversing. Allocation is not supported for Excel or CSV import.

Balance Type*

Balanced, Balanced by Entity, or Unbalanced

Is Single Entity*

True or False

Entity Filter*

Use a Member Filter to specify the Entities used with this journal. 

Consolidation Member

Enter the specific currency or Local Member of Consolidation.

Workflow Name

Enter the Journal Workflow name. For example, if the name of the Workflow Profile is Houston, and the Adj input type is named Journals, enter Houston.Journals.

Workflow Scenario

Enter the Workflow Scenario or make the template dynamic by entering the |WFScenario| Substitution Variable.

Workflow Time

Enter the Workflow Time or make the template dynamic by entering the |WFTime| Substitution Variable. Workflow Time support two fields. The available cell immediately to the right is option representing the CubeTimeName.  This field can be used when the Scenario’s Workflow Tracking Frequency is Yearly, and the Input Frequency is Monthly. For example, the Workflow Time would be |WFTime| or 2019 and the CubeTimeName would be the period to post, 2019M7.

When Tracking Frequency is Yearly and Input Frequency is Monthly:

*See Journal Templates Data Collection for details on these Journal properties.

Dimension Tokens

Next, create the Dimension Tokens necessary to load the journal to the correct Dimensions in OneStream. The Dimension tokens need to be the column header for each data row. The standard tokens used determine the Cube, Entity, Parent, Account, Flow, Intercompany, the User Defined Members, and a Label if needed. Refer to Loading Stage Data for the syntax. The journal specific tokens are as follows:

AMTDR#

This indicates the debited amount.

AMTCR#

This indicates the credited amount.

Header Abbreviations

Static Value

Use  :[] to fix a specific Member to the entire column creating a Static Value for the specified Source Dimension. For example, F#:[None] imports the None Flow Member for every Flow row within the Named Range. This syntax applies to all Dimension Tokens.

After the Dimension Tokens are setup, enter the data in the corresponding column. 

Template Example:

The final step is to create a XFJ Named Range making sure to include the definition of each property, the Dimension tokens, and the data rows. The Named Range must begin with XFJ for OneStream to read and load it correctly. Multiple XFJ Named Ranges can be used within the template over multiple tabs.

NOTE: Loading of Journal Templates or previously exported Journal data only requires a Parent (P#) column value to be populated if the target Consolidation dimension member being updated is OwnerPreAdj or OwnerPostAdj. Otherwise, this entry can be left blank.

Named Range Example:

NOTE: The Named Range only covers each property definition.

Journal CSV Template

To set up a CSV template for a Journal, the Header and Detail values must be specified.

  1. Column A Specifies Row Type
    In the first two rows of Column A, create two Row Type Parameters specifying the Header and the Detail. In the example above, !RowType (H=Header) and !RowType (D=Detail) are used to tag the corresponding rows with H or D identifying where the Header and Detail information is located in the CSV file.

  2. Row One Specifies the Headers
    After the Header Parameter is configured, enter the Journal Column Headers. The required Journal Headers are JournalName, OrigniatingTemplateName, JournalDescription,JournalType, JournalBalanceType, IsSingleEntity, EntityMemberFilter, ConsName, WFProfileName, WFScenarioName, WFTimeName and CubeTimeName. For more details on these, refer to the Journal Excel Template.

  3. Row Two Specifies the Details
    After the Detail Parameter is configured, enter the Journal Detail Headers. The required Journal Detail Headers are JournalName, CubeName, EntityName, ParentName, AccountName, FlowName, ICName, all UDNames, DebitAmount, CreditAmount, and LineDescription. For more details on these, refer to Journal Excel Template.

  1. Header and Detail Tags
    The Journal data is driven by how each row in column A is tagged. Any rows tagged with H load as the Headers and any row tagged with D load as the details.

Loading Journal Details via Workflow

After the template is configured, users can load it directly into OneStream during the Workflow process. While the Journal Input type is selected, click the icon in the Journal toolbar. This lets you select the desired Excel or CSV template and load it into OneStream. After the file is loaded, the journal line items will appear in the journal and the user can save it to the Cube.

Extracting/Loading Journal Details via BRApi

Users can export journal details into a CSV or XSLX file and load journals from a CSV or XSLX file by configuring an Extensibility Business Rule. This allows journal details to be extracted from one application and loaded into another. Users can also extract the journal, make changes, and re-load. 

Extract Journals to CSV

Use BRApi.Journals.Data.ExportJournalstoCSV and define the session, filepath, Workflow Profile, Scenario, Time Filter, and Journal Status.
BRApi.Journals.Data.ExportJournalsToCsv(si, filePath, "Houston", "Actual", "T#|WFYear|.Base", "Posted") 

Extract Journals to XSLX

Export journals associated with defined parameters to an XSLX file using the ExportJournalsToXSLX BRApi. It is sometimes easier to read and modify journal data in an XSLX format as compared to CSV files that are generated by the ExportJournalsToCSV BRApi. Use the parameters listed in the following table.

Parameter Description
si Session information
serverFilePath Name and location to save the file
wfProfileName Name of the Workflow Profile
wfScenarioName Name of the Scenario
wfTimeMemberFilter

Filters workflow profile times. For example, if the scenario's Workflow Tracking Frequency is set to Quarterly, and Input Frequency is set to Monthly, the filter searches for journals associated with the Workflow Tracking Frequency.

Time must be prefixed with T#. You can type a comma separated list of times such as T#2022H1, T#2022H2.
journalName Filters journals that contain the specified text
journalStatus Status of the journal. You can search by status or use All to return all statuses. You cannot type a comma separated list.

The following code example uses the ExportJournalsToXslx BRApi:

Load Example

Use BRApi.Journals.Data.ImportAndProcessJournals and define the session, filepath, and journal tasks to complete upon loading the journal details.
'BRApi.Journals.Data.ImportAndProcessJournals(si, filePath, save, submit, approve, post, unpostAndOverwrite, throwOnError)