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.
11. 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.
12. 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.
13. Click
the plus sign next to OLE DB (ADO). This will display the OLE DB dialog which
allows you to select your AdventureWorks database.
14. Select
SQL Native Client from the list of providers and click Next.
15. Enter
the server name where the SQL Server database is installed.
16. 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.
17. 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'll 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.
18. 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.
19. Click
the plus sign next to AdventureWorks and you'll see the list of schemas defined
in the database. If you click the plus sign next to a schema name you'll 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.
20. 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 Sales schema and
expand the Tables. Click on the SalesOrderHeader table and then click the >
button to move this table to the Selected Tables list. Do the same for the SalesTerritory
table.
21. Now
that you have selected the tables you need to define the relationships between
these tables. To manually configure the relationships click the Links tab in
the Database Expert dialog.
22. 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.
23. The
line should already be drawn between the SalesOrderHeader and SalesTerritory
tables. If you don't see this line you can easily create the relationship by
clicking on the foreign key field and dragging it to the primary key field.
24. Click
the OK buton.
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'll see the
fields in that table. These are now available to be used in the report.
