Question Re: ReportDocument.ReadRecords() does not query for subreport. in Technology Q&A

ReportDocument.ReadRecords() does not query for subreport.

Former Member — Thu, 22 Oct 2020 07:59:13 GMT

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)
					{
					}

			}

Re: ReportDocument.ReadRecords() does not query for subreport.

former_member11696 — Thu, 22 Oct 2020 12:48:24 GMT

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

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Thu, 22 Oct 2020 13:57:08 GMT

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.

Re: ReportDocument.ReadRecords() does not query for subreport.

former_member11696 — Thu, 22 Oct 2020 16:06:01 GMT

Ah, sorry about that. The problem is you are using the ReportDocument (Engine), what you need to do is use the ReportClientDocument (RAS), Engine only allows some info and basic changes to be done, for detailed info retrieval you need to use RAS.

Download my parameter test app:

how-to-parameters-in-crystal-reports-for-visual-studio-net

It has examples of how to open the report using the Engine and then converting that report object to a RAS object

Regarding the error...

When ever you get "Object reference not set to an instance of an object" it means the report object you have has gone out of scope, means exactly what it says, in debug mode mouse over the report object in your code, you'll see it's null.

Don't use Refresh, it's not required, every change you programmed gets triggered when you preview, export or print the report.

Don

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Thu, 22 Oct 2020 19:53:54 GMT

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?)

Re: ReportDocument.ReadRecords() does not query for subreport.

former_member11696 — Thu, 22 Oct 2020 20:08:43 GMT

The Get Query is a bonus, not needed for your example. Actually the sample code doesn't get the row count, just add my code in a button to get it for testing.

OK so that's odd, it works on some PC's but not others.

Likely missing the database driver or Client.

Do some debugging, some dependency is missing.

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Fri, 23 Oct 2020 11:46:44 GMT

Thanks, and I did download the project and try some things. I'm not really sure what i need to do differently though, even based on the code I'm looking at. There is a lot of code in there showing how to get the SQL out of reports, but why is that of interest here? I was under the impression that when you export, all the queries in the report are run to fill in the report PDF. Is this not true? Do I need to do something else? The reason I ask is that it only runs one report query for me when exported, but runs 3 (correct number) on another system that produces the desired report PDF. the 2 subreport links should indicate to the report that is run how to query.

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Fri, 23 Oct 2020 11:47:03 GMT

I've tried the code you posted earlier on my report. First, the "RowsetController.CreateCursor" method thew an exception:

{System.Runtime.InteropServices.COMException (0x8000023A): Error in File VerifyMedsAndAllergies 110480_135384_{103335E6-77B2-4FBD-A9E7-4E481E03EA9C}.rpt: Invalid group number. at CrystalDecisions.ReportAppServer.Controllers.RowsetControllerClass.CreateCursor(ISCRGroupPath GroupPath, RowsetMetaData MetaData, Int32 Reserved) at Program.Main(String[] args) in D:\Program.cs:line 239}

Then, I removed the "gp.FromString("*")" to see if that would help and it doesn't throw, but it gets 0 rows and makes the exact same query as ReportDocument.ReadRecords() on the DB according to sql server profiler. what is that line for? (gp.FromString("*"))

It is definitely not a Database driver issue, since the one query is happening, but nothing else.

Re: ReportDocument.ReadRecords() does not query for subreport.

former_member11696 — Fri, 23 Oct 2020 13:24:47 GMT

Did you uninstall what ever previous version you had installed and replace all CR Assemblies with SP 29 and verify by looking a the properties of each assembly it's being loaded from the same location and same platform?

If all CR assemblies are x86 then you'll see this location for each dll.

C:\Program Files (x86)\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Common\SAP BusinessObjects Enterprise XI 4.0\win32_x86\dotnet\CrystalDecisions.CrystalReports.Engine.dll

Check your references also, you should have all of these:

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportAppServer;
using CrystalDecisions.ReportAppServer.ClientDoc;
using CrystalDecisions.ReportAppServer.Controllers;
using CrystalDecisions.ReportAppServer.ReportDefModel;
using CrystalDecisions.ReportAppServer.CommonControls;
using CrystalDecisions.ReportAppServer.CommLayer;
using CrystalDecisions.ReportAppServer.CommonObjectModel;
using CrystalDecisions.ReportAppServer.ObjectFactory;
using CrystalDecisions.ReportAppServer.Prompting;
using CrystalDecisions.ReportAppServer.DataSetConversion;
using CrystalDecisions.ReportAppServer.DataDefModel;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Windows.Forms;
using CrystalDecisions.ReportAppServer.XmlSerialize;

Re: ReportDocument.ReadRecords() does not query for subreport.

former_member11696 — Fri, 23 Oct 2020 15:20:48 GMT

Hi Trent,

Thanks for the report, without data though I can't get the record count. And the only way to get the count is being logged onto the data source, main and subreports.

Same for you, you need to log on to both before records can be retrieved. And you need to preview the report to be able to get the record count.

We don't have a process that can get the count without saved data, because CRD is a multi-pass processing tool, we need to read records and then depending on the reports format, the placement of data fields into the various parts of the report, Header, Details, subreports etc.

So getting a record count on a report without saved data won't work, and only reason no data would be returned in the subreports is because the log on info failed.

When you set the log on info for both main and subreports try using this:

try
{
    if (crTable.TestConnectivity())
        ConWorks = true;
    else
        ConWorks = false;
}
catch (Exception ex)
{
    btnSQLStatement.Text += "Test Connectivity failed: " + ex.ToString();
}

If that fails then you know why your subs show no data.

As I said, don't use VerifyDatabase, it can cause the report to run a second time without any values you set in code.

Don

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Mon, 26 Oct 2020 04:33:11 GMT

Also, I have run the query that it DOES do, and I get 1 row (pictured below). I ran it directly on the server the report is connected to in SQL Server Management studio.

If the report does a query that should retrieve one row, shouldn't it continue to execute further queries based on the fields in the report? Is there something else I need to do to make that happen?

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Mon, 26 Oct 2020 04:33:21 GMT

I've changed the SetCon method in my app to the below code and debugged. Every time, "testc" was set to true. It's definitely connected, just not actually querying after the initial query.

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


			bool testc = false;


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


				testc = table.TestConnectivity();
				testc = false; //breakpoint is here to check all tables' connectivity for issues
				}


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

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Mon, 26 Oct 2020 08:31:31 GMT

Since I'd like to be thorough and assure you're getting all the necessary tools to troubleshoot, here is a link to the report I'm trying to export as PDF:

report link

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Mon, 26 Oct 2020 09:07:35 GMT

Yes, I have verified All referenced files are from that directory (it is x86) and I've referenced all in the .CS file exactly as you state. I see no change in execution after verification to the resultant PDF, it's still blank after running. The previous code you shared still causes the same exception, and when the gp.FromString("*") line is commented out, returns 0 rows.

The database is still only being queried once when exporting, like so:

SELECT "person"."other_id_number", "person"."first_name", "person"."middle_name", "person"."last_name", "patient"."practice_id", "patient"."person_id", "person"."person_id", "patient_encounter"."enc_id" FROM ("patient" "patient" INNER JOIN "person" "person" ON "patient"."person_id"="person"."person_id") INNER JOIN "patient_encounter" "patient_encounter" ON ("patient"."practice_id"="patient_encounter"."practice_id") AND ("patient"."person_id"="patient_encounter"."person_id") WHERE "person"."person_id"='86A835D1-F85C-4EEB-82CC-43866414F490' AND "patient_encounter"."enc_id"='98AA1E61-9334-4D9E-A3D0-36E041FBCA31'

The interface that IS working correctly (not mine) queries the database 3 times, once EXACTLY the same as above and 2 others, like so:

SELECT "fdb_medication"."brand_name", "patient_medication"."sig_desc", "patient_medication"."date_stopped", "patient"."practice_id", "patient"."person_id", "patient_medication"."reprndc_ind", "fdb_medication"."dose" FROM ("patient" "patient" INNER JOIN "patient_medication" "patient_medication" ON ("patient"."person_id"="patient_medication"."person_id") AND ("patient"."practice_id"="patient_medication"."practice_id")) INNER JOIN "fdb_medication" "fdb_medication" ON "patient_medication"."ndc_id"="fdb_medication"."ndc_id"

SELECT "patient_allergy"."allergy_comment", "patient_allergy"."rxn_desc", "patient_allergy"."person_id", "allergy_mstr"."description", "patient_allergy"."allergy_type", "patient_allergy"."allergy_type_id", "allergy_type_mstr"."allergy_type_id", "allergy_mstr"."allergy_type_id", "patient_allergy"."practice_id" FROM ("NGDemo"."dbo"."patient_allergy" "patient_allergy" INNER JOIN "NGDemo"."dbo"."allergy_mstr" "allergy_mstr" ON "patient_allergy"."allergy_id"="allergy_mstr"."allergy_id") INNER JOIN "NGDemo"."dbo"."allergy_type_mstr" "allergy_type_mstr" ON ("patient_allergy"."allergy_type_id"="allergy_type_mstr"."allergy_type_id") AND ("allergy_mstr"."allergy_type_id"="allergy_type_mstr"."allergy_type_id") WHERE "patient_allergy"."practice_id"='0002' AND "allergy_mstr"."allergy_type_id"="allergy_type_mstr"."allergy_type_id" AND "patient_allergy"."allergy_type_id"="allergy_type_mstr"."allergy_type_id" AND "patient_allergy"."person_id"='86A835D1-F85C-4EEB-82CC-43866414F490' ORDER BY "patient_allergy"."person_id"

Could you re-evaluate my code in the original post? I have to be missing something here.

Re: ReportDocument.ReadRecords() does not query for subreport.

former_member11696 — Mon, 26 Oct 2020 14:14:38 GMT

Hi Trent,

Cool, so we know it's connected so the one thing that would cause the no query is in the Report Options is the Suppress Printing if no Records.

Using a report With saved data could cause this to happen or what ever parameters you are using are not being passed and the subreports have no data.

Enable CRLogger, run the report in CRD and then again in your app and see what the difference is.

Don

PS - use Google and search for crlogger, it'll find a KBA on how to use it.

Re: ReportDocument.ReadRecords() does not query for subreport.

former_member11696 — Wed, 28 Oct 2020 14:35:28 GMT

Bad on is using the Legacy provider=SQLOLEDB

The working one is missing the beginning of the log so I can see what CR is trying to use.

Update your MS SQL Client and use the SQL Native 11 client in the report.

You can get it from the SQL 2012 installer or MSI from Microsoft's downloads.

Requires manually updating the Report using Set Location...

Don

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Wed, 28 Oct 2020 20:13:12 GMT

Thanks Don. For some reason, my old account has been rendered useless and I cannot log on. I've created a new one since I haven't gotten a response to my issue from SAP Community Support <sapnetwork@sap.com> in days.

Anyway, I did run the crlogger, and the results for the failed report are here, and the successful one is here

I've noticed that a line in the successful log says "QERowset.cpp 2814 bindToField succeeded: person.other_id_number is using client buffer"

While the same line in the unsuccessful log says "QERowset.cpp 2814 bindToField succeeded: person.other_id_number is not using client buffer".

Is this significant, or is there anything else you can glean from the logs? Any ideas in terms of what I can change to remedy this?

Re: ReportDocument.ReadRecords() does not query for subreport.

Former Member — Wed, 28 Oct 2020 20:13:49 GMT

Excellent! Don, for the Win! I installed SQL Native Client 11 (64bit, windows 10) and used it as a DSN data source, works a treat! For those ever happening across this issue, use these connection properties to set on your tables:

					string odbcname = "<DSN Name>";
					dbAttributes.Collection.Set("DSN", odbcname);
					dbAttributes.Collection.Set("PreQEDatabaseName", odbcname);
					dbAttributes.Collection.Set("PreQEServerName", odbcname);
					dbAttributes.Collection.Set("Trusted_Connection", false);
					dbAttributes.Collection.Set("UseDSNProperties", false);


					cinfo.DatabaseName = odbcname;
					cinfo.ServerName = odbcname;
					cinfo.UserID = "<username>";
					cinfo.Password = "<password>";
					cinfo.Attributes.Collection.Set("Database DLL", "crdb_odbc.dll");
					cinfo.Attributes.Collection.Set("QE_DatabaseName", odbcname);
					cinfo.Attributes.Collection.Set("QE_DatabaseType", "ODBC (RDO)");
					cinfo.Attributes.Collection.Set("QE_LogonProperties", dbAttributes);
					cinfo.Attributes.Collection.Set("QE_ServerDescription", odbcname);
					cinfo.Attributes.Collection.Set("QE_SQLDB", true);
					cinfo.Attributes.Collection.Set("SSO Enabled", false);
					cinfo.LogonProperties = dbAttributes.Collection;