Howto get the SQL query of a report?

[Former Member]

Hello,

is there a possibility to get the SQL query of a report in .NET similar to the SQLQueryString in the RDC interface?

I'm using Crystal Reports 2008 and C#.

Regards,

Ron

[Former Member · Accepted solution]

Hello Ron,

please see [this sample|https://boc.sdn.sap.com/node/5636].

More samples you can find [here|https://boc.sdn.sap.com/dotnet/samples].

Best regards

Falk

[Former Member · Accepted solution]

Hello Falk,

thank you very much for your answer.

Do you know if there is also a way to set the sql statement?

Greetings,

Ron

[Former Member · Accepted solution]

Hello Ron,

You cannot set the SQL Querystring directly. This is the only [workaround|https://boc.sdn.sap.com/node/6262] I have.

Other possibilities would be to set parameters, use the selectionformula or create the report with database fields , table links etc from the scratch at runtime using inproc RAS.

pls see some good samples here :

https://boc.sdn.sap.com/node/6296

https://boc.sdn.sap.com/node/6193

https://boc.sdn.sap.com/node/18919

Best regards

Falk

[Former Member · Accepted solution]

Now that the RDC is no longer supported does anyone know of any plans to introduce the equivelant of SQLQueryString into the .Net SDK for Crystal Reports 2008?

[former_member183750 · Accepted solution]

No such plans. No need to. The idea is to create an ADO .NET dataset off of your SQL query and pass that to the report as:

explicitly

crReportDocument.Database.Tables(0).SetDataSource(dataSet.Tables("NAME_OF_TABLE"))

implicitly

crReportDocument.SetDataSource(dataSet) or crReportDocument.SetDataSource(dataSet.Tables(0))

More info on Crystal Reports and .NET datasets is here:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/401c4455-a31d-2b10-ae96-fa57af5a...

Ludek

[Former Member · Accepted solution]

But thats not how I use things or maybe i don't understand your solution. Maybe you could answer this question which might explain how I don't need the ability to read and set the SQLQueryString

I have created my reports in Crystal Designer which is obviously the best place to create the report.

I have an Application for users which allows then to run these reports.

As part of this application there is a flexible query builder which essentially allows users to easily create a 'WHERE' clause for the query in the report without having to know anything about the joins between tables and all the tables in the sqlquery. They do need to know a little about SQL.

I then simply run the report and add the where clause they created to the end of it to filter the data as they requested. This could mean a report with 10 tables and complex joins as defined in the report but i don't need to worry about that, I simply issue the command

cSQL=Report.SQLQueryString

Report.SQLQueryString=cSQL" where "cFilter

where cFilter holds the where clause they created, and my report is now filtered. I don't wont to use parameters becuase it's too hard to include all fields in the tables as i can't anticipate al the filters users may want to use and i cant use the record selection formula as i would have to create something to parse the SQL where clause into valid Crystal Syntax (unless there is something which already does that)

Also if i change the report joins or fileds through Crystal Designer it's flexible in that I don't need to change any code in my application.

Any Ideas how I can achieve this easily in 2008 .Net SDK?

Thanks

[former_member183750 · Accepted solution]

Your solution would look something like this (the DB connection would obviously depend on your connection method):

''Build a connection string

Dim connectionString As String = ""

connectionString = "Provider=SQLOLEDB;"

connectionString += "Server=vancsdb05;Database=xtreme;"

connectionString += "User ID=vantech;Password=vantech"

''Create and open a connection using the connection string

adoOleDbConnection = New OleDbConnection(connectionString)

''Build a SQL statement to query the datasource

Dim sqlString As String = ""

sqlString = "Select * From credit"

''Retrieve the data using the SQL statement and existing connection

adoOleDbDataAdapter = New OleDbDataAdapter(sqlString, adoOleDbConnection)

''Create a instance of a Dataset

dataSet = New DataSet()

''Fill the dataset with the data retrieved.

''The name of the table 'in the dataset must be the ''same as the table name in the report.

adoOleDbDataAdapter.Fill(dataSet, "credit")

Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument()

crReportDocument.Load("C:\Crystal\CRNET\vbnet_win_adodotnet\vbnet_win_adodotnet\adotest.rpt")

''Pass the populated dataset to the report

crReportDocument.SetDataSource(dataSet)

''Set the viewer to the report object to be previewed.

CrystalReportViewer1.ReportSource = crReportDocument

Ludek

[Former Member · Accepted solution]

I have the exact same problem as John Burrows.

When I open a crystal report and use "Show Sql Query", I see a really lengthy sql query with 15 different fields and four to five tables with few conditions in where clause.

all I have to do is add few more conditions to where clause.

how do i do that?

In your reply you mentioned the following.

''Build a SQL statement to query the datasource

Dim sqlString As String = ""

sqlString = "Select * From credit"

But I don't have the complete sql string. I just have the where clause or something that I can append to the existing one.

How do I retrive the existing query and modify it?

if I could do that, then I have the complete new SQL query, using which i can create a dataset. and set the datasource of report to the newly created dataset.

I don't see any answer in your reply. Please try to understand our question.

[former_member183750 · Accepted solution]

You can retrieve the query, you can not modify it. Only way to "modify" the SQL is to create a .NET dataset off of the query you would have set (if it were possible) and pass that dataset to the report. See this article for more info:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/401c4455-a31d-2b10-ae96-fa57af5a...

Ludek

[Former Member · Accepted solution]

Thanks for the quick response.

But in my current situation, the current sql from crystal report is (I was able to get the SQl query)

SELECT HCP.LNAME, HCP.FNAME, VW_RXTERR.PROD_ID, VW_RXTERR.V2, VW_RXTERR.V3, VW_RXTERR.V4, VW_RXTERR.V5, VW_RXTERR.V1, VW_RXTERR.GEO_ID, VW_RXTERR.V6, VW_RXTERR.V7, VW_RXTERR.V8, VW_RXTERR.V9, VW_RXTERR.V10, VW_RXTERR.V11, VW_RXTERR.V12, VW_RXTERR.V13, VW_RXTERR.V14, VW_RXTERR.V15, VW_RXTERR.V16, VW_RXTERR.V17, VW_RXTERR.V18, VW_RXTERR.V19, VW_RXTERR.V20, VW_RXTERR.V21

FROM DBA.HCPT HCPT,

DBA.HCP HCP,

DBA.VW_RXTERR VW_RXTERR

WHERE HCPT.HCP_ID = HCP.HCP_ID AND

HCP.HCP_ID = VW_RXTERR.GEO_ID AND

VW_RXTERR.GEO_LEVEL = '1' AND

HCPT.STATUS = 'A'

ORDER BY VW_RXTERR.GEO_ID

and the new where clause is

FROM VW_RXTERR, HCP, HCPT WHERE HCPT.HCP_ID = HCP.HCP_ID AND HCPT.TERR_ID = '01MI' AND VW_RXTERR.PROD_ID IN ('0F74') AND VW_RXTERR.MEAS_ID='1' AND VW_RXTERR.PLAN_ID='0'

What I created a new SQL query combining both of them which is like below

string newSqlQuery = SELECT HCP.LNAME, HCP.FNAME, VW_RXTERR.PROD_ID, VW_RXTERR.V2, VW_RXTERR.V3, VW_RXTERR.V4, VW_RXTERR.V5, VW_RXTERR.V1, VW_RXTERR.GEO_ID, VW_RXTERR.V6, VW_RXTERR.V7, VW_RXTERR.V8, VW_RXTERR.V9, VW_RXTERR.V10, VW_RXTERR.V11, VW_RXTERR.V12, VW_RXTERR.V13, VW_RXTERR.V14, VW_RXTERR.V15, VW_RXTERR.V16, VW_RXTERR.V17, VW_RXTERR.V18, VW_RXTERR.V19, VW_RXTERR.V20, VW_RXTERR.V21

FROM VW_RXTERR, HCP, HCPT WHERE HCPT.HCP_ID = HCP.HCP_ID AND

HCP.HCP_ID = VW_RXTERR.GEO_ID AND

VW_RXTERR.GEO_LEVEL = '1' AND

HCPT.STATUS = 'A'

AND HCPT.HCP_ID = HCP.HCP_ID AND HCPT.TERR_ID = '01MI' AND VW_RXTERR.PROD_ID IN ('0F74') AND VW_RXTERR.MEAS_ID='1' AND VW_RXTERR.PLAN_ID='0'

ORDER BY VW_RXTERR.GEO_ID

and then I have a whole bunch of formulas(around 14) that i set

right after I connect to database

if ((strFormulaList!=null) && (strFormulaList.Count>0))

{

foreach (string Formula in strFormulaList)

{

if(!String.IsNullOrEmpty(Formula))

{

int tabPos = Formula.IndexOf('\t');

FormulaName = Formula.Substring(0, tabPos);

FormulaValue = Formula.Substring(tabPos + 1);

_reportDocument.DataDefinition.FormulaFields[FormulaName].Text = FormulaValue;

}

}

}

then

if (!String.IsNullOrEmpty(newSqlQuery))

{

string tableName = "NewSelectSQLTable";

DataSet dataSet = new DataSet();

Utils.ExecuteSQL(newSqlQuery, tableName, ref dataSet);

//Pass the populated dataset to the report

_reportDocument.SetDataSource(dataSet);

}

//Set the CrystalReportViewer report source to the current ReportDocument obj

crystalReportViewer.ReportSource = _reportDocument;

the crystal report window opens but hangs up.

Can you please help me figure out what I am doing wrong?

[former_member183750 · Accepted solution]

Try this. Add a line of code to your project that writes out the dataset to an XML:

Dataset.WriteXml(xmlPath, XmlWriteMode.WriteSchema)

Do this just before you pass the dataset to the report:

//Pass the populated dataset to the report

_reportDocument.SetDataSource(dataSet);

dataSet.WriteXml(xmlPath, XmlWriteMode.WriteSchema);

//Set the CrystalReportViewer report source to the current ReportDocument obj

crystalReportViewer.ReportSource = _reportDocument;

Now, take the report back it up and open the back up in the CR designer. Go to the Database menu and select "Set datasource location". Choose 'create new connection" in the replace with pane. select ADO .NET (XML). Point the report at the xml you just created above, click on the <Update> button. What happens in the CR designer?

Ludek

[Former Member · Accepted solution]

Ludek

I appreciate the solutions you've given but in terms of flexibility and ease of use the ability to modify the SQL expression is easiest. Would you have any idea of what the plans are to transfer this functionality into the .NET SDK are please

Thanks

[Former Member · Accepted solution]

Hi Lavanya,

Would you be able to provide us how you managed to get the SQL query using the 2008 SDK?

When I attempt to use the GetSQLStatement method, I get an error saying:

Fetching SQL statements is not supported for this report.

Regards,

Paul M.

[Former Member · Accepted solution]

Ludek

I was attempting to retrieve the sql string from a report using your suggested method and i get the following error.

Message OLE IDispatch exception code 0 from rptcontrollers.dll: Fetching SQL statements is not supported for this report... (OLE IDISPATCH EXCEPTION CODE 0 FROM RPTCONTROLLERS.DLL: FETCHING SQL STATEMENTS IS NOT SUPPORTED FOR THIS REPORT..)

Would you have any ideas?

Thanks

[Former Member · Accepted solution]

Hi Paul,

did you set all parameters of the report before trying to read the SQL?

The following code works fine for me:

// creating report document
ReportDocument rdReport = new ReportDocument();                    
// load report
rdReport.Load(sReportName, OpenReportMethod.OpenReportByTempCopy);
// set parameters
rdReport.SetParameterValue(0, iDataId);
//stuff to show sql
CrystalDecisions.ReportAppServer.Controllers.RowsetController rsController;
CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument rdClient = rdReport.ReportClientDocument;
CrystalDecisions.ReportAppServer.DataDefModel.ISCRGroupPath rdGroupPath = new CrystalDecisions.ReportAppServer.DataDefModel.GroupPath();
string temp;
string sql;
rsController = rdClient.RowsetController;
// get sql
sql = rsController.GetSQLStatement(rdGroupPath, out temp);
//print sql
Console.WriteLine(sql);

[Former Member · Accepted solution]

Hi Ron,

I was sure all of the parameters were set but to make sure, I removed all bar 1 parameter, and set the value of it accordingly (as in your thread). The current values for the parameter has 1 item, so it's definately set, but I still get the error. Any ideas?

Paul

[Former Member · Accepted solution]

Lvanya

Did you get your scenario to work? As far as i can tell the solution proposed will not work but maybe you worked out something? Your issue is most like mine

Thanks

John

[Former Member · Accepted solution]

I agree, none of the solutions/examples work.

On this line

sql = rsController.GetSQLStatement(rdGroupPath, out temp);

The exception thrown is - "Fetching SQL statements is not supported for this report."

You can however fetch the SQL statements using Crystal9 in VB6 for the same report files.

Any further ideas?

Regards,

Paul

[Former Member]

Paul,

is your report maybe designed with Crystal Reports < version 9?

With reports designed with Crystal Report 8.5 or older I cannot get the sql either. I think retrieving the sql of such old reports worked only up to Crystal's runtime 10.

[Former Member]

Paul

With regard to retrieving the sql staement from a report the solution provided in this thread does work (for me anyway). Maybe you just are'nt setting the parameters first

John

[Former Member]

The best option I have been given is to convert the report to work of a command object and then modify the command object at runtime. This could work fine on new reports but would mean converting existing reports to work off command objects which may be a big ask depending on your scenario (like mine)

[Former Member]

Guys

All the links to samples in this thread seem to fail