Now that you have built the report you can build a web page
to display it. I will create a page that displays the database fields and
formula fields that are defined in the report. We will do all of this in this
Default.aspx page that is part of the DynamicGrouping web site we created.
1.
Open the Default.aspx page and view its markup. Add the following
Register directive after the Page directive.
Listing 3
<%@ 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 4
<table>
<tr>
<td colspan="2">Group By:</td>
<td colspan="2">or Group By:</td>
</tr>
<tr>
<td>Tables:</td>
<td><asp:DropDownList runat="server" ID="ddlTables" AutoPostBack="True"
onselectedindexchanged="ddlTables_SelectedIndexChanged"></asp:DropDownList>
</td>
<td>Formula Fields:</td>
<td><asp:DropDownList runat="server" ID="ddlFormulaFields"></asp:DropDownList>
</td>
</tr>
<tr>
<td>Fields:</td>
<td><asp:DropDownList runat="server" ID="ddlFields"></asp:DropDownList>
</td>
<td> </td>
</tr>
<tr>
<td colspan="2"><asp:Button ID="btnPreview" runat="server"
onclick="btnPreview_Click" Text="Preview With DB Fields" />
</td>
<td colspan="2"><asp:Button ID="btnPreviewWithFormula" runat="server"
Text="Preview With Formula Fields"
onclick="btnPreviewWithFormula_Click" />
</td>
</tr>
</table>
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"
AutoDataBind="true" />
This adds the tables, fields, and formulas drop down lists.
These will be populated in the page load event. The btnPreview and
btnPreviewWithFormula buttons are used to display the report in the Crystal
Report Viewer control.
Figure 8
3.
Add the following using statements in the code behind.
Listing 5
//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 6
if (!IsPostBack)
{
//Hide the crystal report viewer
CrystalReportViewer1.Visible = false;
//Load the drop down list with all the tables in the report.
//Set the first item to "Select A Table"
ddlTables.Items.Add(new ListItem("Select A Table"));
//Load the report so you can get the tables
ReportDocument report = new ReportDocument();
report.Load(Server.MapPath("EmployeeListing.rpt"));
//Loop around for each table in the report.
foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in
report.Database.Tables)
{
//Add an item to the drop down list for this table.
ddlTables.Items.Add(new ListItem(tbl.Name));
}
//Load the formula drop down list
foreach (FormulaFieldDefinition fld in report.DataDefinition.FormulaFields)
{
ddlFormulaFields.Items.Add(new ListItem(fld.Name));
}
}
else
{
//If this is a postback the rebind the report so the paging works.
if (CrystalReportViewer1.Visible == true)
{
//Rebind the report.
BindReport(UseDatabaseField);
}
}This code loads the Tables drop down list with the tables
defined in the report. It also loads the formula drop down list with the
formulas defined in the report.
The else logic handles a Postback. When the user pages
through a report, a Postback event is fired on the server and the report must
be repopulated. A custom property called "UseDatabaseField" is used
to determine if the user clicked on the Preview With DB Fields or Preview With
Formula Fields button. The property stores its value in the ViewState so it is
persisted for each request.
Add the following code to add the property.
Listing 7
private bool UseDatabaseField
{
set { ViewState["UseDatabaseField"] = value; }
get { return Convert.ToBoolean(ViewState["UseDatabaseField"]); }
}5.
Add the following code for the Preview button's click event.
Listing 8
protected void btnPreview_Click(object sender, EventArgs e)
{
BindReport(true);
}This code calls a custom method called BindReport() and
passes in True so the method knows to use the database fields to set the
grouping condition.
6.
Add the following code for the Preview With Formula Field button's click
event.
Listing 9
protected void btnPreviewWithFormula_Click(object sender, EventArgs e)
{
BindReport(false);
}7.
When the user selects a table from the drop down list, the web page
should populate the Field's drop down list with the fields for that table. Add
the following code to the Table drop down list's selected index changed event.
Listing 10
protected void ddlTables_SelectedIndexChanged(object sender, EventArgs e)
{
ddlFields.Items.Clear();
if (ddlTables.SelectedIndex != 0)
{
//Load the report so you can get the database fields
ReportDocument report = new ReportDocument();
report.Load(Server.MapPath("EmployeeListing.rpt"));
CrystalDecisions.CrystalReports.Engine.Table tbl =
report.Database.Tables[ddlTables.SelectedItem.Text];
foreach (DatabaseFieldDefinition fld in tbl.Fields)
{
ddlFields.Items.Add(new ListItem(fld.Name));
}
}
}8.
Now add the following custom methods.
Listing 11
private void BindReport(bool useDatabaseField)
{
//Set this property to the parameter so on postback you can retain the grouping.
UseDatabaseField = useDatabaseField;
ReportDocument report = new ReportDocument();
report.Load(Server.MapPath("EmployeeListing.rpt"));
SetTableLocation(report.Database.Tables);
//If using a database field then use the database drop down lists.
if (useDatabaseField)
{
report.DataDefinition.Groups[0].ConditionField =
report.Database.Tables[ddlTables.SelectedItem.Text].
Fields[ddlFields.SelectedItem.Text];
}
else
{
//Use the formula drop down list.
report.DataDefinition.Groups[0].ConditionField =
report.DataDefinition.FormulaFields[ddlFormulaFields.SelectedItem.Text];
}
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.Visible = true;
}
private void SetTableLocation(Tables tables)
{
ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.ServerName = @"YOURSERVERNAME";
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 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 SetTableLocation method connects the report to
the database. You should replace the server name, database name, user id, and password
with your information. You should put this in the web.config file, but for demo
purposes I put it directly in the code.
The groups are changed by changing the ConditionField
property of the report.DataDefinition.Groups[0] object. The Groups property is
a collection of groups defined in the report. Since we only have one group we
want to change the first group.
Now try running the project. Select a table and then select
a field and click the Preview With DB Fields button. The report should appear
with the new group. Try selecting a formula field and click the Preview With
Formula Field button. The report should now be grouped by the formula.
