Export to reporting solutions job output

This document contains detailed information about the output generated by the Export to Reporting Solutions job. For more information on transferring consolidated data to reporting solutions, refer to the Move consolidated data section.

Consolidated Closing Data

Output

  • Output Table Name Format: {CustomerCode}_ClosingData

  • Output Table Name Format: {CustomerCode}_ClosingData{_DimensionCode} (if exported with dimensions)

  • Recreates the table each time the export process is run

Columns

  • Account

  • Classification

  • Company

  • Counterparty

  • Flow

  • Reporting Currency

  • Time

  • Version

  • Amount

  • JournalEntry

  • JournalSequence

  • Year

  • Month

  • ConsoCode

  • DataType

  • JobCode

Dimension Data

  • Dimension Group / DimGroupCode

  • Dimension1 / Dim1Code

  • Dimension2 / Dim2Code

  • Dimension3 / Dim3Code

Dimension Structures

We extract 7 different sets of dimension structures (data sets). Each data set has its own output table and query.

Data sets

  • Flow

  • Currency

  • Company

  • Partner / Counterparty

  • Account

  • Journal

  • Dimension Group

  • Dimension Tree

Flow

  • Output Table Name Format: {CustomerCode}_FlowList

  • Recreates the table each time the export process is run

Columns

  • ConsoCode

  • Key

  • Name

  • Parent

  • JobCode

Account

  • Output Table Name Format: {CustomerCode}_AccountList

  • Recreates the table each time the export process is run

Columns

  • ConsoCode

  • Key

  • Name

  • AccountType

  • DebitCredit

  • TCMethod

  • Aggregation

  • Format

  • Parent

  • JobCode

Company

  • Output Table Name Format: {CustomerCode}_CompanyList

  • Recreates the table each time the export process is run

Columns

  • ConsoCode

  • Key

  • Name

  • Parent

  • CountryCode

  • SourceCurrency

  • JobCode

Counterparty

  • Output Table Name Format: {CustomerCode}_CounterPartyList

  • Recreates the table each time the export process is run

Columns

  • ConsoCode

  • Key

  • Name

  • Parent

  • CountryCode

  • SourceCurrency

  • JobCode

Journal

  • Output Table Name Format: {CustomerCode}_JournalList

  • Recreates the table each time the export process is run

Columns

  • ConsoCode

  • Key

  • Name

  • Parent

  • JobCode

DimensionGroup

  • Output Table Name Format: {CustomerCode}_DimGroup

  • Recreates the table each time the export process is run

Columns

  • ConsoCode

  • DimensionGroupCode

  • DimensionGroupName

  • Dimension1Code

  • Dimension2Code

  • Dimension3Code

  • JobCode

Dimension Tree

  • Output Table Name Format: {CustomerCode}_DimTree

  • Recreates the table each time the export process is run

Columns

  • ConsoCode

  • DimensionGroupCode

  • DimensionCode

  • DimensionDetailCode

  • ParentDimensionDetailCode

  • Type

  • ChildCount

  • Level

  • OrderNbr

  • JobCode

Currency

  • Output Table Name Format: {CustomerCode}_CurrencyList

  • Recreates the table each time the export process is run

Columns

  • ConsoCode

  • Key

  • Name

  • JobCode

Pre - Consolidated /Local Data

Output

  • Output Table Name Format: {CustomerCode}_LocalData

  • Output Table Name Format: {CustomerCode}_LocalData{_DimensionCode} (if exported with dimensions)

  • Recreates the table each time the export process is run

Columns

  • Account

  • Classification

  • Company

  • Counterparty

  • Flow

  • Reporting Currency

  • Time

  • Version

  • Amount

  • LocalAmount

  • Local currency

  • GroupAmount

  • GroupCurrency

  • AccountType

  • AverageRate

  • ClosingRate

  • JournalEntry

  • JournalSequence

  • Year

  • Month

  • ConsoCode

  • JobCode

Dimension Data

  • Dimension Group / DimGroupCode

  • Dimension1 / Dim1Code

  • Dimension2 / Dim2Code

  • Dimension3 / Dim3Code

Detailed Transactional Data

Output

Output Table Name Format: {CustomerCode}_ConsolidatedDrillAcross

  1. Deletes the existing data in the target table that matches the period(s) and customer code that are being exported.

  2. Appends the new data to the table.

Columns

  • Account

  • Classification

  • Company

  • Counterparty

  • Flow

  • Reporting Currency

  • Time

  • Version

  • Amount

  • JournalEntry

  • JournalSequence

  • Year

  • Month

  • ConsoCode

  • JournalText

  • CreationUserLogin

  • ModificationUserLogin

  • ModificationDate

  • JournalDescription

  • JobCode

Additional Columns (release 2024.2.1)

This release includes additional columns to give more information, context, and background on the origins of data for the following data types:

  • Consolidated Data

  • Local Data / Locally Adjusted Data

  • Detailed Transactional Data

Columns

  • Period: Information of the originating period. Values are:

    • Current Period

    • Previous Period

  • Source: Information about the source of the data. Some examples are:

    • Closing Amounts

    • Opening Balance for Balance Sheet accounts

    • Flow activity

    • Dimensions activity

  • IsReverse: Flag indicating whether the corresponding line is a duplicate with the amount reversed. Values are 0 (no) or 1 (yes).

  • Reverse_Flow: Contains the original flow code when a line is reversed (a reversal usually results in the flow being set to the Null Member value). This field allows tracking of the original flow line.

  • Reversed_DimGroupCode, Reversed_Dim1Code, Reversed_Dim2Code, Reversed_Dim3Code: Same logic as the Flow / Reverse Flow

  • PrevPeriodCode: The code of the Previous Period if selected on the job options to export, or 'YTD' (default) if none is selected.

Additional Columns (release 2024.4.0)

This release includes additional columns for easy reference of amounts and currencies for Local Data / Locally Adjusted Data.

Columns

  • LocalAmount

  • Local currency

  • GroupAmount

  • GroupCurrency

  • AccountType

  • AverageRate

  • ClosingRate

Cashflow information

Adjusted Bundles

Output

Output Table Name Format: [CustomerCode]_ViewDataCashflow_AdjBundles

Columns

  • ConsoID

  • ConsoCode

  • Time

  • CompanyCode

  • CompanyName

  • Local Currency

  • Local Amount

  • LineNr

  • LineDescription

  • CustomReportCode

  • CustomerCode

  • JobCode

  • DataType= CashFlow

Group Adjusted Bundles

Output

Output Table Name Format: [CustomerCode]_ViewDataCashflow_GroupAdjBundles

Columns

  • ConsoID

  • ConsoCode

  • Time

  • CompanyCode

  • CompanyName

  • JournalType

  • JournalCategory

  • Group Currency

  • GroupAmount

  • LineNr

  • LineDescription

  • CustomReportCode

  • CustomerCode

  • JobCode

  • DataType= CashFlow

Consolidated data by POV

Output

Output Table Name Format: [CustomerCode]_ClosingData_POV

Columns

  • ConsoID

  • ConsoCode

  • Source

  • IsReverse

  • Account

  • AccountDescription

  • Flows

  • Amount

  • Company

  • Partner

  • Journal

  • JournalName

  • POV

  • JobCode

  • DataType =POV