Chained Parameters

Chained Parameters

A chained parameter is one whose values depend on the value selected in another parameter. The parameter that triggers the change is called the "parent". Only Single-Value parameters can be chained parents. A chained parameter can be used as a parent for another parameter, which in turn can have another child parameter and so on.

Example

Suppose we want to view customers who reside in a particular city. We want the user to select a country and then have cities in that country displayed. He'll then pick one of these cities and run the report. We have a CUSTOMERS table, and one of the columns it has is CITY_ID. We also have a COUNTRIES table which has COUNTRY_ID and COUNTRY_NAME columns. Finally, we have a CITIES table with CITY_ID, CITY_NAME and COUNTRY_ID columns.

We could take the following steps to set up the report.

• Create a Dynamic LOV report named Countries that will display country names. Depending on what country is selected, the available cities will change. The countries report would have the following SQL.

  SELECT COUNTRY_ID, COUNTRY_NAME
  FROM COUNTRIES
  ORDER BY 2
  

• Create a Dynamic LOV report named Cities that will display city names. The query needs to have a parameter label for the country parameter. This will be replaced at runtime with the country selected.

  SELECT CITY_ID, CITY_NAME
  FROM CITIES
  WHERE COUNTRY_ID = #countryId#
  ORDER BY 2
  

• Once you have saved the Cities report, click on the Parameters option to assign the countryId parameter

• Use the Add New option to create a Single-Value parameter with a Name of countryId, Label of Country, Data Type of Integer, set it to Shared, set it to Use LOV and select Countries as the LOV Report.

• Create the main report. The SQL for the report will need to have a placeholder for the cityId parameter.

  SELECT FIRST_NAME, LAST_NAME, EMAIL
  FROM CUSTOMERS
  WHERE CITY_ID = #cityId#
  

• Once you have saved the main report, click on the Parameters option to allocate the report parameters

• Use the Add Existing option to add the countryId shared parameter created earlier
• Use the Add New option to create a Single-Value parameter with a Name of cityId, Label of City and Data Type of Integer. Once you have saved this parameter, click on its Edit button on the Report Parameters page and set the Chained Parents field to countryId i.e. the name of the parent parameter for this chained parameter. If the parent parameter data type is Date or DateTime, in the Chained Parents field, put the name of the parent parameter and a "-hidden" suffix e.g. countryId-hidden, and then set the Chained Depends field to the name of the parent parameter e.g. countryId.

Now when you run the main report, you are presented with two drop down boxes. Depending on what country you select, a different set of cities is displayed, from which you can choose and run the report to see the final results.