New to v2016.1 is the ability to add drop-down lists to parameters based on a data object, stored procedure, or custom SQL. The following is a quick demonstration for how to set up and use a parameter drop-down.

Admin Console

Figure 1

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 'X' button to disable the parameter dropdown.

Figure 2

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.

Figure 3

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.

Figure 4

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.

Config XML

Another option for setting parameter drop-downs is adding your data to the Config File XML. 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, 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 drop-downs can be generated at runtime using the Exago .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 statement, 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";

Additional Usage

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.