The Software Chef

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