Previous Topic
Filtering a Parameter with Another Parameter
You can use the value of one parameter to filter another one. In this way you can create your own cascading parameters such as using pCountry to filter the values returned by a parameter listing available states. The following examples show you the two methods that you can use to achieve this purpose.
Method 1 (Use this method when the SQL needed to select the parameter values is very simple.)
- Open the catalog file SampleReports.cat in
<install_root>\Demo\Reports\SampleReports. - In the Catalog Manager, expand the desired data source node.
- Right-click the Parameters node and then select New Parameter. The New Parameter dialog appears.
- 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.
- Select
to add a parameter row, select Region as the Bind Column and Display Column, and then select in the Parameter cell to create the parameter in the cascading group. - Select to add another parameter row, select Country as the Bind Column and Display Column, and then select in the Parameter cell.
- Select to add one more parameter row, select City as the Bind Column and Display Column, select in the Parameter cell, and then select OK in the dialog.
- In the same data source, create a query CustomersInfo on the table Customers and select all the fields in the table.
- 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):
- Create a page report with a table in it based on the query, and have the fields Customer Name, City, Country and Region displayed in the report.
- View the report and the Enter Parameter Values dialog appears.
- 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.
- Select OK. You will now find that only the specified records are shown.
Method 2 (Use this method when you need more customized SQL to show the correct values.)
- Open the catalog file SampleReports.cat in
<install_root>\Demo\Reports\SampleReports. - In the Catalog Manager, expand the desired data source node.
- Right-click the Parameters node and then select New Parameter.
- 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 DISTINCT CUSTOMERS.REGION FROM CUSTOMERS
- Create another parameter named ParamCountry. Bind it with the column Country, then select
in the value cell of Import SQL and edit its SQL statement as follows:
SELECT DISTINCT CUSTOMERS.COUNTRY FROM CUSTOMERS WHERE (Customers.Region=@ParamRegion and Customers.YTDSALES > 0 ) - Create one more parameter named ParamCity. Bind it with the column City, and then edit its SQL statement as follows:
SELECT DISTINCT CUSTOMERS.City FROM CUSTOMERS WHERE (Customers.Country=@ParamCountry and Customers.YTDSALES > 0 ) - 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 - Create a page report with a table in it based on the query, and have the fields Customer Name, City, Country and Region displayed in the report.
- 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. You will not see any countries and cities where their are no sales.