passing multiple parameters in crystal reports

Passing multiple parameters in crystal reports:

hello dears,
i use your code to pass parameter to subreport like code below:

SqlConnection cnn;
            string connectionString = null;
            string sql = null;

            connectionString = "data source=.;initial catalog=Lab2;Integrated Security=yes;";
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            sql = @"EXEC    [dbo].[pro_PrintReceptionReceptionInfo]" +
                            "@ReceptNum = '" + receptionNum + "'" +

                  @"EXEC    [dbo].[pro_PrintReceptionPatientTest]" +
                            "@ReceptionNum = '" + receptionNum + "'" +

                  @"EXEC    [dbo].[pro_PrintReceptionPatientInfo]" +
                            "@PatientNum = '" + patientNum + "'" +

                  @"EXEC    [dbo].[pro_PrintReceptionDepositFund]" +
                            "@ReceptNum = '" + receptionNum + "'";

            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds, "ReceptionInfo, PatientTest, PatientInfo, DepositFund");
            cnn.Close();

            ParameterFields pFields = new ParameterFields();

            ParameterField pf = new ParameterField();
            ParameterDiscreteValue pdv = new ParameterDiscreteValue();
            pdv.Value = int.Parse(receptionNum);
            pf.Name = "@ReceptNum";
            pf.CurrentValues.Add(pdv);

            ParameterField pf2 = new ParameterField();
            ParameterDiscreteValue pdv2 = new ParameterDiscreteValue();
            pdv2.Value = int.Parse(receptionNum);
            pf2.Name = "@ReceptionNum";
            pf2.CurrentValues.Add(pdv2);

            ParameterField pf3 = new ParameterField();
            ParameterDiscreteValue pdv3 = new ParameterDiscreteValue();
            pdv3.Value = Int64.Parse(patientNum);
            pf3.Name = "@PatientNum";
            pf3.CurrentValues.Add(pdv3);
            
            ParameterField pf4 = new ParameterField();
            ParameterDiscreteValue pdv4 = new ParameterDiscreteValue();
            pdv4.Value = int.Parse(receptionNum);
            pf4.Name = "@ReceptNum";
            pf4.CurrentValues.Add(pdv4);

            pFields.Add(pf);
            pFields.Add(pf2);
            pFields.Add(pf3);
            pFields.Add(pf4);

            crystalReportViewer1.ParameterFieldInfo = pFields;

            ReceptionPrint objRpt = new ReceptionPrint();
            objRpt.SetDataSource(ds);
            objRpt.SetParameterValue("@ReceptionNum", int.Parse(receptionNum), "ReceptionPrintTest");
            objRpt.SetParameterValue("@ReceptNum", int.Parse(receptionNum), "DepositPrintSubReport");

            crystalReportViewer1.ReportSource = objRpt;Bottom of Form

Read More

Crystal Report And Parameter Passing Using Stored Procedure

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";

    }

}

Read More