Creating Custom Reports / Configuring Report Parameters |
More advanced Crystal Reports users can write custom SQL queries to define reports, with WHERE clauses that are evaluated and populated with data based on parameters that you specify. Many of the Report Server reports are defined this way. Open the reportconfig.xml file located in the \vocera\reports folder to see how Report Server parameters are specified.
For example, the following SQL command has a WHERE clause that references a report parameter named DateRange_Clause:
SELECT TxDate, BadgeMacAddr, RecStatus, Score, Recognized from RecResults WHERE {DateRange_Clause}
In this example, {DateRange_Clause} is populated by creating two report parameters, which must also be specified in the customconfig.xml file:
The first parameter inputs the report date as a daterange parameter (the From and Through dates).
<PARAMETER>
<DISPLAYNAME>Report Date</DISPLAYNAME>
<BINDNAME>ReportDate</BINDNAME> 12
<TYPE>DATE</TYPE>
</PARAMETER>
ReportDate is the name of the parameter in the RPT file.
The second parameter is a hidden parameter that uses Vocera's $BuildClause() macro to evaluate the expression for {DateRange_Clause} as (RecResults.TxDate >= ReportDate.Lowerbound and RecResults.TxDate <= ReportDate.Upperbound).
The $BuildClause() macro is used to build a WHERE clause for a SQL query. It takes the following arguments:
Column name in the SQL to bind
Name of the input parameter
Operation. Specify any of the following operations:
daterange: Used to generate Date range expressions. [ column > lowerbound and column < upperbound ]
stringequals: Used to generate an expression for Text parameters [ column = parameter value ]
list-in: Used to generate expressions for MultiList parameters [ column in (P1, P2, P3) ]
Here is an example of a hidden parameter with the $BuildClause() macro:
<PARAMETER> <DISPLAYNAME>Hidden Where Clause</DISPLAYNAME> <BINDNAME>DateRange_Clause</BINDNAME> <TYPE>HIDDEN</TYPE> <DEFAULT> $BuildClause(RecResults.TxDate, ReportDate, daterange)</DEFAULT> </PARAMETER>