Skip to content

Parameters: Creating a Report that Selects Records on a Date Range

In this example, we will create a report that selects records based on a date range entered by the user.

Creating a Parameter

In our example, we’ll use a report that shows customer orders, and we want to limit our report to orders entered in a selected date range. This means we’ll need two date parameters: a start date and an end date.

  1. Our first step is to create our parameters. Make sure that your Field Explorer pane is visible. (If not, select View|Field Explorer.)
  2. Right-click on Parameter Fields and select New… from the pop-up menu.
  3. You see the Create Parameter Field dialog.
  4. Enter a Name for the field. This is the actual name of the field, not what appears to the user. For our example, we’ll use FDate.
  5. Enter the Prompting Text. This is the text the user sees, prompting them to enter data. Enter Start of Date Range.
  6. Select the Value Type of the parameter. For our example, it is a Date.
  7. Under Options, we are going to leave the values as they are. We want users to enter a single date, and we don’t want to define default values.
  8. Click OK.
  9. Next, we’ll repeat the process to create the parameter for the end of the date range, using the following settings:

    Field Value
    Name FTDate
    Prompting text End of date range
    Value type Date

Selecting on the parameter

Now we have our parameters created. Our next step is to limit the records we view to those that were ordered between FDate and FTDate.

  1. Select Report|Selection Format|Select Records. You’ll see the Record Selection Formula Editor.
  2. Enter the following formula (you’ll replace {Orders.OrderDate} with the field you are selecting on)
    {Orders.OrderDate} in {?FDate} to {?FTDate}
  3. Click Save and Close to modify the selection formula. You may be prompted to enter values for the FDate and FTDate. Go ahead and enter values and preview your report.

Displaying the Parameters in the Title

I find it helpful to show the values of the parameters in the Report Title; that way the user can easily see if it was run with the correct values. I use a formula to display my report title, so I just modify the report title formula to contain the following:

'Orders placed between ' & {?FDate} & ' and ' & {?FTDate}

Note: This will work because each parameter can only be a single value. A later example will show how to handle a parameter that can contain multiple values.

Place the formula into your Report Header, and take a look:

Leave a Reply