HearAid Version 5.0

Quick Report Templates

Quick Report Templates

Previous topic Next topic  

Quick Report Templates

Previous topic Next topic  

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 OHS "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 5.0 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
 
QRParams
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 two 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

 

Custom Quick Report