Is there a way to summarize data in a group footer band from another in FastReports 5 Embarcadero Edition?
I have a FastReport which displays weekly time sheets for a monthly period. Daily time in and out, and time elapsed are displayed in MasterBand1. Time elapsed, clockable hours and calculation of straight time are in the weekly summary in GroupFooter1. I need to also summarize this information in GroupFooter2 for the month.
Clockable hours are those in which the employee actually works. Sick, personal and vacation time are excluded. Total hours include clockable and non-clockable.
Overtime is calculated as the number of clockable hours in excess of 40 hours in a week. I am able to use variables to calculate each of these in the (weekly) GroupFooter1 bank. In order for the monthly totals for overtime and straight time to be accurate, I must accumulate the straight and over time from the weekly band rather than the master data band. Since FastReports Embarcadero Edition does not include scripting, I am at a loss as to how to do this using variables. The only alternative I can think of is to create calculated fields in a "month" dataset for each employee and link that dataset to GroupFooter2. There creates the possiblity, however, of errors by presenting data from two different sources. Is there a way to do this within FastReports?
Here is the .pas for the form.
unit YTDPayRpt;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option,
FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.FB,
FireDAC.Phys.FBDef, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, FireDAC.DatS,
FireDAC.DApt.Intf, FireDAC.DApt, Vcl.StdCtrls, frxClass, frxDBSet, Data.DB,
FireDAC.Comp.DataSet, FireDAC.Comp.Client, FireDAC.Comp.UI, frxChBox;
type
TForm2 = class(TForm)
Connection: TFDConnection;
WaitCursor: TFDGUIxWaitCursor;
WorkshiftDataset: TFDTable;
Report: TfrxReport;
frxDBDataset1: TfrxDBDataset;
Button1: TButton;
frxCheckBoxObject1: TfrxCheckBoxObject;
WorkshiftDatasetID: TLargeintField;
WorkshiftDatasetEMPLOYEEID: TLargeintField;
WorkshiftDatasetJOBCODE: TSmallintField;
WorkshiftDatasetCLOCKABLE: TBooleanField;
WorkshiftDatasetDAYOFWEEK: TStringField;
WorkshiftDatasetDATEIN: TDateField;
WorkshiftDatasetTIMEIN: TTimeField;
WorkshiftDatasetDATEOUT: TDateField;
WorkshiftDatasetTIMEOUT: TTimeField;
WorkshiftDatasetNOTE: TMemoField;
WorkshiftDatasetTIMEELAPSED: TFMTBCDField;
WorkshiftDatasetWEEKOF: TDateField;
WorkshiftDatasetROWNBR: TSmallintField;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form2: TForm2;
implementation
{$R *.dfm}
procedure TForm2.Button1Click(Sender: TObject);
begin
Report.ShowReport();
end;
end.
Here is text file for the form with componenets needed for the report.
object Form2: TForm2
Left = 0
Top = 0
Caption = 'Form2'
ClientHeight = 211
ClientWidth = 418
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Button1: TButton
Left = 184
Top = 112
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 0
OnClick = Button1Click
end
object Connection: TFDConnection
Params.Strings = (
'ConnectionDef=Timedata - Local')
Connected = True
LoginPrompt = False
Left = 96
Top = 24
end
object WaitCursor: TFDGUIxWaitCursor
Provider = 'Forms'
Left = 208
Top = 24
end
object WorkshiftDataset: TFDTable
Active = True
IndexFieldNames = 'ID;DATEIN;ROWNBR'
Connection = Connection
UpdateOptions.UpdateTableName = 'V_WORKSHIFT'
TableName = 'V_WORKSHIFT'
Left = 288
Top = 32
object WorkshiftDatasetID: TLargeintField
FieldName = 'ID'
Origin = 'ID'
end
object WorkshiftDatasetEMPLOYEEID: TLargeintField
FieldName = 'EMPLOYEEID'
Origin = 'EMPLOYEEID'
end
object WorkshiftDatasetJOBCODE: TSmallintField
FieldName = 'JOBCODE'
Origin = 'JOBCODE'
end
object WorkshiftDatasetCLOCKABLE: TBooleanField
FieldName = 'CLOCKABLE'
Origin = 'CLOCKABLE'
end
object WorkshiftDatasetDAYOFWEEK: TStringField
FieldName = 'DAYOFWEEK'
Origin = 'DAYOFWEEK'
Size = 10
end
object WorkshiftDatasetDATEIN: TDateField
FieldName = 'DATEIN'
Origin = 'DATEIN'
end
object WorkshiftDatasetTIMEIN: TTimeField
FieldName = 'TIMEIN'
Origin = 'TIMEIN'
end
object WorkshiftDatasetDATEOUT: TDateField
FieldName = 'DATEOUT'
Origin = 'DATEOUT'
end
object WorkshiftDatasetTIMEOUT: TTimeField
FieldName = 'TIMEOUT'
Origin = 'TIMEOUT'
end
object WorkshiftDatasetNOTE: TMemoField
FieldName = 'NOTE'
Origin = 'NOTE'
BlobType = ftMemo
end
object WorkshiftDatasetTIMEELAPSED: TFMTBCDField
FieldName = 'TIMEELAPSED'
Origin = 'TIMEELAPSED'
Precision = 18
Size = 9
end
object WorkshiftDatasetWEEKOF: TDateField
FieldName = 'WEEKOF'
Origin = 'WEEKOF'
end
object WorkshiftDatasetROWNBR: TSmallintField
FieldName = 'ROWNBR'
Origin = 'ROWNBR'
end
end
object Report: TfrxReport
Version = '5.3.14'
DotMatrixReport = False
IniFile = '\Software\Fast Reports'
PreviewOptions.Buttons = [pbPrint, pbLoad, pbSave, pbExport, pbZoom, pbFind, pbOutline, pbPageSetup, pbTools, pbEdit, pbNavigator, pbExportQuick]
PreviewOptions.Zoom = 1.000000000000000000
PrintOptions.Printer = 'Default'
PrintOptions.PrintOnSheet = 0
ReportOptions.CreateDate = 42689.921906956000000000
ReportOptions.LastChange = 42689.930818541700000000
ScriptLanguage = 'PascalScript'
ScriptText.Strings = (
''
'begin'
''
'end.')
Left = 56
Top = 103
Datasets = <
item
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
end>
Variables = <
item
Name = ' Workshift'
Value = Null
end
item
Name = 'ClockableTime'
Value =
'iif(<frxDBDataset1."CLOCKABLE"> = true, <frxDBDataset1."TIMEELAP' +
'SED"> * 24, 0)'
end
item
Name = 'WeeklyTimeElapsed'
Value = 'SUM(<frxDBDataset1."TIMEELAPSED"> * 24,MasterData1)'
end
item
Name = 'WeeklyClockableTime'
Value = 'sum(<ClockableTime>)'
end
item
Name = 'WeeklyStraightTime'
Value = '<WeeklyTimeElapsed> - <WeeklyOvertime>'
end
item
Name = 'WeeklyOvertime'
Value = 'iif(<WeeklyClockableTime> > 40, <WeeklyTimeElapsed> - 40, 0)'
end
item
Name = 'EmployeeTimeElapsed'
Value = 'sum(<frxDBDataset1."TIMEELAPSED">, MasterData1)'
end>
Style = <>
object Data: TfrxDataPage
Height = 1000.000000000000000000
Width = 1000.000000000000000000
end
object Page1: TfrxReportPage
PaperWidth = 215.900000000000000000
PaperHeight = 279.400000000000000000
PaperSize = 1
LeftMargin = 10.000000000000000000
RightMargin = 10.000000000000000000
TopMargin = 10.000000000000000000
BottomMargin = 10.000000000000000000
object MasterData1: TfrxMasterData
FillType = ftBrush
Height = 28.800000000000000000
Top = 201.600000000000000000
Width = 740.409927000000000000
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
RowCount = 0
object frxDBDataset1JOBCODE: TfrxMemoView
Left = 172.800000000000000000
Width = 124.800000000000000000
Height = 19.200000000000000000
DataField = 'JOBCODE'
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
Memo.UTF8W = (
'[frxDBDataset1."JOBCODE"]')
end
object frxDBDataset1DATEIN: TfrxMemoView
Width = 76.800000000000000000
Height = 19.200000000000000000
DataField = 'DATEIN'
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
Memo.UTF8W = (
'[frxDBDataset1."DATEIN"]')
end
object frxDBDataset1DAYOFWEEK: TfrxMemoView
Left = 86.400000000000000000
Width = 76.800000000000000000
Height = 19.200000000000000000
DataField = 'DAYOFWEEK'
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
Memo.UTF8W = (
'[frxDBDataset1."DAYOFWEEK"]')
end
object frxDBDataset1TIMEIN: TfrxMemoView
Left = 307.200000000000000000
Width = 76.800000000000000000
Height = 19.200000000000000000
DataField = 'TIMEIN'
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
Memo.UTF8W = (
'[frxDBDataset1."TIMEIN"]')
end
object frxDBDataset1TIMEOUT: TfrxMemoView
Left = 393.600000000000000000
Width = 76.800000000000000000
Height = 19.200000000000000000
DataField = 'TIMEOUT'
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
Memo.UTF8W = (
'[frxDBDataset1."TIMEOUT"]')
end
object frxDBDataset1TIMEELAPSED: TfrxMemoView
Left = 480.000000000000000000
Width = 76.800000000000000000
Height = 19.200000000000000000
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
DisplayFormat.FormatStr = '%2.2n'
DisplayFormat.Kind = fkNumeric
Font.Charset = DEFAULT_CHARSET
Font.Color = clBlack
Font.Height = -13
Font.Name = 'Arial'
Font.Style = []
HAlign = haRight
Memo.UTF8W = (
'[<frxDBDataset1."TIMEELAPSED"> * 24]')
ParentFont = False
end
object Memo3: TfrxMemoView
Left = 566.400000000000000000
Width = 76.800000000000000000
Height = 19.200000000000000000
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
DisplayFormat.FormatStr = '%2.2n'
DisplayFormat.Kind = fkNumeric
Font.Charset = DEFAULT_CHARSET
Font.Color = clBlack
Font.Height = -13
Font.Name = 'Arial'
Font.Style = []
HAlign = haRight
Memo.UTF8W = (
'[ClockableTime]')
ParentFont = False
end
object CheckBox1: TfrxCheckBoxView
Left = 662.400000000000000000
Width = 18.897650000000000000
Height = 18.897650000000000000
CheckColor = clBlack
CheckStyle = csCheck
DataField = 'CLOCKABLE'
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
end
end
object PageFooter1: TfrxPageFooter
FillType = ftBrush
Height = 28.800000000000000000
Top = 441.600000000000000000
Width = 740.409927000000000000
object Memo1: TfrxMemoView
Left = 665.197280000000000000
Width = 75.590600000000000000
Height = 18.897650000000000000
HAlign = haRight
Memo.UTF8W = (
'[Page#]')
end
end
object PageHeader1: TfrxPageHeader
FillType = ftBrush
Height = 28.800000000000000000
Top = 19.200000000000000000
Width = 740.409927000000000000
end
object ReportSummary1: TfrxReportSummary
FillType = ftBrush
Height = 28.800000000000000000
Top = 393.600000000000000000
Width = 740.409927000000000000
end
object GroupHeader1: TfrxGroupHeader
FillType = ftBrush
Height = 28.800000000000000000
Top = 105.600000000000000000
Width = 740.409927000000000000
Condition = 'frxDBDataset1."EMPLOYEEID"'
StartNewPage = True
object frxDBDataset1EMPLOYEEID: TfrxMemoView
Width = 115.200000000000000000
Height = 19.200000000000000000
DataField = 'EMPLOYEEID'
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
Memo.UTF8W = (
'[frxDBDataset1."EMPLOYEEID"]')
end
end
object GroupFooter1: TfrxGroupFooter
FillType = ftBrush
Height = 28.800000000000000000
Top = 307.200000000000000000
Width = 740.409927000000000000
object Memo7: TfrxMemoView
Left = 460.800000000000000000
Top = 9.600000000000023000
Width = 96.000000000000000000
Height = 19.200000000000000000
DisplayFormat.FormatStr = '%2.2n'
DisplayFormat.Kind = fkNumeric
Font.Charset = DEFAULT_CHARSET
Font.Color = clBlack
Font.Height = -13
Font.Name = 'Arial'
Font.Style = []
HAlign = haRight
Memo.UTF8W = (
'[SUM(<frxDBDataset1."TIMEELAPSED"> * 24,MasterData1)]')
ParentFont = False
end
end
object GroupHeader2: TfrxGroupHeader
FillType = ftBrush
Height = 28.800000000000000000
Top = 153.600000000000000000
Width = 740.409927000000000000
Condition = 'frxDBDataset1."WEEKOF"'
object frxDBDataset1WEEKOF: TfrxMemoView
Width = 105.600000000000000000
Height = 19.200000000000000000
DataSet = frxDBDataset1
DataSetName = 'frxDBDataset1'
Memo.UTF8W = (
'w/o [frxDBDataset1."WEEKOF"]')
end
end
object GroupFooter2: TfrxGroupFooter
FillType = ftBrush
Height = 38.400000000000000000
Top = 249.600000000000000000
Width = 740.409927000000000000
object Memo2: TfrxMemoView
Left = 480.000000000000000000
Top = 9.599999999999995000
Width = 76.800000000000000000
Height = 19.200000000000000000
DisplayFormat.FormatStr = '%2.2n'
DisplayFormat.Kind = fkNumeric
Font.Charset = DEFAULT_CHARSET
Font.Color = clBlack
Font.Height = -13
Font.Name = 'Arial'
Font.Style = []
HAlign = haRight
Memo.UTF8W = (
'[WeeklyTimeElapsed]')
ParentFont = False
end
object Memo4: TfrxMemoView
Left = 566.400000000000000000
Top = 9.599999999999995000
Width = 76.800000000000000000
Height = 19.200000000000000000
DisplayFormat.FormatStr = '%2.2n'
DisplayFormat.Kind = fkNumeric
Font.Charset = DEFAULT_CHARSET
Font.Color = clBlack
Font.Height = -13
Font.Name = 'Arial'
Font.Style = []
HAlign = haRight
Memo.UTF8W = (
'[WeeklyClockableTime]')
ParentFont = False
end
object Memo5: TfrxMemoView
Left = 9.600000000000000000
Top = 9.599999999999995000
Width = 201.600000000000000000
Height = 19.200000000000000000
DisplayFormat.FormatStr = '%2.2n'
DisplayFormat.Kind = fkNumeric
Memo.UTF8W = (
'Straight Time: [WeeklyStraightTime]')
end
object Memo6: TfrxMemoView
Left = 220.800000000000000000
Top = 9.599999999999995000
Width = 172.800000000000000000
Height = 19.200000000000000000
DisplayFormat.FormatStr = '%2.2n'
DisplayFormat.Kind = fkNumeric
Memo.UTF8W = (
'Overtime hours: [WeeklyOvertime]')
end
end
end
end
object frxDBDataset1: TfrxDBDataset
UserName = 'frxDBDataset1'
CloseDataSource = False
DataSet = WorkshiftDataset
BCDToCurrency = False
Left = 360
Top = 32
end
object frxCheckBoxObject1: TfrxCheckBoxObject
Left = 112
Top = 112
end
end
