Consultor Eletrônico

Status: Verified

GOAL:

How to use a temp-table or ProDataSet with Crystal Reports

GOAL:

Can a temp-table be passed to Crystal Reports?

GOAL:

Can a ProDataSet be passed to Crystal Reports?

FACT(s) (Environment):

Progress 9.x
OpenEdge 10.x
Crystal Reports 9
Crystal Reports 10
Crystal Reports 11 (XI)
Windows

FIX:

Option #1 - applies to Temp-Tables and ProDataSets
Temp-Table:
Crystal Reports can connect to a Progress AppServer, run 4GL code on that AppServer, and use the resulting Temptable as data for a report layout.
It works with Progress 9 and 10 (OpenEdge) Java Open Client, and with Crystal Reports 9, 10, or 11 (XI). Minimum requirement is Crystal Reports Advanced, this offers a Java connection.

ProDataSet:
In OpenEdge 10.0x, the .NET Open Client would have to be used since the ProDataSet is not supported by the Java Open Client.
A .NET class, wrapping the .NET proxy generated from ProxyGen, has to be used as the ADO.NET Data Source for the Crystal Reports report

For example, the following C# code has been tested with Visual Studio 2003, OpenEdge 10.0B and Crystal Reports XI.

CrystalWrapper.cls:

using System;
using Progress.Open4GL.Proxy ;
using ProxyNameSpace; // Generated from ProxyGen
using ProxyNameSpace.StrongTypesNS ; // Generated from ProxyGen
using System.Data;
namespace CrystalWrapper
{
public class ProCR
{
public ProCR()
{
}

public DataSet getDataSet(string conString)
{
Connection con = new Connection(conString,"","","");
dataSetProxy ds = new dataSetProxy (); // Strong typed DataSet coming from the .NET proxy generated from ProxyGen
Proxy ao = new Proxy(con);
ao.ProxyMethod(out ds); // ProxyMethod returns a DataSet
return ds;
}
}
}

In Crystal Reports, when creating a new connection, an ADO.NET (XML) data source can be defined with CrystalWrapper.dll as the .NET DataSet provider.
For the class name, specify "CrystalWrapper.ProCR" and select "getDataSet" for the DataSet Names.

Once done, it asks for the input parameter (conString) for the "getDataSet" method.
The connection string can be "AppServer://HostName:5162/asbroker1"
- HostName: Host name where the AppServer is located
- 5162: NameServer's port
- asbroker1: Name of the AppServer


Option #2 - applies to Temp-Tables only
Pass the temp-table to Crystal Reports via an intermediary custom ADO RecordSet.
The ADO RecordSet's structure can be defined (in a similar manner to a Progress temp-table) and then populated with data The ADO RecordSet can then be passed directly to Crystal Reports via the SetDataSource method.

/* Sample ADO RecordSet definition */
DEFINE VARIABLE ObjRecordSet AS COM-HANDLE NO-UNDO.

PAN>CREATE "ADODB.RecordSet" ObjRecordSet.
ObjRecordSet:FIELDS:APPEND("custnum",3,,). /* Integer */
ObjRecordSet:FIELDS:APPEND("name",200,30,). /* Character */
ObjRecordSet:FIELDS:APPEND("city",200,25,).
ObjRecordSet:FIELDS:APPEND("country",200,20,).
ObjRecordSet:FIELDS:APPEND("salesrep",200,4,).

ObjRecordSet:OPEN(,,,,).

The values supplied in the APPEND method represent the field name, ADO equivalent to the Progress data-type, field-length. Refer to the Microsoft ADO DataTypeEnum documentation for additional data-types.

/* Sample ADO RecordSet population via Temp-Table */
FOR EACH ttCustomer:
ObjRecordSet:ADDNEW.
ASSIGN
ObjRecordSet:FIELDS("custnum"):VALUE = ttCustomer.custnum
ObjRecordSet:FIELDS("name"):VALUE = ttCustomer.name
ObjRecordSet:FIELDS("city"):VALUE = ttCustomer.city
ObjRecordSet:FIELDS("country"):VALUE = ttCustomer.country
ObjRecordSet:FIELDS("salesrep"):VALUE = ttCustomer.salesrep
.
ObjRecordSet:UPDATE.
END.

The populated ObjRecordSet can then be passed to the report via the RDC's SetDataSource method..