Here, I will create a simple application for how to create Crystal Report using stored procedure and passing parameter to that report. I have experienced this sample project in Visual Studio2005 and SQL Server.
First, let’s assume that we have a database in SQL server –
Step -1:
There I will create a sample table for this project purpose –
drop table [dbo].[USERS]
GO
CREATE TABLE [dbo].[USERS] (
[OID] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Department] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Designation] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PassWord] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LogQues] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuesAns] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccessLevel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GroupName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GroupEmail] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Step -2: Then I will create a simple stored procedure (UserInfo) for using into my report.
CREATE PROCEDURE UserInfo @Dept VARCHAR(20) AS SELECT * FROM USERS WHERE USERS.Department = @Dept
GO
Step -3:
Now I will design my crystal report. For that I will have to add a crystal report in my project.
a. Double click on the report to go to the design mode.
b. In field explorer pan of crystal report. Right click ‘Database Fields’. Then choose ‘Database Expert’
c. Then from left pane – click on ‘Create New Connection’ node
d. Choose OLEDB (ADO)
e. Right Click on ‘Make New Connection’ and select ‘Make New Connection’
f. Then from the list of OLEDB providers select ‘Microsoft OLEDB Provider for SQL Server’ then Next
g. Provide required information for Database server ex. Server Name (IP Address), User ID, password, database name. Then click next and finished.
h. Then add stored procedure ‘UserInfo’ from left pane to right pane.
Step -4: Now drag and drop the fields you want to display in report. Just drag the fields and drop into ‘Details section’ of crystal report’s design view.
Step -5: Now I will edit my Web.Config file for database information –
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
<section name="databaseinformation" type="System.Configuration.SingleTagSectionHandler"/>
</configSections>
<databaseinformation name="data" username="sa" password="pims" servername="192.168.201.69" pvdr="SQLOLEDB.1" databasename="TROUBLETICKET"/>
Step -6: Now I will create a page for report loading-
ReportLoader.aspx
<%@ Page Language="C#" MasterPageFile="~/Pages/MasterPage.master" AutoEventWireup="true" CodeFile="ReportLoader.aspx.cs" Inherits="Pages_ReportLoader" Title="Untitled Page" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:Label ID="Label1" runat="server" Text="Insert Department:"></asp:Label>
<asp:TextBox ID="txtParamDept" runat="server"></asp:TextBox>
<asp:Button ID="btnShowReport" runat="server" Text="Show Report" OnClick="btnShowReport_Click" />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</asp:Content>
ReportLoader.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
public partial class Pages_ReportLoader : System.Web.UI.Page
{
ReportDocument reportDocument = new ReportDocument();
ParameterField paramField = new ParameterField();
ParameterFields paramFields = new ParameterFields();
ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
protected void Page_Load(object sender, EventArgs e)
{
//Instantiate variables
if (!IsPostBack)
{
//Set instances for input parameter 1 - @Dept
paramField.Name = "@Dept";
//*Remember to reconstruct the paramDiscreteValue and paramField objects
paramDiscreteValue.Value = "CS";
paramField.CurrentValues.Add(paramDiscreteValue);
//Add the paramField to paramFields
paramFields.Add(paramField);
CrystalReportViewer1.ParameterFieldInfo = paramFields;
reportDocument.Load(Server.MapPath("CrystalReportTest.rpt"));
//Load the report by setting the report source
CrystalReportViewer1.ReportSource = reportDocument;
//set the database loggon information.
reportDocument.SetDatabaseLogon("sa", "pims", "192.168.201.69", "TROUBLETICKET");
}
}
protected void btnShowReport_Click(object sender, EventArgs e)
{
paramField.Name = "@Dept";
paramDiscreteValue.Value = txtParamDept.Text.ToString();
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
CrystalReportViewer1.ParameterFieldInfo = paramFields;
reportDocument.Load(Server.MapPath("CrystalReportTest.rpt"));
CrystalReportViewer1.ReportSource = reportDocument;
reportDocument.SetDatabaseLogon("sa", "pims", "192.168.201.69", "TROUBLETICKET");
}
}
**Alternative way of Report Loading:
ReportLoader2.aspx
<%@ Page Language="C#" MasterPageFile="~/Pages/MasterPage.master" AutoEventWireup="true" CodeFile="ReportLoader2.aspx.cs" Inherits="Pages_ReportLoader" Title="Untitled Page" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:Label ID="Label1" runat="server" Text="Insert Department:"></asp:Label>
<asp:TextBox ID="txtParamDept" runat="server"></asp:TextBox>
<asp:Button ID="btnShowReport" runat="server" Text="Show Report" OnClick="btnShowReport_Click" />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
<CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
<Report FileName="CrystalReportTest.rpt">
<Parameters>
<CR:ControlParameter ControlID="txtParamDept" ConvertEmptyStringToNull="False" DefaultValue="IT"
Name="@Dept" PropertyName="Text" ReportName="" />
</Parameters>
</Report>
</CR:CrystalReportSource>
</asp:Content>
ReportLoader2.aspx.cs
public partial class Pages_ReportLoader2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
CrystalReportViewer1.Visible = true;
CrystalReportSource1.ReportDocument.SetDatabaseLogon("sa", "pims", "192.168.201.69", "TROUBLETICKET");
CrystalReportViewer1.ReportSourceID = "CrystalReportSource1";
}
protected void btnShowReport_Click(object sender, EventArgs e)
{
CrystalReportViewer1.Visible = true;
CrystalReportViewer1.ReportSourceID = "CrystalReportSource1";
}
}
Sample Project code here |