Example: Creating an Import Specification

Steps

1. Make a back-up copy of the file so you can work with the copy.
2. Check the content and requirements of the external file:
   • The file contains fixed fields, i.e. the fields are separated by spaces.
   • The company code and the period are on row 1, position 1 (4 characters) and position 7 (4 characters).
   • The file contains no actuality, so you have to specify one.
   • There is no currency either, but it can be loaded from the company table in IBM® Cognos Controller.
   • Accounts will be imported from row 3 onwards in position 1 (4 characters), and will be translated using a lookup table to corresponding accounts in Cognos Controller.
   • The amounts are entered in Swedish Kronor and should be rounded to thousands of SEK. The amounts are loaded from row 3, position 12 (15 characters). The import of rows will be repeated from row 3.
   • Row 2 is superfluous and will be skipped.
   • Values of income and liability accounts are marked with a minus in the file. These values must change sign before they are saved in Cognos Controller. The values of expenses accounts are marked with a plus in the file and need to changed to a minus. This requires that the entire balance sheet and income accounts are entered with a plus sign, and expenses accounts with a minus sign in Cognos Controller.
   • Define a lookup table to translate account codes from the external file to the account codes, as in the account structure of Cognos Controller. The lookup table will be of the Interval + Factor type. Because the liability and income accounts in the external file are marked with a minus sign, they must be multiplied by -1 to become positive values in Cognos Controller. The expenses accounts are marked with a plus sign in the external file and must be multiplied by -1 to become negative values in Cognos Controller.
3. Define a lookup table to translate account codes from the external file to the account codes, as in the account structure of Cognos Controller.

   The lookup table will be of the Interval + Factor type. Because the liability and income accounts in the external file are marked with a minus sign, they must be multiplied by -1 to become positive values in Cognos Controller. The expenses accounts are marked with a plus sign in the external file and must be multiplied by -1 to become negative values in Cognos Controller.

4. Create a new import specification and enter a description of what is going to be imported.
5. Define the file format of the external file. In this example you need only enter a period (.) in the Decimal Symbol text box and select Automatic transfer of Net Income between IS and BS. Select Test Run while running a test.
6. Enable reconciliation between accounts, in accordance with what has been defined in the account structure. Enter the account where any differences will be booked.
7. Define the START filter, which tells Cognos Controller to start reading repeating rows, starting from row 3 in the file. The filter will skip blank rows
8. Define variables for information, separate from the information repeated in the file. The company code and the period are fixed information and can be loaded from row 1 in the file. The actuality is entered with the help of a prompt during the importing process. Define the variables &ACTUALITY, &COM and &PER. See the table below with variables.
9. Define the fields to which values will be imported.
10. Use temporary fields and operation templates to carry out calculations in multiple steps. See the table below with operation templates.
11. Carry out a test import of the file and check that the import is working without errors.
12. Check that the figures in Cognos Controller correspond to those in the original file.
13. Clear Test Run on the General tab and import the file properly.