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.
