ReportDocument.ReadRecords() does not query for subreport.

[#1]

Hello,

May I preface that I am a first time user of these libraries and have just started this project with very little knowledge or help, just some examples of what our business has done in the past.

I'm building my project around reportxml.txt (attached, can't upload the actual report so I converted it with RptToXML).

The report has 1 main report and 2 subreports. the main has no parameters, which I'm using ReportDocument.RecordSelectionFormula instead for. there are 4 linked parameters on the subgroups.

I've been able to set the ConnectionInfo and DbConnectionAttributes correctly for the main report and subreports, and the ReportDocument.ReadRecords method appears to query the DB at least once correctly, according to SQL Server Profiler.

That is where everything shuts down. It doesn't make any further queries and when I try and export a document, there is nothing in it. ReadRecords produces 0 ReportDocument.Rows.

The report comes from a system that is currently querying the DB and successfully producing a report when I use their interface. I've watched the Sql Server profiler and it appears to query 3 times (one for main, 2 subreport queries) when running a report in their interface.

EDIT: using SAP Crystal Reports version: 13.0.3501

Here is the code I am using:

		static void SetConn(ReportDocument doc, ConnectionInfo cinfo)
			{
			Tables tables = doc.Database.Tables;


			foreach (Table table in tables)
				{
				TableLogOnInfo tableLogonInfo = table.LogOnInfo;
				tableLogonInfo.ConnectionInfo = cinfo;
				table.ApplyLogOnInfo(tableLogonInfo);
				}


			if (!doc.IsSubreport)
				{
				foreach (ReportDocument theSub in doc.Subreports)
					SetConn(theSub, cinfo);
				}
			}


		static void Main(string[] args)
			{
				string file = "C:\\report.rpt";
				try
					{
					ReportDocument doc = null;
					ConnectionInfo cinfo = new ConnectionInfo();
					DbConnectionAttributes dbAttributes = new DbConnectionAttributes();
					cinfo.LogonProperties.Clear();
					cinfo.Attributes.Collection.Clear();


					dbAttributes.Collection.Set("Auto Translate", "-1");
					dbAttributes.Collection.Set("Connect Timeout", "15");
					dbAttributes.Collection.Set("Data Source", "<servername>");
					dbAttributes.Collection.Set("General Timeout", "0");
					dbAttributes.Collection.Set("Initial Catalog", "<dbname>");
					dbAttributes.Collection.Set("Integrated Security", false);
					dbAttributes.Collection.Set("Locale Identifier", "1033");
					dbAttributes.Collection.Set("OLE DB Services", "-5");
					dbAttributes.Collection.Set("Provider", "SQLOLEDB");
					dbAttributes.Collection.Set("Tag with column collation when possible", "0");
					dbAttributes.Collection.Set("Use DSN Default Properties", false);
					dbAttributes.Collection.Set("Use Encryption for Data", "0");


					cinfo.LogonProperties.Clear();
					cinfo.Attributes.Collection.Clear();
					cinfo.DatabaseName = "<dbname>";
					cinfo.ServerName = "<servername>";
					cinfo.UserID = "<username>";
					cinfo.Password = "<password>";
					cinfo.Attributes.Collection.Set("Database DLL", "crdb_ado.dll");
					cinfo.Attributes.Collection.Set("QE_DatabaseName", "<dbname>");
					cinfo.Attributes.Collection.Set("QE_DatabaseType", "OLE DB (ADO)");
					cinfo.Attributes.Collection.Set("QE_LogonProperties", dbAttributes);
					cinfo.Attributes.Collection.Set("QE_ServerDescription", "<servername>");
					cinfo.Attributes.Collection.Set("QE_SQLDB", true);
					cinfo.Attributes.Collection.Set("SSO Enabled", false);
					cinfo.LogonProperties = dbAttributes.Collection;


					doc = new ReportDocument();
					doc.Load(file);


					SetConn(doc, cinfo);
					doc.Refresh();


					doc.RecordSelectionFormula = "{person.person_id}='86A835D1-F85C-4EEB-82CC-43866414F490' AND {patient_encounter.enc_id}='98AA1E61-9334-4D9E-A3D0-36E041FBCA31'";


					doc.VerifyDatabase();
					doc.ReadRecords();
					int rowcnt = doc.Rows.Count;
					
					doc.ExportToDisk(ExportFormatType.PortableDocFormat, @"C:\DocTemp\" + DateTime.Now.ToString("yyyyMMddHHmm") + ".pdf");




					}
				catch (Exception ex)
					{
					}

			}

reportxml.txt

[former_member11696]

First thing to do is not use that FlexNet site for downloading CR runtime, we don't support it.

SP 28 is actually version 13.0.3500.0, for some reason they rename it.

Go here:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

SP 29 is the latest release, the assembly versions will be 13.0.4000.0.

Try this code:

RowsetController boRowsetController;
RowsetCursor boRowsetCursor;


GroupPath gp = new GroupPath();
gp.FromString("*"); // this changes the results but it is not the complete count and it does not get the subreport SQL
int rows;
boRowsetController = rptClientDoc.RowsetController;
try
{
    if (!IsLoggedOn && rptSQLExpress == 0)
    {
        btnReportObjects.Text += "\nPlease wait, attempting to get SQL Statement, requires being logged on first\n";
        btnReportObjects.Update();
        boRowsetCursor = boRowsetController.CreateCursor(gp, new RowsetMetaData(), 0);


        (boRowsetCursor).RowsetController.RowsetBatchSize = 1000;
        (boRowsetCursor).Rowset.BatchSize = 1000;
        (boRowsetCursor).MoveLast();
        rows = boRowsetCursor.RecordCount;
        txtRecordCount.Text = rows.ToString();
    }
    else
    {
        btnReportObjects.Text += "\nSee SQL Statement box for SQL info for main and/or subreport";
        btnReportObjects.Text += "\nReport may be using SQLExpressions which require being logged on first";
    }
}
catch (Exception ex)
{

Don

[Former Member]

Can you explain what's happening in this code? I can't see where the document is loaded nor where I would filter results. I am updating now to SP29, but I remember having an exception thrown in ReportDocument.Refresh() "Object reference not set to an instance of an object". I'll let you know how that goes.

[Former Member]

The problem with SP29 (ReportDocument.Refresh() "Object reference not set to an instance of an object") seems to be resolved, but It appears I'm having the same problem as when I originally posted, regardless of the reinstall/upgrade to SP29. I will need a better explanation of how to use your code above, as it doesn't seem to tie in to the code I'm currently using (ex. What is a GroupPath and RowsetController?)