Uploading Reported Financial Data

When you execute the Data Collection task in the data monitor you can download a data upload template for reported data. You have the option to download the template as a CSV file or a XLSX file.

In the upload file template, there are several special characters that are used by the system:

• * – Comment
• P – Parameter
• D – Data structure
• 2 – Data rows.

The Header row (marked with D in the first column) includes all mandatory input fields for the data import. You can add fields from theAdditional fields section to row D, if necessary.

If you add AmountInTransactionCurrency as additional field, you also have to provide TransactionCurrency.

If you add QuantityInBaseUnit, you also need to provide BaseUnit.

LocalCurrency is derived from the currency assigned to the consolidation unit automatically.

Group Currency is derived from the currency assigned to the consolidation version.

In Value row (marked with '2' in the first column) you enter your values for individual fields or leave it empty.

You can use the Manage Substitution and Validation Rules - Group Journal Entries app to create and use substitution and validation rules to fill fields automatically, or to substitute or validate field values based on defined preconditions and rules. If you want to restrict a rule to a specific process, you have to define a precondition for the rule on the document type level. If you don’t specify a document type, the rule applies to all processes, such as manual posting, API import, and upload.

In addition, there are the following parameters that are included in the file as well:

• Periodical – import period or YTD data
• Update Mode – delete, overwrite, or add
• NSEP – number format
• DOCTY – document type used for this file
• TOTALS - Totals Mode
• CUSTSIGN - Custom Sign Convention.

The flexible upload file parameters are described in detail as follows:

• Periodical
  • If you enter x, the data of the file is interpreted as period data. The data of the file is written to the period specified in the file with no additional calculation.
  • If you leave the Periodical field blank, the data of the file is interpreted as cumulative (year-to-date) data. The system subtracts the cumulative data from the previous period and stores the periodic value.
• Update Mode
  • Enter 1 to delete all: All existing records in a block (consisting of ledger, consolidation chart of accounts, version, fiscal year, period, and consolidation unit) are reset to zero before loading the new data.
  • Enter 2 to overwrite: The deletion replaces any existing data that was uploaded using the same combination of version, period, consolidation unit, and document type. The system does not delete any data physically in the system. Instead, it calculates a delta between the new data and any data that was uploaded previously, then creates new records for the delta data.
  • Enter 5 to add: Only the uploaded records are added to the existing records. Any existing records including those which share the same item and all of the same subassignments are unchanged.
• NSEP - Number separator

  If you use the CSV file format you have to define the decimal separator in the input file as follows:

  • Value 1 stands for ‘.‘ (dot) as the decimal separator. Example: number format 1,000.00 .

  • Value 2 stands for ‘,‘ (comma) as the decimal separator. Example: number format 1.000,00 .

  If you use the XLSX file format, the NSEP parameter is ignored. In this case you have the following options to define the decimal separator:

  • Either use the decimal separator settings of your system in the input file.

  • Or use ‘.‘ (dot) as the decimal separator.

• DOCTY - Document Type

  Optional parameter that defines to which document type the data is written. Only document types with the following settings are allowed:

  • Posting level 00, reported data.

  • Automatic posting.

  • Minimum of one currency or quantity allowed.

  • Deferred tax calculation disabled.

  If you enter invalid characters in this field, errors will occur when the file is uploaded.

  If you do not provide the parameter, e.g. line is set to be ignored by adding an asterisk before P in column A.

  Or if the field is left empty, then the default value will automatically be activated.

  The default value for flexible upload of document types is the alphabetically first value of posting level 00 document type IDs. In most cases, this value is 00.

• TOTALS - Totals Mode

  You can use the Import Reported Data app to upload reported data with some missing dimension values. The total value of such a dimension is then totaled without changing the existing values of the same dimension.

  This is necessary, for example, to load a provisional balance sheet without breaking it down into subitems that can contain the details of a functional transaction type. The balance carryforward has already filled the opening subitem and this value will not change when the balance sheet is loaded. Instead, the delta is calculated and saved in a standard transaction type.

  See see following example in the documentation:https://help.sap.com/docs/SAP_S4HANA_CLOUD/90c07e91c7a64f328be3fd6b48955b13/8c68c6bfaf4d4790b9738b1437969eb9.html?locale=en-US&state=PRODUCTION&version=2602.500&q=Import+reported+data .

• CUSTSIGN - Custom Sign Convention

  The SAP Sign logic interprets positive amounts for each FS item as debit amounts and negative amounts as credit amounts. For instance, revenue is stored as a negative amount.

  When the Custom Sign Convention logic is set up, you can define your own sign logic (Custom Sign Convention logic) instead of the SAP Sign logic. At FS item type level and at FS item level, you can specify if the FS items should be posted as debit amounts or credit amounts.

  If you activate the Custom Sign Convention logic, you can define which sign convention is applied to the external input values using a specific parameter in the upload file.

  • If you set the parameter to X, the Custom Sign Convention logic is used.
  • If you leave the parameter blank, the SAP Sign logic is used.

In case there’s new data in the source system, the flat file can be imported again. There are three update options in the Parameter section of the flat file:

• Update Mode 1 - Delete Mode
• Update Mode 2 - Overwrite Mode
• Update Mode 5 - Add Mode.

If the second source file represents the correct result for all FS items for CU01 (consolidation unit 01), use Update Mode 1 - Delete Mode:

• Cash is increased by 500. The resulting net amount is 120,500.
• AR (accounts receivable) is decreased by 25,000 so the net amount is 0.

If the second source file represents the correct result for FS item 111100 and existing FS item 121100 values should be retained, use Update Mode 2 - Overwrite Mode:

• Cash is increased by 500. The resulting net amount is 120,500.
• AR (accounts receivable) is not changed.

If you use Update Mode 5 - Add Mode:

• 120,500 for Cash in the second file is added to the initial Cash value of 120,000. This totals cash to 240,500.
• Existing records are not changed.

You can import reported financial data for one or several periods into group reporting. This is done on the specific posting level 00, which includes line check logic. The document type is specified in the upload file.

Alternatively, you can use posting level 0C. This level is useful for fixing incorrect data released from accounting via the Release Universal Journal task. It's also handy for migrating data from legacy or external systems.

The uploaded data is written into table ACDOCU along with a document number.

To perform the import of reported data, follow these steps:

1. In the data monitor, choose Data Collection for the Consolidation Group → right-click and select Update.
2. In the Import Reported Data screen, choose Browse and select your file.
3. Choose Import.

When uploading you can select the Test Ignores Status option in order to import the file in a Test Run excluding task and period status check. You can use this option, for example, to simulate the execution when the period is closed.

You can also import the data in a Test Run with checking the task and period status.

You can choose Import and execute the update run, if the Test Ignores Status is not selected.

To upload reported financial data, the following prerequisites apply:

• Open all periods that are included in the upload file.
• Unblock the Data Entry task for the respective consolidation units.

When the import of reported data was run, you will get a notification as described in the figure above. Clicking the notification message opens the Task Log.

As we proceed through the corporate close process, we'll need to run reports to check the results and other items. Most reporting is performed with the Group Data Analysis app.

With the Group Data Analysis app, you can display different views of consolidated data. You can use a group view based on the group structure that includes consolidation groups and units. Alternatively, you can use a hierarchy view based on hierarchies of profit centers, segments, and consolidation units. By selecting different FS item hierarchies within the app, you can generate consolidated financial statements.

From any measure, you can drill through to the Display Group Journal Entries - With Reporting Logic app to view the posting details at the line item level.

The Group Data Analysis report includes the following options:

1. Defer layout update
2. Bookmark your filter and layout settings
3. Open the filter area
4. Navigate to line items
5. Download to Microsoft Excel
6. Hide the navigation panel
7. Select any dimension in ACDOCU.

Now that you've learned some basic reporting features, let's see how a group data analysis report can be used to analyze imported data.

In the following image, the two source records for the 111100 cash FS item: 222,129 for opening balance/transaction type 900 plus -1,800 for variation / transaction type 915 = 220,239 in the ACDOCU table.

To validate imported data:

1. Select the document type used in the source file.
2. Select posting level 00.