Using the Group Expert and Hierarchical Grouping in SAP Crystal Reports for Visual Studio 2010
page 3 of 4
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 29072/ 18

Step 2: Create the Report with Simple Grouping

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.


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 10:07:07 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search