Using Reports As a Datasource

Because Crystal Reports access so many datasources and often include high-value business logic, customers increasingly request to use their Crystal Reports as datasources.

Access is provided through the RAS getRowsetController cursor. It is advisable to consult the API documentation, but here are some tips to get you started:

• Set batch size Unless you have the ability to request records in batches, set the batch size to the total number of data rows in the report.

• Rowcount is the last column in the record datastructure The number of columns in the metadata fields datastructure is one less column than the records datastructure. You can ignore the last column; it is just the row number.

• Description is the only unique column It is possible for reports to contain many formulas and summaries. To ensure uniqueness, use the description rather than the name.

• ADO.NET XML available if you prefer extracting an ADO.NET recordset, use the resultCursor.getRowset().getXMLData() method.

Listing 30.6 shows how to use the RowsetController to extract data from a Crystal Report.

Listing 30.6. Extracting Data from a Crystal Report

[View full width]

IInfoObject object = (IInfoObject) BOEUtil.getReportByName(reportName, iStore); IReport report = (IReport) object; IReportAppFactory rptAppFactory = (IReportAppFactory) es.getService("",  "RASReportService"); // Get the InfoStore service from Crystal Enterprise clientDoc = rptAppFactory.openDocument(report, 0, Locale.ENGLISH); if (clientDoc != null) { //create metadata structure RowsetMetaData rowSetMetaData = new RowsetMetaData();  rowSetMetaData.setDataFields (clientDoc.getDataDefinition().getResultFields());        Fields fields = rowSetMetaData.getDataFields();        String colName,colTypeName;        int colType, len;        Field field;        HashMap colNames = new HashMap();        HashMap colTypes = new HashMap();        for (int i = 0; i < fields.size(); i++) {                field = (Field) fields.get(i);                colName =field.getDisplayName(FieldDisplayNameType.description, Locale .ENGLISH);                colNames.put("COL"+i,colName);        colType = getColTypeFromVariant(field.getType().toVariantTypeString());        len=field.getLength();               //Placeholder         //add column metadata to data structure or XML         }         Object colValue;         int rowCount=0;         if (getMaxRows() != 0) {                RowsetCursor rowsetCursor = clientDoc.getRowsetController(). createCursor (null, rowSetMetaData);               //Setting the batch size to make sure that you get back all the               //rows by setting the batch size.                     clientDoc.getRowsetController().setRowsetBatchSize (rowsetCursor .getRowset().getTotalRecordCount());            //Getting the Results back starting from the first row            RowsetCursor resultCursor = clientDoc.getRowsetController(). createCursor(null,  rowSetMetaData);            resultCursor.moveTo(0);               while (!resultCursor.isEOF()) {                   for (int i = 0; i < fields.size(); i++) {                            colName=(String)colNames.get("COL"+i);                            colTypeName=(String)colTypes.get("COL"+i); colValue=record.getValue(i);                             //Placeholder //add row data to data structure or XML                     } resultCursor.moveNext();                }            }