Question Re: Programmatic editing of sql in Crystal Report .rpt file in Technology Q&A

Programmatic editing of sql in Crystal Report .rpt file

peggyshupp — Thu, 18 Jul 2024 22:03:13 GMT

What development tools do I need to be able to read and modify the SQL in Crystal Report 2020 .rpt files. I've looked at a variety of api's. SAP Crystal Reports JavaScript API, Business Intelligence Platform Java SDK, SAP Crystal Reports for Visual .NET SDK ... From my understanding, I need to use the ReportClientDocument Object Model which only comes with report server in the Crystal Reports for Visual Studio .NET SDK. "You install and gain direct access to the ReportClientDocument model only with an upgraded license to the unmanaged RAS server." I don't mind getting report server if that is what I need, just want to make sure that is my only "SAP" sanctioned options. Thanks for any help.

Re: Programmatic editing of sql in Crystal Report .rpt file

ido_millet — Fri, 19 Jul 2024 01:39:22 GMT

Any (non-ancient) Crystal for Visual Studio provides the RAS (RCD) API. No need for Crystal server.

What aspect of the SQL do you need to change? If it's just record filtering, there is a simpler way.

Re: Programmatic editing of sql in Crystal Report .rpt file

DonWilliams — Fri, 19 Jul 2024 04:00:15 GMT

Hi Peggy,

You cannot modify the SQL in the Report or subreport, CR hasn't be able to do this for quite some time, it used to be able to but it caused all sorts of problems so the function was removed. To many objects to make changes to for complex reports with complex data structures. So what Product did was add the Command Object, basically add your own SQL and then you can edit it as required, as long as it didn't break the report.

And before you ask there is no way to change the data source from tables to a command object, well maybe bit to complex, someone may be able to describe how to, without your data source it's too much to explain.

Here's the link for CR for VS:

https://help.sap.com/docs/SUPPORT_CONTENT/crystalreports/3354091173.html

On that page is a test app, Parameter one, I created to get more info from the report like the SQL used, note you need to log onto the DB first because CR dynamically generates the SQL based on the DB connection.

How to use the new CR 2020 export to Microsoft Excel(XLXS) in Visual Studio .NET code (new)

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

Printing Crystal Reports in .NET

As Ido indicated depending on what you need to change you can used the Record Selection formula to alter the filtering in the WHERE clause but you cannot change Tables or other parts.

To change your report from direct table connection to a Command Object Open 2 instances of CR Designer, open the original report and log in and click on Database... Show SQL Query and copy it.

In the second instance of CRD create a new report, log into your DB and select Command, paste in your SQL from the original.

Now you can drop the fields onto the report and depending on the formulae and other objects you may be able to copy and paste then onto the new report.