Building an Invoice Application with ASP.NET and Crystal Reports - Part 1
 
Published: 21 Apr 2009
Abstract
In this first part of the series, Vince examines the creation of an invoice with the help of Crystal Reports by using the Adventure Works sample database. He provides detailed coverage of each step including the concept of header and detail records and one to many relationships with the help of relevant screen shots and source code. The article also demonstrates the usage of the Crystal Report Viewer and the Object Model to view the report as a PDF using ASP.NET.
by Vince Varallo
Feedback
Average Rating: 
Views (Total / Last 10 Days): 118570/ 223

Introduction

The free version of Crystal Reports that comes with Visual Studio allows you to create professional looking reports within your application and can polish up a line of business applications to make it look professional. This article will build an invoice for the AdventureWorks sample database that can be downloaded from http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407 for SQL Server 2008. Download and install the SQL2008.AdventureWorks_All_Databases.x86.msi file.  The sample was written using Visual Studio 2008, but it will work with Visual Studio 2005 as well.  If you do not have SQL Server 2008 you can use SQL Server 2005, but you will need to download the 2005 AdventureWorks samples.

The goal of this article is to create an invoice that looks like the following figure.

Figure 1

This article will demonstrate how to use master\detail records, create formula fields, use variables in a report, attach to tables in a SQL Server database, use the Database Expert to create links between tables, insert groups in a report, dynamically format groups using the Section Expert, and how to create summary fields.

 

The code for this article can be downloaded from the link given at the end of this article.

Step 1: Create a new solution

1.    Launch Visual Studio 2008.

2.    Select FileàNew Web Site from the menu.

3.    Choose ASP.NET from the list of templates. You can select File or HTTP from the Location drop down list. For this sample I will use File.

4.    Set the Web Site Name to AdventureWorksReports. The path should be "..\Visual Studio 2008\WebSites\AdventureWorksReports."

5.    Select Visual C# from the Language drop down list and then click OK.

Visual Studio will create a new web site and add a Default.aspx page and web.config file. Now you need to add a Crystal Report file to your site. To do this, follow these steps:

1.    Right click on the web site in your Solution Explorer.

2.    Select Add New Item… from the pop-up menu.

3.    Click on Crystal Report from the templates. If you do not see Crystal Report as an option then you need to run the Visual Studio 2008 installation and select the option to install Crystal Reports. It comes free with Visual Studio 2008.

4.    Change the name of the report to Invoice.rpt.  The Language drop down should already be set to Visual C#, but if it is not, do so now.

5.    Click the Add button.

Visual Studio will add the Crystal Report file to your web site and bring up the Crystal Reports Gallery dialog. You may be prompted to register your product if this is the first time you are using Crystal Reports.  You can skip registering the product, but it will keep prompting you every time you open a report so it is worth taking the extra minute or two to register. Once you get to the Crystal Reports Gallery dialog you should select the "As a blank report" option and click OK.

Figure 2

Your report will be displayed in Visual Studio and you are ready to start building your report.

Figure 3

 

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.

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

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.

Step 7: Create the page footer section

The last step in designing the report is to add the page number to the page footer section. 

1.    Right click in the Page Footer section and select InsertàSpecial FieldàPage N of M from the pop-up menu.

2.    Drop the field in the report's page footer section.

3.    Make the field the entire width of the page.

4.    Right click on the field and select Format Object from the pop-up menu.

5.    On the Common tab, change the Horizontal Alignment to Centered and click OK.

If you preview the report you should notice that the page number gets reset to 1 after each SaleOrderNumber change.

Figure 19

Step 8: Viewing the report in an ASP.NET page

Now that you have built the report you can build a web page to display it. I will create a simple page that lets the user select a customer and then preview any invoice for that customer. We will do all of this in this Default.aspx page that is part of the AdventureWorksReports web site we created.

1.    Open the Default.aspx page and view its markup. Add the following Register directive after the Page directive.

Listing 5

<%@ Register assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, 
PublicKeyToken=692fbea5521e1304" namespace="CrystalDecisions.Web" tagprefix="CR" %>

This allows you to use the Crystal Reports Viewer control that comes with Visual Studio.

2.    Add the following code between the div tags.

Listing 6

Select a customer:<asp:DropDownList ID="ddlCustomer" runat="server">
</asp:DropDownList>
    
<asp:Button ID="btnPreview" runat="server" onclick="btnPreview_Click" 
  Text="Preview" />
 
<br />
<br />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" 
  AutoDataBind="true" />

This adds a drop down list that will be populated with the list of customers in the Page_Load event. The Preview button will get all the data for the selected customer and bind the data to the report. 

3.    Add the following using statements in the code behind.

Listing 7

//Custom using statements
using System.Data.SqlClient;
using System.Configuration;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

4.    Add the following code to the Page_Load event.

Listing 8

   if (!IsPostBack)
   {
      //Load the drop down list with customers.
      SqlConnection cn = new   SqlConnection(
        ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString);
      cn.Open();
 
      SqlCommand cmd = new SqlCommand("SELECT DISTINCT LastName + ', ' + 
                                              FirstName AS Name, 
                                              Person.Contact.ContactID " +
                                        "FROM Sales.SalesOrderHeader " +
                                  "INNER JOIN Person.Contact " +
                                          "ON Sales.SalesOrderHeader.ContactID = 
                                              Person.Contact.ContactID " +
                                    "ORDER BY LastName + ', ' + FirstName", cn);
      SqlDataReader dr = cmd.ExecuteReader();
 
      ddlCustomer.DataSource = dr;
      ddlCustomer.DataTextField = "Name";
      ddlCustomer.DataValueField = "ContactId";
      ddlCustomer.DataBind();
 
      CrystalReportViewer1.Visible = false;
   }
   else
   {
      if (CrystalReportViewer1.Visible == true)
      {
         //Rebind the report.
         BindReport();
      }
   }

This code loads the drop down list with the customers in the database. You need to add the connection string to your database in the web.config file for this to work. In the <appSettings> section you should add the following:

Listing 9

<connectionStrings>
<add name="AdventureWorks" connectionString="Data Source=YOURSERVER;User 
  ID=aspalliance;Password=aspalliance;Initial Catalog=AdventureWorks;"/>
</connectionStrings>

This code assumes you have added a SQL Login called "aspalliance" with the password "aspalliance" and given them permissions to select data from the database.  Your SQL Server needs to be setup for Mixed Authentication mode so SQL Logins are allowed.

5.    Add the following code for the Preview button's click event.

Listing 10

protected void btnPreview_Click(object sender, EventArgs e)
{
    BindReport();
    CrystalReportViewer1.Visible = true;
}

This code calls a custom method called BindReport() and then shows the Crystal Report Viewer Control.

6.    Now add the following custom methods.

Listing 11

private void BindReport()
{
    ReportDocument report = new ReportDocument();
    report.Load(Server.MapPath("Invoice.rpt"));
 
    SetTableLocation(report.Database.Tables);
 
    CrystalReportViewer1.ReportSource = report;
 
    report.DataDefinition.RecordSelectionFormula = 
               "{SalesOrderHeader.ContactID} = " + ddlCustomer.SelectedItem.Value; 
}
 
private void SetTableLocation(Tables tables)
{
    ConnectionInfo connectionInfo = new ConnectionInfo();
 
    connectionInfo.ServerName = @"LTMTI30\SQL2008";
    connectionInfo.DatabaseName = "AdventureWorks";
    connectionInfo.UserID = "aspalliance";
    connectionInfo.Password = "aspalliance";
 
    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
        TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
        tableLogOnInfo.ConnectionInfo = connectionInfo;
        table.ApplyLogOnInfo(tableLogOnInfo);
    }
}

The first method creates an instance of the ReportDocument class. This represents the report that you created earlier and allows you to manipulate it at runtime. The SetTableLocation() method sets the table location for each table in the report. Again, this assumes you have created an "aspalliance" SQL Login and have given it access to the database. The Crystal Report Viewer's source is then set to the report object. The next line dynamically filters the report based on the customer selected in the drop down list. You simply create a string just like a where clause in an SQL Statement. You have to use the report fields surrounded by brackets {} when you reference a field.

You can now run the project. If you choose "Abel, Catherine" from the list and click the Preview button you should see the following page.

Figure 20

You can scroll through the report using the navigation buttons at the top of the page or you can click directly to an invoice by clicking the SO number in the group tree on the left hand side of the report.

Downloads

Summary

This article showed you how to create a report using master\detail records in a database and showed you how to use formulas and dynamic formatting. Hopefully, you can take this example and apply it to your own requirements. Crystal Reports is a great tool for integrating reports directly in your ASP.NET application and, best of all, it is free with Visual Studio. Good luck on your project and happy coding.



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-2018 ASPAlliance.com  |  Page Processed at 2018-12-18 6:06:52 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search