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.