This topic uses "Groups and Sorting" report to demonstrate how grouping, group total, subtotal, and sorting are performed in a report.

Grouping

Grouping allows you to create groups of records based on common attributes of the records. For example, in this report, all the records having same EmployeeID are grouped together to form EmployeeGroup. For more information on grouping feature in FlexReport, refer Grouping Data.

This report contains two groups, namely EmployeeGroup and OrderGroup with EmployeeID and OrderID as their respective grouping criterion. Here, EmployeeGroup is added first and then the OrderGroup, so the data is first grouped on the basis of Employee ID and then the subgroups are formed within these groups on the basis of Order ID.

The report also calculates the group totals and subtotals. You can calculate the total or subtotal using =Sum(OrderSum) expression in a TextField. When a TextField with this expression is dropped in the EmployeeGroup header section, it calculates the total sales for all the orders by an employee, which is the group total. Similarly, when a TextField with the same expression is dropped in the OrderGroup footer section, it calculates the group subtotal.

Sorting

Sorting allows you to organize data in ascending or descending order. In FlexReport, the groups are sorted using group expressions. However, you can change the manner in which the groups are sorted using Group.SortExpression property. The expression may contain aggregate functions, for example in this report the groups are sorted using Sum(OrderSum) expression which contains an aggregate function. For more information on sorting, refer Sorting Data.

In this report, the EmployeeGroup is sorted on the basis of total sales in descending order. However, you can also sort the group alphabetically. In addition, you can provide the sorting options in Parameters Panel too. Perform the following steps to see how this can be done.

To create the report in FlexReportDesigner

• Step 1: Create a report
• Step 2: Connect the report to a data source
• Step 3: Add calculated fields and parameters
• Step 4: Group and sort data
• 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 a 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 Employees.EmployeeID as EmployeeID,
          Employees.FirstName as FirstName,
          Employees.LastName as LastName,
          Orders.OrderID as OrderID,
          Orders.OrderDate as OrderDate,
          Orders.ShippedDate as ShippedDate,
          Products.ProductName as ProductName,
          od.UnitPrice as UnitPrice,
          od.Quantity as Quantity
     FROM (([Order Details] od INNER JOIN Orders ON od.OrderID = Orders.OrderID)
          INNER JOIN Products ON od.ProductID = Products.ProductID)
          INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
   

   
   

Step 3: Add calculated fields and parameters

1. Switch to the Calculated Fields tab and add two calculated fields with the following properties:
   

   Name	Expression
   FullName	FirstName & " " & LastName
   OrderSum	UnitPrice * Quantity

   
   
2. In the Data tab, right-click Parameters and select Add Parameters to add three parameters, pEmployeeSortExpression, pEmployeeSortOrder, and pRepeatEmployeeHeader.
3. Set the following properties for the parameters from the Properties window.
   

   pEmployeeSortExpression properties
   Prompt	Value	DataType
   Employees Sort Expression	Sum(OrderSum)	String

   
   

   pEmployeeSortOrder properties
   Prompt	Value	DataType
   Employees Sort Order	Descending	String

   
   

   pRepeatEmployeeHeader properties
   Prompt	Value	DataType
   Repeat Employee Header	False	Boolean

   
   
4. Click the ellipsis button next to the AllowedValuesDefinition property, select Values radio button, click Add button to add the following values for pEmployeeSortExpression and pEmployeeSortOrder parameters:
   

   pEmployeeSortExpression properties
   Label	Value
   Alphabetical	FullName
   Sales	Sum(OrderSum)

   
   

   pEmployeeSortOrder properties
   Label	Value
   NoSort	NoSort
   Ascending	Ascending
   Descending	Descending

   
   Note that Add button will add blank values. To edit them, double click below the Label to add the required labels and Values to add the required values.
   

Step 4: Group and sort data

1. Click Groups in Home tab and add EmployeeGroup and then add OrderGroup.
   Note that the groups are nested in the order in which they appear in the group wizard. Therefore, as you add the OrderGroup after the EmployeeGroup, it is added as the subgroup of the EmployeeGroup.
2. In the Groups wizard, set the following properties for the newly created groups:
   

   EmployeeGroup properties	Values
   GroupBy	EmployeeID
   Keep Together	KeepFirstDetail
   Sort	Ascending
   ShowGroupFooter	True
   ShowGroupHeader	True
   OutlineLabel	=FullName

   
   

   OrderGroup properties	Values
   GroupBy	OrderID
   Keep Together	KeepWholeGroup
   Sort	No Sort
   ShowGroupFooter	True
   ShowGroupHeader	True

3. Click OK to close the Groups wizard.
4. In the EmployeeGroup_Header, add three TextFields from the INSERT tab and set the following properties:
   

   Property	Value
   TextField1.Text	=FullName
   TextField2.Text	Total Sales:
   TextField3.Text	=Sum(OrderSum)

   
   The Sum(OrderSum) calculates the group total, i.e., total sales for all the orders by an Employee.
5. Add a ParagraphField in the EmployeeGroup_Footer and display the group total using the following expression:
   Total Sales for {FullName}:{Sum(OrderSum)}
6. Right-Click the OrderGroup_Header and select Add SubSection from the context menu to divide the header into two parts.
7. In OrderGroup_Header/ <A>, add six more TextFields and set the following properties:
   

   Property	Value
   TextField4.Text	Order ID
   TextField5.Text	=OrderID
   TextField6.Text	Order Date
   TextField7.Text	=OrderDate
   TextField8.Text	Shipped Date
   TextField9.Text	=ShippedDate

8. In OrderGroup_Header/ <B>, add three more TextFields, Product Name, Unit Price, and Quantity.
9. In the Details section, add three more TextFields corresponding to the TextFields in OrderGroup_Header/ <B>, and set the following properties:
   

   Property	Value
   TextField13.Text	=ProductName
   TextField14.Text	=UnitPrice
   TextField15.Text	=Quantity

10. In the OrderGroup_Footer, display the subtotal of the order group by using a TextField with its Text property set to =Sum(OrderSum).
11. Select the report name from the drop-down situated above the list of properties in the Properties window.
12. Go to GlobalScripts property of the report and write following expression in the VBScriptEditor to sort the groups using SortExpression.
    
    

    EmployeeGroup.SortExpression = pEmployeeSortExpression.Value
    EmployeeGroup.Sort = pEmployeeSortOrder.Value
    

    
    
13. Go to OnOpen property of the report and write the following expression in the VBScript Editor.
    
    

    If pEmployeeSortOrder = "NoSort" Then
      fldSortDesc = "No Sorting"
    Else
      fldSortDesc = pEmployeeSortExpression.DisplayText & " (" & pEmployeeSortOrder.DisplayText & ")"
    EndIf
    
    EmployeeGroup_Header.Repeat = pRepeatEmployeeHeader.Value
    

    
    

Step 5: View the report

1. Preview the report.
2. In the Preview mode, click Parameters from the View group to open the parameters panel and apply parameters.