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.