How best to present my Delphi database table to FastReport for lists and aggregates

Question

I have spent several days so far laying the ground work to use FastReport in my Application. The Application stores device test result data in the form of a DBF file comprising several fixed fields(DeviceID, Passed etc) plus a variable number of result fields, each of which correspond to the type of measurement data available. There can be as few as one of these fields and as many as 100. Each field has a letter code name such as OV and RV. Total record counts can be from zero up to some 10's of thousands.

A specific report template will have already included in its design the field names that it will display. Missing fields will be empty on the report.

My question involves the best way of designing the report and the data supplied to the report so that the report construction is as simple as possible - I'm going to allow my users to generate their own reports - and I need two kinds of report output - list of results and aggregates. It is the aggregates that are giving me the headache. I need not only MIN, MAX, COUNT etc (as provided internally in FastReport) but Standard Deviation as well. Further, I would like to use the FastReport 'drill down' feature where you can click on a group header and the data table is revealed or hidden. My aggregates should ideally be in the header, not the footer so that they appear all the time.

I have found that SQL in a TQuery gives me a lot of flexibility since it provides the 'StDev' aggregrate (FastREport does not) but as far as I can see I would need a fixed TQuery for each of my fields. So far, the nicest solution that I can come up with involves using a filter on the main table for 'Passed' (so that the user can view passe, failed or all) and then to build a separate 'stats' table with the same field name columns, but with MIN, MAX, COUNT, MEAN, STDEV as individual records. I would then use a TfrxDBDataSet to expose this table to FastReport. I see that I can also use FastReport's own ADODatabase and ADOQuery to directly access my DBF file. This works well but again I did not want to expose this access layer to my user in the report if possible.

This just seems so messy when aggregate functions must be a fundamental database requirement. Am I missing a much easier way of doing this? I've worked my way through the (excellent) demos supplied with FastReport (professional) and I'm using XE2. I'm also aware of the useful functions in the MATH unit if I need to calculate StDev myself.

I would appreciate any guidance, thanks.

Warren P Warren P · Accepted Answer · 2012-06-01T19:47:04

For anything you could calculate in code, lists of array values, aggregate or functional calculation results, I prefer to use the TfrxUserDataSet and implement the TfrxReport.OnGetvalue event.

Although it might initially be confusing, the user datasets simply declare a data set name, and the list of fields available through that data set name and use events which fire to let you "navigate" (first, next record) and declare when you've reached the end of your calculated data. This allows you to build a "generator" or else, just a normal virtual-data-provider set of logic for your calculations.

Here's what my OnGetValue events look like:

procedure TfrmReport.frxReportGetValue(const VarName: string; var Value: Variant);
begin
   Value := GetReportValue(VarName);
end;

// INPUT:  VarName = '(<GlobalArea."hdReportTitle">)'
// OUTPUT: tableName = 'GlobalArea', fieldName = 'hdReportTitle'
function ParseVar(const VarName:String; var tableName,fieldName:String; var ParenFlag:Boolean):Boolean;
var
 paVarName:String;
 angleBracketFlag:Boolean;
 dotPos:Integer;
 fieldQuoteFlag:Boolean;
 procedure RemoveOuter(var str:String; initialChar,finalChar:Char; var flag);
 var
  n:Integer;
 begin
    n := Length(str);
   if n>2 then begin
      ParenFlag := (str[1]=initialChar) and (str[n]=finalChar);
      if ParenFlag then begin
         str := Copy(str,2,n-2);

      end;
   end;
 end;
begin
   result := false;
   fieldQuoteFlag := false;
   paVarName := SysUtils.Trim(VarName);
   ParenFlag := false;
   tableName := '';
   fieldName := '';
   RemoveOuter(paVarName, '(',')',parenFlag);
   RemoveOuter(paVarName,'<','>',angleBracketFlag);
   dotPos := Pos('.',paVarName);
   if dotPos >0 then begin
    tableName := Copy(paVarName,1,dotPos-1);
    fieldName := Copy(paVarName,dotPos+1,Length(paVarName));
    RemoveOuter(fieldName, '"','"',fieldQuoteFlag);
    result := true;
   end else begin
      tableName := '';
      fieldName := paVarName;
   end;
end;

function TfrmProfitAnalysisReport.GetReportValue(const VarName:String):Variant;
var
 tableName:String;
 fieldName:String;
 parenFlag:Boolean;
begin
 ParseVar(VarName,tableName,fieldName,parenFlag);
 result := NULL;
   { Global Area - Header Values }
 if sameText(tableName,'GlobalArea') then begin
   if fieldName='hdReportTitle' then
      result := GetTitle; { A function that calculates a title for the report }
   else if fieldName='hdReportSubtitle' then
      result := 'Report for Customer XYZ'
   else if fieldName='....' then begin
      ...
   end;

   if Variants.VarIsNull( result) then
      result :=  '?'+fieldName+'?';

end;

How best to present my Delphi database table to FastReport for lists and aggregates

2 Answers