Crystal Report And Parameter Passing Using Stored Procedure

By Jakir Hossain
SQL Server
Articles

Here's sample 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.

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
Popularity  (28550 Views)
View Jakir Hossain's Articles
Create New Account
Article Discussion: Parameter Passing Crystal Report using Stored Procedure
Jakir Hossain posted at Monday, August 04, 2008 12:55 AM
Original Article
crystal report
mayank jain replied to Jakir Hossain at Monday, August 04, 2008 8:21 AM

that soulton was great

but i m facing problem if there are more than parameter ,there is error encountered

my code is

ReportDocument reportDocument = new ReportDocument();

ParameterField paramField = new ParameterField();

ParameterFields paramFields = new ParameterFields();

ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();

paramField.Name = "@Dept";

paramDiscreteValue.Value = TextBox1.Text.ToString();

paramField.CurrentValues.Add(paramDiscreteValue);

paramFields.Add(paramField);

paramField.Name = "@Name";

paramDiscreteValue.Value = TextBox2.Text.ToString();

paramField.CurrentValues.Add(paramDiscreteValue);

paramFields.Add(paramField);

CrystalReportViewer1.ParameterFieldInfo = paramFields;

reportDocument.Load(Server.MapPath("CrystalReport.rpt"));

CrystalReportViewer1.ReportSource = reportDocument;

reportDocument.SetDatabaseLogon("sa", "sa", "OPWFMS-7KYGZ7SB", "test");

this code is wrkong fr one parameter but not fr two plz tel m the soution

Crystal Report-- adding multiple parameters
Sreekumar P replied to mayank jain at Monday, August 04, 2008 8:21 AM
hi,

        just u have to do is that assign the reportSource to CrystalReportViewer1.ReportSource before u are adding the parameters . try this code

and also this code supports adding 2 or more parameters. 

//--Initializing CrystalReport
        ReportDocument myReportDocument;
        myReportDocument = new ReportDocument();
        myReportDocument.Load(Server.MapPath("Reports/rptEstimateSchedule.rpt"));
        //--Binding report with CrystalReportViewer       
        CrystalReportViewer1.ReportSource = myReportDocument;

        //Set instances for input parameters
        this.CrystalReportViewer1.ParameterFieldInfo.Clear();


        ParameterFields paramFields = new ParameterFields();

        //Set instances for input parameter QuotationID
        ParameterField paramField = new ParameterField();
        ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
        paramField.Name = "@QuotationID";
        paramDiscreteValue.Value = QuotationID;
        paramField.CurrentValues.Add(paramDiscreteValue);

        //Add the paramField to paramFields
        paramFields.Add(paramField);

        //Set instances for input parameters Name
        ParameterField paramField2 = new ParameterField();
        ParameterDiscreteValue paramDiscreteValue2 = new ParameterDiscreteValue();
        paramField2.Name = "@Name";
        paramDiscreteValue2.Value = "Sree";
        paramField2.CurrentValues.Add(paramDiscreteValue2);

        //Add the paramField to paramFields
        paramFields.Add(paramField2);


        CrystalReportViewer1.ParameterFieldInfo = paramFields;
Munir replied to Sreekumar P at Monday, August 04, 2008 8:21 AM
nice work
Sreekumar P replied to Munir at Monday, August 04, 2008 8:21 AM
thank u
guna replied to Sreekumar P at Monday, August 04, 2008 8:21 AM
Thanks for your post about 

passing multiple parameters to sp crystal report

but i think it is so complex when a situation comes to pass more than two values we reinitialize object evry time

if there is any solution plz forward me if i found i will also post 
satish replied to Jakir Hossain at Monday, August 04, 2008 8:21 AM
Thanks ......................a lot..........................
It really works ................
Brave ali replied to Sreekumar P at Monday, August 04, 2008 8:21 AM
When i run the Report it ask me for database login why ?
galahad replied to Jakir Hossain at Monday, August 04, 2008 8:21 AM
Dear Jakir, 

   regarding your your Step # 3: 
   h.    Then add stored procedure ‘UserInfo’ from left pane to right pane.

  I do not have the luxury to find any stored procedure from the left pane.  I only have the Add Command above the table.  I've found some sites that simply says to put the : exec storedproc 'param1', 'param2'   command in the command window.  However, if i use a dynamic parameter value, it no longer works for me.


  fyi, i am using .net 2002 dev environment.   Please help me to use the dynamic parameter value.

Thank you so much in advance!
   
kear replied to Sreekumar P at Monday, August 04, 2008 8:21 AM
i use code bellow is ok but i want to export to pdf,can you help me,
//--Initializing CrystalReport
      ReportDocument myReportDocument;
      myReportDocument = new ReportDocument();
      myReportDocument.Load(Server.MapPath("Reports/rptEstimateSchedule.rpt"));
      //--Binding report with CrystalReportViewer     
      CrystalReportViewer1.ReportSource = myReportDocument;

      //Set instances for input parameters
      this.CrystalReportViewer1.ParameterFieldInfo.Clear();


      ParameterFields paramFields = new ParameterFields();

      //Set instances for input parameter QuotationID
      ParameterField paramField = new ParameterField();
      ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
      paramField.Name = "@QuotationID";
      paramDiscreteValue.Value = QuotationID;
      paramField.CurrentValues.Add(paramDiscreteValue);

      //Add the paramField to paramFields
      paramFields.Add(paramField);

      //Set instances for input parameters Name
      ParameterField paramField2 = new ParameterField();
      ParameterDiscreteValue paramDiscreteValue2 = new ParameterDiscreteValue();
      paramField2.Name = "@Name";
      paramDiscreteValue2.Value = "Sree";
      paramField2.CurrentValues.Add(paramDiscreteValue2);

      //Add the paramField to paramFields
      paramFields.Add(paramField2);


      CrystalReportViewer1.ParameterFieldInfo = paramFields;

Thanks
kear replied to kear at Monday, August 04, 2008 8:21 AM
To pdf  

Dim theReport As New ReportDocument
      theReport.FileName = (Server.MapPath("Invoice.rpt"))
      theReport.SetDatabaseLogon("sa", "1", "KEAR-PC", "MKNVAT")

      'Set the required parameter for Crystal Report
      theReport.SetParameterValue("@AccountID", Me.TextBox1.Text)
      theReport.SetParameterValue("@InvoiceID", Me.TextBox2.Text)

      '` export to pdf
      theReport.ExportToDisk(ExportFormatType.PortableDocFormat, "D:\1.pdf")
      theReport.Close()
kear replied to Sreekumar P at Monday, August 04, 2008 8:21 AM
hi ,why i can't use toolbar crystall reports,next,.... function?
can you help me ?
May GUO replied to Jakir Hossain at Monday, August 04, 2008 8:21 AM

Ugg, a legendary brand , http://www.pradaoutletx.com first saw snow boots Ugg simple-minded people will be too cold cartoon form, and http://www.uggsclearancesaleu.com is such a boot, a lot of street shooting star in Europe http://www.burberryoutletx.com and America are full boardhttp://www.uggoutletstoreu.com snow boots look pretty and popular in Europe and the earth, Ugg wind http://www.mulberrypursessale.com continued blowing the popular Madden, in Japan, Taiwan, Ugg has a lot of fans.

UGG Suburb Crochet Gray Boots
sfsfe replied to kear at Monday, August 04, 2008 8:21 AM
A spokesman for Sweden's details Inspection Board mentioned the
authority may possibly <a href="http://www.saleuggbots.com/ugg-sundance-ii-boots-chestnut-p-74.html">Ugg Sundance II Boots Chestnut</a>  investigate the matter.

"It could possibly be instead harmless, or it could impact factors of
privacy," Erik Janzon said. "It will depend on what type of information
and details you feed to the method as well as the target from the use.'
I've by no signifies  <a href="http://www.saleuggbots.com/ugg-suburb-crochet-gray-boots-p-120.html">UGG Suburb Crochet Gray Boots</a>   liked a producer new real estate development, but
there's one thing belonging toward quintessential shady lane about
Buckley Ave in Hobsonville point that tends to make you overlook you're
producing through the use of one.
ugg outlet replied to sfsfe at Monday, August 04, 2008 8:21 AM

and copy chief abroad to cure to climb nerve price to learn neurosurgery to climb to divide volume now<BR>The health department organization writes large mental derangement to learn reference book 1 set in 1981, total three parts:namely nervous system disease'diagnose<BR>Break to learn", " the symptoms learn'and.The treatment learns".I taught to grind room to undertake"nervous system disease the treatment learn'one book of whole<BR>The department writes a task <A href="http://www.metacritics.org">christian shoes</A>, total 60 remainingses are ten thousand words <A href="http://www.outdoorkit.org">ugg outlet</A>, have already been in September <A href="http://www.outdoorkit.org">ugg outlets</A>, 1984 published by big people health publisher.In addition I teach to grind room to return<BR>Took part in parts of articles of national medical science encyclopedia mental derangement credit book to have relation with other various books writing of nerve section work<BR>Make.Combined to take part in Shanghai second medical college copy chief of'the theory and fulfillment of Medicine'writing of relevant part.<BR>I teach to grind a set of nerve Medicine and neurosurgery to accept central

 

Anil Kumar replied to Jakir Hossain at Monday, August 04, 2008 8:21 AM
Thanks you Dr. Dotnetsky for such beautiful article. One question. Using above methods, Though I am able to view the report,  I am not able to export it. It says invalid parameters.
Can you help please.
Ralph Lauren Outlet Online Ralph Lauren Outlet Online replied to ugg outlet at Monday, August 04, 2008 8:21 AM

Save Up to 74% OFF with Free Delivery Worldwide on all http://www.toryburchs-outletshops.com/! Orders $120 or more to get Extra 2% OFF with code TBS802. Buy more, save more! Select your Tory Burch now to enjoy a more wonderful holiday! Everyday Free Shipping and Fast Delivery for all http://www.toryburch-outletmall.us/ online store. Hurry, make these popular Tory Burch Outlet shoes become your new go-to shoes. Chic Tory Burch Outlet is waiting for you. Get cheap yet chic http://www.burberrysoutletus.com/ now to enjoy a very low price. http://www.burberrysoutletus.com/ online is waiting for you. Order Now!

Ralph Lauren Outlet Online Ralph Lauren Outlet Online replied to ugg outlet at Monday, August 04, 2008 8:21 AM

Save Up to 74% OFF with Free Delivery Worldwide on all http://www.toryburchs-outletshops.com/! Orders $120 or more to get Extra 2% OFF with code TBS802. Buy more, save more! Select your Tory Burch now to enjoy a more wonderful holiday! Everyday Free Shipping and Fast Delivery for all http://www.toryburch-outletmall.us/ online store. Hurry, make these popular Tory Burch Outlet shoes become your new go-to shoes. Chic Tory Burch Outlet is waiting for you. Get cheap yet chic http://www.burberrysoutletus.com/ now to enjoy a very low price. http://www.burberrysoutletus.com/ online is waiting for you. Order Now!

Ralph Lauren Outlet Online Ralph Lauren Outlet Online replied to ugg outlet at Monday, August 04, 2008 8:21 AM

Save Up to 74% OFF with Free Delivery Worldwide on all http://www.toryburchs-outletshops.com/! Orders $120 or more to get Extra 2% OFF with code TBS802. Buy more, save more! Select your Tory Burch now to enjoy a more wonderful holiday! Everyday Free Shipping and Fast Delivery for all http://www.toryburch-outletmall.us/ online store. Hurry, make these popular Tory Burch Outlet shoes become your new go-to shoes. Chic Tory Burch Outlet is waiting for you. Get cheap yet chic http://www.burberrysoutletus.com/ now to enjoy a very low price. http://www.burberrysoutletus.com/ online is waiting for you. Order Now!

Ralph Lauren Outlet Online Ralph Lauren Outlet Online replied to ugg outlet at Monday, August 04, 2008 8:21 AM

Save Up to 74% OFF with Free Delivery Worldwide on all http://www.toryburchs-outletshops.com/! Orders $120 or more to get Extra 2% OFF with code TBS802. Buy more, save more! Select your Tory Burch now to enjoy a more wonderful holiday! Everyday Free Shipping and Fast Delivery for all http://www.toryburch-outletmall.us/ online store. Hurry, make these popular Tory Burch Outlet shoes become your new go-to shoes. Chic Tory Burch Outlet is waiting for you. Get cheap yet chic http://www.burberrysoutletus.com/ now to enjoy a very low price. http://www.burberrysoutletus.com/ online is waiting for you. Order Now!

Ralph Lauren Outlet Online Ralph Lauren Outlet Online replied to Ralph Lauren Outlet Online Ralph Lauren Outlet Online at Monday, August 04, 2008 8:21 AM

Save Up to 74% OFF with Free Delivery Worldwide on all http://www.toryburchs-outletshops.com/! Orders $120 or more to get Extra 2% OFF with code TBS802. Buy more, save more! Select your Tory Burch now to enjoy a more wonderful holiday! Everyday Free Shipping and Fast Delivery for all http://www.toryburch-outletmall.us/ online store. Hurry, make these popular Tory Burch Outlet shoes become your new go-to shoes. Chic Tory Burch Outlet is waiting for you. Get cheap yet chic http://www.burberrysoutletus.com/ now to enjoy a very low price. http://www.burberrysoutletus.com/ online is waiting for you. Order Now!

Ralph Lauren Outlet Online Ralph Lauren Outlet Online replied to ugg outlet at Monday, August 04, 2008 8:21 AM

Save Up to 74% OFF with Free Delivery Worldwide on all http://www.toryburchs-outletshops.com/! Orders $120 or more to get Extra 2% OFF with code TBS802. Buy more, save more! Select your Tory Burch now to enjoy a more wonderful holiday! Everyday Free Shipping and Fast Delivery for all http://www.toryburch-outletmall.us/ online store. Hurry, make these popular Tory Burch Outlet shoes become your new go-to shoes. Chic Tory Burch Outlet is waiting for you. Get cheap yet chic http://www.burberrysoutletus.com/ now to enjoy a very low price. http://www.burberrysoutletus.com/ online is waiting for you. Order Now!

Tory Burch Outlet
Tory Burch Outlet Tory Burch Outlet replied to Ralph Lauren Outlet Online Ralph Lauren Outlet Online at Monday, August 04, 2008 8:21 AM

comsetic, people inside London<a href='http://www.toryburchs-outlet-store.com'><strong>Tory Burch Outlet</strong></a>,echarpe burberry, Johnson Burberry opened his 1st shop inside Burberry’s principal<a href='http://www.toryburchs-outlet-store.com'><strong>Tory Burch Flats</strong></a> secret headquarters to sell modern Burberry hobo tote

Tory Burch Outlet
Tory Burch Outlet Tory Burch Outlet replied to Tory Burch Outlet Tory Burch Outlet at Monday, August 04, 2008 8:21 AM

comsetic, people inside Londonhttp://www.toryburchs-outlet-store.com/,echarpe burberry, Johnson Burberry opened his 1st shop inside Burberry’s principalhttp://www.toryburchs-outlet-store.com/ secret headquarters to sell modern Burberry hobo tote