Crystal Reports for Eclipse Version 2 - Programmatically Create Reports off of XML or Web Services Data Sources

[ted_ueda]

<p>+I work for SAP Business Objects in Technical Customer Assurance.  My speciality is the Software Development Kits (SDKs) that we provide with our Business Intelligence products - BusinessObjects Enterprise, Web Intelligence, Desktop Intelligence, Crystal Reports and Crystal Xcelsius.  </p><p>In my blog, I discuss subjects that I personally find interesting - little known or not-well-documented corners of the SDK, new functionality or new SDKs, or interesting issues that I've come across in a SAP Incident or SAP Developer Network forums.  </p><p>You're more than welcome to suggest any topic (SAP Business Objects SDK related, of course...) that you'd like me to discuss - I have a dozen or so items on my blog to-do list, but I'm always on the hunt for anything interesting with our SDKs.+   </p>

The new Crystal Reports for Eclipse Version 2 brings a feature-rich report design tool to the Eclipse Platform .   To utilize the Platform enviroment, the report designer uses the Eclipse Database Explorer and SQL Views to represent database connections - you can drag-and-drop Tables and Stored Procedures that you define in the Database Explorer onto the Data View of the designer to insert it into a report.

Unfortunately, there's an impedance mismatch between the Web Services and XML data sources as presented by the Database Explorer and Crystal Reports rpt format.  You can't drag-and-drop these data sources into a report at this time.

Crystal Reports has its own representation of XML and Web Services connectivity, and it's something that can be used cross-platform: via the Crystal Reports Java (CRJ) SDK, the Crystal Reports .NET (CR.NET) SDK, the Report Application Server (RAS), and even Crystal Reports Server or BusinessObjects Enterprise - scalable from tens to thousands of users!

If you have a copy of Crystal Reports 2008 , then you can fully design a report against XML or Web Services data sources and take advantages of the [features | http://www.businessobjects.com/jump/xi/gettingstarted/whatsnew_cr2008.pdf] available with the CR 2008 designer.

Coders without a copy of CR 2008 who require reporting off of XML or Web Services data sources, however, aren't left out in the cold.

You can use the new Crystal Reports Java report creation/modification API features to programmatically create reports off of these data sources!

In this blog entry, I'll present code snippets that show you how to create reports off of XML - either from HTTP or local files - and Web Services, including Query as a Web Service (QaaWS) and RSS 2.0.

h5. Sample Codes

Here's a brief outline of the workflow:  (1) connect to the Crystal Reports Java report engine, (2) define XML or Web Services connection info, (3) define the data source as a Table or Procedure, (4) add to the report and save.

Once you programmatically create a report using CRJ, then you can open the report in the CR4E embedded designer, and use the table or procedure data fields in the report - you need not go through the Eclipse Database Explorer to access the connection.

Since the width restrictions current with SCN limits the column size and obscures the formating, I recommend copying the content of each sample to a text editor for ease-of-reading.

h6. Crystal Reports off of local XML data file

First sample creates a report off of a XML file, where the schema is defined in a XSD file:

bq. Create Crystal Report against customer.xml and customer.xsd
package com.businessobjects.samples;import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;import com.crystaldecisions.sdk.occa.report.application.ReportSaveAsOptions;import com.crystaldecisions.sdk.occa.report.data.ConnectionInfo;import com.crystaldecisions.sdk.occa.report.data.Table;import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;public class CreateLocalXML {          /*      * Create a Crystal Report reporting off of a local XML file, where the schema      * is defined in a XSD file.      /     public static void main(String[] args) throws Exception {          ReportClientDocument reportClientDocument;          ConnectionInfo connectionInfo;          PropertyBag propertyBag;                    /           * Connect to the Java Print Engine and create a new document.           /                    reportClientDocument = new ReportClientDocument();          reportClientDocument.setReportAppServer(ReportClientDocument.inprocConnectionString);          reportClientDocument.newDocument();                    /           * Define connection to the XML and XSD files.           /          propertyBag = new PropertyBag();          propertyBag.put("Local XML File", "H:
XMLData
Xtreme
customer.xml");          propertyBag.put("Local Schema File", "H:
XMLData
Xtreme
customer.xsd");          propertyBag.put("Convert Multivalue to Table", Boolean.FALSE);          propertyBag.put("Database DLL", "crdb_xml.dll");          connectionInfo = new ConnectionInfo();          connectionInfo.setAttributes(propertyBag);                    /           * Specify the dataset in the XML as a Table, and add to the report.           /                    Table table = new Table();          table.setConnectionInfo(connectionInfo);          table.setName("dataroot/Customer_Query");          table.setAlias("dataroot/Customer_Query");                    reportClientDocument.getDatabaseController().addTable(table, null);                    /           * Save report to file C:local_xml.rpt           */               reportClientDocument.saveAs("local_xml.rpt", "C:
", ReportSaveAsOptions._overwriteExisting);          reportClientDocument.close();               }}

You can find the copy of the XML and XSD here: customer.xml customer.xsd .

h6. Crystal Reports off of HTTP XML data URL

Instead of reporting off of a local XML file, let's report off of the HTTP URL where the XML and XSD are found:

bq. Create Crystal Report  from HTTP XML data source
package com.businessobjects.samples;import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;import com.crystaldecisions.sdk.occa.report.application.ReportSaveAsOptions;import com.crystaldecisions.sdk.occa.report.data.ConnectionInfo;import com.crystaldecisions.sdk.occa.report.data.FieldDisplayNameType;import com.crystaldecisions.sdk.occa.report.data.FieldLink;import com.crystaldecisions.sdk.occa.report.data.FieldLinkOperator;import com.crystaldecisions.sdk.occa.report.data.ITable;import com.crystaldecisions.sdk.occa.report.data.Procedure;import com.crystaldecisions.sdk.occa.report.data.TableJoin;import com.crystaldecisions.sdk.occa.report.data.TableJoinEnforced;import com.crystaldecisions.sdk.occa.report.data.TableJoinOperator;import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;public class CreateHttpXML {          /*      * Create a Crystal Report reporting off two XML data sources located at HTTP URLs.       * Link the two using a TableJoin.      /     public static void main(String[] args) throws Exception {          ReportClientDocument reportClientDocument;          ConnectionInfo connectionInfo;          PropertyBag propertyBag;                    /           * Connect to the Java Print Engine and create a new document.           /          reportClientDocument = new ReportClientDocument();          reportClientDocument.setReportAppServer(ReportClientDocument.inprocConnectionString);          reportClientDocument.newDocument();                    /           * Define connection to the Orders XML table found on the Business Objects web site.             * The XML data source is modeled as a stored procedure (Procedure).           /          propertyBag = new PropertyBag();          propertyBag.put("Http(s) XML URL", "http://resources.businessobjects.com/support/downloads/samples/cr/customer_db/orders.xml");          propertyBag.put("Http(s) Schema URL", "http://resources.businessobjects.com/support/downloads/samples/cr/customer_db/orders.xsd");          propertyBag.put("UserID Http(s) XML", "");          propertyBag.put("UserID Http(s) Schema", "");          propertyBag.put("Convert Multivalue to Table", Boolean.FALSE);          propertyBag.put("Database DLL", "crdb_xml.dll");          connectionInfo = new ConnectionInfo();          connectionInfo.setAttributes(propertyBag);                    Procedure orders = new Procedure();          orders.setConnectionInfo(connectionInfo);          orders.setName("dataroot/Orders_Query");          orders.setAlias("Orders");          /           * Define connection to the Customer XML table found on the Business Objects web site.             * The XML data source is modeled as a stored procedure (Procedure).           /          propertyBag = new PropertyBag();          propertyBag.put("Http(s) XML URL", "http://resources.businessobjects.com/support/downloads/samples/cr/customer_db/customer.xml");          propertyBag.put("Http(s) Schema URL", "http://resources.businessobjects.com/support/downloads/samples/cr/customer_db/customer.xsd");          propertyBag.put("UserID Http(s) XML", "");          propertyBag.put("UserID Http(s) Schema", "");          propertyBag.put("Convert Multivalue to Table", Boolean.FALSE);          propertyBag.put("Database DLL", "crdb_xml.dll");          connectionInfo = new ConnectionInfo();          connectionInfo.setAttributes(propertyBag);                    Procedure customer = new Procedure();          customer.setConnectionInfo(connectionInfo);          customer.setName("dataroot/Customer_Query");          customer.setAlias("Customer");                    /           * Add the Orders and Customer data sources to the report, then retrieve them back            * to define joins between the two.  Note that the joining is done internally in the           * Java Print Engine - both data sources will be retrieved independently.           /                    reportClientDocument.getDatabaseController().addTable(orders, null);          reportClientDocument.getDatabaseController().addTable(customer, null);                    for(ITable table : reportClientDocument.getDatabaseController().getDatabase().getTables()) {               if("Orders".equals(table.getAlias())) {                    orders = (Procedure) table;               } else if ("Customer".equals(table.getAlias())){                    customer = (Procedure) table;               }          }                    /           * Link the two sources by the Customer_ID field.           /                    FieldLink fieldLink = new FieldLink();          fieldLink.setFromField(orders.getDataFields().findField("Customer_ID", FieldDisplayNameType.fieldName, null));          fieldLink.setToField(customer.getDataFields().findField("Customer_ID", FieldDisplayNameType.fieldName, null));          fieldLink.setLinkOperator(FieldLinkOperator.equalLink);                    TableJoin tableJoin = new TableJoin();          tableJoin.getFieldLinks().add(fieldLink);          tableJoin.setSourceTableAlias(orders.getAlias());          tableJoin.setTargetTableAlias(customer.getAlias());          tableJoin.setEnforceJoin(TableJoinEnforced.notEnforced);          tableJoin.setJoinOperator(TableJoinOperator.innerJoin);                   reportClientDocument.getDatabaseController().addTableJoin(tableJoin);                    /           * Save to file C:http_xml.rpt           */                    reportClientDocument.saveAs("http_xml.rpt", "C:
", ReportSaveAsOptions._overwriteExisting);          reportClientDocument.close();               }}

The code defines connections to customer.xml customer.xsd  and orders.xml  [orders.xsd | http://resources.businessobjects.com/support/downloads/samples/cr/customer_db/orders.xsd], specifies data sources as Procedure objects, then creates a TableJoin to link the two.

h6. Crystal Reports off of local XML file with inline schema

The following code creates a report off of a local XML file, where the XML contains the schema definition in-line:

bq. Create Crystal Report against XML file with inline schema
package com.businessobjects.samples;import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;import com.crystaldecisions.sdk.occa.report.application.ReportSaveAsOptions;import com.crystaldecisions.sdk.occa.report.data.ConnectionInfo;import com.crystaldecisions.sdk.occa.report.data.Table;import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;public class CreateXMLInlineSchemaReport {          /*       * Create a Crystal Report reporting off of a XML data source with schema definition inlined.      /     public static void main(String[] args) throws Exception {          ReportClientDocument reportClientDocument;          ConnectionInfo connectionInfo;          PropertyBag propertyBag;                    /           * Connect to the Java Print Engine and create a new report.           /          reportClientDocument = new ReportClientDocument();          reportClientDocument.setReportAppServer(ReportClientDocument.inprocConnectionString);          reportClientDocument.newDocument();                    /           * Define connection properties to the XML file.           /          propertyBag = new PropertyBag();          propertyBag.put("Local XML File", "C:
XMLData
inline_schema.xml");          propertyBag.put("Convert Multivalue to Table", Boolean.FALSE);          propertyBag.put("Database DLL", "crdb_xml.dll");          connectionInfo = new ConnectionInfo();          connectionInfo.setAttributes(propertyBag);                    /           * Specify the data source modeled as a Table           /          Table table = new Table();          table.setConnectionInfo(connectionInfo);          table.setName("root/Person");          table.setAlias("root/Person");                    reportClientDocument.getDatabaseController().addTable(table, null);                    /           * Save the report to file C:inline_xml_schema.rpt           */          reportClientDocument.saveAs("inline_xml_schema.rpt", "C:
", ReportSaveAsOptions._overwriteExisting);          reportClientDocument.close();               }}

Here's the inline_schema.xml file that the above report utilizes:

bq. XML with inline schemaHere's a sample for creating a Crystal Report against a RSS 2.0 feed - the feed is from the SAP Crystal Reports, version for Eclipse:</p>bq. Create Crystal Reports against a RSS 2.0 feed
package com.businessobjects.samples;import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;import com.crystaldecisions.sdk.occa.report.application.ReportSaveAsOptions;import com.crystaldecisions.sdk.occa.report.data.ConnectionInfo;import com.crystaldecisions.sdk.occa.report.data.Table;import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;public class CreateRSS {          /*      * Create a Crystal Report off of RSS 2.0 data source.  Here the RSS is for the SCN       * Crystal Reports - Java Development forum.       /     public static void main(String[] args) throws Exception {          ReportClientDocument reportClientDocument;          ConnectionInfo connectionInfo;          PropertyBag propertyBag;          Table table;                    /           * Connect to the Java Print Engine and create a new document.           /                         reportClientDocument = new ReportClientDocument();          reportClientDocument.setReportAppServer(ReportClientDocument.inprocConnectionString);          reportClientDocument.newDocument();                    /           * Define the RSS data source connection - we use the TheArchitect RSS 2.0 XSD definition           * to define the schema.           /          propertyBag = new PropertyBag();          propertyBag.put("forumID", "315#");  // Forum for Crystal Reports - Java Development          propertyBag.put("Server Type", "XML and Web Services");          propertyBag.put("Http(s) XML URL", "https://forums.sdn.sap.com/rss/rssmessages.jspa?forumID=315#");          propertyBag.put("Http(s) Schema URL", "http://www.thearchitect.co.uk/schemas/rss-2_0.xsd");          propertyBag.put("UserID Http(s) XML", "");          propertyBag.put("UserID Http(s) Schema", "");          propertyBag.put("Convert Mulitivalue to Table", "0");          propertyBag.put("Database DLL", "crdb_xml.dll");          connectionInfo = new ConnectionInfo();          connectionInfo.setAttributes(propertyBag);                    /           * Create Table for the RSS elements and add to the report.           /          table = new Table();          table.setName("rss/channel/item");          table.setAlias("rss/channel/item");          table.setConnectionInfo(connectionInfo);                    reportClientDocument.getDatabaseController().addTable(table, null);                    /           * Save report to file C:
ss.rpt           */          reportClientDocument.saveAs("rss.rpt", "C:
", ReportSaveAsOptions._overwriteExisting);          reportClientDocument.close();               }}

The above code uses the RSS 2.0 schema XSD as created by TheArchitect.co.uk .

h6. Crystal Reports off of a Web Service

Query as a Web Service is a easy-to-use tool for exposing data from a Business Objects Enterprise Universe Semantica Layer as a Web Service XML Schema.  The following sample shows how to create a Crystal Report reporting off of a Web Service:

bq. Create Crystal Reports against a Web Services - QaaWS
package com.businessobjects.samples;import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;import com.crystaldecisions.sdk.occa.report.application.ReportSaveAsOptions;import com.crystaldecisions.sdk.occa.report.data.ConnectionInfo;import com.crystaldecisions.sdk.occa.report.data.FieldValueType;import com.crystaldecisions.sdk.occa.report.data.Fields;import com.crystaldecisions.sdk.occa.report.data.IParameterField;import com.crystaldecisions.sdk.occa.report.data.ParameterField;import com.crystaldecisions.sdk.occa.report.data.Procedure;import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;public class CreateQaaWSReport {          /*      * Create a Crystal Report reporting off of a Web Service.  Here a Query as a Web Service      * data source is utilized.      /     public static void main(String[] args) throws Exception {          ReportClientDocument reportClientDocument;          ConnectionInfo connectionInfo;          PropertyBag propertyBag;                    /           * Connect to the Java Print Engine and create a new document.           /                         reportClientDocument = new ReportClientDocument();          reportClientDocument.setReportAppServer(ReportClientDocument.inprocConnectionString);          reportClientDocument.newDocument();                    /           * Define connections to the Web Service data source.           /          propertyBag = new PropertyBag();          propertyBag.put("Http WebService URL", "http://tueda-bexi3:8080/dswsbobje/qaawsservices/?wsdl&cuid=Ab1TpcYIzKVAvfWM8GBRUy0");          propertyBag.put("List Services", "QaaWSTest1");          propertyBag.put("List Ports", "QueryAsAServiceSoap");          propertyBag.put("List Methods", "runQueryAsAService");          propertyBag.put("WS-Security File Location", "");          propertyBag.put("Web Service Authentication User", "");          propertyBag.put("UserID Http(s) WebService", "");          propertyBag.put("Convert Multivalue to Table", Boolean.FALSE);          propertyBag.put("Database DLL", "crdb_xml.dll");          connectionInfo = new ConnectionInfo();          connectionInfo.setAttributes(propertyBag);                    /           * QaaWS is parametrized with logon fields - it's modeled as a stored procedure           * (Procedure).  Create the Procedure and define the parameter fields.           /          Procedure procedure = new Procedure();          procedure.setConnectionInfo(connectionInfo);          procedure.setName("runQueryAsAServiceResponse/table/row");          procedure.setAlias("runQueryAsAServiceResponse/table/row");          // Web Services session ID          Fields<IParameterField> parameterFields = new Fields<IParameterField>();          ParameterField param1 = new ParameterField();          param1.setName("request_header.QaaWSHeader.sessionID");          param1.setId("request_header.QaaWSHeader.sessionID");          param1.setType(FieldValueType.stringField);          parameterFields.add(param1);          // BusinessObjects Enterprise Session - serialized.          param1 = new ParameterField();            param1.setName("request_header.QaaWSHeader.serializedSession");          param1.setId("request_header.QaaWSHeader.serializedSession");          param1.setType(FieldValueType.stringField);          parameterFields.add(param1);                    // BusinessObjects Enterprise User name          param1 = new ParameterField();          param1.setName("parameters.runQueryAsAService.login");          param1.setId("parameters.runQueryAsAService.login");          param1.setType(FieldValueType.stringField);          parameterFields.add(param1);          // BusinessObjects Enterprise User password          param1 = new ParameterField();          param1.setName("parameters.runQueryAsAService.password");          param1.setId("parameters.runQueryAsAService.password");          param1.setType(FieldValueType.stringField);          parameterFields.add(param1);          procedure.setParameters(parameterFields);                    reportClientDocument.getDatabaseController().addTable(procedure, null);                    /           * Save report to file web_services_qaaws.rpt           */          reportClientDocument.saveAs("web_services_qaaws.rpt", "C:
", ReportSaveAsOptions._overwriteExisting);          reportClientDocument.close();               }}

The QaaWS parametrizes Enterprise authentication credentials, and this is modeled within the connectivity as stored procedure parametrizations.

h6. Crystal Reports off of a Web Services as a local WSDL File

You can specify Web Services connectivity to a local WSDL file, to allow for redirection to a different Web Services URL:

bq. Create Crystal Reports against a local WSDL file
package com.businessobjects.samples;import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;import com.crystaldecisions.sdk.occa.report.application.ReportSaveAsOptions;import com.crystaldecisions.sdk.occa.report.data.ConnectionInfo;import com.crystaldecisions.sdk.occa.report.data.FieldValueType;import com.crystaldecisions.sdk.occa.report.data.Fields;import com.crystaldecisions.sdk.occa.report.data.IParameterField;import com.crystaldecisions.sdk.occa.report.data.ParameterField;import com.crystaldecisions.sdk.occa.report.data.Procedure;import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;public class CreateQaaWSLocalWSDL {          public static void main(String[] args) throws Exception {          ReportClientDocument reportClientDocument;          ConnectionInfo connectionInfo;          PropertyBag propertyBag;                    /*           * Connect to the Java Print Engine and create a new document.           /               reportClientDocument = new ReportClientDocument();          reportClientDocument.setReportAppServer(ReportClientDocument.inprocConnectionString);          reportClientDocument.newDocument();                    /           * Define connections to the Web Service data source WSDL file.           /               propertyBag = new PropertyBag();          propertyBag.put("Local WebService File", "H:
XMLData
QaaWS
QaaWSTest1.wsdl");          propertyBag.put("List Services", "QaaWSTest1");          propertyBag.put("List Ports", "QueryAsAServiceSoap");          propertyBag.put("List Methods", "runQueryAsAService");          propertyBag.put("WS-Security File Location", "");          propertyBag.put("Web Service Authentication User", "");          propertyBag.put("Convert Multivalue to Table", Boolean.FALSE);          propertyBag.put("Database DLL", "crdb_xml.dll");          connectionInfo = new ConnectionInfo();          connectionInfo.setAttributes(propertyBag);          /           * QaaWS is parametrized with logon fields - it's modeled as a stored procedure           * (Procedure).  Create the Procedure and define the parameter fields.           /          Procedure procedure = new Procedure();          procedure.setConnectionInfo(connectionInfo);          procedure.setName("runQueryAsAServiceResponse/table/row");          procedure.setAlias("runQueryAsAServiceResponse/table/row");          // Web Services session ID          Fields<IParameterField> parameterFields = new Fields<IParameterField>();          ParameterField param1 = new ParameterField();          param1.setName("request_header.QaaWSHeader.sessionID");          param1.setId("request_header.QaaWSHeader.sessionID");          param1.setType(FieldValueType.stringField);          parameterFields.add(param1);          // BusinessObjects Enterprise Session - serialized.          param1 = new ParameterField();          param1.setName("request_header.QaaWSHeader.serializedSession");          param1.setId("request_header.QaaWSHeader.serializedSession");          param1.setType(FieldValueType.stringField);          parameterFields.add(param1);                    // BusinessObjects Enterprise User name          param1 = new ParameterField();          param1.setName("parameters.runQueryAsAService.login");          param1.setId("parameters.runQueryAsAService.login");          param1.setType(FieldValueType.stringField);          parameterFields.add(param1);                    // BusinessObjects Enterprise User password          param1 = new ParameterField();          param1.setName("parameters.runQueryAsAService.password");          param1.setId("parameters.runQueryAsAService.password");          param1.setType(FieldValueType.stringField);          parameterFields.add(param1);          procedure.setParameters(parameterFields);                    reportClientDocument.getDatabaseController().addTable(procedure, null);                    /           * Save report to file C:web_services_local_wsdl.rpt           */          reportClientDocument.saveAs("web_services_local_wsdl.rpt", "C:
", ReportSaveAsOptions._overwriteExisting);          reportClientDocument.close();               }}

For reference, here's the WSDL as generated by the QaaWS:

bq. Query as a Web Service generated WSDL(Postscript: for more sample codes - Shawn's report creation/modification samples included - see my blog here: Crystal Reports for Eclipse 2.0 - Sample Codes Package)