How to use correlated queries with record selection formula in crystal report 2016.

[former_member993074]

Hi Team,

I am migrating some report modules from crystal report 8.5 to 2016, my codebase is using MFC C++ and using COM object for crystal report 8.5 but after it was obsoleted so we are preparing dynamic 'where' clause in C++ which may contain multiple correlated subqueries based on some dynamic conditions and processing it further in C# using the record selection formula.

Previously as a solution, I was executing subqueries(uncorrelated) in my C++ code and appending their result further in the where clause but now I have correlated queries that depend on the outer query so I cannot do this.

I could not found any possible solution other than hardcode the whole main query and then execute that correlated query and then append their result in the 'where' clause.

Do we have any approach to handle correlated queries with the record selection formula?

Note: My correlated queries are dynamic and may return multiple values.

Looking forward to your quick response. Thanks in advance!

[former_member11696 · Accepted solution]

Hello,

The only SDK available now is CR for VS .NET or Java.

CR for VS (.NET) here:

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

CR for Eclipse ( Java) here:

https://wiki.scn.sap.com/wiki/display/BOBJ/SAP+Crystal+Reports+version+for+Eclipse+-+Downloads

As of CR 9 we no longer allow users to edit the SQL we send to the DB.

You can rewrite the report and use a Command to use your own SQL statement and then be able to edit it using .NET C# API's.

See the .NET WIKI for more info on using the SDK reference Guides.

I recommend using C#, you can use C++ but not many sample available.

Don

[former_member993074 · Accepted solution]

Hi Don,
Actually, I am already using C# sdk but my problem is related to correlated queries since I am creating where clause in my CPP file and then passing this dynamic where clause further at C# level to be appended with main query and then finally passing it to crystal reports using record selection formula,

But now the problem is how to pass or execute these correlated queries because record selection formula does not allow those.

[former_member11696]

As I said, you can not edit the SQL CR passes to the DB Server directly anymore.

Options are use record selection formula to add the filtering or convert your reports to use Commands and then you can edit the Query in code.

Create a new report, log onto your server, Click on the Command in the connection and paste in your original query, drop the fields onto your report.

Now you can use code to edit the query set to the server.

Don