Since a lot of People have been asking about a successful integration of digital metaphor’s wonderful Reportbuilder with RemObjects’ DataAbstract I thought it might be a good idea to post what I have been doing to integrate these two in an elegant fashion.
Objective
- Give the end-user access to the full spectrum of possibilities, including design of queries, Report-Designer Preview, Report Explorer and so on.
- When running reports within you application server, make use of direct database access for increased speed.
Approach
- Write a DADE plug-in (data driver) that abstracts where the report is actually executed
- On the client-side: Use a locally available Channel and Message to access a ReportService on the server-side which allows arbitrary SQL to be executed. This has already been solved by Wouter Devos of XLent Solutions and I will extend his solution.
- On the server-side: Access the active service the report is executed within and execute sql generated by ReportBuilder’s DADE directly without the overhead of a network round-trip
Caveats
The whole point of a multi-tier setup is to abstract the fact that there is a database from the client, it should not send or know about sql in any case. By allowing the execution of arbitrary SQL by the client you give blank access to all information. To minimise the potential security risk, the associated transaction should be read-only to prevent any updates by the client. Additionally you should specify a security token that is only granted to selected users (“End-User Reporting Allowed”).
Because all data that is being retrieved is re-packaged and transmitted over the wire ultimately ending up in a memory dataset the resulting speed is definitely slower than direct database access and you should likely limit the number of records returned to a reasonable number, so the client does not “hang” while waiting for the whole database to be pulled to local memory.
Technical Details
Client-Side
On the client implement one interface in a singleton (your hydra host would be a good candidate) and assign the global variable in unit daDataAbstract.pas.
{ Implement this interface on the client. Assumptions:
- Single thread on the client (UI)
- One connection to one server
- The Remoteservice.Message.ClientId is implicitly used for session management
set global variable gReportingClientController on the client
}
IReportingClientController = interface
['{9D6908F8-BB64-400F-BE94-0CF550EC2BA2}']
function GetRemoteService: TRORemoteService;
function GetDataStreamer: TDADataStreamer;
end;
This will provide the minimal means to connect to your server
Server-side
On the server-side you obviously need to implement a service that hosts a TppReport Component and gives necessary information to the client. This service must implement the following interface
{ Implement this interface in the Service that contains the Report component.
When retrieving data on the server, Report.Owner is querie'd for this
interface. Fall-back to local service discovery (FindClassFactory), and
instantiate the reporting service (slower than the former).
}
IReportingServiceRBuilderSupport = interface
['{AFF049E4-5B72-430F-9973-33CF4DC53B6E}']
function GetConnection: IDAConnection;
function GetDataStreamer: TDADataStreamer;
procedure GetTableNames(out TableNames: string);
end;
This interface is used when executing reports “locally” within the server context. The class factory for the reporting service should be a pooled one, since we want to utilize the positive effects of full multi-threading.
The service needs to implement a few additional methods, so the client can gather the necessary information and could look like this
{ TReportingService }
TReportingService = class(TRORemoteDataModule, IReportingService,
IReportingServiceRBuilderSupport
)
BinDataStreamer: TDABinDataStreamer;
bpReportFolders: TDABusinessProcessor;
bpReportItems: TDABusinessProcessor;
ppReport: TppReport;
ppReportItemWithData: TppDBPipeline;
tblReportItemWithData: TDACDSDataTable;
dsReportItemWithData: TDADataSource;
Schema: TDASchema;
procedure RORemoteDataModuleActivate(const aClientID: TGUID;
aSession: TROSession; const aMessage: IROMessage);
procedure RORemoteDataModuleDeactivate(const aClientID: TGUID;
aSession: TROSession);
private
FConnection: IDAConnection;
protected
{ IReportingService methods }
// Needed for report explorer only
function GetAllReportItems: Binary;
function GetReportTemplateData(const ReportItemId: Integer): Binary;
function UpdateReports(const Data: TROBinaryMemoryStream): TROBinaryMemoryStream;
public
constructor Create(AOwner: TComponent); override;
//Called by client-side reporting
function GetReportDatasetData(const SQL: string; const TableName: string;
const LoadAll: Boolean): TROBinaryMemoryStream;
function GetReportDatasetSchema(const SQL: string;
const TableName: string): TROBinaryMemoryStream;
// Only needed if the client should use human readable translated reporting meta-data
function GetDictionaryData: TROBinaryMemoryStream;
// Custom method to execute a certain report on the server-side
function GenerateReportPreview(const ReportItemId: Integer;
const Params: TReportParamList): TROBinaryMemoryStream;
// IReportingServiceRBuilderSupport
function GetConnection: IDAConnection;
function GetDataStreamer: TDADataStreamer;
procedure GetTableNames(out TableNames: string);
end;
The most important part of the implementation is
function TReportingService.GetReportDatasetSchema(const SQL,
TableName: string): TROBinaryMemoryStream;
var
ds: IDADataset;
begin
Result := Binary.Create();
BinDataStreamer.Initialize(Result, aiWrite);
try
if SQL = '' then
ds := FConnection.NewDataset(Format('SELECT * FROM %s WHERE 1 = 0',
[TableName]), 'dsReportData')
else
ds := FConnection.NewDataset(SQL, 'dsReportData');
ds.Prepared := True;
BinDataStreamer.WriteDataset(ds, [woSchema]);
finally
BinDataStreamer.Finalize;
end;
end;
function TReportingService.GetReportDatasetData(const SQL, TableName: string;
const LoadAll: Boolean): TROBinaryMemoryStream;
var
ds: IDADataset;
begin
Result := Binary.Create();
BinDataStreamer.Initialize(Result, aiWrite);
try
if SQL = '' then
ds := FConnection.NewDataset(Format('SELECT * FROM %s WHERE 1 = 0',
[TableName]), 'dsReportData')
else
ds := FConnection.NewDataset(SQL, 'dsReportData');
ds.Open;
if LoadAll then
BinDataStreamer.WriteDataset(ds, [woSchema, woRows], -1)
else
BinDataStreamer.WriteDataset(ds, [woSchema, woRows], 0);
finally
BinDataStreamer.Finalize;
end;
end;
procedure TReportingService.GetTableNames(out TableNames: string);
var
Tables: IROStrings;
begin
FConnection.GetTableNames(Tables);
TableNames := Tables.CommaText;
end;
Summary
This article is just a brief overview of what is actually needed to get a fully integrated reporting solution and there are a lot of nuts and bolts involved in the full solution that I will not show here. I hope this solution will help a lot of people who just want to get something working.
Download link: DADE-Plugin for DataAbstract