Report Parameters

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.

Report Parameters Dialog

The syntax for a report parameter is:

<%ParameterName|PromptString|DefaultValue|Type%>

Where:

  • 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.

SELECT *
FROM   qryEquipmentMaster
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.

SELECT *
FROM   qryEquipmentMaster
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:

SELECT *
FROM   qryEquipmentMaster
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:

  1. Table or SQL *:  Table or SQL for the records used in the combobox
  2. Value Member *:  Field name for the value, usually an ID field
  3. Display Member *:  Field name for the text displayed in the combobox
  4. Filter:  SQL filter phrase that limits the records displayed (e.g., "Active = -1")
  5. 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.
  6. 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.

tblPeople/PersonId/DisplayName///

Last updated:  11 Feb 2014