Pass data to SQL statements at run time
Use Report Parameters in Calibration Control (our Calibration Management Software) to pass information to the report's SQL string at run time. A Parameter dialog will prompt the user for input when generating reports.
The syntax for a report parameter is:
- ParameterName: Must be a unique Parameter name and is often the same or similar to the field name the parameter relates to.
- PromptString: The text displayed in the Parameter dialog asking for input.
- DefaultValue: The default value of the Parameter.
- Type: The Parameter type code of the parameter.
Parameter Type Codes
- AS: String
- AD: Date
- AB: Boolean
- AI: Integer
- ADB: Double
- AC: Combobox (i.e., drop down list)
The following is an SQL statement that uses a 'hard coded' date, which means the SQL statement must be edited each time the default date needs to change.
WHERE CalibrationDue < #10/15/2012#
Conversely, the following sample shows the same SQL statement using a Parameter, which allows users to enter a date value at run time.
WHERE CalibrationDue < #<%CalibrationDue|Due Date:||AD%>#
Date Default Values
When using the AD (Date) Parameter Type code, several options are available for the Default Value:
- Specific Date: Exact date with the syntax of #MM/DD/YYYY#
- Number of Days: Positive or negative integer (counting number) indicating the number days added (or subtracted) from the current date
- BOM: Beginning of current month
- EOM: End of current month
- BONM: Beginning of next month
- EONM: End of next month
- BOPM: Beginning of previous month
- EOPM: End of previous month
The following example uses the EOM Default Value code for the End of the Current Month:
WHERE CalibrationDue < #<%CalibrationDue|Due Date:|EOM|AD%>#
Combobox Parameter Type
Using the AC (Combobox) data type requires the use of the DefaultValue Parameter field to configure the Combobox. Within DefaultValue exactly six sub-fields, each separated by a slash ("/"), are required. This also means that exactly 5 slashes must be present in the DefaultValue field.
The six sub-fields are:
- Table or SQL *: Table or SQL for the records used in the combobox
- Value Member *: Field name for the value, usually an ID field
- Display Member *: Field name for the text displayed in the combobox
- Filter: SQL filter phrase that limits the records displayed (e.g., "Active = -1")
- Sort: SQL sort phrase that places the list of choices in a specific order (e.g., "Name"). When left blank, the Display Member is the default sort field.
- Default Value: One of the values within the list created by the Value Members field (e.g., "55ec4215-7f9b-4e9f-b583-56be9871b895")
Although not all fields are REQUIRED (*), their places must be defined with slashes like in the following DefaultValue example. Note that there are still five slashes ("/") even when only three sub-fields are used.
Last updated: 11 Feb 2014