Statistics report to send to excel
Learn how to create a statistics report in Amlib.
Creating a Statistics Report
There are a few types of statistics reports that can be sent to Excel
- Details by Location, Trans Type, Item Form Code &xstat53.qrp
- Details by Location, Trans Type, Item Stats Code &xstat52.qrp
- Details by Location, Trans Type, Borrower Type, Item Form &xstat50.qrp
- Summary by Location, Trans Type, Year &xstat10.qrp
- Summary by Location, Trans Type, Year, Month &xstat20.qrp
- Summary by Location, Trans Type, Year, Month, Day &xstat30.qrp
This Guide will show the Statistics Details by Location, TransType and Item Stats Code (&xstat52.qrp) but other ones can be substituted if required.
- From Main > Reports > RepStatistics
- Select New [F1]
- The Select Report Format window will display
- Highlight the MS Excel format – Details by Location, Trans Type , Item Stats code
- Choose the Select Button
- Type a Description – for example Statistics 2013/14
- Select F3 Save
- Select F7 Where
- Choose the relevant Column from Box 1. The choice will depend on what is required.
- Select a Location if required – Select Location from Box 1, choose = from Operator and enter the Location code for the transaction and Paste.
- Select TransDate if you wish to reports on those Issues for a Calendar or Financial Year. If it is a Financial Year choose TransDate from Column 1. Select the ≥ symbol from the Operator (Box 2) and in Box 3 enter the first date for the Financial Year – for example enter 01/07/2013 and Paste. Highlight TransDate again from Column 1.
Select the ≤ symbol from the Operator (Box 2) and in Box 3 enter the last date for the Financial Year – for example enter 30/06/2014 and Paste - Select the Transaction Type by highlighting Trans Type (Issue, Ret) in Box 1, the Operator = and the Transaction Type (for example 05 for Issues) and Paste Note: These transaction types are listed in Main>Supervisor>StatsParams
- Select F3 Save
- The Order should be greyed out
- Select F6 Print
- In the Print dialogue box, To Section, click the Radio button File, to save the report to a file. Press OK.
- A Report - Save As Dialogue Box will display. Choose the Destination Folder for the Report and ensure that the Report is saved as a .txt file (Text Document).
- This will set the Report ready to be saved to a File. To proceed, Report Scheduler must be activated. Select RepStartSchedule from Reports/Application. Ensure Save to File is included in the selections.
- The Report’s progress can be checked in Reports, Application, RepPrintProgress.
- When the Scheduler has completed the task and the Report has been saved it can be accessed in Excel.
- Open a Blank page in EXCEL. Use File>Open to load the saved .txt file. The file was saved as a .txt file. Ensure that ‘All Files (*,*)’ is selected from the ‘Files of type:’ dropdown list
- EXCEL will identify that your file is a data delimited file. Use the Text Import Wizard (3 steps) to import your file.
- Press Next.
- Select Semicolon as this is the Delimiting character used for these reports.
- Press Next.
- Select Finish
- The data will be transferred into the EXCEL table. Use EXCEL Formatting tools to customise the file as required