|
<< Click to Display Table of Contents >> Quick Report Templates |
  
|
|
<< Click to Display Table of Contents >> Quick Report Templates |
|
Quick Reports templates consist of two sections
1.The SQL (query) section, which is used to retrieve the data for the report (shown in red in the example below)
2.The "Report Setup" section which tells the program how to display the data (shown in green in the sample below)
For example, here is the content of the HSP "Unsubmitted Items Report"
/* QuickReport: Unsubmitted Claims */
SELECT
CLIENTS.CLIENT_PK,
CLIENTS.LIST_NAME,
CLAIMS.ITEM,
CLAIMS.SERVICE_DATE,
CLAIMS.ITEM_TOTAL
FROM
CLAIMS
INNER JOIN CLIENTS
ON (CLIENTS.CLIENT_PK = CLAIMS.CLIENT_PK)
WHERE
(CLAIMS.E_CLAIM = 'F') AND
(CLAIMS.MANUAL_CLAIM = 'F') AND
(CLAIMS.PAID = 'F') AND
(CLAIMS.SERVICE_DATE BETWEEN '2000-07-01' AND CURRENT_DATE)
ORDER BY
CLIENTS.LIST_NAME,
CLAIMS.SERVICE_DATE,
CLAIMS.ITEM
/* Report setup
[DisplayWidth]
LIST_NAME=40
ITEM=15
SERVICE_DATE=15
CLIENT_PK=0
[Summary]
;SummaryKinds: None = 0, Sum = 1, Min = 2, Max = 3, Count = 4, Average = 5
;Format after |
LIST_NAME=4|Total Unsubmitted Items: 0
ITEM_TOTAL=1|$,0.00
[Groups]
ITEM=0
[Report]
ReportTitle=Unsubmitted Items Outstanding
AutoWidth=1
[Labels]
LIST_NAME=Client
ITEM_TOTAL=Amount
*/
These template files are unique to HearAid and combine the features of an SQL script file and an INI file with four sections. The following rules apply:
1.The SQL section must be a single valid SELECT statement; sub-queries are permitted but multiple statements (as is possible in the Query work-area) are not
2.The Query can include user-input to specify selection criteria, called parameters. Parameters have the format :Parameter ie a name preceded by a colon, and if parameters are included in a script the user will be prompted to enter the parameter (or search value) before the report is executed. For example, if your custom sql included the parameters :Item and :Site, the user would be prompted to enter those values
3.Date ranges can use the :BEGINNING and :ENDING parameters; any SQL containing one or both of those parameters will cause the "Period" items to display on the toolbar and the :BEGINNING and :ENDING values will be read automatically from the date range on the toolbar
4.The Report Setup section must be enclosed within a /* at the start of the section and a */ at the end of the section
5.The Report Setup section contains five sub-sections:
•[DisplayWidth] which defines the column width to use for specified columns. The columns must be included in the SELECT statement of the SQL. Columns with no specified width will adjust their width automatically. Setting a display width of 0 will hide that column e.g. in the sample above, CLIENT_PK=0 means the CLIENT_PK column will not appear in the table
•[Summary] defines which columns have summary information calculated. The column used for the summary is specified first, followed by a | and the formatting string which controls exactly how the summary numbers are formatted Using the sample above, the report will show the Count of rows in the LIST_NAME column and the Sum of the ITEM_TOTAL column which will be displayed in standard Currency format. If the data uses Groups, summaries are calculated for each group. If there are no groups then the summaries are displayed at the bottom of the table. In this example, the report will be displayed with a group for each ITEM and there will be Count and Sum calculated for each group. If there are no groups, summary di
•[Groups] defines which columns are used for grouping the data. If there are to be groups-within-groups then each group must be numbered. In the example there is just one group ITEM=0 but if for example we wanted to group the data first by LIST_NAME then by ITEM, the Groups section would look like this
[Groups]
LIST_NAME=0
ITEM=1
•[Report] sets the title of the report and whether column widths are adjusted so that columns are displayed entirely within the window (ie no horizontal scrolling and the entire table area is filled) AutoWidth=1, or alternatively if AutoWidth=0 then no width adjustment is performed and users may need to scroll horizontally to view all the data. UseCurrentDate=1 will set the date range for "today" and UseCurrentDate=0 will set the default date range to the current month
•[Labels] allows you to specify a different display name for a column. In the example above, LIST_NAME will have "Client" as the header, rather than "List Name" and ITEM_TOTAL will appear as "Amount", rather than "Item Total"
There are several sample custom Quick Reports supplied which demonstrate how to use this feature. This is the "Sample Date Range Custom Quick Report"
/* Sample Custom QuickReport */
SELECT DISTINCT
CLIENTS.LIST_NAME,
FITTINGS.DATE_FITTED
FROM
CLIENTS
INNER JOIN FITTINGS
ON (FITTINGS.CLIENT_PK = CLIENTS.CLIENT_PK)
WHERE
(FITTINGS.DATE_FITTED BETWEEN :BEGINNING AND :ENDING)
ORDER BY
CLIENTS.LIST_NAME
/* Report setup
[DisplayWidth]
LIST_NAME=40
CLIENT_PK=0
[Summary]
;SummaryKinds: None = 0, Sum = 1, Min = 2, Max = 3, Count = 4, Average = 5
;Format after |
DATE_FITTED=4|Number of Fittings: 0
[Groups]
LIST_NAME=0
[Report]
ReportTitle=Client Fittings
UseCurrentDate=0
AutoWidth=1
[Labels]
LIST_NAME=Client
DATE_FITTED=Last Fitted
*/
and this is how the report appears, with some sections expanded to show group summaries
