Crystal Reports not selecting any records when I add a parameter to the report

[Former Member]

I'm using Crystal Reports inside of Visual Studio 2010 with ASP.NET.

I've got a DataSet that pulls data from a database, and it seems to load into the report just fine. When I view the report in my browser, all records load correctly.

I have to add a parameter for the user to enter in order to filter out entries in the report. The problem is that when I add any sort of parameter to the report. If the parameter isn't on the page or being used in the selection expert, the records load fine. But as soon as I add the parameter to the page, no records can be found. In other words, if the parameter prompt is displayed, the viewer loads no records.

I added a drop down list parameter just containing the numbers 1, 2, and 3. Without anything in the selection expert this shouldn't affect anything. But for whatever reason, when this is in the report no records get selected. As soon as I remove it from the page, all records load fine.

I'm adding the parameter and its values through the field explorer if that makes a difference.

The odd thing is that is implementation worked when I had the report pull its data directly from the database, instead of creating a DataSet first. The reason I can't use this implementation is that it kept asking for the database login information. Even when I predefined the information. I switched to a DataSet so I would avoid the login prompt.

Here is the relevant code:

myDataSet ds = new myDataSet();

myDataSetTableAdapters.myTableTableAdapter dsTA = new myDataSetTableAdapters.myTableTableAdapter();
dsTA.Fill(ds.myTable);

// Initializing the report file
rpt = new ReportDocument();
string reportPath = Server.MapPath(mapPath);

rpt.Load(reportPath);

Response.Write("<script>alert('DataSet has "+ ds.myTable.Rows.Count +" rows');</script>");
rpt.SetDataSource(ds);

//Response.Write("<script>alert('Report has "+ rpt.Rows.Count +" rows');</script>");

// Binding the report file to the report viewer on the page

CrystalReportViewer1.ReportSource = rpt;

The first Response.Write does indeed print the correct number of rows being sent to the report viewer. But the second one that counts the rows in the report throws a "COM Exception: Missing Parameter Values" when the parameter is on the page. Both work when the parameter isn't on the page. rpt.HasRecords also fails.

I can see this behavior when the website is being run in VS2010's debugging mode, and in the report preview within VS2010. Even without the DataSet containing any data, the report still displays the filler data when there aren't parameters on the page, and no filler data when a parameter is added.

I didn't want to have to pass in a parameter, because I want the user to be prompted for one at the start. But I did try adding one in.

rpt.SetParameterValue("par1", 1);

Interestingly, this allowed all of the data to be loaded in the viewer... until I passed it a new parameter. Then it returned no results. Even when setting it back to 1. It seems that for some reason, when the viewer is given a parameter to handle it drops all of the records.

I honestly have no idea of where to go with this now. The errors appear to either be coming from rpt.SetDataSource, CrystalReportViewer1.ReportSource.

[Former Member · Accepted solution]

Ludek, thank you so much for your responses. I was able to figure it out.

It turns out that sessions were the answer, but I couldn't find an example of an implementation that worked for me. I took most of the ideas from this KBA.

Here's my implementation:

private ReportDocument rpt;

private myDataSet ds;

protected void Page_Init(object sender, EventArgs e)
{

   if (!IsPostBack)
   {

      Session["Report"] = null;

   }

   if (Session["Report"] == null)
   {

      ds = new myDataSet();
      myDataSetTableAdapters.myTableTableAdapter dsTA = new myDataSetTableAdapters.myTableTableAdapter();
      DataTable dt = dsTA.GetData();

      rpt = new ReportDocument();
      rpt.Load(Server.MapPath("myReport.rpt"));
      rpt.SetDataSource(dt);
      Session.Add("Report", rpt);
      CrystalReportViewer1.ReportSource = rpt;
      CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.ParameterPanel;
      CrystalReportViewer1.HasToggleGroupTreeButton = false;

   }

}

protected void Page_Load(object sender, EventArgs e)
{

   if (Page.IsPostBack)

   {

      rpt = (ReportDocument)Session["Report"];
      CrystalReportViewer1.ReportSource = rpt;
      CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.ParameterPanel;
      CrystalReportViewer1.HasToggleGroupTreeButton = false;

   }


}


protected void butReport_Click(object sender, EventArgs e)
{

   if (Session["Report"] == null) // Report is not in session (previously loaded) so load report, set params, view and place in session
   {

      ds = new myDataSet();
      myDataSetTableAdapters.myTableTableAdapter dsTA = new myDataSetTableAdapters.myTableTableAdapter();
      DataTable dt = dsTA.GetData();

      rpt = new ReportDocument();
      rpt.Load(Server.MapPath("myReport.rpt"));
      rpt.SetDataSource(dt);
      Session.Add("Report", rpt);
      CrystalReportViewer1.ReportSource = rpt;
      CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.ParameterPanel;
      CrystalReportViewer1.HasToggleGroupTreeButton = false;
     
   }
   else
   {
     
      rpt = (ReportDocument)Session["Report"];
      CrystalReportViewer1.ReportSource = rpt;
      CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.ParameterPanel;
      CrystalReportViewer1.HasToggleGroupTreeButton = false;

   }

}

The big difference is this line: DataTable dt = dsTA.GetData();

Before I was passing in the entire DataSet to rpt. I'm not sure why, but creating a new DataTable with GetData()'s results lets the report stay in session.

Also, I couldn't get the Page_Unload() part of the KBA code to work. Unload seems to get called before the page posts back, which is a lot. rpt couldn't stay in session with it on. Without it everything appears to be fine.

Everything loads. Everything persists when buttons are pressed. And parameters work for record selection.

Now the only problem I have is that my parameters don't show up in the parameter panel on the left of the viewer. Even though I set them to 'editable' in the parameter edit window.  This is something that worked when the rest of the report didn't.

Should I try to find help for that in this thread, or make a new one?

[former_member183750 · Accepted solution]

Please do create a new discussion. And many thanks for sharing the solution

- Ludek

[former_member183750]

Hi Justin

Do make sure you are using 'SAP Crystal Reports, Developer Version for Visual Studio .NET", SP 13. Link to SP 13 is here:

Next, how was the report created? Off of an XML?

Can you try the report in the SAP Crystal Reports 2013 designer? Eval is here:

SME Free Trials | SME Software | SAP

You obviously will not be able to connect the report to the dataset in the designer, but should be no problem connecting it to XML or what ever the initial datasource was. Create the parameter prompt there, test, then try to run the report in the app.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter

[Former Member]

Hey Ludek

I am using SP 13.

As for how the report was created I first added a DataSet (.xsd) file to the project, and through the server explorer, I dragged over the relevant view. Then I went into the Crystal Reports design wizard. One thing I noticed was that there appear to be multiple ways to get to the DataSet. It appears under

ProjectData>ADO.NET DataSets>

and I can find it through the dialog when creating a new connection for ADO.NET (XML), or Database Files.

When I connected the report to my dataset, I created a new connection and found it as a Database file, not an ADO.NET (XML) file. Is that an issue? I didn't think so because either way all of the datafields were transferred into the report. To make sure, I tried making a new DataSet and then I set up a new crystal report with it treating the DataSet as an ADO.NET XML file. Still didn't work.

I'm downloading the trial now. I'll report back with what happens when I use it.

[Former Member]

Ludek,

I installed Crystal Reports, and created a new report linked to my DataSet. From there I'm not quite sure about what you wanted me to do. Is my DataSet supposed to actually contain data? Or just fields? It gets the fields just fine, but there isn't anything to display in the preview. I had thought that my C# code was what filled the DataSet.

Either way, I saved the report, and had my C# code point to it as the report file. It loaded all records in my browser just fine, without a parameter. When I added the parameter in Crystal Reports 2013, no records loaded, same as before.

At this point I tried running the report in IE and FireFox, as I had been using Chrome. None of them worked.

So going back to my earlier question, is there a way for me to generate this report purely inside of Crystal Reports 2013 (without using C# to bind the data to the DataSet)? If so then that might be the issue, and I can do that within Visual Studio to get it to run on my website.

[Former Member]

I tried changing up my implementation based on some feedback I've gotten on a stack overflow thread. I eventually ran into the infinite 'Please wait while the document is being processed.' popup. This happens after entering the parameter for the page.

Through this, I can see that the problem is probably something to do with the page being posted back after the parameter is entered.

I've seen a bunch of threads on this issue, and they all call for the report to be loaded during the Page_Init() event, and for the report to kept in session.

The problem now is that report documents can't be kept when the sessionState is in StateSever mode.  I read about one guy keeping the DataSet in session, and then repassing that into a new report but that didn't work for me either.

I'd change the sessionState mode to InProc, but the rest of the website might need it in the other mode. I wasn't the one who made it, I'm just adding the Crystal Reports functionality.

Here's my current relevant code. I just want to get something to work, then I'd try making it more complex than it was before.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data; 



private ReportDocument rpt;
private myDataSet ds;

protected void Page_Init(object sender, EventArgs e)
   {

   if (!Page.IsPostBack)
   {

       ds = new myDataSet();


       myDataSetTableAdapters.myTableTableAdapter dsTA = new myDataSetTableAdapters.myTableTableAdapter();

       dsTA.Fill(ds.myTable);
       // Initializing the report file
       rpt = new ReportDocument();
       string reportPath = Server.MapPath("myReport.rpt");
       rpt.Load(reportPath);
       Session.Add("dataSet", ds);
       Session["dataSet"] = ds;

       rpt.SetDataSource(ds);

       CrystalReportViewer1.ReportSource = rpt;

   }
   else
   {

       ds = (myDataSet)Session["Report"];
       rpt = new ReportDocument();
       string reportPath = Server.MapPath("myReport.rpt");
       rpt.Load(reportPath);
       rpt.SetDataSource(ds);
       CrystalReportViewer1.ReportSource = rpt;

   }

}

Edit: I just tried changing the sessionState to InProc, and this still doesn't work. Even when I make it so the report is what's being saved in session.

[Former Member]

I tried putting a breakpoint in the Page_Init(), and it revealed some interesting things.

I could see that Session["Report"]; was correctly being saved during the initial run through the code.

But after entering the parameter I watched Page_Init() again, and when it tried doing rpt = (ReportDocument)Session["Report"]; everything in Session["Report"] was null. It threw lots of null exceptions, but these were only visible in the breakpoint debugger, they never seemed to come up during a normal run. I'm pretty sure that's why it keeps loading forever after the post back.

So the problem now is that the post back after entering a parameter causes all data in the session to be lost.

[Former Member]

You can disregard the previous reply. I commented out this code:

protected void Page_UnLoad(object sender, EventArgs e)
{
   try
   {


       rpt.Close();
       rpt.Dispose();


   }
   catch { }
}

After getting rid of this code, I looked through the Session["Report"] variable again and it is no longer null.

Interestingly, Page_UnLoad is being called BEFORE even getting to the parameter entry. If rpt.Close(); is dumping all references to rpt, then it's as if the Session variable is acting like a pointer, instead of storing an actual rpt object. So when rpt is Disposed(), Session["Report"] appears to just be a null pointer.

So now it with the right stuff being passed around... it still has the infinite 'document processing' screen.

[former_member183750]

Maybe the best thing will be to take a lok at this KBA: 1511438 - How to use datasets to pass data to crystal reports and follow the links there.

- Ludek