Building Reports Using ASP.NET and Crystal Reports - Part 6 - Build a Sales Forecast Report Using Parameter Fields
page 3 of 12
by Vince Varallo
Average Rating: 
Views (Total / Last 10 Days): 63149/ 85

Step 2: Connect to the database

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.

View Entire Article

User Comments

Title: Help   
Name: Arjun
Date: 2010-05-21 4:28:35 AM
Hi! anybody body can assit me to generate Accouting report trial balance, profit & loss, and balance sheet. using crystal report with ASP.NET with C#
Title: Nice Article   
Name: Rick
Date: 2009-12-01 4:56:29 PM
I read the entire series and I fell more comforable with reports. They were all put together well and every piece of code worked as expected. Thank for taking the time to share.

Product Spotlight
Product Spotlight 

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

©Copyright 1998-2021  |  Page Processed at 2021-12-04 6:34:28 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search