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

Step 4: Create a group

When displaying the report, all the Sales Order Detail records should be displayed for the current Sales Order Number. To accomplish this you can create a group based on the SalesOrderNumber field and then add a page break after the group footer. When you add a group to a Crystal Report you automatically get a group header and footer section. You can dynamically format these sections to page break, suppress, reset the page number, and a variety of other features. For our report we want to break the page after the group footer so the next invoice starts on the next page and we want to reset the page number so you can print out invoices in bulk but mail them individually.

1.    Click back the Main Report view. Right click any white space on the report and select InsertàGroup… from the pop-up menu.

2.    The Insert Group dialog box should appear. Select the SalesOrderHeader.SalesOrderNumber field from the drop down list. Click OK.

3.    You will notice that two new sections were added to the report: Group Header #1 and Group Footer #1. By default, Crystal adds the special field called Group #1 Name to the Group Header #1 section. This will display the value for whatever field you are grouping on. For this report you do not need the Group Name since it is the Sales Order Number and it is printed in the Page Header. You can remove this field by clicking on the field and pressing the delete key.

4.    We do not need to show Group Header #1 so you can suppress this section by right clicking on the section header in the report and selecting Suppress (No Drill-Down) from the pop-up menu.

5.    The next step is to tell Crystal to break the page after the page footer and reset the page number. Right click the Group Footer #1 section heading and select Section Expert from the pop-up menu.

6.    Check the boxes next to "New Page After," "Reset Page Number After" and then click OK.

Figure 15

7.    The next step is to add the line items for the invoice. The details section will repeat for each record in the SalesOrderDetail table. Expand the Product table in the Field Explorer. Drag the ProductNumber field to the Details section. Notice that when you add a field to the Details section, Crystal Reports automatically adds a column header field in the Page Header Section.  The column header is simply a text object that you can edit to a friendlier name. Change the column header to "Product Number."

8.    Drag the Product.Name field to the Details section. Change the width of the field to 4800.

9.    Drag the SalesOrderDetail.OrderQty field to the Details section. Change the column header to "Qty."

10. Drag the SalesOrderDetail.UnitPrice field to the Details section. Change the column header to "Unit Price."

11. Drag the SalesOderDetail.UnitPriceDiscount field to the Details section. Change the width of the field to 600. Change the column header to "Disc."

12. Drag the SalesOrderDetail.LineTotal field to the Details section. Change the column header to "Line Total."

13. The next step is to add a box so the column headers and details are surrounded and stand out in the report. Right click on the report and select InsertàBox. Drag the box around the column headers and down to the Group Footer section. 

Figure 16

14. Preview the report and you should see the line items appear.

The last step is to create the totals in the Group Footer section. The sum of the LineTotal field equates to the sub total for the invoice. There is a field on the SalesOrderHeader record called subtotal, but for some reason it does not equal the sum of the details. To get around this we will create our own subtotal in the report. We will then add in the Freight and Tax to calculate the invoice total.

15. Right click on the LineTotal field in the details section. Select InsertàSummary… from the pop-up menu.

16. The field to summarize should have defaulted to SalesOrderDetail.LineTotal and the calculation should be set to Sum. Change the Summary Location drop down to Group #1: SalesOrderHeader.SalesOrderNumber - A. Click OK.

Figure 17

17. This will automatically add the summary field to the footer section below the LineTotal field.  You may need to increase the height of the group footer section so it appears correctly.

18. Right click on the field and select Format Object. Click the Font tab and change the style to Regular. Click the Number tab and check the box next to Display Currency Symbol. Click OK.

19. Click on the summary field and the line total field while holding the Ctrl key. Make the fields the same size and align the right sides.

20. Right click to the left of the summary field and select InsertàText Object from the pop-up menu. Change the text to "Subtotal:" and right align the field. This is the label for the summary field.

21. Now add the Tax field. Expand the Database Fields in the Field Explorer. Expand the SalesOrderHeader table. Drag the Tax field to the group footer just below the LineTotal summary field.

22. Right click to the left of the Tax field and select InsertàText Object from the pop-up menu.  Change the text to "Tax:" and right align the field.

23. Now drag the SalesOrderHeader.Freight field below the Tax field.

24. Right click to the left of the Freight field and select InsertàText Object from the pop-up menu.  Change the text to "Freight:" and right align the field.

25. Click on the summary field, tax field, and freight field while holding Ctrl. Make them all the same size and align their right side.

26. Now you have to create a formula for the invoice total. Right click the formula fields in the Field Explorer. Select New… from the pop-up menu and enter InvoiceTotal for the name. Click Use Editor.

27. Enter the following formula. This adds the subtotal, tax, and freight.

Listing 4

Sum ({SalesOrderDetail.LineTotal}, {SalesOrderHeader.SalesOrderNumber}) + 
  {SalesOrderHeader.TaxAmt} + {SalesOrderHeader.Freight}

28.  Click the Save and close button.

29. Drag the InvoiceTotal formula field to the group footer below the Freight field. Make this field the same size as the sub total field and align their right sides.

30. Since this is a total field we will add a single line above the field and a double line below the field. To do this, right click on the InvoiceTotal field in the report and select Format Object from the pop-up menu.

31. Click the Border tab and set the Top line style to Single and set the Bottom line style to Double. Click OK.

32. Now insert a label to the left of the Invoice Total. Right click to the left of the Invoice Total field and select InsertàText Object. Change the text to "Invoice Total:" and right align the field.

The Report Footer should look like the following:

Figure 18

The Report Header and Report Footer sections are not used in this report so you can suppress them. To suppress these sections, right click on the section header and select Suppress (No Drill-Down) from the pop-up menu.

View Entire Article

User Comments

Title: pranav   
Name: pranav
Date: 2012-11-26 1:22:05 AM
Title: Need the Images ... Please   
Name: KM
Date: 2011-12-25 8:26:20 AM
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
Where are the pictures!! Its hard to follow without the images
Title: birendra yogi   
Name: bina
Date: 2011-02-02 8:16:21 AM
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
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
it is useful to all
Title: Fantastic Article   
Name: Phil Smith
Date: 2010-10-25 7:05:46 AM
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
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
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
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
This article was of great help. Thanks a ton :)
Title: Nice Article   
Name: Rick
Date: 2009-12-01 11:21:23 AM
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
thank you gr8 work
Title: Thank you   
Name: Tabitha
Date: 2009-11-18 10:00:57 AM
Thank you very much!!!
Title: Thanks for doing such an awesome article!   
Name: Julius
Date: 2009-10-28 9:16:06 PM
Very good article. Really appreciate it.
Title: Awesome tutorial!   
Name: Nivi
Date: 2009-09-13 12:22:10 AM
Thanks for the awesome tutorial. It really helped me.
Thanks again
Title: Excellent Article   
Name: sai
Date: 2009-09-01 9:11:15 AM
Thankyou so much for such a wonderful article on Crystal Reports.
Title: Landsailor   
Name: Pete Lyons
Date: 2009-08-31 8:34:22 PM
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
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
Error in formula . 'Sum({SalesOrderDetail.LineTotal}, {SalesOrderHeader.SalesOrderNumber}) ' The result of selection formula must be a boolean.

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

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

about crystal report.

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

Title: Child Nodes not allowed   
Name: Paul
Date: 2009-05-27 11:05:56 AM
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?
Title: Link for the Adventure Works database   
Name: Vince
Date: 2009-05-27 7:58:34 AM
The page that has the download is

Click on the SQL2008.AdventureWorks All Databases.x86.msi link.
Title: Thanks so much   
Name: Paul
Date: 2009-05-26 5:23:07 AM
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,
Title: Thanks - How about a version for VB users?   
Name: Art
Date: 2009-05-22 10:07:14 AM
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
Title: Thanks   
Name: Suresh Kumar Gundala
Date: 2009-05-08 5:04:13 AM
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
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-2024  |  Page Processed at 2024-05-25 11:40:14 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search