Building an Invoice Application with ASP.NET and Crystal Reports - Part 1
page 3 of 9
by Vince Varallo
Feedback
Average Rating: 
Views (Total / Last 10 Days): 103797/ 212

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.

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.


View Entire Article

User Comments

Title: pranav   
Name: pranav
Date: 2012-11-26 1:22:05 AM
Comment:
helllo
Title: Need the Images ... Please   
Name: KM
Date: 2011-12-25 8:26:20 AM
Comment:
Hi the article doesn't load any images. Please check. Will really appreciate your help
Title: Missing Images   
Name: Johnny come lately
Date: 2011-11-22 10:22:47 AM
Comment:
Where are the pictures!! Its hard to follow without the images
Title: birendra yogi   
Name: bina
Date: 2011-02-02 8:16:21 AM
Comment:
I have used this article to create my own invoices for my business, and now back on to refresh my memory for a client's invoices
Title: This was perfect   
Name: Edward Pinto
Date: 2010-12-11 1:35:06 PM
Comment:
Thanks for the Listing 3 formula. I remember using such a formula a while back and needed it at a client site in a real hurry. this worked great esp some address line 2 or 3 were blank,

Here is my variation.

// Edward M Pinto 12/11/2010
// This formula was created to avoid the nested subreport for the shipping address.
//It evaluates each feild for null values and suppresses them if they are null

local StringVar shipTo := {@ShipAddr1} + chr(13);

if isnull({@ShipAddr2}) = false then
shipTo := shipTo + {@ShipAddr2} + chr(13);

if isnull({@ShipAddr3}) = false then
shipTo := shipTo + {@ShipAddr3} + chr(13);

if isnull({@ShipAddr4}) = false then
shipTo := shipTo + {@ShipAddr4} + chr(13);

//builds the shipping label
shipTo := shipTo + {@ShipCityStateZip}+Chr(13)+{@ShipCountry}
Title: exlent   
Name: latha
Date: 2010-11-08 6:17:00 AM
Comment:
it is useful to all
Title: Fantastic Article   
Name: Phil Smith
Date: 2010-10-25 7:05:46 AM
Comment:
I have used this article to create my own invoices for my business, and now back on to refresh my memory for a client's invoices.
Title: good practice code   
Name: vishwajeet singh
Date: 2010-09-30 6:24:39 AM
Comment:
its realy good practice code i god lots of conceptual knpwledge through this
Title: Miss   
Name: Kanchana Sinha
Date: 2010-09-23 3:08:51 AM
Comment:
Thanxs for your sample code,it is very easy to understand and it helped me to create invoice.
Title: Mr   
Name: Kumar
Date: 2010-03-19 2:53:12 AM
Comment:
Thank you for your sample code, which is really a good stuff for a beginners, and also it is very easy to understand.
Title: Mr.   
Name: Sam
Date: 2010-03-01 5:24:21 AM
Comment:
This article was of great help. Thanks a ton :)
Title: Nice Article   
Name: Rick
Date: 2009-12-01 11:21:23 AM
Comment:
The article was easy to follow and touched on thing I would not normally experiment with. Thanks for taking the time to put the article together
Title: gr8 stuff   
Name: sabata mereeotlhe
Date: 2009-11-25 7:34:42 AM
Comment:
thank you gr8 work
Title: Thank you   
Name: Tabitha
Date: 2009-11-18 10:00:57 AM
Comment:
Thank you very much!!!
Title: Thanks for doing such an awesome article!   
Name: Julius
Date: 2009-10-28 9:16:06 PM
Comment:
Very good article. Really appreciate it.
Title: Awesome tutorial!   
Name: Nivi
Date: 2009-09-13 12:22:10 AM
Comment:
Thanks for the awesome tutorial. It really helped me.
Thanks again
Title: Excellent Article   
Name: sai
Date: 2009-09-01 9:11:15 AM
Comment:
Thankyou so much for such a wonderful article on Crystal Reports.
Title: Landsailor   
Name: Pete Lyons
Date: 2009-08-31 8:34:22 PM
Comment:
Vince- What an amazing tutorial. There are many ways to skin a cat when it comes to reports delivery, but this is by far the most insightful way to tie-in .net with my crystal skills. Thanks again :)))))) ~Pete
Title: Error in Sum formula   
Name: Pavan
Date: 2009-08-25 10:28:17 AM
Comment:
I am not sure where I made mistake but I started from scratch again making sure I check the Preview after each field added to the report and it worked this time.

I have a quick question, Since we are using pull method, Do we need to use BindReport method? Why cant we just use the reportviewer.selectionformula?

Thanks for the wonderful tutorial!!
Title: Error in Sum formula   
Name: Pavan
Date: 2009-08-25 2:31:29 AM
Comment:
Hi,
Error in formula . 'Sum({SalesOrderDetail.LineTotal}, {SalesOrderHeader.SalesOrderNumber}) ' The result of selection formula must be a boolean.

Help please.
Thanks
Title: Best for beginner   
Name: murugaperumalwin
Date: 2009-08-07 8:30:36 AM
Comment:
hai Vince Varallo...

this is very useful for a people who still have't knowledge

about crystal report.

thanks
MP
Title: superb article   
Name: Raj
Date: 2009-07-30 2:27:41 AM
Comment:
very great article
Title: good article   
Name: john
Date: 2009-07-30 2:25:06 AM
Comment:
very good article.........
Title: Great Article   
Name: Caitriona
Date: 2009-06-11 8:50:55 AM
Comment:
I found this article extreemly useful. Thank you so much for sharing your talents!

Caitriona
Title: Child Nodes not allowed   
Name: Paul
Date: 2009-05-27 11:05:56 AM
Comment:
Hi,
I am getting an error like:-
Child nodes not allowed from the web.config file.
I am using ASP.NET2005 and SQL2005.
Is it because of this I got this error?
Thanks,
Paul
Title: Link for the Adventure Works database   
Name: Vince
Date: 2009-05-27 7:58:34 AM
Comment:
The page that has the download is http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407

Click on the SQL2008.AdventureWorks All Databases.x86.msi link.
Title: Thanks so much   
Name: Paul
Date: 2009-05-26 5:23:07 AM
Comment:
Hi Vince,
It is really an excellent article.
I could not find the Adventure database in the downloads.
Where can I find it?
Thanks so much,
Paul
Title: Thanks - How about a version for VB users?   
Name: Art
Date: 2009-05-22 10:07:14 AM
Comment:
Thanks Vince!

I know some people are never satisfied ;-) but how about adding the "version b" listings for those of us who use VB?
Title: dr   
Name: kola
Date: 2009-05-11 11:12:06 AM
Comment:
good
Title: Thanks   
Name: Suresh Kumar Gundala
Date: 2009-05-08 5:04:13 AM
Comment:
Hi Vince,

Nice article. Actually i dont know anything about crystal reports. Now i got an idea.. This is a wonderful article.
Great work yaar. Thank you very much for writting such a nice article
Title: Good Article for a Quick start   
Name: Niki
Date: 2009-05-05 7:44:10 AM
Comment:
Thank you Vince, A very good article for a quick start on crystal report designing.We expect more articles from you in the same space

Product Spotlight
Product Spotlight 



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


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-21 5:27:45 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search