The first step in designing this report is connecting to the
AdventureWorks database and bringing in the tables and views 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" and 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. IMPORTANT: Do not use the AdventureWorks2008 database. This database
uses the new geography type in the Person.Address table which is not recognized
by Crystal Reports.
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 4
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 expand 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
Production.Product, Sales.SalesOrderDetail, and Sales.SalesOrderHeader tables.
11. The
Sales.SalesOrderHeader table has two address fields, one for the bill to
address and one for the ship to address. Each of these fields is a foreign key
to the Person.Address table. We will have to add two "copies" of the
address table for each foreign key, but we can alias the table so we know which
is which. Add the Person.Address table to the Selected Tables list using the
> button.
12. Right
click on the Address table in the Selected Tables list and select Rename from
the pop-up menu. Change the name to AddressBillTo and press Enter. Crystal will lowercase the alias automatically.
13. Now
add the Person.Address table to the Selected Tables list again. This time
rename the table to AddressShipTo.
Figure 5
14. The
Person.Address table has a foreign key to the Person.StateProvince table so you
will also need to add the Person.StateProvince table to the Selected Tables
list twice. Rename the table to StateProvinceBillTo and StateProvinceShipTo
following the same pattern in steps 12 and 13.
Figure 6
15. 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.
16. 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.
17. The
SalesOrderHeader is the main table that has the data we are looking for. Crystal should have figured out the relationships between the
SalesOrderHeader.SalesOrderId and SalesOrderDetail.SalesOrderId, the
SalesOrderHeader.ContactId and Contact.ContactId, the
SalesOrderHeader.BillToAddressId and AddressBillTo.AddressId, and the
AddressBillTo.StateProvinceId and StateProvinceBillTo.StateProvinceId 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.
18. You
need to add the relationship between the SalesOrderDetail.ProductId and the
Product.ProductId. Click on the SalesOrderDetail.ProductId field and drag it
over top of the Product.ProductId field. You should see a blue line appear
between the two tables to denote the relationship. What this is doing is
building the joins for you in the query that selects the data from the
database. It is similar to the Access Query Designer. If you needed to do an
outer join, you can double click on the line between the two tables and change
the join to a Left, Right, or Full Outer Join.
19. Create
the rest of the relationships the same way by dragging the foreign key over the
primary key. The rest of the relationships are SalesOrderHeader.ShipToAddressId
= ShipToAddress.AddressID, and ShipToAddress.StateProvinceId =
StateProvinceShipTo.StateProvinceId. Click the OK button.
Figure 7
You should now see the report in Visual Studio 2008 again. 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.
