Introduction
The Report Wizard allows you to create a lot of predefined reports.
Sometimes, customers need reports that do not exist in the Report Wizard, like reports that summarise several fields from different database tables (e.g. the list of all the cardholders with their card and the description of their card). This short manual explains how to add customised reports in the application that you can design yourself.
These reports are based on queries/views in the application database including tables from within the application database(s).
These reports would finally appear under the ‘Other Reports’ section of the Report Wizard, Step 2/4, like the example ‘Number of cardholders by department’ as shown here:
How did this get to be created?
1. Open the application database with MS-Access (if MDB database) or with MS-SQL Server Management studio (if SQL database).
By using MDB/SQL internal tools, create a new Query / View that contains the requested data.
For example, for having the number of cardholders by department, the query is:
SELECT DPT.Name As Department, Count(CRDHLD.ID) AS CountOfID FROM
CRDHLD RIGHT JOIN DPT ON CRDHLD.Department = DPT.ID GROUP BY DPT.Name;
2. The query/view should be saved in the application database, in the existing Query/View list, with a specific name. The name should be without spaces. Example: MyCardholdersQuery.
3. Run the query/view in their corresponding program (MS-Access / MS-SQL) to verify you get the
desired results.
Example:
4. Once the query is ready, it is necessary to create a special ‘prp’ file in the ‘Reports’ directory that will define the fields of the expected report. This file should be built with the same form as the “config.xml” file located in the main application folder.
To do so, make a new text file in the \Reports folder.
You can copy the example below and change accordingly:
In our example, for having the number of cardholders by department, the file will be:
<reports>
<template name="MyCardholdersQuery" caption="Number of cardholders by department">
<field name="Department" caption="Department Name" type="Text" displayed="1" choices=""/>
<field name="CountOfID" caption="Number of Cardholders" type="Number" displayed="1" choices=""/>
</template>
</reports>
- 1st line: <reports>
- 2nd line: Details about the query/view name and the caption to display for the user. Example:
<template name="MyCardholdersQuery" caption="Number of cardholders by department">
‘Template name’ defines the name of the previous query/view on which the report is based.
‘Caption’ specifies the caption name that will be displayed in the list of reports and that will be also the report title.
In our example, the query/view contains one field from the cardholder table (CountOfID) and one field from the department table (Department).
Next lines would be one line per each field included in the query/view.
Example:
<field name="Department" caption="Department Name" type="Text" displayed="1" choices=""/>
‘Field name’ defines the corresponding field name used by the query/view and taken from the database.
‘Caption’ is the field title that will be displayed in the report.
‘Type’ is the field type that can be Number, Text, Date or Boolean.
‘Displayed’ specifies if the field will be visible in the report (=1) or not (=0).
In our example, the query/view contains one field from the cardholder table (CountOfID) and one field from the department table (Department).
- Last line: </template></reports>
5. Save this file as Unicode format. Once the file is created, you can change its filename extension with the extension ‘.xml’ in order to open it with Internet Explorer and to check there are no mistakes.
6. Give a logical name to the text file and change its extension again with the extension ‘.prp’ (any valid filename can be used, but it is recommended to use the ‘Caption’ as the name or something similar so that the file can be recognised).
Save the file in the \Reports folder.
7. Now in the Report Wizard Step 2/4, in the last section under 'Other Reports', the new personal report would appear with the name given to the field ‘Caption’ defined on the second line of the .prp file.
At the next step it is possible to sort data, like for example, displaying the list of departments, which have less than 10 cardholders.
Comments
0 comments
Please sign in to leave a comment.