One of the powerful features of Crystal Reports is the great amount of flexibility you have as a report developer in grouping your data.  Once you can group your data you can sum, count, average, perform standard deviation, variance, and a host of other operations on your data.  Grouping data in a SQL statement can be problematic because you can only include the fields you're grouping on in your select statement.  Crystal Reports does not have this limitation. 

Crystal also has a feature for hierarchy grouping for situations where you have nested groups and you don't know how many levels of grouping there are.  This article will show you how to create a report with simple grouping and summation fields and will also create a report using the hierarchy grouping feature. 

Before you begin you will need to have installed Visual Studio 2010. Crystal Reports is no longer distributed with Visual Studio and is available as a separate download. You can download Crystal Reports here. You also will need to download the AdventureWorks sample database from here.  Once you've installed the database you need to create a SQL Login that the application can use to connect to the database. My sample code expects a SQL Login to be called "aspalliance" and the password should be set to "aspalliance". The sample code for this application can be downloaded here.

1.    Launch Visual Studio 2010.

2.    Select FileàNew Project… from the menu.

3.    Select the ASP.NET Empty Web Application template from the list of C# templates.

4.    Name the project CrystalGrouping and click the OK button. Visual Studio will create an empty ASP.NET project with just the web.config file.

The first report we create will display all the employees grouped by department and display the count of employees in each department.

1.    Right click on the project file in the Solution Explorer and select Add New Item…

2.    Click on the Reporting node under the Installed Templates and then select the Crystal Reports template.  Name the report Department.rpt and click the Add button.

3.    Visual Studio will add the Report file to the project and display the Crystal Reports Gallery dialog box.

4.    Select the "As A Blank Report" option and click the OK button.

5.    Now you need to tell the report which database to connect to.  In the Field Explorer window, right click on the Database Fields node and select Database Expert from the pop-up menu.

6.    This will display the Database Expert dialog.  Click the plus sign next to the Create New Connection node.  Now click the plus sign next to the OLE DB(ADO) node.

7.    This will display the OLE DB (ADO) dialog.  Select SQL Server Native Client 10.0 from the list of providers and click the Next button.

8.    Fill in the appropriate server, user id, password, and database to point to the Adventure Works database.  Click the Next button and then click the Finish button.

9.    This should return you back to the Database Expert window.  You should see the AdventureWorks database listed under the OLE DB (ADO) node.

10. Click the plus sign next to the AdventureWorks node.  This displays the list of schemas defined in the database.  Expand the HumanResources schema.  This will display the object types in the schema.  Click the plus sign next to the Tables node.  This displays all the tables in that schema.

11. Click on the Employee table and then click the greater than (>) arrow to move this table the list of selected tables.

12. Repeat the same steps for the HumanResources.Department, HumanResources.EmployeeDepartmentHistory, and Person.Contact tables.

13. Click the OK button.  This will display the links tab.

14. You should see a link between each table which tells Crystal how the joins are structured.  Click the OK button.

This report will be quite simple.  It will display the employee's last and first name in the details section and group by the department name.  In the group header will add a count of the number of employees in that department.

15. Drag the Contact.LastName and the Contact.FirstName fields to the Details section in the report.

16. From the main menu click Crystal ReportsàInsertàGroup…

17. This will display the Insert Group Dialog.  Change the grouping condition to Department.GroupName and click the OK button.

18. You should now see two more sections added to the report, GroupHeaderSection1 and GroupFooterSection1.  By default the Group Name is added to the group header section.  Now let's add a count to the group header to display the count of employees in the department.

19. Right click on the LastName field in the Details section.  Select InsertàSummary from the pop-up menu.

20. The Insert Summary dialog box should be shown.  Change the "Calculate this summary" list to Count.

21. Change the "Summary location" to Group #1:Department.GroupName - A.  Click the OK button.

22. By default the field will be placed in the group footer section.  Move the field into the group header section to the right of the group name.

23. Hide the group footer section by right clicking to the gray bar that says "GroupFooterSection1 (Group Footer #1:Department.GroupName - A).  Click the Suppress from the pop-up menu.

Now you're ready to view the report.  Click the Main Report Preview button.  You should see the report with 6 groups.  The first group is the Executive General and Administration.  The count of employees should be 36.  Notice the group tree on the left hand side.  When you click on each group name it brings to directly to that group in the report.  This is a nice feature when viewing a report.

Step 3: Create the Report with Hierarchy Grouping

The next report will demonstrate how to create a report with nested groups similar to a tree view control.  In this example we'll use the management structure defined in the AdventureWorks database.  Each employee has one manager and all employees eventually roll up into one manager.  However, just like at a real company, each manager has a different number of employees and can have any number of managers underneath them.

1.    Right click on the project file and select AddàNew Item… from the pop-up menu.

2.    Select the Crystal Report template and change the name of the file to Managers.rpt.

3.    The Crystal Reports gallery dialog box will appear.  Select the "As a Blank Report" option and click the OK button.

4.    Right click on the Database Fields node in the Field Explorer and select Database Expert from the pop-up menu.

5.    You should see the AdventureWorks connection listed under My Connections.  Expand the node by clicking on the plus sign.  Expand the HumanResources schema and the tables. 

6.    Select the Employee table and then click the > button to move the table to the list of selected tables.

7.    The employee's name is stored in the Person.Contact table so move this table to the list of selected tables also.

8.    The employee\manager relation is depicted with the ManagerID field in the Employee table.  This relates back to the Employee table.  You'll need to add an alias of the Employee table and an alias for the Contact table to return the manager's name.  To do this click on the HumanResources.Employee table and move it to the list of selected tables.

9.    You will get a message saying that this table is already added.  Click the Yes button.  Crystal will alias the table as Employee_1.  That's not really a good name.  Click on the Employee_1 name in the selected tables list and then press F2.  This will allow you to rename the alias.  Change the name to Manager.

10. Repeat the same steps for the Person.Contact table.  Rename this alias to Manager_Contact.

11. Click the OK button to create the joins between the tables.  You'll need to create the joins to the Manager and Employee tables as well as the Manager and Manager_Contact tables.  Since one employee does not have a manager you need to make the join type between the Manager and Employee table a right outer join.  You need to do the same for the Manager_Contact and Manager join.  You do this by double clicking on the line between the two tables to bring up the join properties.

12. The Links tab of the Database Expert should look like the following image.

13. Click the OK button.

14. The next step is to create a group on the EmployeeID field.  From the main menu select Crystal ReportsàInsertàGroup…

15. Set the grouping field to Employee.EmployeeID.

16. Click the Options tab and then check the "Customize Group Name Field" box.

17. Select the Option to "Use a Formula as Group Name" and then click the button with the pencil on it.

18. This will bring up the formula editor which will allow you to enter a custom formula to display instead of the EmployeeID field.  Set the formula to the following.

ToText ({Contact.LastName} + ", " + {Contact.FirstName})

19. Click the Save and close button.

20. Click the OK button.

21. Now you need to tell Crystal about the hierarchal relationship between the EmployeeID and the MangerID field.  From the main menu select Crystal ReportsàReportàHierarchical Group Options.  This will display the Hierarchical Group Options dialog.

22. Check the box that says "Sort Data Hierarchically".  Change the Parent ID field to the Manager.EmployeeID field.  Only fields that match the data type of the grouping field is displayed in this list of choices.

23. Set the "Group Indent" to 0.25 inches.  This will indent the group header a quarter inch for each level.

24. Click the OK button.

25. There is nothing to display in the Details or Group Footer Section so you can suppress these two sections.  To suppress the section simply right click on the section header and select Suppress from the pop-up menu.

26. Click the Main Report Preview button.  You should get a report that looks like the following image.

Notice that the employees are nested under the manager just like a tree view in the report.  The Group pane actually has the tree structure so you can easily navigate to the employee or manager.  This is a nice feature when dealing with data in a hierarchy structure.

This article demonstrated the power and flexibility of the grouping features within Crystal Reports.  Once you have your groups setup you can then easily add fields to perform aggregation, averages, counts, standard deviation and a host of other functions.  Crystal also has a powerful feature of displaying hierarchical data which is useful for displaying data in a tree structure.  I hope you found this article useful and I wish you good luck on your project and happy coding.