Filtering a parameter with another parameter

You can use the value of one parameter to filter another one. The following examples show you the two methods that you can use to achieve this purpose.

Method 1

  1. Open the demo catalog SampleReports.cat and in the Catalog Browser, expand the desired data source node.
  2. Right-click the Parameters node and then click Add Parameter.
  3. In the Create Parameter dialog, enter CasParam in the Name field, select Bind with Cascading Columns from the Value Setting drop-down list, and then select Customers of the Tables type from the Data Source drop-down list.
  4. Click to add a parameter row, select Region as the Bind Column and Display Column, and then click in the Parameter cell to create the parameter in the cascading group.
  5. Click to add another parameter row, select Country as the Bind Column and Display Column, and then click in the Parameter cell.
  6. Click to add one more parameter row, select City as the Bind Column and Display Column, click in the Parameter cell, and then click OK in the dialog.
  7. In the same data source, create a query CustomersInfo on the table Customers and select all the fields in the table.
  8. Filter the records of the query by adding a condition as follows in the Search Condition dialog (for details, see Filtering with the filter format):

  9. Create a table report based on the query, and have the fields Customer Name, City, Country and Region displayed in the report.
  10. View the report and the Enter Parameter Values dialog appears.
  11. From the region drop-down list, select a region, then only the countries in the selected region will be displayed in the country drop-down list. Choose a country from the list, and then only the cities in the selected country will be displayed in the city drop-down list.

  12. Click OK. You will now find that only the specified records are shown.

Method 2

  1. Open the demo catalog SampleReports.cat and in the Catalog Browser, expand the desired data source node.
  2. Right-click the Parameters node and then click Add Parameter.
  3. Create a parameter named ParamRegion, select Bind with Single Column from the Value Setting drop-down list, select Tables and Views from the Source drop-down list, select Region as the Bind Column and Display Column. Then in the value cell of the Import SQL option, you can see the following statement:

    SELECT CUSTOMERS.REGION FROM CUSTOMERS

  4. Create another parameter named ParamCountry. Bind it with the column Country, then click in the value cell of Import SQL and edit its SQL statement as follows:

    SELECT CUSTOMERS.COUNTRY FROM CUSTOMERS WHERE (Customers.Region=@ParamRegion)

  5. Create one more parameter named ParamCity. Bind it with the column City, and then edit its SQL statement as follows:

    SELECT CUSTOMERS.City FROM CUSTOMERS WHERE (Customers.Country=@ParamCountry)

  6. In the same data source, create a query CustomersInfo on the table Customers and select all the fields in the table, and filter the records of the query by adding a condition as follows:

    CUSTOMERS.CITY=@ParamCity

  7. Create a table report based on the query, and have the fields Customer Name, City, Country and Region displayed in the report.
  8. View the report. In the Enter Parameter Values dialog, you can use the value of ParamRegion to filter the value of ParamCounty, and use the value of ParamCounty to filter the value of ParamCity.