Associating the Report Input with the Data Set

Create a parameter for the data set and associate it with the report input. Then use the parameter in the data set query that was created for the report. In a dynamic input, the associated data set contains all of the values that the end user can select.

Before You Begin

Add Inputs to Reports
To use drop down or list box input control types for Oracle data sets, you must first Configure List Inputs for Oracle Database Reports.
To create data sets, build reports, manage reports, and deploy reports to the dashboard, your CommCell user account requires a role with the following permissions: Add Report, Delete Report, Edit Report, Execute Report, Add Datasource, Delete Datasource, Edit Datasource, Query Datasource.
Your CommCell user account must have an association with the CommCell entity.

For more information on users, permissions, and associations, see User Administration and Security - Overview.

Procedure

On Web Console, click Reports.

The Reports page appears.
From the navigation pane, click Configuration > Reports, and next to the Report Name under Actions, click Edit.
On the Data Set tab, select the main data set for the report and click Edit .
In the Edit Data Set dialog box, on the Parameters tab, click Add.
In the Parameter box, type a name for the parameter.

Do not include spaces in the parameter name.
In the Type list, configure the type of data that will appear in the input:
- To use the same type of data that you specified in the Edit Report Input dialog box, leave the selected value.
- To change the type of data, select a different value from the list.
  
  If you change data types, the data types must be compatible. For example, you can change Integer to String. However, you cannot change Date to Boolean.
If you selected Allow Multiselection when you added the input to the report, select Multi.

If you configure multi selection, you must also update the report query to include a table variable that will get the data, rather than a simple variable.

Important: The variables that you add must be preceded by the at symbol (@) if you are using them in the Post query.
To make this report parameter required for report end users, select Required.

When you make a parameter required, the report end users must select an option from the input to view any data in the report.
To assign the value of the report input, click Insert, expand Input, select the report input, and then click Insert.

The system automatically creates a variable for each data set parameter.
On the Query tab, in the Query box, update the report query to include the variable:
- If you gather data from the CommServ database or another type of SQL database, include the variable with the @ symbol.
  
  For example, type:
```
 
where idataagent = @agenttype.
```
- If you configured multiselection for a check box or list box input control type, then include the variable in the where clause in parentheses.
  
  For example, type:
```
 
where idataagent in (select agenttype from @agenttype).
```
- If you configured a custom range for a date range input control type, then include the variable in the where clause with startdate and enddate.
  
  For example:
```
where startdate >= @duration_from and enddate <= @duration_to
```
  Where duration is the variable you created for the date range input on the Parameter tab. For more information, see Date Range Input Control: Custom Range.
  
  We automatically append _to and _from to the variable used in the input parameter.
- If you gather data from an Oracle database, include the variable without the @ symbol.
  
  You must write statements in PL/SQL and use the @cursor moniker with the OPEN-FOR statement before you add the variable.
  
  For example, type:
```
 
OPEN @cursor FOR SELECT * FROM DEPARTMENTSemployee_id = emp_id;
```
- If you configured a drop down or list box input control type using an Oracle database, then include the variable without the @ symbol to refer to the collection types configured in the Oracle database.
  
  You must write statements in PL/SQL and use the @cursor moniker with the OPEN-FOR statement before you add the variable.
  
  For example, type:
```
OPEN ? FOR SELECT * FROM COUNTRIES
 WHERE REGION_ID in (@id)
```
To configure the input as a post query filter, on the Advanced tab, select Post Query Filter, and then add the variable.
- Enter a where clause that includes the variable.
  
  For example, type:
```
 
select * from $this$ where idataagent = @agenttype.
```
- If you configured multiselection for a check box or list box input control type, then enter a where clause that includes the variable without the @ symbol.
  
  For example, type:
```
 
select * from $this$ where idataagent in (select agenttype from agenttype).
```
- If you configured a custom range for a date range input control type, then enter a where clause that includes the variable with startdate and enddate.
  
  For example, type:
```
select * from $this$ where startdate >= @duration_from and enddate <= @duration_to
```
Click Done.