Standard Excel Reports
Some of the standard reports available in Calm are produced using Microsoft Excel, for example Accession and Entry/Loans In statistical reports and Location space usage reports. For further examples see the complete List of Reports.
Please note: The instructions provided in this manual relate to versions of Excel that are currently supported by Microsoft. These are; Office XP/2002 (version 10), Office 2003 (version 11) and Office 2007 (version 12). Instructions for older versions of Excel are not included as they are no longer supported by Microsoft.
Excel reports use data exported from your Calm records in combination with Excel Templates to create Excel Spreadsheets. The Excel Templates have the file extension .xls and are located in the Calm folder. (When Calm is installed, master copies of the Excel templates are supplied in the Calm templates folder - usually dscribe\archive\templates. When you log into Calm for the first time, these templates are copied to your Calm output folder - usually dscribe\archive\output. If you have already modified these templates to create your own versions, they will not be overwritten, but the originals will remain in the templates folder.) The data from your Calm records is exported as a text file with the extension .txt into the Calm output folder. Please Note: In a client/server configuration, these folders are found on the client PCs. Any changed files will need to be copied to other client PCs if they are to be employed by all Calm users.
If Excel is not installed on your computer then the data can be exported into any spreadsheet package with an appropriate template set up. Contact the Calm Help Desk for details.
The Excel templates that are provided with Calm can be customised to suit your individual requirements.
When you run an Excel report the data that is exported from your Calm records is controlled by a file called mailmrg.ini. This file can also be customised to change the data that is exported when a particular report is run.
For more complex reporting requirements Axiell offers a Custom Reports design service.
Running an Excel report from Calm
Excel reports are usually accessed via the side button strip in Calm.
Click the
button followed by the button for the report you require. The default template for the
report you have chosen will open in Excel.
To produce the report you need to combine the data that has been exported from your Calm records with the template as follows:
Click on the Data tab, and choose 'From Text' in the 'Get External Data' section.
For older versions of Excel, select 'Import External Data' on the 'Data' dropdown menu, then 'Import Data' on the sub-menu.Browse for the text file that contains the data from your Calm records, the file is named in the instructions on the spreadsheet and has the file extension .txt, it is found in your Calm output folder. Please Note: you may have to change the Files of Type drop down box to show "All Files" or "Text Files" to find the relevant file.
Select the relevant text file and click 'Open'.
The Text Import Wizard dialogue box appears, use this to define the file as comma delimited as follows:
Step One: Ensure the "Delimited" radio button is selected and click Next
Step Two: Under "Delimiters" select Comma (remove any other ticks) and click Finish.
-
When the 'Import Data' dialogue box appears, ensure the 'Existing worksheet:' radio button is selected and click
.
The data is merged with the spreadsheet template to
create the report.
There are specific instructions for each individual report throughout this guide. For example, see the Accessions database section for Accession Statistics.
When you have produced the report you can use the Excel printing features to create a paper copy of the document.
Working with the Spreadsheet Templates in Excel
Default spreadsheet templates are provided with Calm. These will automatically open when Excel reports are run, however they may be customised to meet individual requirements.
If you wish to customise a template, first run the report from Calm. This will allow you to view the template to decide what customisations to make, and will also enable Microsoft Excel to associate the correct data source with the template.
After you have run the report for the first time, close it without saving any changes. You are now ready to edit the default template.
The default templates are located in the Calm templates folder (usually DScribe\Archive\Templates) on your Calm client PC. The Excel templates are those files ending .xls. It is advisable to save a copy of the template in case you need to revert to the original in future. To open the template for editing, in Excel, select the report name you require, right click, and click Open. Please note: You must open the template from within Excel.
Use Microsoft Excel features to edit the template, formatting the report as required.
To add or remove fields from the report, or change their order, you can edit the Mailmrg.ini file.
When you have made the required changes, save the template in the output folder (usually DScribe\Archive\Output), using its original file name. Please Note: renaming the file will stop the report from running properly. Close the file, return to Calm and run the report again. When the report is run from now on, Calm will use the updated template.
Customising the mailmrg.ini file
Mailmrg.ini is a file that determines which fields are exported from Calm records for use in your report templates. For Excel reports, the order in which fields appear is also determined by the mailmrg.ini file.
To add or remove fields from your spreadsheet reports or change the order in which fields are shown, customise the mailmrg.ini file.