How to get select query for crystal report in c# code

[Former Member]

In C#, (Visual Studio 2008 ) I need to use the native Crystal Reports objects to get the complete SQL statement as it appears when you view it in Crystal Reports.

In Crystal Reports 12.0, Go to "Database > Show SQL Query..." ... That is what I need to get via C# code. I can get bits and pieces of parameter info etc but I just want the whole SQL statement.

How to get this?

[Former Member · Accepted solution]

Hi, Ganesh;

Here is a sample that shows using RAS to retrieve the SQL Query. It was written for Crystal Reports XI R2, so you will have to add the correct references for version 12 (2008).

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f002eadb-d266-2b10-3bb4-825227ce...

Regards,

Jonathan

[Former Member · Accepted solution]

Hi Jonathan,

Is there any way to get same thing without RAS because I am not using RAS so it is throwing exception "The ReportClientDocument property can only be accessed when the report is opened using a Report Application Server."

-

---

Thanks & Regards,

Ganesh

[Former Member · Accepted solution]

Hi Ganesh,

This functionality can not be achived using .Net Sdk's. You need to use RAS sdk.

You can download sample code from[here|https://www.sdn.sap.com/irj/boc/businessobjects-samples]

Have a look to our [DevLibrary|http://devlibrary.businessobjects.com/BusinessObjectsXIR2SP2/en/devsuite.htm]

Regards,

Shweta

[former_member11696 · Accepted solution]

Hi Ganesh,

It's simple to use RAS, include the assemblies and use this to open your report document:

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using CrystalDecisions.ReportAppServer.ClientDoc;

using CrystalDecisions.ReportAppServer.Controllers;

using CrystalDecisions.ReportAppServer.ReportDefModel;

using CrystalDecisions.ReportAppServer.DataSetConversion;

using CrystalDecisions.ReportAppServer.DataDefModel;

CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

ISCDReportClientDocument rptClientDoc;

To open the report use:

object rptName = openFileDialog.FileName;

rpt.Load(rptName.ToString());

rptClientDoc = rpt.ReportClientDocument;

Then to get the SQL set the log on info:

rptClientDoc.DatabaseController.LogonEx("10.50.212.77,1433", "xtreme", "sa", "password");

GroupPath gp = new GroupPath();

string tmp = String.Empty;

rptClientDoc.RowsetController.GetSQLStatement(gp, out tmp);

MessageBox.Show(tmp, "Data Source Set and SQL Statement", MessageBoxButtons.OK, MessageBoxIcon.Information);

Thank you

Don