Export Data with User Defined Import Specifications
On the Transfer/External Data/Define Import Specifications menu, you can create your own Import Specifications with the help of an Import Specification template, called #FEXPVAL.
You copy and save the template with a name of your own choice and make your desired changes to the specification. Then you can use the new specification in Microsoft Excel when exporting data to IBM® Controller.
Before you begin
Before you create your Excel sheet for the export of data, try to define an efficient design. Enter for each and every parameter the reference to the corresponding cell in the Excel sheet. If you use absolute and relative references in this function you can easily copy and paste the function to cover all records in your Excel sheet.
Before the actual export of data starts, all the information defined for the import is validated. First there is a control that all the parameters in the Excel sheet exist in Controller. Then there is a validation that all fExpVal functions are correctly defined and finally there is a validation of the parameters in combination including linked structures, e.g. combinations of Companies and Extended Dimensions.
After the validation the export of data from the entire workbook begins. Please note that the export will not begin unless all the information in the fExpVal functions is correct.
If you first make an export and after that change for example Counter Company, Dimension 1-4, Counter Dimension or Transaction Currency Code and make an additional export, the first values will be overwritten.
This feature is also limited by User Rights in Controller. Therefore, if the user does not have access to the menu item Transfer/External Data/Import..., then the Controller/Export Data menu in Excel is not available, and the user cannot use this function.
The fExpVal function must not be defined with other Controller functions nested in the formula itself.
Procedure
- Follow steps 1 to 7 in Export Data.
- Select Insert/Name/Define.
- In the Names in workbook: field, enter CC_SPEC
- In the Refers to: field, enter ="#FEXPVAL_USER", where #FEXPVAL_USER is the name of the template specification that you have created in the Transfer/External Data/Define Import Specifications window.
- To execute the export, log on to the IBM Cognos® Controller Link for Microsoft Excel and click the menu Export Data under the Controller menu.