The first step in designing this report is connecting to the
AdventureWorks database and bringing in the tables that you need. These steps
assume you have already downloaded and run the install for the AdventureWorks
database.
1.
Right click on the Database Fields node in the Field Explorer. If you do
not see the Field Explorer window, select Crystal ReportsàField Explorer from the menu. Select
Database Expert… from the pop-up menu.
2.
This report is going to connect directly to the database using OLE DB.
To connect to the database click the plus sign next to the Create New
Connection node.
3.
Click the plus sign next to OLE DB (ADO). This will display the OLE DB
dialog which allows you to select your AdventureWorks database.
4.
Select SQL Native Client from the list of providers and click Next.
5.
Enter the server name where the SQL Server database is installed.
6.
You can either use integrated security or a SQL Login and password to
connect to the database. This depends on how you setup your database when you
installed it. I created a SQL Login called "aspalliance," gave it a
password of "aspalliance" and added the Login to the db_owner role in
the AdventureWorks database. Enter the User ID and Password.
7.
Now click on the Database drop down list. You should see AdventureWorks
as one of the options. If you do not then the SQL Login does not have access to
the database. You will have to use SQL Server Management Studio to add the
aspalliance Login and add them to the db_owner role for the AdventureWorks
database.
8.
Once you have selected AdventureWorks for the database, click Next and
then click Finish.
Your SQL Server should appear under the OLE DB (ADO) node
and the AdventureWorks database should be listed under the server name.
Figure 3
9.
Click the plus sign next to AdventureWorks and you will see the list of
schemas defined in the database. If you click the plus sign next to a schema
name, you will see two nodes, one for Tables and one for Views. Clicking the
plus sign next to either will display the list of tables or views in that
schema.
10. The
next step is to select the appropriate tables and views for the report and move
them to the Selected Tables list in the dialog box. Expand the Person schema
and the Tables. Click on the Contact table and then click the > button to
move this table to the Selected Tables list. Do the same for the HumanResources.Employee,
Sales.SalesOrderHeader, Sales.SalesPerson, and Sales.SalesTerritory tables.
11. Now
that you have selected the tables, you need to define the relationships between
these tables. Crystal does a pretty good job of figuring this out, but cannot
figure everything out automatically. To manually configure the relationships
click the Links tab in the Database Expert dialog.
12. This
displays a graphical representation of the tables. You can make the dialog box
bigger by dragging the lower right hand corner of the dialog box. I like to
make this big so I can see as many tables as possible.
13. Crystal should have figured out the relationships between the SalesOrderHeader.SalesPersonId
and SalesPerson.SalesPersonId, the SalesPerson.SalesPersonId and Employee.EmployeeId,
the Employee.ContactId and Contact.ContactId, and the SalesPerson.TerritoryId
and SalesTerritory.TerritoryId tables. You should see a line between the primary
and foreign keys between these tables. If you do not, you can easily create the
relationship by clicking on the foreign key field and dragging it to the
primary key field. You will need to remove the relationships between the
SalesOrderHeader.ContactId and the Contact.ContactId, and the relationship
between the SalesOrderHeader.TerritoryId and the SalesTerritory.TerritoryID.
Figure 4
14. Click
the OK button.
You should now see the report in Visual Studio 2008. Click
the plus sign next to the Database Fields node in the Field Explorer. You see
all your tables listed. Click the plus sign next to a table and you will see
the fields in that table. These are now available to be used in the report.
