SQL

This is applicable when input type for the parameter is Dropdown or Option. Values that should appear on Dropdown / Option can be pre-defined or fetched dynamically from database using an SQL.

To get dynamic values:

You need to follow the below steps to get dynamic values for a parameter:

1. Under Source, select Dynamic

2. In Source Statement, specify SQL to be used to get data.

Tip: To fetch all the fields from a table, instead of writing entire SQL (for example, SELECT * FROM PRODUCT), you may specify only table name (For example, PRODUCT).

3. Click Verify If the SQL is valid, the fields it returned will be listed in Display Column and Value Column.

Note: If SQL is invalid, an error message will be displayed in SQL box.

4. In Display Column, select the field whose value should be displayed to the user. For example, SALESPERSON.

5. In Value Column, select the field whose value should be used (passed as filter). For example, SALESPERSONID.

Figure 15: Defining an SQL (under Dynamic) for a Dropdown Parameter

A parameter can also be used on report as a control (like other report fields).  Check Display Parameter Name checkbox and specify a name.  This name will appear as control name on report designer, which you can drag and drop on canvas (the report layout area).  Display Parameter Name has no effect when the Parameter Object is used in Ad hoc Report.