Would like to get SQL statments for multiple table - non command crystal report 2020?

[former_member1079770]

Hi,

I need to convert CR 11.5 to CR 2020. We have opened CR 11.5 reports in CR 2020 designer. When we integrate the CR 2020 reports using SP 32 .NET runtime engine. It is not working for multiple table reports, Only working for single table reports. We have looked into it and found that getSQLstatement is failing in multiple table reports and we have seen in the SAP and other forum says to convert the command based object for multiple table reports. If we convert command based then this will be a major work for us need to re-wiring all the fields for command based and need to involve good number of changes into client code side and also we need to educate to customer for converting custom reports. If it is possible to get a SQL statement for multiple table reports without making command object based will make life easier.

The below is the one of the sample code which we have tried and got failed... Can you please make my life easier.

<code>    var reportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
    reportDocument.Load(file);<br>    var controller = reportDocument.ReportClientDocument.RowsetController;<br>    var groupPath = new CrystalDecisions.ReportAppServer.DataDefModel.GroupPath();
    string sqlStatment = String.Empty;
    string commandSql = controller.GetSQLStatement(groupPath, out sqlStatment);<br><br>

[DellSC]

Are you getting an error message or is it just not returning the SQL? If you get an error message, what is it?

-Dell

[former_member1079770]

I m getting the below error and not able to get the SQL if I not convert the report to "Command" for multi-table report

"Fetching SQL statements is not supported for this report"

[former_member11696 · Accepted solution]

Hi James,

As noted we don't Convert multiple Table reports to Commands, to much work to do this in code.

Rather than adding a WHERE clause manually you can use a Record Selection formula to do the same.

See if that works for you, CR will convert the Record Selection formula to a WHERE clause as long as can be.

Don

[former_member1079770]

Can you please share the .NET viewer code. It would be great helpful. Code snippet for core logic will be helpful.Find the attached sample code for your reference. Where the problem occurs... If we want to add where clause to add some filter from .NET code. We need to get SQL query which we are not able to get to update the where clause. Which resulted to convert command based report to get SQL query to update where clause.

samplecode.txt

[ido_millet]

I tested your report in my .NET viewer application and it previews fine.

Please clarify the problem. It doesn't preview in your application?
What are the steps to replicate the problem?

[former_member1079770]

sample-multitable.txt

Please find the attached sample mutiple table report for your reference. In case if it is not working then can you please share the working multi-table sample report. So that I will compare with my report.

My application is 32 bit, so we have installed 32 bit runtime to connect to 64 bit CR 2020 designer. We are using SQL server 2019. Let me know if you require any more details.

Thanks,

James J

[former_member1079770]

sample-multitable.txt

Please find the attached sample mutiple table report for your reference. In case if it is not working then can you please share the working multi-table sample report. So that I will compare with my report.

My application is 32 bit, so we have installed 32 bit runtime to connect to 64 bit CR 2020 designer. We are using SQL server 2019. Let me know if you require any more details.

Thanks,

James J

[former_member11696]

You only get that message if the reports are using a NON SQL based data source.

And you need to be logged onto the Database before CR can get the SQL because the SQL is dynamic, depending on the database depends on what the SQL looks like.

If you can attach one of your reports, rename it to *.txt and I'll run it through my test app.

I need to know what Database and client your are using.

You can also do the same in CR 2020, open the report, log onto your database and then Show SQL Query under the database menu.

If you see the SQL and the Link tab is enable it should work in code also.

CR 2020 is a 64 bit application so you do need a 64 bit Client installed.

And stop suggesting to convert to a Command, it's not needed.