Building Reports using ASP.NET and Crystal Reports - Part 3 - An Invoice Report Using Disconnected Data
 
Published: 20 Jul 2009
Abstract
In this third part of the series, Varallo shows how to rebuild the Invoice created in Part 1 using disconnected datasets instead of connecting directly to the database. This gives the user the flexibility to manipulate the data and implement rules in a c# business class rather than in the report. He provides a detailed overview of each concept in a step-by-step manner supported with screen shots and source code.
by Vince Varallo
Feedback
Average Rating: 
Views (Total / Last 10 Days): 32222/ 33

Introduction

This is the third article of a series that uses ASP.NET and Crystal Reports to build reports using the Adventure Works Sample Database. Before reading this article it would be helpful to have read Part 1 and Part 2, but it is not required. This article focuses on redeveloping the Invoice created in Part 1 using disconnected datasets instead of connecting directly to the database. Using disconnected datasets gives the developer the flexibility to manipulate the data and implement rules in a C# business class rather than in the report.

Before you begin you will need to have installed Visual Studio 2008 with Crystal Reports for .NET.  The samples are written in Visual Studio 2008, but they will work with Visual Studio 2005 as well.  You also will need to download the AdventureWorks sample database 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. 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 image. 

Figure 1

If you have read Part 1 then you will notice that this is the same report. If you have not read Part 1 then this article will also demonstrate how to use master\detail records, create formula fields, use variables in a report, insert groups in a report, dynamically format groups using the Section Expert, and how to create summary fields.

An Invoice Report Using Disconnected Data

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. 

Step 2: Create a schema file

In part 1, I showed you how to connect directly to the database using the Database Expert in Crystal Reports. You point to a database, add the tables that have your data, and create the joins between the tables. This is similar to creating a query using the Query Designer in SQL Server Management Studio. However, in the report being developed in this article, you are not going to connect to the database at all. Instead, you will connect to a disconnected dataset that is generated are runtime. When you build the Crystal Report you still need to know what columns will be in the dataset that is "hooked" to the report. To do this you create an XML schema file. An easy way to create the XML schema file is to add a Dataset to your project.

1.    Right click on the web site in the Solution Explorer and select Add New Item… from the pop up menu.

2.    Click on Dataset from the list of templates.

3.    Rename the file to Invoice.xsd and click the Add button.

4.    A warning message will appear asking you to add the file to the App_Code folder. Click Yes.

5.    The xsd should appear in design mode. From the toolbar, drag a TableAdapter onto the XSD design surface. The Table Adapter Configuration Wizard will appear.

6.    If you already have a connection to the AdventureWorks database defined in your Server Explorer then the AdventureWorks database will be in the drop down list on the first page of the wizard. If the AdventureWorks database is in the list then select it and skip to step 7. If it is not in the list then click the New Connection… button. This will allow you to specify the server name, database, login, and password that you want to use to connect to the database.  Once you have entered the appropriate information, click the OK button in the Add Connection dialog.

7.    Click the Next button on the Table Adapter Configuration Wizard. Make sure you have the AdventureWorks database selected in the drop down list.

8.    The next dialog asks you to choose a command type. For this example we will use a SQL statement to retrieve the data. Select Use SQL statements and click the Next button.

9.    The next dialog asks you for the SQL Statement. Enter the following:

Listing 1

SELECT Title, FirstName, LastName, Suffix, OrderDate, DueDate, ShipDate, 
SalesOrderNumber, PurchaseOrderNumber, addressbillto.AddressLine1, 
addressbillto.AddressLine2, addressbillto.City, addressbillto.PostalCode, 
addressshipto.AddressLine1, addressshipto.AddressLine2, addressshipto.City,
addressshipto.PostalCode, stateprovincebillto.StateProvinceCode,
stateprovinceshipto.StateProvinceCode, Product.Name, Product.ProductNumber, 
OrderQty, UnitPrice, UnitPriceDiscount, LineTotal, TaxAmt, Freight, SalesOrderHeader.ContactID 
FROM   ((((((Sales.SalesOrderDetail SalesOrderDetail 
INNER JOIN Sales.SalesOrderHeader SalesOrderHeader 
ON SalesOrderDetail.SalesOrderID=SalesOrderHeader.SalesOrderID) 
INNER JOIN Production.Product Product 
ON SalesOrderDetail.ProductID=Product.ProductID) 
INNER JOIN Person.Address addressbillto 
ON SalesOrderHeader.BillToAddressID=addressbillto.AddressID) 
INNER JOIN Person.Contact Contact 
ON SalesOrderHeader.ContactID=Contact.ContactID) 
INNER JOIN Person.Address addressshipto 
ON SalesOrderHeader.ShipToAddressID=addressshipto.AddressID) 
INNER JOIN Person.StateProvince stateprovinceshipto 
ON addressshipto.StateProvinceID=stateprovinceshipto.StateProvinceID) 
INNER JOIN Person.StateProvince stateprovincebillto 
ON addressbillto.StateProvinceID=stateprovincebillto.StateProvinceID 

This will select the appropriate fields for this report. In Part 1, Crystal Reports generated this SQL statement for you on the fly based on how you set the tables up in the Database Expert window and which fields were used on the report. In this version of the report you have control over the SQL Statement, so if you needed to optimize it you could.

10. Click the Next button. The wizard is now asking what methods you want to generate on the Dataset to retrieve the data. Uncheck the Fill a DataTable, but keep the Return a DataTable checked. Click the Next button.

Figure 2

11. Click the Finish button on the Wizard Results dialog.

You should now see the Dataset in design view in Visual Studio.

Figure 3

You will use this dataset to identify the fields for the Crystal Report.

Step 3: Add the Crystal Report 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 then do so.

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 4

 

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

Figure 5

Step 4: Attach the report to the schema file

The first step in designing this report is to attach to the Dataset you just created. This will allow you to then drag the fields onto the report.

1.    Right click on the Database node in the Field Explorer. Select Database Expert from the pop-up menu.

2.    Click the plus sign (+) next to Create New Connection.

3.    Click the plus sign (+) next to ADO.NET.

4.    This will bring up a dialog that allows you to specify the location of the schema file you want to use for this report. Click the button with three dots next to the File Path text box.

5.    Browse to the location of your Invoice.xsd file. This should be in the App_Code folder of your web site. Click the OK button.

6.    Click the Finish button on the dialog box.

7.    You should see Invoice listed under the ADO.NET node in the Database Expert. DataTable1 should appear under that node. Click DataTable1 and then click the > button to move the DataTable1 to the list of selected tables.

Figure 6

8.    Click the OK button.

If you click the plus sign next to the Database Fields node in the Field Explorer, you will see DataTable1. Click the plus sign next to DataTable1 to see all the fields. You can now start adding these fields to the report.

Figure 7

Step 5: 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 SalesOrderNumber field. Even though this is not the invoice number, we will treat it as such for this example.  Drag the SalesOrderNumber field from the Field Explorer 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 and right clicking the SalesOrderNumber field and select 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. Since the report is not connecting to the database, Crystal uses junk data to be displayed on the report. That is one disadvantage to using disconnected data; you cannot preview the report at design time. 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.

22. 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.

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

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

25. 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 2

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

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

27. 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.

28. Click the Main Report Preview button and you should see where the customer name will appear. 

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.

29. Click back to the Main Report view.

30. 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.

31. 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.

32. Enter the following code.

Listing 3

local StringVar billTo := {DataTable1.AddressLine1} + chr(13);
 
if isnull({DataTable1.AddressLine2}) = false then
    billTo := billTo + {DataTable1.AddressLine2} + chr(13);
 
billTo := billTo + {DataTable1.City} + ", " 
      + {DataTable1.StateProvinceCode} + "  " + {DataTable1.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.

33. Click the Save and close button. 

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

35. 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.

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

37. 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.

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

39. Click the Main Report Preview button to see the formula in action. You will only see junk data, but at least you can get a feel for what it would look like.

Figure 13

40. 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.

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

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

Listing 4

local StringVar shipTo := {DataTable1.AddressLine11} + chr(13);
 
if isnull({DataTable1.AddressLine21}) = false then
   shipTo := shipTo + {DataTable1.AddressLine21} + chr(13);
 
shipTo := shipTo + {DataTable1.City1} + ", " +
      {DataTable1.StateProvinceCode} + "  " + {DataTable1.PostalCode1}

43. 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.

44. 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 6: 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 DataTable1.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" and "Reset Page Number After" and 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. 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 Name field to the Details section. Change the width of the field to 4800.

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

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

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

12. Drag the 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 DataTable1.LineTotal and the calculation should be set to Sum. Change the Summary Location drop down to Group #1: DataTable1.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 and 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. Drag the TaxAmt 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 Freight field below the TaxAmt 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 and 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 5

Sum ({DataTable1.LineTotal}, {DataTable1.SalesOrderNumber})
      + {DataTable1.TaxAmt} + {DataTable1.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.

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 6

<%@ 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 7

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 8

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

4.    Add the following code the Page_Load event.

Listing 9

   if (!IsPostBack)
   {
      //Load the drop down list with customers.
      SqlConnection cn = new   SqlConnection
            (ConfigurationManager.ConnectionStrings
            ["AdventureWorksConnectionString"].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 10

<connectionStrings>
<add name=" AdventureWorksConnectionString " 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 11

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 12

private void BindReport()
{
    ReportDocument report = new ReportDocument();
    report.Load(Server.MapPath("Invoice.rpt"));
 
    InvoiceTableAdapters.DataTable1TableAdapter ta = new 
        InvoiceTableAdapters.DataTable1TableAdapter();
 
    report.SetDataSource((DataTable)ta.GetData());
 
    CrystalReportViewer1.ReportSource = report;                        
}

The BindReport 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 Invoice Dataset that was created in step 3 can be generated with data by creating an instance of the InvoiceTableAdapters.DataTableAdapter class and calling the GetData method. The GetData method automatically executes the SQL statement that you used to create the Invoice Dataset.   The Crystal Report Viewer's source is then set to Invoice dataset. For now, this code is not using the customer filter selected by the user. I will get to that code in a moment.

You can now run the project. Click the Preview button.

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.

Now let us implement the filtering. Change the BindReport method to the following code.

Listing 13

ReportDocument report = new ReportDocument();
report.Load(Server.MapPath("Invoice.rpt"));
 
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings
      ["AdventureWorksConnectionString"].ConnectionString);
 
cn.Open();
 
SqlCommand cmd = new SqlCommand("SELECT Title, FirstName, LastName, Suffix, 
OrderDate, DueDate, ShipDate, SalesOrderNumber, PurchaseOrderNumber, " +
"addressbillto.AddressLine1, addressbillto.AddressLine2, addressbillto.City,
 addressbillto.PostalCode, " +
"addressshipto.AddressLine1, addressshipto.AddressLine2, addressshipto.City, 
addressshipto.PostalCode, " +
"stateprovincebillto.StateProvinceCode, stateprovinceshipto.StateProvinceCode,
 Product.Name, Product.ProductNumber, " +
"OrderQty, UnitPrice, UnitPriceDiscount, LineTotal, TaxAmt, Freight, 
SalesOrderHeader.ContactID " +
"FROM   ((((((Sales.SalesOrderDetail SalesOrderDetail " +
"INNER JOIN Sales.SalesOrderHeader SalesOrderHeader " +
"ON SalesOrderDetail.SalesOrderID=SalesOrderHeader.SalesOrderID) " +
"INNER JOIN Production.Product Product " +
"ON SalesOrderDetail.ProductID=Product.ProductID) " +
"INNER JOIN Person.Address addressbillto " +
"ON SalesOrderHeader.BillToAddressID=addressbillto.AddressID) " +
"INNER JOIN Person.Contact Contact " +
"ON SalesOrderHeader.ContactID=Contact.ContactID) " +
"INNER JOIN Person.Address addressshipto " +
"ON SalesOrderHeader.ShipToAddressID=addressshipto.AddressID) " +
"INNER JOIN Person.StateProvince stateprovinceshipto " +
"ON addressshipto.StateProvinceID=stateprovinceshipto.StateProvinceID) " +
"INNER JOIN Person.StateProvince stateprovincebillto " +
"ON addressbillto.StateProvinceID=stateprovincebillto.StateProvinceID " +
"WHERE SalesOrderHeader.ContactID = " + ddlCustomer.SelectedItem.Value, cn);
 
SqlDataAdapter da = new SqlDataAdapter(cmd);
Invoice invoice = new Invoice();
da.Fill(invoice, "DataTable1");
                
report.SetDataSource(invoice);
 
CrystalReportViewer1.ReportSource = report;        

This code connects to the database and then executes the SQL statement with a command object. The Where clause includes the customer selected in the drop down list on the web page.  The generic SqlDataAdapter is then used to populate an Invoice Dataset.

References

Summary

This article showed you how to create a report using disconnected datasets. It also demonstrated how to use 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: Report   
Name: Sagar
Date: 2010-04-05 5:42:37 AM
Comment:
Hi,

I am not able to see any data on the report and instead it shows blank columns.
Please Help
Title: Nice Article   
Name: Rick
Date: 2009-12-01 2:17:51 PM
Comment:
The example was very easy to follow. Thanks for taking the time to document. I am sure I can apply this imformation. I renamed my default.aspx from Part 1 and it worked well. It would have been nice to build all tutorials on the same page, however it was still a great article.
Title: vineet   
Name: vineet
Date: 2009-11-14 8:40:46 AM
Comment:
I can See the group Tree But I Can we resize group Tree.
Left nevigation always show to see the group items.
If i press Group item Item is on the lower side of the page.I have to scroll up.Can we set group item and its child aligned.

Product Spotlight
Product Spotlight 



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


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 9:31:19 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search