WBR - Custom web-services client integrations

ejaeger · ‎Nov 09, 2012

Hello everyone,

Roy Clarke created this excellent walkthrough on how to integrate Windchill Business Reporting custom reports with other clients. This one is specific to PDFs but should be useful for anyone looking to utilize WBR reports outside of the Windchill UI. Understand that this is considered a customization, but definitely worth sharing with everyone.

Thanks Roy!!!

-Eric

Cognos via Web Services

by Clarke, Roy on 11/9/2012 10:15 AM

We had a recent benchmark scenario involving Business Intelligence / Business Reporting, for which Cognos provided a natural fit (business reports design, data source integration etc). The last aspect of the benchmark scenario was to test the ability to define custom reports and custom web-services client integrations to integrate such reports. The customer wanted to see the vendors demonstrate development of this from the "ground-up" rather than just show an existing OOTB capability (e.g. Windchill Cognos reports exposed to SharePoint). Writing this up to share the experience and see if anyone has better ideas in this area …

The benchmark scenario was to define and execute a custom BOM report; this lead us straight away to use of Info*Engine as the data source provider to the Cognos report as Report Builder is not really geared towards such multi-level recursive reports. This is a relatively straightforward exercise using the Query-Tree webject in the Windchill Adapter, so I won't focus on this aspect further here. The end result was a conventional tabular Cognos report accessible from the Windchill reports menu that takes a single parameter – object_ref – to define the top level WTPart.

Now onto the subject of exposing this report as a Web Service from Windchill. The idea here was to utilise Windchill Info*Engine Web Services tooling to expose the web service using the Task Delegate administrator. The Task Delegate registered was (for reference):

Name: Ext_ExportReport
Repository Type: com.ptc.windchill
Type Identifier: com.ptc.windchill.ws
Source URL: /ext/export/ExportReport.xml

Note the use of the existing type identifier "com.ptc.windchill.ws" for this example, and that the Task Delegate mechanism exposes a Web Service named "Ext_ExportReport" by registering the Info*Engine task specified in the source URL. A bit of extra work here to complete the web service definition, specifically within the header of the Info*Engine task itself:

<!--com.infoengine.soap.rpc.def
Task to execute and export a report

@string object_ref top level obid
@return java.io.InputStream {contentType:text/pdf}
-->

The WSDL automatically generated from this header therefore declares object_ref as an input parameter and that the output is a PDF stream.

Now to the implementation of ExportReport.xml task. For our benchmark scenario this needed to call Cognos to generate a PDF report and stream it back as an output. Our first thought for calling Cognos programmatically to generate the PDF report was to form the correct URL (including report input parameters) for the report, then stream the URL content back to the exportedFile variable above. There is a very good reference for generating Cognos URLs here: http://www.ibm.com/developerworks/data/library/cognos/page123.html. However, this didn't work out too well – the URLs we generated worked perfectly if entered into a web browser, but not if streamed back using Java code. The reason for this is that Cognos generates a wrapper HTML page for the report, which has embedded javascript to then go back directly to the server with additional information to actually render the report. So when streaming the URL from Java code, all we got was that initial wrapper HTML page. Plan B required …

Clearly there must be some Cognos Java API that PTC uses for the Windchill integration – there is, bundled in WT_HOME/srclib/cognos/axisCognosClient.jar. Not so easy to understand how to drive it all though – so started investigating how Windchill calls this Java SDK. After following a few links, there are plenty of goodies in our API. The most obvious one for us to use was com.ptc.windchill.enterprise.report.ReportHelper and the access it provides to the underlying ReportService implementation. In particular, this has an executeReport() API method that does the job perfectly for us (for PDF reports) without having to worry at all with Cognos connection, authentication etc. We ended up coding the following Helper class to wrap this for convenience. This uses a Windchill query to get a Report object given a report name, and uses that in a call to the ReportHelper.service.executeReport() method.

import java.io.File;
import java.io.FileOutputStream;
import java.util.Map;

import org.apache.log4j.Logger;

import com.ptc.windchill.enterprise.report.Report;
import com.ptc.windchill.enterprise.report.ReportHelper;

import wt.fc.PersistenceHelper;
import wt.fc.QueryResult;
import wt.log4j.LogR;
import wt.method.OutputStreamProxy;
import wt.pds.StatementSpec;
import wt.query.ClassAttribute;
import wt.query.ConstantExpression;
import wt.query.QuerySpec;
import wt.query.SearchCondition;
import wt.util.WTException;
import wt.util.WTProperties;

/**
* API using URL connection for streaming Cognos reports.
*
* @author rclarke
*
*/

public class CognosHelper {

private static String tmpDirectory;
private static Logger log;

    static {
        log = LogR.getLogger(CognosHelper.class.getName());
        try {
            WTProperties wtprops = WTProperties.getLocalProperties();
            tmpDirectory = wtprops.getProperty("wt.temp");
        } catch (Exception e) {
            log.debug(e.getLocalizedMessage());
        }
    }

    /**
     * Populates a File with contents of a Cognos Report
     *
     * @param reportName
     * @param reportParams
     * @return
     */

    public static File executeReport(String reportName, Map<String, String> reportParams) {
        File reportFile = new File(tmpDirectory + File.separator + System.nanoTime() + ".pdf");
        FileOutputStream out = null;
        OutputStreamProxy outproxy = null;

        try {
            out = new FileOutputStream(reportFile);
            outproxy = new OutputStreamProxy(out);
            Report report = getReport(reportName);
            ReportHelper.manager.executeReport(report, reportParams, ReportHelper.REP_PDF, outproxy);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
                if (outproxy != null) {
                    outproxy.closeOutputStream();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return reportFile;
    }

    /**
     * Returns a Windchill Report Object given a Report Name.
     *
     * @param reportName
     * @return
     * @throws WTException
     */

    public static Report getReport(String reportName) throws WTException {
        QuerySpec querySpec = new QuerySpec(Report.class);
        querySpec.appendWhere(new SearchCondition(new ClassAttribute(
                Report.class, Report.NAME), SearchCondition.EQUAL,
                ConstantExpression.newExpression(reportName)), new int[] { 0 });
        QueryResult queryResult = PersistenceHelper.manager.find((StatementSpec) querySpec);
        Report report = null;
        while (queryResult.hasMoreElements()) {
            report = (Report) queryResult.nextElement();
        }
        return report;
    }
}

Then this custom helper class is called from our I*E task to provide the Web Service – code excerpt below from the task below:

// get object ref passed in and initialse input params for report
String object_ref = (String) getParam("ufids");
Map<String,String> inputParams = new HashMap<String,String> ();
inputParams.put("object_ref", object_ref);
String exportFileName="BOMReport.pdf";

// generate report
File exportedFile = CognosHelper.executeReport("Rafael BOM", inputParams);

// stream exported PDF file to client
IeMultipartOutputStream iemos = (IeMultipartOutputStream) getTaskletOutputStream();
FileInputStream fis = new FileInputStream(exportedFile);
iemos.writeBlob(fis,exportFileName,exportFileName,"application/jar");

With all the server-side aspects dealt with, the benchmark required us to show development of a simple client to access this web-service to get the generated report. Certain tools supplied with Info*Engine make use of the WSDL to generate client side data access objects (DAOs) that can be used to invoke Info*Engine tasks and abstract all of the details involved in using SOAP as the communication protocol. We used this approach to provide a simple command-line DAO client that could be run from Eclipse (as a client) to show the concept.

First we defined a DAO utility class to use – the relevant aspects are below (we have a number of other methods in here from a previous project):

public class DAO extends com.infoengine.connector.DataAccessObject {

    public DAO(javax.resource.cci.Connection c) {
        super(c, com.infoengine.connector.IeRecordFactory.getFactory(), "com.ptc.windchill.ws");
    }

    public java.io.InputStream Export(java.lang.String[] ufids,
            java.lang.String[] propertyNames, String webServiceName) throws Exception {
        String[] ie$dao$names = { "ufids", "propertyNames", };
        Object[] ie$dao$vals = { ufids, propertyNames, };
        Object ie$dao$ret = execute(webServiceName, ie$dao$names, ie$dao$vals);
        return (java.io.InputStream) ie$dao$ret;
    }
}

And finally the command-line client itself to run in Eclipse – again, the relevant aspects are below:

/** SOAP end point details **/
private static final String WINDCHILL_URL = "http://eeclinux01.ptcnet.ptc.com/Windchill/";
private static final String SOAP_END_POINT_ENV_VARIABLE = WINDCHILL_URL + "servlet/SimpleTaskDispatcher";

/** Local vars **/
String username = …;
String password = …;
String localDirectory = …;

/** DAO connection **/
String soapEndPoint = SOAP_END_POINT_ENV_VARIABLE.replace("//", "//" + username + ":" + password + "@");
IeManagedConnectionFactory mcxf = new IeManagedConnectionFactory();
mcxf.setConnectionProperty("ConnectionURL", soapEndPoint);
ConnectionFactory cxf = (ConnectionFactory) mcxf.createConnectionFactory();
System.setProperty("com.ptc.windchill.ws.GenericBusinessObject.client","true");
DAO myDAO = new DAO(cxf.getConnection());

/** Get Part Reference **/
GenericBusinessObject localObject = …;

/** Web Service call to get Report generated by Cognos **/
File exportFileDAO = new File(localDirectory+File.separator+"ExportBOM.pdf");
InputStream is = myDAO.Export(new String[] {localObject.getUfid()}, new String[] {"number"}, "Ext_ExportReport");
FileUtils.copyInputStreamToFile(is, exportFileDAO);
myDAO.close();

All in all, what was looking like a bit of a nightmare to implement ended up being quite straightforward …