Passing parameters to Crystal report from webforms

I have a report and I want to display it on a webform. Reports without parameters are working well. Reports with parameters are cr开发者_StackOverflow社区eating headache for me. This is the code I have written in BindReport method, which is called on page load event of the form.

    ReportDocument rpt = new ReportDocument();
    rpt.Load(Server.MapPath("rptPositionwiseMontwiseActualSale.rpt"));
    rpt.FileName = Server.MapPath("rptPositionwiseMontwiseActualSale.rpt");
    rpt.SetParameterValue("CompanyName", "Cirin Pharmaceutical Pvt. Limited".ToString());
    rpt.SetParameterValue("ReportTitle", "PositionWise MonthWise Sales".ToString());
    rpt.SetParameterValue("parameters", "Year:2011".ToString());
    //CrystalReportViewer1.ParameterFieldInfo = paramFields;
    DataSet ds = getReportData();
    rpt.SetDataSource(ds.Tables[0]);
    CrystalReportViewer1.ReportSource = rpt;
    CrystalReportViewer1.ReuseParameterValuesOnRefresh = true;
   
    //CrystalReportViewer1.RefreshReport();

I have tried variety of things like assigning ParameterFieldInfo to reportviewer control, but it shows me prompt on page load asking for parameter values of the report. I'm using .NET 4.0.

Edit

I'm using push model for Crystal Reports. Does it change the way we can pass parameters to report from asp.net?

When setting Parameter values at runtime for ASP.NET I think you need to do a bit more work than just called SetParameterValue

string rptTitle = "This is Report Title";

rpt.SetParameterValue("ReportTitle", rptTitle);

ParameterDiscreteValue val = new ParameterDiscreteValue();
val.Value = rptTitle;

ParameterValues paramVals = new ParameterValues();

paramVals.Add(val);

rpt.ParameterFields["ReportTitle"].CurrentValues = paramVals;

rpt.DataDefinition.ParameterFields[0].ApplyCurrentValues(paramVals);

This is probably a bit of overkill but it does actually work, well for me anyway. You must ensure that the parameter names match exactly.

Here is the code Im using in a project to view crystal reports (its a web control)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Objects.Controls;
using System.Data.SqlClient;
using Objects.Database;
using System.IO;
using System.Configuration;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;

namespace App.WebControls
{
    /// <summary>
    /// Crystal Report Viewer Control
    /// </summary>
    public partial class CrystalReportViewer : BaseWebControl
    {
        //- MEMBER VARIABLES --------------------------------------------------------------------------------------------------------

        #region Members

        private CrystalDecisions.Shared.ParameterFields m_ParameterFields = new CrystalDecisions.Shared.ParameterFields();
        private CrystalDecisions.Web.CrystalReportSource m_CrystalReportSource = new CrystalDecisions.Web.CrystalReportSource();

        #endregion Members

        //- PROPERTIES --------------------------------------------------------------------------------------------------------------

        #region Properties

        /// <summary>
        /// Gets or Sets the Crystal Report Source
        /// </summary>
        public CrystalDecisions.Web.CrystalReportSource ReportSource
        {
            get { return m_CrystalReportSource; }
            set { m_CrystalReportSource = value; }
        }

        /// <summary>
        /// Gets the Name of the WAP data source name
        /// </summary>
        public string WAPDataSourceName
        {
            get
            {
                if ( ViewState[ "WAPDataSourceName" ] == null )
                {
                    ViewState[ "WAPDataSourceName" ] = "WAP";
                }

                return ViewState[ "WAPDataSourceName" ] as string;
            }
            set
            {
                ViewState[ "WAPDataSourceName" ] = value;
            }
        }

        /// <summary>
        /// Gets the Name of the Sage Datasource Name
        /// </summary>
        public string SageDataSourceName
        {
            get
            {
                if ( ViewState[ "SageDataSourceName" ] == null )
                {
                    ViewState[ "SageDataSourceName" ] = "WAP_Sage";
                }

                return ViewState[ "SageDataSourceName" ] as string;
            }
            set
            {
                ViewState[ "SageDataSourceName" ] = value;
            }
        }

        /// <summary>
        /// Gets or Sets the Report Filename
        /// </summary>
        public string ReportFileName
        {
            get
            {
                return ReportSource.Report.FileName;
            }
            set
            {
                ReportSource.Report.FileName = value;
            }
        }

        /// <summary>
        /// Gets or Sets the Sage Database
        /// </summary>
        public SageDatabase SageDatabase
        {
            get
            {
                if ( ViewState[ "SageDatabase" ] == null )
                {
                    ViewState[ "SageDatabase" ] = new SageDatabase();
                }

                return ViewState[ "SageDatabase" ] as SageDatabase;
            }
            set
            {
                ViewState[ "SageDatabase" ] = value;
            }
        }

        /// <summary>
        /// Gets the Current Paramter Fields
        /// </summary>
        public CrystalDecisions.Shared.ParameterFields ParameterFields
        {
            get
            {
                return m_ParameterFields;
            }
        }

        #endregion Properties

        //- EVENTS ------------------------------------------------------------------------------------------------------------------

        #region Events

        /// <summary>
        /// Page Load
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load( object sender, EventArgs e )
        {
            try
            {
                if ( !this.IsPostBack )
                {
                    //Set up the Report Source
                    this.SetReportSource();

                    //Set the Connections
                    this.ConfigureReports();

                    //Sets the Parameters
                    this.SetParameters();
                }
            }
            catch ( Exception )
            {
                throw;
            }
        }

        #endregion Events

        //- METHODS -----------------------------------------------------------------------------------------------------------------

        #region Methods

        /// <summary>
        /// Gets a connection info object based on a SQL connection string
        /// </summary>
        /// <param name="oSqlConnectionStringBuilder">The connection string builder</param>
        /// <param name="ServerName">The server name the connection is for</param>
        /// <returns>Connection Info</returns>
        private ConnectionInfo GetConnectionInfo( SqlConnectionStringBuilder oSqlConnectionStringBuilder, string ServerName )
        {
            try
            {
                ConnectionInfo oConnectionInfo = new ConnectionInfo();

                oConnectionInfo.ServerName = ServerName;
                oConnectionInfo.DatabaseName = oSqlConnectionStringBuilder.InitialCatalog;
                oConnectionInfo.UserID = oSqlConnectionStringBuilder.UserID;
                oConnectionInfo.Password = oSqlConnectionStringBuilder.Password;
                oConnectionInfo.IntegratedSecurity = oSqlConnectionStringBuilder.IntegratedSecurity;
                oConnectionInfo.Type = ConnectionInfoType.SQL;
                oConnectionInfo.AllowCustomConnection = true;

                return oConnectionInfo;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Sets the DB logon info for the report
        /// </summary>
        /// <param name="oConnectionInfo">The connection info to set</param>
        private void SetDBLogonForReport( ConnectionInfo oConnectionInfo )
        {
            try
            {
                TableLogOnInfos oTableLogOnInfos = ReportViewer.LogOnInfo;

                foreach ( CrystalDecisions.CrystalReports.Engine.Table oTable in ReportSource.ReportDocument.Database.Tables )
                {
                    if ( oTable.LogOnInfo.ConnectionInfo.ServerName == oConnectionInfo.ServerName )
                    {
                        TableLogOnInfo oTableLogOnInfo = oTable.LogOnInfo;

                        oTableLogOnInfo.ConnectionInfo = oConnectionInfo;

                        oTable.ApplyLogOnInfo( oTableLogOnInfo );

                       // oTable.Location = String.Format( "{0}.dbo.{1}", oConnectionInfo.DatabaseName, oTable.Name );

                        bool b = oTable.TestConnectivity();

                        if ( !b )
                        {
                        }
                    }
                }

            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Configures the reports
        /// </summary>
        private void ConfigureReports()
        {
            try
            {
                //Get connection infos
                ConnectionInfo sageConnectionInfo = this.GetConnectionInfo( new SqlConnectionStringBuilder( this.SageDatabase.ConnectString ), this.SageDataSourceName );
                ConnectionInfo wapConnectionInfo = this.GetConnectionInfo( new SqlConnectionStringBuilder( ConfigurationManager.ConnectionStrings[ "DatabaseConnectString" ].ConnectionString ), this.WAPDataSourceName );

                //Set db logon for the connection infos
                this.SetDBLogonForReport( sageConnectionInfo );
                this.SetDBLogonForReport( wapConnectionInfo );
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Adds a discrete parameteer value
        /// </summary>
        /// <param name="ParameterName">The namee of the parameter to set</param>
        /// <param name="value">The value of the parameter</param>
        public void AddDiscreteValue( string ParameterName, object value )
        {
            try
            {
                //Create a new Parameter Field
                CrystalDecisions.Shared.ParameterField oParameterField = new CrystalDecisions.Shared.ParameterField();
                oParameterField.Name = ParameterName;

                //Create a new Discrete Value
                CrystalDecisions.Shared.ParameterDiscreteValue oParameterDiscreteValue = new CrystalDecisions.Shared.ParameterDiscreteValue();
                oParameterDiscreteValue.Value = value;

                //Add the value
                oParameterField.CurrentValues.Add( oParameterDiscreteValue );

                //Add the parameter field
                this.ParameterFields.Add( oParameterField );
            }
            catch ( Exception )
            {
                throw;
            }
        }

        /// <summary>
        /// Sets up the Report Source
        /// </summary>
        private void SetReportSource()
        {
            try
            {
                //Load the report based on Filename
                this.ReportSource.ReportDocument.Load( Server.MapPath( this.ReportFileName ) );
            }
            catch ( Exception )
            {
                throw;
            }
        }

        /// <summary>
        /// Exports the report to disk
        /// </summary>
        /// <param name="FileName">The name of the file</param>
        public void ExportToDisk( string FileName )
        {
            try
            {
                this.ReportSource.ReportDocument.ExportToDisk( CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, FileName );
            }
            catch ( Exception )
            {
                throw;
            }
        }

        /// <summary>
        /// Exports the Report to Email 
        /// </summary>
        [Obsolete( "Bug in Crystal Reports objects that causes the attachment to be called untitled.txt", true )]
        public void ExportToMAPI()
        {
            try
            {
                CrystalDecisions.Shared.ExportOptions oExportOptions = new CrystalDecisions.Shared.ExportOptions();
                {
                    CrystalDecisions.Shared.MicrosoftMailDestinationOptions oMicrosoftMailDestinationOptions = new CrystalDecisions.Shared.MicrosoftMailDestinationOptions();
                    oMicrosoftMailDestinationOptions.MailToList = "nathanf@nfs.co.uk";
                    oMicrosoftMailDestinationOptions.MailSubject = "test";
                    oMicrosoftMailDestinationOptions.MailMessage = "Body text";

                    CrystalDecisions.Shared.PdfRtfWordFormatOptions oPdfRtfWordFormatOptions = new CrystalDecisions.Shared.PdfRtfWordFormatOptions();


                    oExportOptions.ExportDestinationOptions = oMicrosoftMailDestinationOptions;
                    oExportOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.MicrosoftMail;
                    oExportOptions.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                    oExportOptions.ExportFormatOptions = oPdfRtfWordFormatOptions;

                    this.ReportSource.ReportDocument.Export( oExportOptions );
                }
            }
            catch ( Exception )
            {
                throw;
            }
        }

        /// <summary>
        /// Exports the Current Report to a Stream
        /// </summary>
        /// <returns></returns>
        public Stream ExportToStream()
        {
            try
            {
                return this.ReportSource.ReportDocument.ExportToStream( CrystalDecisions.Shared.ExportFormatType.PortableDocFormat );
            }
            catch ( Exception )
            {
                throw;
            }
        }

        /// <summary>
        /// Sets the Parameters
        /// </summary>
        private void SetParameters()
        {
            try
            {
                if ( this.ParameterFields.Count > 0 )
                {
                    //Set the Parameters
                    this.ReportViewer.ParameterFieldInfo = this.ParameterFields;
                }

                //Set the report source
                this.ReportViewer.ReportSource = this.ReportSource;
            }
            catch ( Exception )
            {
                throw;
            }
        }

        #endregion Methods

        //---------------------------------------------------------------------------------------------------------------------------
    }
}

To use this in code:

/// <summary>
/// Page Load
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load( object sender, EventArgs e )
{
    try
    {
        if ( !Page.IsPostBack )
        {
            //Set the report filename
            this.CrystalReportViewer.ReportFileName = @"~/Reports/WAP Std Sage PurchaseOrder.rpt";

            if ( base.CurrentOrder != null )
            {
                //Set the Sage Database
                this.CrystalReportViewer.SageDatabase = base.CurrentOrder.SageDatabase;

                //Set Order ID parameter
                this.CrystalReportViewer.AddDiscreteValue( "OrderID", base.CurrentOrder.OrderID );

            }
        }
    }
    catch ( Exception ex )
    {
        ErrorLogging.LogError( ex );
    }
}

The main methods to look at are AddDiscreteValue and SetParameters in the User Control

EDIT:

Abstracted relevant methods:

/// <summary>
            /// Sets the Parameters
            /// </summary>
            private void SetParameters()
            {
                try
                {
                    if ( this.ParameterFields.Count > 0 )
                    {
                        //Set the Parameters
                        this.ReportViewer.ParameterFieldInfo = this.ParameterFields;
                    }

                    //Set the report source
                    this.ReportViewer.ReportSource = this.ReportSource;
                }
                catch ( Exception )
                {
                    throw;
                }
            }

/// Adds a discrete parameteer value
            /// </summary>
            /// <param name="ParameterName">The namee of the parameter to set</param>
            /// <param name="value">The value of the parameter</param>
            public void AddDiscreteValue( string ParameterName, object value )
            {
                try
                {
                    //Create a new Parameter Field
                    CrystalDecisions.Shared.ParameterField oParameterField = new CrystalDecisions.Shared.ParameterField();
                    oParameterField.Name = ParameterName;

                    //Create a new Discrete Value
                    CrystalDecisions.Shared.ParameterDiscreteValue oParameterDiscreteValue = new CrystalDecisions.Shared.ParameterDiscreteValue();
                    oParameterDiscreteValue.Value = value;

                    //Add the value
                    oParameterField.CurrentValues.Add( oParameterDiscreteValue );

                    //Add the parameter field
                    this.ParameterFields.Add( oParameterField );
                }
                catch ( Exception )
                {
                    throw;
                }
            }

Use like that.

    SqlConnection Con = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString);
    SqlCommand cmd = new SqlCommand();

    #region Help Method
    private void ProjectDataBind()
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        try
        {
            Con.Open();
            cmd.CommandText = "sp_Project_SelectAll";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Connection = Con;

            adapter.Fill(ds, "tbl_Project");
          }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            cmd.Dispose();
            if (Con.State != ConnectionState.Closed)
                Con.Close();
        }
        //DataTable dt = controller.SelectAll();
        cboFromProject.DataTextField = "Project";
        cboFromProject.DataValueField = "ProjectID";
        cboFromProject.DataSource = ds.Tables[0];
        cboFromProject.DataBind();

    }
    private void ToProjectDataBind()
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        try
        {
            Con.Open();
            cmd.CommandText = "sp_Project_SelectAll";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Connection = Con;

            adapter.Fill(ds, "tbl_Project");
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            cmd.Dispose();
            if (Con.State != ConnectionState.Closed)
                Con.Close();
        }
        //DataTable dtable = controller.SelectAll();
        cboToProject.DataTextField = "Project";
        cboToProject.DataValueField = "ProjectID";
        cboToProject.DataSource = ds.Tables[0];//dtable;
        cboToProject.DataBind();
    }
    public DataSet getFromTOProjects(int fproject, int toproject)
    {

        //string sqlCon = ConfigurationManager.AppSettings["MainDB"].ToString();

        DataSet ds = null;
        SqlDataAdapter adapter;
        try
        {
            Con.Open();
            cmd.CommandText = "sp_projectReport_ByProject";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@fproject", fproject));
            cmd.Parameters.Add(new SqlParameter("@toproject", toproject));
            cmd.Connection = Con;
            ds = new DataSet();
            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds, "tbl_Project");
            //adapter.Fill(ds);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            cmd.Dispose();
            if (Con.State != ConnectionState.Closed)
                Con.Close();
        }
        return ds;
    }
    #endregion


   protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ProjectDataBind();
            ToProjectDataBind();
        }
        if (IsPostBack)
        {
            try
            {
                CrystalReportViewer2.ReportSource = (ReportDocument)Session["Report"];
                //CrystalReportViewer2.RefreshReport(); write this code,will prompt
                //CrystalReportViewer2.DataBind();
            }
            catch (Exception ex)
            {

                // throw;
            }
        }

    }
    protected void btnPreview_Click(object sender, EventArgs e)
    {

            ReportDocument rptDoc = new ReportDocument();
            DataSet ds = new DataSet();
            DataTable dtable = new DataTable();
            dtable.TableName = "tbl_Project";
            ds = getFromTOProjects(Convert.ToInt32(cboFromProject.SelectedValue), Convert.ToInt32(cboToProject.SelectedValue));
            dtable = ds.Tables[0];

            rptDoc.Load(Server.MapPath("~\\Reports\\ProjectReportWithPara.rpt"));
            rptDoc.SetDataSource(dtable);
            rptDoc.SetParameterValue("ReportTitle", "Project Report By Project");
            rptDoc.SetParameterValue("FromProject", cboFromProject.SelectedItem.Text);
            rptDoc.SetParameterValue("ToProject", cboToProject.SelectedItem.Text);

            //string rptTitle = "This is Report Title";

            //rpt.SetParameterValue("ReportTitle", rptTitle);

            //ParameterDiscreteValue val = new ParameterDiscreteValue();
            //val.Value = rptTitle;

            //ParameterValues paramVals = new ParameterValues();

            //paramVals.Add(val);

            //rpt.ParameterFields["ReportTitle"].CurrentValues = paramVals;

            //rpt.DataDefinition.ParameterFields[0].ApplyCurrentValues(paramVals);

            Session["Report"] = rptDoc;
            CrystalReportViewer2.ReportSource = rptDoc;

            //CrystalReportViewer2.ReuseParameterValuesOnRefresh = false; 
            //CrystalReportViewer2.DataBind();
            //CrystalReportViewer2.ReportSource = rptDoc.Clone(); 
     // can print and export but prompt already set parameters
            //CrystalReportViewer2.RefreshReport();

    }