Loading Data With Actual Data Templates

Using this approach, data is loaded into the Planful suite via data input templates in which data is manually entered. Administrative users can design and publish the data input templates, which are then accessed by end users who input data into the templates.

This approach is recommended when the data needs to be loaded into the Planful suite from several disparate General Ledgers with significant dissimilarities in the chart of accounts and the data translation mappings from the subsidiary general ledger data to the common reporting chart of accounts do not exist or would be hard to create.

How to Load Data With Actual Data Templates?

To load data with Actual Data Templates, perform the following steps:

Create a New Actual Data Template:

To create a new actual data template, follow the steps below:

1. Navigate to Maintenance > Data Integration > Actual Data Templates.
2. Click Add.
   
   Note:

   The top menu bar options are displayed based on the access settings configured on the Data Configuration Securitypage.

   • If the user is assigned Create & Edit access, they can view and use Add, Edit, Delete, Template Setup, and Filter options.
   • If the user is assigned Edit access, they can view and use Edit, Template Setup, and Filter options.
3. Enter the template name.
4. Select template parameters. These parameters are common to all new templates, unlike the COA Segment members which are populated based on the segments defined on the Configuration Tasks page.
   • The scenario is fixed as you can only load data to the Actual scenario.
   • Select the reporting currency to load data in local or common currency.
   • Select Yes for Apply Credit Debit if the data contains credit account numbers in positive form, else select No.
   • For Data Type, select MTD to allow the opening balance and net change accounts for the data load period for all accounts (balance and flow), select YTD to allow a year to date amount for each period for all accounts, or select Account Based to allow a mixed format with YTD amount for balance accounts and net changes for flow accounts.
   • For Enable Opening Balance, select Yes to include an opening balance column in the Data Load interface.
   • Display Line Code - Select Yes to display the code column details.
     
5. Select COA Segment members that you want to be included in the ETL (extract, transform, and load) data file/ data load definition.
6. Click Save.
   The user will be directed to the Actual Data Template List page.

Input Data to the Actual Data Templates:

To input the data into the Actual Data Templates, follow the steps below:

1. Navigate to Maintenance > Data Integration > Actual Data Templates.
2. Select the template to which you want to input the data.
3. Click Template Setup.
   
4. To design the template with a structure (data lines), the user can set the line type of each line in the template by selecting the appropriate type.

   Line Types:

   • LINE (L): Allows data entry or calculations to be performed for the row. Reference and destination accounts may be applied to a line.
   • CALC ©: Allows calculations to be defined for a row within the template design, preventing budget users from modifying the formula. Destination accounts may be applied to a calc line.
   • HEADER (H): Allows formatting sections, or providing additional spacing.
     If you have opted for enhanced mapping, click here to learn more.
5. Once the line type is selected, click the Add Line Item icon. To insert more rows, increase the value in the field right to the Add Line Item icon.
   
   Note:

   When users apply any format from the available options, it applies to the selected lines.
   
   
6. Mapping options help to design the layout or structure of the template. To map template rows to segments, select any of the Account Mapping categories from the following options:
   • Destination Account: Destination accounts are used to save the budget or forecast data entered or calculated in a selected budget template against a General Ledger account. These lines are labeled DA in the first column.
   • Reference Account:  Populate the template line by referencing an account from another template. For example, payroll accounts may be looked up from the Workforce template. These lines display with RA.
   • Reference Cube: Opens the Rule page to look up data from the cube. For example, you can look up total revenue for all sales departments within a legal entity. These lines display with RC.
   • Mass load: The Mass Load page is enabled where you can select members to load based on the dimension member parameters you select. These lines are also labeled with L.
     
7. Click Save.

   
   

How to Edit the Actual Data Template?

To edit data in Actual Data Templates, perform the following steps:

1. Navigate to Maintenance > Data Integration > Actual Data Templates.
2. Select the Template that you want to modify and click the Edit icon.
   
   
   Note:

   The top menu bar options are displayed based on the access settings configured on the Data Configuration Security page.

   • If the user is assigned Create & Edit access, they can view and use Add, Edit, Delete, Template Setup, and Filter options.
   • If the user is assigned Edit access, they can view and use Edit, Template Setup, and Filter options.
3. Make the required changes on the Edit Template page and then click Save.

Description of Fields

Select mapping options and design the layout or structure of the template.

Adjust column widths by dragging the lines (similar to an Excel spreadsheet). Once saved, the column widths are saved to the template. Column widths are not user specific.

Mappings

Map template rows to segments. Select one of the following options:

• Destination Account - map template rows to segment combinations.

• Reference Account - look up of data from another template. For example, payroll accounts may be looked up from the HR template.
• Reference Cube - look up data from the cube. For example, you can look up total revenue for all sales departments within a Legal entity.
• Mass Load - Insert rows and configure the account mapping within the template.

More

Account Reports

• Destination Report - open a dialog page to view each line that is identified as a destination account and print or download the report in Excel format.
• Reference Report - open a dialog page where each line that is identified as a reference account is displayed. You may print or download the report in Excel format.
• Show Rule - open the Rule dialog page to view associated multi-dimensional expressions.

Ribbon

Line Type List Box

Set the line type of each line in the template by selecting the appropriate type. Select from the following options:

• LINE (L) - Allow data entry or calculations to be performed for the row. Reference and destination accounts may be applied to a line.
• CALC (C) - Allow calculations to be defined for a row within the template design, preventing budget users from modifying the formula. Destination accounts may be applied to a calc line.
• HEADER (H) - Format sections or provide additional spacing within a template.

Format

Apply number, currency, or percent formatting to rows.

Decimals

Apply up to 6 decimals per row.

FX

• Summary information for the cell.
• Calculation associated with the cell.
• Result of the equation or calculation associated with the cell.