Crystal Report: Missing Parameter Values

Question

I am new to Crystal report, application is in ASP.net 3.5 and MySQL 5.1, going to develop report between dates like from date and to date, first page of report is shown good but when i tried to navigate on another page i got error like Missing Parameter Values same error i got in Printing and Export action Thanks in advance

public partial class BookingStatement : System.Web.UI.Page {

//DAL is my Data Access Layer Class

//Book is ReportClass

DAL obj = new DAL();
Book bkStmt = new Book();
protected void Page_Load(object sender, EventArgs e)
{

    if (!IsPostBack)
    {
       //crvBooking is Crystal Report Viewer
       //reportFill method is to fill Report 

        reportFill();
        crvBooking.EnableViewState = true;
        crvBooking.EnableParameterPrompt = false;
    }


   /* Also try reportFill() out side !IsPostBack but didn't work */


    //Check if the parmeters have been shown.
 /*   if ((ViewState["ParametersShown"] != null) && (ViewState["ParametersShown"].ToString() == "True"))
    {
        bkStmt.SetParameterValue(0, "20/04/2010");
        bkStmt.SetParameterValue(1, "20/04/2010");
    }*/

}


protected void crvBooking_navigate(object sender, CrystalDecisions.Web.NavigateEventArgs e)
{
   // reportFill();
}

protected void reportFill()
{

    //bkStmt.rpt is Report file
    //bookingstatment is View
    //bkStmt is ReportClass object of Book

    string rptPath = "bkStmt.rpt";

    string query = "select * from bookingstatment";


    crvBooking.RefreshReport();
    crvBooking.Height = 600;
    crvBooking.Width = 900;



    bkStmt.ResourceName = rptPath;


    String dtFrm = bkStmt.ParameterFields[0].CurrentValues.ToString();

    obj.SetCommandType(CommandType.Text);
    obj.CommText = query;
    DataTable dtst = obj.GetDataTable();

    crvBooking.ParameterFieldInfo.Clear();



    ParameterDiscreteValue discretevalue = new ParameterDiscreteValue();
    discretevalue.Value = "20/04/2010"; // Assign parameter
    ParameterValues values = new ParameterValues();
    values.Add(discretevalue);

    bkStmt.SetDataSource(dtst);

    ViewState["ParametersShown"] = "True";
    crvBooking.EnableViewState = true;

    bkStmt.DataDefinition.ParameterFields[0].ApplyCurrentValues(values);
    bkStmt.DataDefinition.ParameterFields[1].ApplyCurrentValues(values);


    crvBooking.ReportSource = bkStmt;
}

}

Answer 1

The problem seems to occur because Crystal Reports does not persist its parameter values in its ViewState when a postback occurs. So when the CrystalReportViewer attempts to load up the ReportClass it used as its ReportSource again, the parameter values are no longer there.

A solution which we've used successfully is to save the ReportClass (i.e. your Crystal Report object) into Session after all its parameter values have been set & then load this into the CrystalReportViewer upon each PostBack in the Page_Init event. An example:

// instantiate the Crystal Report
var report = new DeliveryLabelsSingle();

// set the required parameters
report.DataSourceConnections[0].SetConnection("DBServer", "DatabaseName", "DatabaseUser", "DatabasePassword");
report.SetParameterValue("@Param1", "val1");
report.SetParameterValue("@Param2", "val2");

// set the data source of the viewer
crvLabels.ReportSource = report;

// save the report object in session for postback binding
Session["rptDeliveryLabels"] = report;

Then the Page_Init event for the page looks like the following:

protected void Page_Init(object sender, EventArgs e)
{
    if (IsPostBack) {
        if (Session["rptDeliveryLabels"] != null) {
            // cast the report from object to ReportClass so it can be set as the CrystalReportViewer ReportSource
            // (All Crystal Reports inherit from ReportClass, so it serves as an acceptable data type through polymorphism)
            crvLabels.ReportSource = (ReportClass)Session["rptDeliveryLabels"];
        }
    }
}

In this way, we will always set a report object for the viewer, which has already been initialized with the appropriate values.

Something to keep in mind with this approach is that you will potentially fill up your server memory very quickly, especially if you have lots of users generating lots of different reports. So some housekeeping is in order. We've done this through implementing a base class for all our ASP.NET pages that contain a report (and thus this report loading code). In this base class, we set all possible Session variables that are reports to null. Like so:

// class definition for ASP.NET page containing CrystalReportViewer & associated report(s)
public partial class DeliveryLabelPrint : BaseReport

Then the definition for BaseReport is as follows:

public class BaseReport : System.Web.UI.Page
{
    protected override void OnLoad(EventArgs e)
    {
        if (!IsPostBack) {
            for (var i = 0; i < Session.Count; i++) {
                var sv = Session[i];
                // if this session variable contains a Crystal Report, destroy it
                if (sv is ReportClass) {
                    sv = null;
                }
            }

            base.OnLoad(e);
        }
    }
}

In this way, you ensure that any user only ever has one report in memory at any given time.

If memory is a concern, even with this approach, an alternative could be to store the individual variable values in Session & to then instantiate a new report in Page_Init & repopulate it with the saved values before assigning it to CrystalReportViewer.ReportSource. But in our case, with 40 users pulling 50+ different reports on a daily basis, this implementation of storing of the ReportClass object & the accompanying housekeeping, we haven't run into any memory problems since the application went live 3 years ago. I would still suggest doing appropriate load testing & monitoring before pushing this solution to production, as the results may vary depending on the specific implementation.

Answer 2

When I am writing SQL for a Crystal report, the code for the parameters in SQL is like this:

--Date Range
(
(table.datetime >= '{?Start Date}')
and table.datetime < '{?End Date}')
)

--Location
('{?Facility}'= 'All' OR '{?Facility}' = table.location))

Of course you always have the option of programming the parameters straight into Crystal. This approach is not as efficient but sometimes easier.

Answer 3

I had to use explicit ReportDocument type instead of ReportClass because that raised an invalid cast for some reason, but otherwise, works EXACTLY as advertised AFAICT. viz.

...
...
 if (Session["<some identifier>"] != null)
                {
                    switch (Session["<some identifier>"])
                    {
                        case ReportClass rc:
                            crystalReportViewer1.ReportSource = rc;
                            break;

                        case ReportDocument rd:
                            crystalReportViewer1.ReportSource = rd;
                            break;

                        default:
                            return;
                    }
                }