Report Output - Export

Should the RTF Letter facility not provide the cosmetic flexibility required to output a suitable document you can export data using Report Generator, and then utilise that exported data within spreadsheets and word processors.

You can, through derived fields, output to a specifically named spreadsheet by defining a spreadsheet name, a worksheet number and a cell offset. You can also allow excel macros to be initiated. Once an export facility is set up you can also run the report as part of the Batched reports option.

In order to use the function you will need to pay attention to the output type, and then set up one or more derived fields.

Note: Data is written to a spreadsheet that already exists. Autoline does not create a new file for you.

What do you want to do?

• Set up the export facility
• Export direct to Excel spreadsheets
• Write an export report
• Run an export report
• Export tables to PC

Settings on the Report Generator Form

Export reports are written in exactly the same way as print or display reports with just a couple of extra considerations.

Note: Do not try to create this output type within the Export Facilities option in the Management menu. The lower case ‘excel’ output type is pre-installed.

• Title tab should have the output type set to E. Select this by clicking on the drop down selection, adjacent to the Output type field, if required.
• The sequence of the report may be set up as usual but there is no need to set breaks unless the report is printing totals only. If breaks are set on a report which is not totals only, then they will be ignored.
• The Format tab Totals are not needed on the format unless the report is totals only. Otherwise they is ignored. Totals must be set to the required fields if the report is to be run as a totals only report. The headings on the report format tab are ignored by the export function.

Setting up the Export Output in System Utilities

In order for the export function to work at all there must be an export facility set up for the system to utilise. The export facilities are set up in the Management menu within the System utilities module and would normally be created by the System Administrator.

Description: The name of the export option. This may contain a user name, for example if this specific export facility is only going to be used by one person their name may go into the description. The system will allow as many export facilities as required to be set up.

Host directory: Before exporting to a PC the system will write the table into a directory. The host address is the directory that is used for this purpose. Typically it is ../xfer on a Unix operating system.

Note: If the directory named does not exist then the system will create it for you, however in order for other export functions to work, the directory used must be the normal system export directory, so ask your system manager or AUTOLINE software support for guidance if you are unsure.

Export to PC: This determines whether the resulting table is transferred to a PC or whether it is left in Unix.

Note: Whenever an export report is run, the table is written into the Host transfer directory prior to export. If the completed table can then not be written to a PC for whatever reason, then it is held in Unix for you to collect and transfer in due course. The menu option in the Report Generator menu Export files to PC will examine the ../xfer directory and list all of the tables there that have not yet been deleted. These are then offered to the user on a window and can be transferred manually. This is quite a common occurrence if export reports are included in the end of day report batch.

• Y to transfer the table directly to the PC running the report. If the terminal running the report is not a PC then it will still write the Unix table, however it will not complete the transfer to DOS.
• N to write the Unix table but not transfer the table to DOS.
• P to prompt you who runs the report whether the export to PC should be done immediately.
• I to operate the export facility solely with the spreadsheet interface function.

PC directory: Enter the full path name to describe which directory on the PC the file is to be written to. There is a limit of 12 characters here. Remember to include the drive letter. The directory named must exist for the export to operate correctly.

Tip: To avoid confusion system managers should ensure that users with PCs should have a c:\temp or c:\import directory already set up on the PC. This means that the export facility can be used by other users who will have a similar path on their PC.

Format: The formats allowed for standard Report Generator exports are csv or slk. These are comma separated values and symbolic tables respectively. The prn type tables are used for spreadsheet interface.

Scratch after export: Whether the box is set to Y or left blank will determine whether the table is removed from Unix once it has successfully written to a PC. It is as well to make sure this is set to 'Y' unless a good reason exists to keep the table in Unix for other people to download.

Control Functions

There are three control functions you can make use of, they are input as derived fields.

Specify Export Filename this is input as ‘rg_export_filename(name,save,hide)

This function enables you to name the spreadsheet name you want to work with. Three parameters are allowed. The first is the spreadsheet name, complete with DOS path. This file must exist, the Report Generator will not create it for you.

The second parameter will ask whether you want to save the spreadsheet once output is complete, you should normally set this to a value of "Y", otherwise the file may not perform correctly, or may be recorded as a read only file.

The third option allows you to hide excel while the export is in progress, this is the preferred option.

Position in spreadsheet input as ‘rg_export_position(sheet, row offset, column offset)

This function enables you to identify the sheet number within a previously named spreadsheet that will receive the output. It also allows specification of a row and column offset from where the population of the sheet isgin.

In the example above the data is put into the third sheet, and the import isgin at the second row, in the second column

Execute macro (excel only) 'rg_export_macro(macro name,optional parameter, TRUE)

This function is used in conjunction with the two other export options. Export macro enables you to execute a macro before and/or after the population of data.

Example derived fields

You could have two derived fields as follows:

• 'rg_export_macro("Before","",TRUE)

• 'rg_export_macro("After")

The first macro routine has a third parameter set to TRUE. If this parameter is set this way, the macro executes before the data is loaded. The second example routine is executed after.

The second optional parameter allows you to define a range in the spreadsheet covering all the data downloaded. E.g.

'rg_export_macro("After","ExportedData")

This will define the range "ExportedData" which can then be used in the "After" macro with a command such as:

Application.Goto Reference:="ExportedData"

Or if you are recording then a simple Edit/Goto command is ok.

Related Topics :

Export facilities.