Cascading Parameters

In order to manage large amount of data in reports, you need to use cascading parameters. With cascading parameters, set of related parameters can be defined so that the list of values for one parameter depends on the value selected in another parameter.

Here, we will create a report that contains two parameters pCountry and pCustomers. After selecting a country, you can select one or more customers from the selected country and list all orders for those customers. The ReportParameter.DisplayText property is used to display parameters in this report.

Let us create a report in which cascading parameters is used.

Create a new report and bind it to the Main data source using the following Sql Statement:

Select orderid, orders.customerid, companyname, employees.firstname,
employees.lastname, orderdate, RequiredDate, shippeddate, Freight
from (orders inner join customers on orders.customerid = customers.customerid)
inner join employees on orders.employeeid = employees.employeeid
where orders.CustomerID in pCustomers

Switch to the Calculated Fields tab and add a field named Salesperson with the following expression:
```
FirstName & " " & lastname
```
Add a new data source, dsCountries, and bind the report to the data source using the following Sql Statement:
```
Select Country, Count(*) as CustomerCount
from Customers group by Country order by Country
```
Switch to the Calculated Fields tab and add a field named CountryDesc with the following expression:
```
Country & " (" & CustomerCount & " customers)"
```
Add another data source named dsCustomers and bind the report to the data source using the following Sql Statement:
```
Select CustomerID, CompanyName from Customers where Country = pCountry
```

Add a parameter, pCountry, and set the following properties from the Properties window.

DataType	String
Prompt	Country
Value	Germany

Click the ellipsis button next to the AllowedValuesDefinition property, select From Data Source radio button, and set the following properties:

Data Source	dsCountries
Label	CountryDesc
Value	Country

Add a parameter, pCustomers, and set the following properties from the Properties window.

DataType	String
MultiValue	True
Prompt	Customers
Value	[MORGK, LEHMS]

Click the ellipsis button next to the AllowedValuesDefinition property and select From Data Source radio button, and set the following properties:

Data Source	dsCustomers
Label	CompanyName
Value	CustomerID

Preview the report.

Note: For the complete report, see report 'Cascading Parameters' in the FlexCommonTasks.flxr report definition file, which is available in the ComponentOne Samples\Winforms\C1FlexReport\CS\FlexCommonTasks folder. The data base used is C1NWind.mdb which is also available in the ComponentOne Samples folder.