Title: Runtime reporting
Question: More often than not I need reports based on the runtime situation. Qreport has the createlist function but that is far too inflexible, even as a starting point! That is why I developed my own runtime report functionality
Answer:
Introduction
More often than not I need reports based on the runtime situation. Qreport has the createlist function but that is far too inflexible, even as a starting point! That is why I developed my own runtime report functionality for my basic db-unit. My basic db-unit consists of
a dataset, being a ADO query, within the datamodule,
a form with a grid
a data entry form
The form with the grid has baked in functionality: changing the order of dataset/grid/report (up to 2 columns), setting a filter, changing the font/fontsize, changing the display properties for grid/report and the production of a standard report. Baked in means here, that I only have to provide the dataset object with the SQL-select statement and that with that the whole functionality is operational.
The Display/Report Properties functionality is an important part of the way my apps interface with the users in this respect:
[picture 1 - will be loaded by webmaster]
A simple stringgrid loaded with the fieldname, displaylabel, displaywidth, displayformat, the fieldtype (not visible here) and visible properties of the fields in the dataset. I use the tag property for creating two additional properties: values 00,01,10,11 (first digit = Y/N sum and second digit = Y/N group). The tag property is a endless source of additional boolean type properties. Eventually a simple function does the work.
Sum = Y means that the user wants reports to total on this field. The user may mark as many field as (s)he likes. For that reason I implemented each sum on a separate line. Leaving space for a description. Group = Y means the user wants reports to be grouped on this field. I limited the grouping in the standard report to one level. More levels are easy to implement. B.t.w.: the order in which the fields appear depends on the order in the dataset. The other properties - like order, filter and font - are handled on the form, e.g. clicking on the columntitle to set/reset the sortorder of the dataset.
The standard report.
The user produces a standard report by invoking the reportdialog (source see unit: genreportunit):
------------------------
[ ] Reporttitle
Pagefooter
[x] Date
[x] Username
[ ] Connection filename
[ ] Script filename
[x] Number of records
[x] Pagenumber
Grouping
[ ] Force new page
[ ] # records on band
----------------------
[ OK ] [Cancel]
----------------------
If the user checks the Reporttitle, he will be able - inputbox - to enter a reporttitle. For the rest he can concoct the pagefooter. The user can choose to have a groupcounter implemented and to have the groupcount placed on the groupband. She or he can (if applicable) let the groups start on a new page. After hitting the OK button the report will be presented.
The whole functionality of the standard runtime report is in this genreportunit with its genreportform This unit uses the standardreportunit with its repform, being only a form with a quickreport object (named Report) on it, and all the bands I ever need. This is the source. Observe the bands used:
unit standardreportunit;
interface
uses
Windows, Messages, QuickRpt, Classes, Controls, ExtCtrls, Forms;
type
Trepform = class(TForm)
report: TQuickRep;
TitleBand1: TQRBand;
ColumnHeaderBand1: TQRBand;
DetailBand1: TQRBand;
PageFooterBand1: TQRBand;
groupheaderband1: TQRGroup;
groupfooterband1: TQRBand;
SummaryBand1: TQRBand;
private
{ Private declarations }
public
{ Public declarations }
end;
var
repform: Trepform;
implementation
{$R *.dfm}
end.
As said, the making of the report takes place in the genreportunit. You will see that there is no hocus pocus involved and no underdocumented Qreports functionality is used, but I can assure you that the result is a fully acceptable business report. It is not written ASAP (As Smart As Possible) but more ATAP where T stands for transparant. Maybe you will want to adapt the look and feel to your own taste, and that is ok, but after that it is and should be one size fits all.
In the source of the genreportunit is the comment with // programming comment deviding the source in logical chunks. The comment with {} is article comment.
unit genreportunit;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ExtCtrls, Buttons, Grids, dbGrids, db, ADOdb,
printers, QuickRpt, qrextra, qrprntr, qrctrls ; {important}
type
TGenReportForm = class(TForm)
c1: TCheckBox; {Date}
c2: TCheckBox; (Username}
c3: TCheckBox; {Connection filename - variable on mainform}
c4: TCheckBox; {Script filename - variable on mainform}
c5: TCheckBox; {Number of records}
c6: TCheckBox; {Page number}
ch: TCheckBox; {Title - invokes inputbox}
NewPageBox: TCheckBox;
NmbrRec: Tcheckbox; {number of records in group on groupfooterband}
okbtn: TBitBtn;
Cancelbtn: TBitBtn;
Label1: TLabel;
Label2: TLabel;
procedure okbtnClick(Sender: TObject);
procedure chClick(Sender: TObject);
procedure FormShow(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
GenReportForm: TGenReportForm;
repheader: string;
function makefooter: string;
procedure initreport(Grid: TDBGrid);
function iGetCurrentUserName: string;
implementation
uses mainunit, dataunit, standardreportunit;
{$R *.dfm}
procedure TGenReportForm.okbtnClick(Sender: TObject);
begin
initreport(mainform.dbgrid1);
GenReportUnit.repheader := '';
standardreportunit.repform.report.preview;
end;
procedure initreport(Grid: TDBGrid);
var
indatasetcounter, bandhi, currentleft, currenttop, summaryleft,
summarytop, tempwidth: integer;
myhasgroup, myhassummary: boolean;
pagefooterstring: string;
tempalignment: talignment;
begin
{the grid parameter connects the unit to the grid and the dataset}
with grid do
begin
repform.report.font.name := font.name;
repform.report.font.size := font.size;
bandhi := abs(font.height) + 10;
repform.report.dataset := datasource.dataset ;
end;
myhasgroup := false;
myhassummary := false;
currentleft := 12;
currenttop := 6;
summaryleft := currentleft ;
summarytop := currenttop;
with repform do
begin
report.font.color := clblack;
report.page.orientation := poportrait;
{open all bands for business}
report.bands.hasdetail := true;
report.bands.hascolumnheader := true;
report.bands.haspagefooter := true;
report.bands.hastitle := true;
report.bands.hassummary := true;
groupheaderband1.enabled := true;
groupfooterband1.enabled := true;
{clear the band from all - previous - objects}
while detailband1.controlcount 0 do
detailband1.controls[0].free;
while columnheaderband1.controlcount 0 do
columnheaderband1.controls[0].free;
while pagefooterband1.controlcount 0 do
pagefooterband1.controls[0].free;
while titleband1.controlcount 0 do
titleband1.controls[0].free;
while summaryband1.controlcount 0 do
summaryband1.controls[0].free;
while groupheaderband1.ControlCount 0 do
groupheaderband1.Controls[0].free;
while groupfooterband1.ControlCount 0 do
groupfooterband1.Controls[0].free;
{your preferences or - via an inifile - the preferences of the user}
pagefooterband1.color := clwhite;
titleband1.color := clwhite;
detailband1.color := clwhite;
summaryband1.color := clwhite;
columnheaderband1.color := clsilver;
groupheaderband1.color := clgray;
groupfooterband1.color := clgray;
pagefooterband1.height := bandhi;
titleband1.height := bandhi;
summaryband1.height := bandhi;
groupheaderband1.height := bandhi;
groupfooterband1.height := bandhi;
columnheaderband1.height := bandhi;
detailband1.height := bandhi;
groupheaderband1.Frame.DrawTop := true;
groupheaderband1.Frame.Width := 2;
SummaryBand1.Frame.DrawTop := true;
SummaryBand1.Frame.Drawbottom := true;
SummaryBand1.Frame.Style := pssolid;
SummaryBand1.Frame.width := 2;
end;
with repform.groupheaderband1 do
begin
master := repform.report;
footerband := repform.groupfooterband1;
forcenewpage := genreportunit.GenReportForm.NewPageBox.Checked;
end;
with repform.report.dataset do
begin
for indatasetcounter := 0 to fieldcount - 1 do
begin
if fields[indatasetcounter].visible then
begin
{choose the alignment: Numerics/date rightjust./rest leftjust.}
if fields[indatasetcounter].datatype in
[ftfloat, ftcurrency, ftsmallint, ftinteger,
ftdate, ftdatetime, fttime]
then tempalignment := tarightjustify
else tempalignment := taleftjustify;
tempwidth := fields[indatasetcounter].displaywidth *
repform.canvas.textwidth('a');
{the report switches automatically from
portrait - landschape - stop adding columns}
if (currentleft + tempwidth) repform.columnheaderband1.width then
begin
if repform.report.page.orientation = polandscape
then break
else repform.report.page.orientation := polandscape;
end;
// columnheaderband - label
with tqrlabel.create(repform.columnheaderband1) do
begin
parent := repform.columnheaderband1;
autosize := false;
left := currentleft;
top := currenttop;
width := tempwidth;
alignment := tempalignment;
caption := fields[indatasetcounter].displaylabel;
color := repform.columnheaderband1.color;
end;
// detailband - dbtext
with tqrdbtext.create(repform.detailband1) do
begin
parent := repform.detailband1;
autosize := false;
autostretch := true; {memofields are printed nicely}
left := currentleft;
top := currenttop;
width := tempwidth;
alignment := tempalignment;
dataset := repform.report.dataset;
datafield := fields[indatasetcounter].fieldname;
currentleft := currentleft + tempwidth + 15;
color := repform.detailband1.color;
end;
if (fields[indatasetcounter].tag 9) then
begin
// there is a sum and a summeryband
// summeryband - setup
myhassummary := true;
if repform.SummaryBand1.ControlCount 0 then
begin
// records counts already placed
repform.SummaryBand1.Height :=
repform.SummaryBand1.Height + bandhi;
inc(summarytop, bandhi - 4);
end
else
begin
{ empty summary band and groopfooterband,
eventualy place
# records (= counts) on summary and groupband
groupcount on the first line of the groupfooterband
total number of records on summaryband}
if genreportform.NmbrRec.Checked then
begin
// summeryband - label
with tqrlabel.create(repform.SummaryBand1) do
begin
parent := repform.SummaryBand1;
autosize := false;
left := summaryleft;
top := summarytop;
width := 200;
alignment := taleftjustify;
font.style := [fsbold];
color := repform.SummaryBand1.color;
caption := 'Number of records';
end;
// summeryband - sum expression
// observe difference with recordcount and count
// both are Qreport variables
with tqrlabel.create(repform.report.bands.summaryband) do
begin
parent := repform.SummaryBand1;
autosize := true;
left := summaryleft + 200;
top := summarytop;
width := 100;
alignment := tarightjustify;
font.style := [fsbold];
color := repform.SummaryBand1.color;
caption := inttostr(recordcount); {observe}
end;
// also on groupfooterband - label
with tqrlabel.create(repform.groupfooterband1) do
begin
parent := repform.groupfooterband1;
autosize := false;
left := summaryleft;
top := summarytop;
width := 200;
alignment := taleftjustify;
color := repform.groupfooterband1.color;
font.style := [fsbold];
font.Color := clwhite;
caption := 'Number of records';
end;
// also on groupfooterband - sum expression
with tqrexpr.create(repform.groupfooterband1) do
begin
parent := repform.groupfooterband1;
autosize := true;
left := summaryleft + 200;
top := summarytop;
width := 100;
alignment := tarightjustify;
color := repform.groupfooterband1.color;
font.style := [fsbold];
font.Color := clwhite;
expression := 'count'; {observe, doesn't work on label}
resetafterprint := true;
end;
repform.SummaryBand1.Height :=
repform.SummaryBand1.Height + bandhi;
inc(summarytop, bandhi - 4);
end;
end;
// the fieldsums
// summeryband - label
with tqrlabel.create(repform.SummaryBand1) do
begin
parent := repform.SummaryBand1;
autosize := false;
left := summaryleft;
top := summarytop;
width := 200;
alignment := taleftjustify;
font.style := [fsbold];
color := repform.SummaryBand1.color;
caption := 'Total ' + fields[indatasetcounter].displaylabel;
end;
// summeryband - sum expression
with tqrexpr.create(repform.SummaryBand1) do
begin
parent := repform.SummaryBand1;
autosize := true;
left := summaryleft + 200;
top := summarytop;
width := 100;
alignment := tarightjustify;
font.style := [fsbold];
color := repform.SummaryBand1.color;
expression := 'sum(' + fields[indatasetcounter].fieldname + ')';
mask := (fields[InDatasetCounter] as tnumericfield).displayformat;
end;
// also on groupfooterband - label
with tqrlabel.create(repform.groupfooterband1) do
begin
parent := repform.groupfooterband1;
autosize := false;
left := summaryleft;
top := summarytop;
width := 200;
alignment := taleftjustify;
color := repform.groupfooterband1.color;
font.style := [fsbold];
font.Color := clwhite;
caption := 'Total ' + fields[indatasetcounter].displaylabel;
end;
// also on groupfooterband - sum expression
with tqrexpr.create(repform.groupfooterband1) do
begin
parent := repform.groupfooterband1;
autosize := true;
left := summaryleft + 200;
top := summarytop;
width := 100;
alignment := tarightjustify;
color := repform.groupfooterband1.color;
font.style := [fsbold];
font.Color := clwhite;
resetafterprint := true;
expression := 'sum(' + fields[indatasetcounter].fieldname + ')';
mask := (fields[InDatasetCounter] as tnumericfield).displayformat;
end;
end;
// groupheader - grouping expression
if (fields[indatasetcounter].tag in [1, 11]) then
begin
myhasgroup := true;
repform.groupheaderband1.expression :=
fields[indatasetcounter].fieldname;
with tqrdbtext.create(repform.groupheaderband1) do
begin
parent := repform.groupheaderband1;
top := 5;
color := repform.groupheaderband1.color;
font.color := clwhite;
font.Style := [fsbold];
parent := repform.groupheaderband1;
dataset := repform.report.dataset;
datafield := fields[indatasetcounter].fieldname;
end;
end;
end;
end;
// final touch
pagefooterstring := makefooter;
repform.report.bands.haspagefooter := (length(trim(pagefooterstring)) 0);
repform.report.bands.hastitle :=
(length(trim(genreportunit.repheader)) 0);
repform.report.bands.hassummary := myhassummary;
repform.groupheaderband1.enabled := myhasgroup;
repform.groupfooterband1.enabled := myhasgroup;
if repform.report.bands.hastitle then
begin
with tqrlabel.create(repform.titleband1) do
begin
parent := repform.titleband1;
color := repform.titleband1.color;
font.size := 16;
font.style := [fsbold];
caption := trim(genreportunit.repheader);
left := summaryleft;
aligntoband := true;
end;
end;
if repform.report.bands.haspagefooter then
begin
repform.pagefooterband1.frame.drawtop := true;
if genreportform.c6.checked then
begin
if genreportform.c6.checked then
with tqrsysdata.Create(repform.pagefooterband1) do
begin
parent := repform.pagefooterband1;
color := repform.pagefooterband1.Color;
font.name := 'small fonts';
font.size := 7;
left := trunc((repform.report.width * 0.90) - 65.0);
data := qrspagenumber;
end;
end;
with tqrlabel.Create(repform.pagefooterband1) do
begin
parent := repform.pagefooterband1;
autosize := false;
color := repform.pagefooterband1.color;
font.name := 'small fonts';
font.size := 7;
width := trunc((repform.report.width * 0.90) - 65.0);
left := 0;
alignment := tarightjustify;
caption := pagefooterstring;
end;
end;
end;
end;
procedure TGenReportForm.chClick(Sender: TObject);
begin
if ch.checked then
GenReportUnit.repheader := inputbox('Report', 'Title: ', GenReportUnit.repheader);
end;
procedure TGenReportForm.FormShow(Sender: TObject);
begin
ch.Checked := false;
end;
function makefooter: string;
begin
with genreportunit.GenReportForm do
begin
{the fields taken from the mainform are selfexplaining, I hope}
result := '';
if c1.Checked then result := result +
FormatDateTime(Shortdateformat, date) + ' - ';
if c2.Checked then result := result + iGetCurrentUserName + ' - ';
if c3.Checked then result := result + mainform.udl_edit.text + ' - ';
if c4.checked and (length(trim(mainform.currfile.Text)) 0) and
not (mainform.currfile.Text = 'Script filename')
then result := result + mainform.currfile.Text + ' - ';
result := uppercase(result);
if c5.Checked then result := result + mainform.showrecnmbr.Caption;
if c6.checked then
if length(trim(result)) 0
then result := result + ' - page '
else result := 'page ';
end;
end;
function iGetCurrentUserName: string;
var
Len: Cardinal;
begin
Len := 255;
SetLength(Result, Len - 1); { -1 for null terminator }
if GetUserName(PChar(Result), Len)
then SetLength(Result, Len - 1)
else result := 'LocalUser';
end;
end.
My previous programming environment was Foxpro. There is not much positive to say about Foxpro anymore. An exception might be the possibility for users to built every report they desired. I promoted this very much. Even with courses. However, looking back, 99.99 % of my users were simply to occupied to even try make their own reports. While I was programming the reportfunctionality as describe above, I realized how simple it is to build a adequate - that is: covering at least 80 % of the needs of my users - reporting system in Delphi.
Not really much more is required than presented above.
What should be added?
1. A second grouping level
2. The possibility to indicate on what footerbands the totals
should and should not be placed (Group 1, Group 2 and/or Summary Band)
3. The possibility 'to place' the fields:
- the order on the detailband (and the columnheaderband)
- Multiline detailband
- Concatination of fields
4. suppressing repeating fieldvalues
These additions are no big deal from a programmers point of view. The design of the userinterface where the user can indicate the place of the field on the detailband (addition 3) is a different story. Drag and drop is the only way but how do you do that in this case ? Any ideas?
Important fact is, that in my situation the dataset and the underlying SQL statement are always created at runtime. I don't have master/detail tables, but as a result I have repeating groups.
B.t.w. rate this article, because I like to know if I should stop to bother you with my writing or that there are people out there who appreciate the contents.