passing parameters to Crystal report from webforms

Question

i have a report and i want to display it on a webform. Reports without parameters are working nice and fine. Reports with parameters are creating 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
thanks

Answer 1

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.

Answer 2

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 = "[e-mail]";
                    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;
                }
            }

Answer 3

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();

    }