Is 'Show SQL' feature accessible programmatically at runtime?

[Former Member]

We were excited to see that the new version of Crystal Reports for Eclipse 2.0 was recently posted to your download site. Since we have a big investment in existing reports developed in Crystal Reports XI already used in our VB6 projects, we were hoping to leverage these same .rpt files, eliminating any need for a rewrite, and integrate them with our ported Java/JSP/JSF/Tomcat Web version of the same product. One feature we leverage heavily from VB6 is the u2018Show SQLu2019 feature that allows us to dynamically change the SQL Statement u2018on the flyu2019 programmatically from VB6 code. For Example:

Global myReport As CRAXDRT.Report

mySQLString = myReport.SQLQueryString

'... Manipulate 'From Clause' in mySQLString

myReport.SQLQueryString = mySQLString

I saw somewhere that the u2018Show SQLu2019 feature was not supported in the 1.0 version, however I was wondering if u2018Show Sqlu2019 is now supported in the new version of Crystal Reports for Eclipse 2.0?

If not, can you suggest any workaround that may allow us to programmatically manage the SQL statement u2018on the flyu2019 at runtime rather than design time. Without this ability, the value of the Crystal Reports for Eclipse plugin is substantially diminished? Or maybe, we're missing the boat... is there a better way to perform filtering on the resulting recordset (?) including swapping out table names in the 'From' clause?

Thank you in advance...

[ted_ueda · Accepted solution]

The direct modification of SQL statements was last with the RDC, and not with current SDKs.

Sincerely,

Ted Ueda

[Former Member · Accepted solution]

Thank you for your response.

Ok, I accept that the CR4E API does not expose the SQL Statement embedded in the .rpt to allow for modification at runtime. So we're looking for a workaround. I've begun to investigate our next option that attempts to add/modify record filters at runtime. I've tried implementing the following code:

IDataDefinition dataDefinition = reportClientDoc.getDataDefController().getDataDefinition();

But to our regret, the DataDefinition object appears only to expose the getParameterFields() method and does not appear to publish a method that would allow us to manipulate the record filters at runtime.

Has the recently released 'Crystal Reports for Eclipse Version 2' API been updated to include report modification/creation functionality to the JRC that would allow us to change the report definition via code at runtime?

[ted_ueda · Accepted solution]

CR4E Version 2 has the report creation/modification API - instead of com.crystaldecisions.reports.sdk.IDataDefinition, you'd be getting com.crystaldecisions.sdk.occa.report.data.IDataDefinition.

Sincerely,

Ted Ueda

[Former Member · Accepted solution]

Thanks again for your reply... sounds very encouraging, however I'm trying to modify an existing record filter (or add a new one), however the following code fails since the .getRecordFilter() and .getRecordFilterController() methods are not available. Can you direct me towards any sample code that can show me how to implement this in CR4E Version 2? Is this possible to do using the JRC in CR4E Version 2?

<%//Crystal Java Reporting Component (JRC) imports.%>
<%@page import="com.crystaldecisions.reports.sdk.*" %>
<%@page import="com.crystaldecisions.sdk.occa.report.lib.*" %>
<%@page import="com.crystaldecisions.sdk.occa.report.data.*" %>

ReportClientDocument reportClientDoc = new ReportClientDocument();
reportClientDoc.open(REPORT_NAME, 0);

String freeEditingFilter = "OPERATOR.OP_NUMBER = 3";
IFilter myFilter = reportClientDoc.getDataDefController().getDataDefinition().getRecordFilter();
myFilter.setFreeEditingText(freeEditingFilter);
reportClientDoc.getDataDefController().getRecordFilterController().modify(myFilter);

[ted_ueda · Accepted solution]

Replace

com.crystaldecisions.reports.sdk.*"

with

com.crystaldecisions.sdk.occa.report.application.*

You're still getting the old JRC interface.

[Crystal Reports for Eclipse Version 2 - Migrating From Java Reporting Component SDK to Crystal Reports Java SDK|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/12805] [original link is broken] [original link is broken] [original link is broken];

Sincerely,

Ted Ueda

[Former Member · Accepted solution]

Ted,

That solved my problem. After replacing the old JRC jar files with the new CRJ SDK jar files everything worked exactly as advertised. CR4E v2 holds tremendous promise and substantially eases the burden of developing a Java Web Application delivered with an embedded reporting engine. Although we regret not having the ability to manipulate the SQL Statement embedded in the .rpt (Show SQL) previously available in the RDC, I think having the ability to dynamically modify the report components, including 'record filters', formulas, parameters, tables, fields, etc programmatically at runtime should provide us with a very viable workaround. These new features in the CRJ should allow us to migrate hundreds of reports previously designed using the RDC in VB to a Java Web application with mimimum tweaking.

Thank you for taking the time to help.