Export report data to Excel

MS Excel/CSV report templates

A number of report templates have been specifically set up to be exported and used in MS Excel.

Save the template

• It is possible to customise any of the above templates to add/delete fields.
• Save the customised template into the Amlib/Reports folder on the Amlib server. This will make the template available for use for all users. Alternatively, you may save it into a local folder on your PC – but please be aware, that the template (once loaded) will only be available for use on that PC.
• Ensure that the template is saved with a name that clearly identifies it as a customised overdue letter template (e.g., XSTKCSV2.QRP).

Load the template

1. Launch the Amlib client.
2. Go to Main > Reports > RepAddNew.
3. The Report Files window will open. Ensure that the Report Entity matches that in the table above.
4. Click the F1 New button.
5. Enter a Description (e.g., Stockitem CSV Export).
6. Browse to the Amlib/Reports folder on your Amlib Server and locate the template to be loaded (if the template has been loaded onto your PC – then navigate to the local folder).
7. Click the Open button.
8. Select the appropriate Choose Type – see table above.
9. You can leave Default Stats Code blank.
10. Update Entity (Y/N) = N.
11. Click the F3 Update button.
12. Close out of the Report Files window.

The template is now loaded and available for use in a report.

Create the report

There are several Excel report templates available which can be used to save to a file.

In this example, we are going to use the &XSTKCSV.QRP template in the Reports > RepStockitem module:

1. Go to Main > Reports > RepStockitem. The Stockitem Reports screen will open.
2. Click the F1 New button and select the &XSTKCSV.QRP template.
3. Enter an appropriate Description and click the F3 Save button.
4. Enter an appropriate – F7 Where search (e.g., Title LIKE Harry Potter).
5. F9 Order the Report (e.g., Title | ASC).
6. Select Application > RepStartSchedule. Ensure that Save to File is ticked and click the OK button.
   
7. Click the F6 Print button. Change the To: option to File and click the OK button.
   
8. A Report – Save As dialogue box will open:
   1. To save it for a Word document, leave the Save as type: as Rich Text Format (RTF).
   2. To be able to open it in Excel, change the Save as type: to Text Document.
      
9. You can check the progress of your report in Reports > RepPrintProgress.
10. If you’ve saved the report to open in Excel (Text Document) then you will need to follow these steps so it is displayed correctly:
    1. Open Microsoft Excel.
    2. Use File > Open to open the text file you’ve saved. Ensure that the Files of type: = All Files (*.*).
    3. The Text Import Wizard will open.
       
    4. Excel will recognise your file as Delimited, so click the Next button.
    5. Delimiters: Select Semicolon OR add a pipe | in the Other box (it is possible to use both if you don’t know which one the report is using but this may cause cell contents to be misaligned – so check carefully!).
    6. Click the Next button and then the Finish button.
       
11. Your data will be transferred into the Excel sheet and you can now use the formatting tools to customise it: