Drop-down lists can be added to parameters based on a data object, stored procedure, or custom SQL object.

Admin Console

The Parameter Dropdown Object field, if set, uses an object or procedure to populate a custom list of parameter values for the user when prompted. The fields below it are hidden until Parameter Dropdown Object contains a value.

The first drop-down list contains the list of Data Sources in the current configuration (see "Northwind" in Figure 1). Use this to select a preexisting database. Set to blank or click the Delete icon to disable the parameter dropdown.

The second field, when clicked, queries the selected data source and displays a breakdown of available data objects contained in the selected data source. (see Figure 2). Select one and press OK to use that data object for your list.

If the desired procedure isn't available in the data source, you can also use custom SQL to populate the drop-down list. Click on the SQL button and a custom SQL object dialog will open (see Figure 3).

You can enter custom SQL into the dialog box. Other parameters can be used as variables by typing them in @MyParameter@ form, or by selecting it from the parameter list and clicking the '+ Add' button. You can verify the syntax of the SQL by clicking on the 'Test ✓' button.

The Stored Procedure Parameters field (see Figure 4) allows you to select preexisting parameters to use as variables for the selected stored procedure. This field will only display if the selected parameter dropdown object is a stored procedure.

The Value field is a column from the data object or custom SQL that sets the actual value of the parameter at runtime. This represents a set of values that are not displayed to the end user but are instead used when parameter values are required in custom SQL or stored procedures, or other server side processing.

The Display Value field is a column from the data object or custom SQL that sets the display value of the parameter for the dropdown selector.  This represents the set of values that should be presented to the end user when they are executing or scheduling a report.

Note

Parameter Dropdowns do not support default values.

Configuration File

Another option for setting parameter dropdowns is adding them to the configuration file. The following XML corresponds to the selected options in Figure 1 above.

<parameter>
   <id>EmployeeList</id>
   <data_type>string</data_type>
   <value />
   <hidden>False</hidden>
   <prompt_text>Select an Employee</prompt_text>
   <parameter_dropdown>
      <db_name>Employees</db_name>
      <sql_stmt />
      <datasource_id>0</datasource_id>
      <object_type>table</object_type>
      <schema>dbo</schema>
      <value_field>LastName</value_field>
      <display_value_field>LastName</display_value_field>
      <display_data_type>string</display_data_type>
      <display_value />
      <sp_params />
   </parameter_dropdown>
</parameter>

If using a custom SQL statement object, insert that as a formatted string in the <sql_stmt> field:

<sql_stmt>SELECT @MyParameter@
FROM dbo.Employees</sql_stmt>

If using stored procedure parameters, insert them as a comma-delimited list in the <sp_params> field:

<sp_params>MyParameter,MyParameter2</sp_params>

API Application

Parameter dropdowns can be generated at runtime using the .NET API. The following code corresponds to the selected options in Figure 1 above.

Parameter myParam = myApi.Parameters.NewParameter();

myParam.Id = "EmployeeList";
myParam.DataType = (int)DataType.String;
myParam.IsHidden = false;
myParam.PromptText = "Select an Employee";

myParam.DropdownDataSourceId = myApi.DataSources.GetDataSource("Northwind").Id;
myParam.DropdownObjectType = DataObjectType.Table;
myParam.DropdownSchemaName = "dbo";
myParam.DropdownDbName = "Employees";
myParam.DropdownValueField = "LastName";
myParam.DropdownDisplayValueField = "LastName";

If using a custom SQL object, insert that as a formatted string in the DropdownSqlStmt field:

myParam.DropdownSqlStmt = @"SELECT @MyParameter@ FROM dbo.Employees";

If using stored procedure parameters, insert them as a comma-delimited string in the StoredProcParams field:

myParam.StoredProcParams = "MyParameter,MyParameter2";

Extensibility

Two new events were added to provide additional support and customization: The OnLoadReportParameters Server Event, and the OnChangeParameterValue Action Event.

Server Event

OnLoadReportParameters passes a list of Parameter elements that can be reordered or modified before they are sent to the client for display. Called when report parameters are loaded, but before any processing has occurred.

args[] contains one object, a list of Parameter elements.

The event has a void return value.

For the full documentation, see Server Events.

Action Event

OnChangeParameterValue fires when a change is made to a parameter value in a report. The list of report parameters can be retrieved from the clientInfo.parameterListCtrl object.