This topic uses "Cascading Parameters" report to demonstrate how to add cascading parameters in a report.

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.

In this report, there are two related parameters, pCountry and pCustomers, where the list of customers for pCustomers parameter depends on the selected country in pCountry. After selecting a country, you can select one or more customers from the selected country and list all orders for those customers. The report uses ReportParameter.DisplayText property to display parameters in this report in a user-friendly way. This report also demonstrates the use of Calculated Fields feature using a calculated field named CountryDesc defined on the dsCountries data source. Perform the following steps to see how this can be done.

To create a report with cascading parameters

• Step 1: Create a report
• Step 2: Connect the report to data source
• Step 3: Adding cascading parameters
• Step 4: Create Layout
• Step 5: View the report

Step 1: Create a report

1. Open the C1FlexReportDesigner and go to FILE|New.
2. Click New Report drop down from the Reports tab and select Empty Report to create a report.

Step 2: Connect the report to data source

1. Switch to the Data tab, right-click the Main data source and choose Edit to open and the Data Sources Wizard and start editing.
2. Select OLEDB Data Provider from the Data provider drop-down and click the ellipsis button next to the Connection string textbox to select the C1NWind.mdb file.
3. Specify the following Sql statement in the Sql statement tab:
   

   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
   

4. Switch to the Calculated Fields tab and add a field named Salesperson with the following expression:

   FirstName & " " & lastname
   

   
   
5. 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
   

   
   
6. Switch to the Calculated Fields tab and add a field named CountryDesc with the following expression:

   Country & " (" & CustomerCount & " customers)"
   

   
   
7. 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
   

   
   

Step 3: Adding cascading parameters

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

   Property	Value
   DataType	String
   Prompt	Country
   Value	Germany

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

   Property	Value
   Data Source	dsCountries
   Label	CountryDesc
   Value	Country

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

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

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

   Property	Value
   Data Source	dsCustomers
   Label	CompanyName
   Value	CustomerID

   
   

Step 4: Create Layout

1. In the Header section, add four TextFields and set the following properties:

   Property	Value
   TextBox1.Text	=pCountry.Prompt & ":"
   TextBox2.Text	=pCountry.DisplayText & " (" & Count(*) & " orders total)"
   TextBox3.Text	=pCustomers.Prompt & ":"
   TextBox4.Text	=pCustomers.DisplayText

   
   
2. Click Groups in Home tab and add a group named CustomerGroup.
3. In the Groups wizard, set the following properties for the CustomerGroup group:
   

   Property	Value
   GroupBy	CustomerID
   Keep Together	KeepWholeGroup
   Sort	No Sort
   ShowGroupFooter	True
   ShowGroupHeader	True

   
   
4. Click OK to close the Groups wizard.
5. In CustomGroup_Header, add a Paragraph field and double-click the field to enter in edit mode.
6. In edit mode, right-click the Paragraph field and select Insert Expression.
7. In the Expression editor, select companyname from the Database Fields dropdown.
8. Click Done.
9. Right-click the Paragraph field again and select Insert Expression.
10. In the Expression editor, select Database Fields from the toolbar and type Count(*).
11. Click Done.
12. Type orders total in the field.
13. Add five TextFields in the header and set the following properties:

    Property	Value
    TextBox5.Text	Order ID
    TextBox6.Text	Salesperson
    TextBox7.Text	Order Date
    TextBox8.Text	Shipped Date
    TextBox9.Text	Freight

    
    
14. In the Details section, add five TextFields corresponding to the TextFields in the header and set the following properties:
    

    Property	Value
    TextField10.Text	=OrderID
    TextField11.Text	=Salesperson
    TextField12.Text	=OrderDate
    TextField13.Text	=ShippedDate
    TextField14.Text	=Freight

    
    
15. In CustomerGroup_Footer, add a TextField with the following property:

    Property	Value
    TextBox15.Text	="Total orders: " & Count(*)

Step 4: View the report

Preview the report with cascading parameters.