Crystal Reports allow you to display the data in a formatted report style form. You can easily create Crystal Reports by adding the "New Item" from the Visual Studio and going through the wizard. The problem comes when you need to make a report that is dependent on the parameters being passed from the ASP.NET application. In this blog entry I will quickly go over the method in which you can pass the parameters from ASP.NET application to Crystal Reports and display them in a webform. I will soon write a complete article about this scenario.
Please also note that if all the steps are performed correctly the total time to produce a report dependent on the parameters is only 2 MINUTES
I am using the Northwind database. The category names from the "Categories" table are displayed in the DropDownList. Once, the category is selected the corresponding products are displayed as a report on the webform. The first thing that you must do is to add a Crystal Report in the project. Once, the report is added you can select the tables. Since, we are dealing with Categories and Products you need to select both the tables. When you select tables it will show you the join between them which will be on CategoryID. Now, you must add the ParameterField to the report. The ParameterField have to be "CategoryID" since your result is based on the selection of Category. You can simply open the report and select ( View -> Other Windows -> Document OutLine ) this will display the additional features you can add with the report. Then select the PropertyField and type the name of the field as "CategoryID" and make it a "Number" datatype.
After this you need to configure the "Select Expert". Simply, select "Crytal Reports" from the top menu then select "Report" -> "Select Report". Here you can adjust the parameter to "is equal to" and then select the "CategoryID" from the parameter list.
At this point you are set from the Crystal Reports side now you need to do some configuration on the ASP.NET side. First you need to fill the DropDownList with the category name and categoryID as DataTextField and DataValueField respectively.
After this you need to implement the button click event which will take the categoryID from the DropDownList and publish the report based on the categoryID.
Here is the complete code for the page:
Code behind:
using
System;
using
System.Data;
using
System.Configuration;
using
System.Web;
using
System.Web.Security;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using
System.Web.UI.HtmlControls;
using
System.Data.SqlClient;
using
CrystalDecisions.CrystalReports.Engine;
using
CrystalDecisions.Shared;
public
partial class _Default : System.Web.UI.
Page {
private ReportDocument report = new ReportDocument();
protected void Page_Load(object sender, EventArgs e)
{
report.Load(Server.MapPath("CrystalReport.rpt"));
report.FileName = Server.MapPath("CrystalReport.rpt");
if (!Page.IsPostBack)
{
BindData();
}
}
protected override void OnUnload(EventArgs e)
{
base.OnUnload(e);
this.Unload+=new EventHandler(Page_Unload);
}
public void Page_Unload(object sender, EventArgs e)
{
// clean up the resources
report.Clone();
report.Dispose();
}
private void BindData()
{
string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT CategoryName,CategoryID FROM Categories", myConnection);
DataSet ds = new DataSet();
ad.Fill(ds);
ddlCategory.DataSource = ds;
ddlCategory.DataTextField = "CategoryName";
ddlCategory.DataValueField = "CategoryID";
ddlCategory.DataBind();
}
// This will display the report
protected void Btn_DisplayReport(object sender, EventArgs e)
{
// get the selected CategoryID
int categoryID = Convert.ToInt32(ddlCategory.SelectedValue);
report.SetParameterValue("CategoryID", categoryID);
CrystalReportViewer1.ReportSource = report;
}
}
And here is the HTML part of the code:
<div>
Select a category: <asp:DropDownList ID="ddlCategory" runat="server" >
</asp:DropDownList>
<asp:Button ID="Btn_Display" runat="server" Text="Display Report" OnClick="Btn_DisplayReport" />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</div>