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

Step 3: Create the page header section

The Page Header section repeats itself on every page. For the invoice sample we want to repeat the company logo, company name, invoice number, purchase order number, order date, ship date, due date, customer name, the bill to address, and the ship to address. The image for the logo can be found with the sample code that you can download. Make sure you copy logo.bmp into your web site project. You can simply copy the file using Windows Explorer into the web site folder and then refresh the project in Visual Studio for it to appear in the Solution Explorer.  Follow these steps to create the page header.

1.    Make the height of the Page Header section taller by placing your mouse pointer over the top of the Details section. The mouse pointer should change to a horizontal bar. Once you see the horizontal bar for the mouse pointer, click and drag down. This will make the section larger. 

2.    Right click on the white space in the Page Header section and select InsertàPicture… from the pop-up menu. Select the logo.bmp file and click Open. Drop the picture in the upper left hand corner of the page header section.

3.    Using the Properties window change the Height property of the picture to 2058 and the Width to 3066. 

Figure 8

4.    Next, you need to add the company name. Right click in the white space next to the picture and select InsertàText Object from the pop-up menu. Drop the text object to the right of the picture.

5.    Enter "Adventure Works Cycle" for the text. Be sure not to hit the Enter key when you are done typing. To stop editing the text object click somewhere else on the report. If you press the Enter key it would put a hard return in the text object.

6.    Change the Height property of the text object to 2058 and set the Width to 5400.

7.    Right click on the text object and click Format Object from the pop-up menu. This menu gives you more options for formatting than the Property window.

8.    Click the Font tab. Change the font size to 28 and the color to Blue.

9.    Click the Paragraph tab. Change the horizontal alignment to Centered and click OK.

Figure 9

10. The next step is to drag the database fields on to the report. Start with the SalesOrderHeader.SalesOrderNumber field. Even though this is not the invoice number, we will treat it as such for this example. Expand the Database Fields node in the Field Explorer.  Expand the SalesOrderHeader table. Drag the SalesOrderNumber field to the right corner of the Page Header section.

11. Change the width to 1365. This will make the field smaller so you will have to move the field again to the left corner. 

12. Insert a text object to the left of the SalesOrderNumber field by right clicking in the Page Header section and inserting a text object. Set the text to "Invoice #:."  Right align the text object. You can use the right align button in the toolbar to set the horizontal alignment or right click on the object and use the Format Object dialog to set the horizontal alignment.

13. Resize the Invoice # text object so it does not overlap with the SalesOrderNumber field.

Figure 10

14. Add the PurchaseOrderNumber, OrderDate, ShipDate, and DueDate the same way.  You will need to add a text object for each field to act as a label.

15. You can easily make all the database fields the same size by holding down the Ctrl key and clicking on each field.

16. Right click the SalesOrderNumber field and select SizeàSame Size from the pop-up menu.  This will make all the fields the same size as the SalesOrderNumber field.

17. You also can align all the fields at once by keeping them selected, right clicking the SalesOrderNumber field and selecting AlignàRights from the pop-up menu.

18. Preview your report by clicking the Main Report Preview button at the bottom of the report.

Figure 11

19. Notice that the three date fields have a time appended to them. To remove the time component click back to the Main Report view. Hold down Ctrl and click on each field. Right click on the OrderDate field and select Format Multiple Objects from the pop-up menu.

20. Click the Date and Time tab and choose "03/01/1999" from the Style list. Click OK.

21. Click the Main Report Preview button again and the time should be gone.

The next step is to add the customer name, bill to, and ship to address fields. We will use the Formula Fields feature in Crystal Reports to accomplish this. Formula Fields are a powerful feature in Crystal Reports which allow you to use programming logic to create fields. You can use If\Then, for loops, do loops, while loops, create local and global variable, manipulate arrays, and a wide variety of other features. The Formula Fields enable you to do almost anything in a report. Let us start by creating the Customer text object and a formula field to display the customer name.

1.    Click back to the Main Report view. Insert a text object under the picture and set the text to "Customer:."  Resize the text object so it is only the size of the text.

2.    Right click on the Formula Fields node in the Field Explorer and select New… from the pop-up menu.

3.    Enter CustomerFullName for the formula name and click Use Editor.

4.    Not all customers have a title, middle name, and suffix. We will create a local string variable in the formula and some if logic to concatenate the name fields if they exist. Enter the following code for the formula.

Listing 1

local stringVar customerName := "";
 
if isnull({Contact.Title}) = false then
    customerName := customerName + {Contact.Title} + " ";
customerName := customerName + {Contact.FirstName} + " " + {Contact.LastName};
 
if isnull({Contact.Suffix}) = false then
    customerName := customerName + " " + {Contact.Suffix};
 
customerName;

5.    Click the Save and close button in the upper left hand corner of the Formula Editor.

6.    Expand the formula field in the Field Explorer and then drag the CustomerFullName field next to the Customer text object. You can make the width of the field larger by clicking on the field and dragging the window handle on the right side of the box. I made the field stretch to the right side of the Adventure Works Cycle text object.

7.    Click the Main Report Preview button and you should see the customer name appear.  Use the page scrolling buttons to page through so you can see some of the names that have titles and middle names.  Be aware that some of the data in the sample database is screwy. There are people that have a title of Ms., but also have a suffix of Jr.

Figure 12

The next step is to create the "bill to address" formula. This has a similar issue as the name, in that all customers do not have an address line 2. We only want to include that field if it is not null. Now we could just drag all the fields on the report on separate lines, but then any record missing address line 2 would have a blank spot on the report. Formula field can solve this problem.

8.    Click back to the Main Report view.

9.    Insert a text object under the Customer text object and enter "Bill To:" for the text. Format the text object so the text is Bold.

10. Right click on the Formula Fields in the Field Explorer and click New… from the pop-up menu.  Enter BillToAddress for the name and click the Use Editor button.

11. Enter the following code.

Listing 2

local StringVar billTo := {addressbillto.AddressLine1} + chr(13);
 
if isnull({addressbillto.AddressLine2}) = false then
    billTo := billTo + {addressbillto.AddressLine2} + chr(13);
 
billTo := billTo + {addressbillto.City} + ", " + 
  {stateprovincebillto.StateProvinceCode} + "  " + {addressbillto.PostalCode}

This code creates a local string variable and initializes it to AddressLine1 and adds a line feed.  The chr(13) creates a line feed in the formula. Next it checks if the AddressLine2 is null and appends it to the string if it has a value. Then it concatenates the City, State\Province and Postal Code.

12. Click the Save and close button. 

13. Drag the BillToAddress formula field below the Bill To text object. Make the field about have the width of the report.

14. Since you do not know how much height is needed because it will vary depending on if the customer has an Address Line 2, you can leave the height of the field the same. However, you can make the field adjust its height automatically by right clicking on the field and selecting Format Object from the pop-up menu.

15. Check the Can Grow check box. This allows a field to grow vertically.

16. We also want to surround the field with a box so it stands out on the report. Click the Border tab from the Format Editor dialog.

17. Select Single for the Left, Right, Top, and Bottom Line Style drop down lists. Click OK.

18. Click the Main Report Preview button to see the formula in action.

Figure 13

19. Now you can create the Ship To address the same way. First, insert a text object and set the text to "Ship To:/"  Make the text object Bold. Place the text object on the middle of the report.

20. Right click on the Formula Fields in the Field Explorer and select New…. 

21. Enter BillToAddress for the name and click Use Editor. Enter the following formula.

Listing 3

local StringVar shipTo := {addressshipto.AddressLine1} + chr(13);
 
if isnull({addressshipto.AddressLine2}) = false then
   shipTo := shipTo + {addressshipto.AddressLine2} + chr(13);
 
shipTo := shipTo + {addressshipto.City} + ", " + 
  {stateprovinceshipto.StateProvinceCode} + "  " + {addressshipto.PostalCode}

22. Click the Save and close button and then drag the formula to the right of the Bill To Address field on the report. Make the field width stretch close to the right side of the report.

23. Right click on the field and select Format Object. Check the Can Grow box and set the border fields as you did for the Bill To Address.

Figure 14


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:56:38 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search