Working with Custom Report Parameters

Report Parameters can be used to prompt the user for input prior to running the report. Create parameters using Report Builder from Microsoft. Below we will explore how Crows Nest processes parameters and uses them during the initial querying process while running the report. For more information on steps like adding a dataset, see the user documentation for Report Builder online.

Types of Parameters

Crows Nest supports the following types of Parameters.

• Single ID - A popup window allowing a single selection from a list

• Multiple ID - A popup window allowing multiple selections from a list

• Date - A popup calendar window allowing user to select a date

• Text - A popup window allowing user to enter text

See below for info on how to configure each of these types of parameters. For any parameter property not mentioned leave it in the default state.

Adding Single and Multiple ID Parameters

1. Add a Dataset to populate the selection list. The query results must include the following two fields.

   1. ID - Integer, used to identify the selected items. Positive or negative numbers can be used but selecting an ID of Zero will cancel running the report.

   2. Description - Text field used to display the item in the list

2. Add a Parameter and configure with the following

   1. Name - The name of the Parameter. Use one of the available built-in names or create your own

   2. Prompt - Used as the title of the popup window (ignored when using a built-in selection window) Example: Select Vendors(s)

   3. Data type - Integer

   4. Allow Multiple Values - Check this option to allow multiple selections, uncheck it to allow only a single selection. Note: Some of the built-in selections may not support multiple selections.

   5. Available Values - If using a Dataset for a custom list, configure Available Values with the following

      1. Select option ‘Get Values from a query’

      2. Dataset - Select the Dataset created in the previous step

      3. Value field - ID

      4. Label field - Description

Example

A Dataset named VendorList is created and uses a query which lists Businesses tagged with a Label that contains the text “vendor” in the name of the label. It also includes an option for “All” vendors.

The following pic shows the Dataset Properties in Report Builder

Open

The following two pics show the Report Parameter Properties in Report Builder

Open

The following pic shows the popup displayed in Crows Nest

Adding Date Parameters

1. Add a Parameter and configure with the following

   1. Name - Use any name that meets the requirements of Report Builder

   2. Prompt - Enter two arguments split by a tilde (~) character. The first argument is the default date selection. If left blank the selection window will start with the current date selected. A VB Script function can be used to populate the default date. Custom Formulas saved in Options are available here. The second argument is the title for the popup window.
      The following example uses the first day of the current year as the default date with a title of “Start Date” DateSerial(Year(Now()), 1, 1)~Start Date

   3. Data type - Date

Example

The following pic shows the Report Parameter Properties in Report Builder

The following pic shows the popup displayed in Crows Nest in this example

Adding Text Parameters

1. Add a Parameter and configure with the following

   1. Name - Use any name that meets the requirements of Report Builder

   2. Allow blank value (““) - Checked

   3. Prompt - Enter two arguments split by a tilde (~) character. The first argument is the Prompt for the user. The second argument is the default text and can be omitted.

Example

The following pic shows the Report Parameter Properties in Report Builder

The following pic shows the popup displayed in Crows Nest in this example

Processing Order

Each Parameter is processed in the order they appear in the report definition. Selections made for a Parameter can be used in Datasets for subsequent Single or Multiple ID parameters in a cascading manner. For example the first Parameter could prompt for an ID, then a subsequent parameter could use the selected ID to filter the list of items available.

How Parameters are Processed in Queries

The parameters in queries are replaced by the values selected by the user prior to sending the query to server using the following logic

Single and Multiple ID Parameters

The parameter is replaced by the selected integer value. For multiple values, it’s replaced by a comma separated list of integers.

Example 1

For a parameter named ProjectID the user selects 3 items which return the values 1, 2 and 3
The following line in the query…
WHERE (-1 IN (@ProjectID) OR Project.ID IN (@ProjectID))
…would be replaced with…
WHERE (-1 IN (1,2,3) OR Project.ID IN (1,2,3))

Example 2

For a parameter named ProjectID the user selects an single item which returns a -1
The following line in the query…
WHERE (-1 IN (@ProjectID) OR Project.ID IN (@ProjectID))
…would be replaced with…
WHERE (-1 IN (-1) OR Project.ID IN (-1))

Date Parameters

The parameter is replaced by the date converted to text using the custom date/time format of
yyyy-MM-dd HH:mm and is enclosed in single quotes.

Example

For a parameter named StartDate the user selects January 1st, 2020 at 5:30 pm
The following line in the query…
WHERE Date1 >= @StartDate
…would be replaced with…
WHERE Date1 >= ‘2020-01-01 17:30’

Text Parameters

The parameter is replaced by the entered text and is enclosed in single quotes. Any single quotes within the text are replaced by two single quotes.

Example

For a parameter named Title the user enters the text: Paul’s Samples
The following line in the query…
SELECT @Title as Title
…would be replaced with…
SELECT ‘Paul’'s Samples' as Title