4.2 Excel Import Templates - Generating
Once you have created a report and initialised all the necessary tables, you can generate an import template by clicking on the Create Import template button:
Create Import Template Form
Type
Import Templates can be generated in one of two types:
- Tables
- Fact list
Tables Type
An import template generated as tables will consist of Excel versions of each template in their own sheet. Cells are each given a named range based on their address, and DPM Authority will read in the values based on the names cells.
This is the most common type used, and cells can be populated direclty, or via formulae that can refer to custom sheets mported into the workbook.
The workbook is protected, but without a password. If any changes to the workbook are made, take care not to rename the import template sheets, or change/delete/override any names ranges.
Fact List Type
The fact list type involves a single sheet called "Fact List" with a single value stored per row with columns for
- Template code
- Sheet code (can be null if the template does not have any sheets)
- Row code
- Column code
- Value
This format is convenient to automate template population. It is no longer necessary to change the template structure for changes to the taxonomy. Each and every template can be sourced from the same sheet with this standard layout.
It is not necessary to include a row for each data point. For example, if a table has 100 cells, and only 1 has a value, you only need to report the one fact with the value. The value of all others will assumed to be null, and would be cleared on import if they previously had a value.
Variable row tables
For variable row tables, DPM Authority will generate reference sheets that can be used to understand the data that is required.
For example:
The table above is a reference table for S.06.02.01.01 in the EIOPA Taxonomies. From the above, you can see that:
- There are 6 columns for row dimensions. The first two are mandatory, the next four are optional. For the Row code in the fact list, you will need to combone all 6 delimited with commas, For example "1,test,,,,,".
- The last row dimension refers to the dimension "s2c_dim:VC". Further information about this dimension is found in the sheet "Cn_s2c_dim.VC":
When importing data, you can use refer to the dimension member in one of three ways. The code (e.g. "s2c_LB:x0"), the short code (e.g. "x0") or the label, (e.g. "Total/NA").
These three would be equivalent as rows code for S.06.02.01.01:
- "1,test,,,,,Total/NA"
- "1,test,,,,,s2c_LB:x0"
- "1,test,,,,,x0"
Variable row tables as tables
It is possible to use the fact list method for fixed row and column tables but use the template method for variable row tables. The reason you may want to do this is it can be unintuitive to transpose a large variable row table into a fact list. For example, if the table has 20 columns, there will be 20 rows in the fact list for each single row in the template.
To use the tables method for variable row tables but the fact list method for the fixed row and column tables, sleect the "Variable row tables and tables" checkbox.
List of Tables
Select any number of the initialised tables to be included in the import template (Ctrl + A for all). If you intend for more than one person to work on the data for the report, you can create a number of different import templates containing different sets of the initialised tables.
Auto connect and link
If this option is not selected, you will still have to connect to the produced spreadsheet and link the data points (see Microsoft Excel - Connecting to Excel Workbooks). With this option selected, you will have to provide a connection name and DPM Authority will automatically connect to the created spreadsheet and auto-link each data point.
DPM Authority will ask you for a location to create the file, and it will connect to the spreadsheet at that location.
If you move the Excel file to a different location, you will have to update the connection using the Manage Connection button in the ribbon (see Microsoft Excel - Connecting to Excel Workbooks).
Once you are complete with populating the data within the Excel import template, you will need to save the file and then return to DPM Authority. Click on the Refresh All Links button:
The Refreshing Links form will appear and process any changes to the data points
Before any changes are made, you can review the changes prior to acceptance.
- Accept All Value Changes - accepts all processed changes from Excel import template.
- Discard Any Changes - discards all processed changes.
- Review Value Changes - opens the Review Changes form:
- Process Selected Items - accept one, multiple or all changes using the tick boxes in the "Process" column.
- Export - export the changes to another format for review.
- Abort all changes - close the form and reject all changes from the Excel import template.