Repeating Report

rbuser

Hi

I can't determine which group is the best to post this in, could someone
suggest which group would be better?

Basically I have been trying to do a report which refreshes periodically.
It wouldn't be good to have the search criteria displayed every time, so I
put the search criteria onto a Delphi 6 form, which populates temporary
tables and launches the report, which uses the temporary tables as its data.

The report displays first time no problem, but when it is time to refresh
the report, the program crashes. Below is the routine to refresh the
report:

procedure TForm1.Timer1Timer(Sender: TObject);
begin
MainDataModule.ppReport1.Reset;

if fileexists(tempdirectory + '\CONTRACT.adt') then
deletefile(tempdirectory + '\CONTRACT.adt');
ContractQuery.Close;
ContractQuery.Open;
ContractQuery.AdsCopyTable(tempdirectory + '\CONTRACT.adt');

if fileexists(tempdirectory + '\JOBSHEET.adt') then
deletefile(tempdirectory + '\JOBSHEET.adt');
JobsheetQuery.Close;
JobsheetQuery.Open;
JobsheetQuery.AdsCopyTable(tempdirectory + '\JOBSHEET.adt');

if fileexists(tempdirectory + '\PERIODS.adt') then
deletefile(tempdirectory + '\PERIODS.adt');
PeriodsQuery.Close;
PeriodsQuery.Open;
PeriodsQuery.AdsCopyTable(tempdirectory + '\PERIODS.adt');

if fileexists(tempdirectory + '\DIARY.adt') then
deletefile(tempdirectory + '\DIARY.adt');
DiaryQuery.Close;
DiaryQuery.Open;
DiaryQuery.AdsCopyTable(tempdirectory + '\DIARY.adt');

if fileexists(tempdirectory + '\SYS_TYP.adt') then
deletefile(tempdirectory + '\SYS_TYP.adt');
Sys_TypQuery.Close;
Sys_TypQuery.Open;
Sys_TypQuery.AdsCopyTable(tempdirectory + '\SYS_TYP.adt');

try
Screen.Cursor := crhourglass;
with MainDataModule.ppReport1 do
begin
MainDataModule.AdvantageReport.IsConnected := FALSE;
MainDataModule.AdvantageReport.ConnectPath :=
MainDataModule.AdsTempConnection.ConnectPath;
MainDataModule.AdvantageReport.IsConnected := TRUE;
Template.New;
Template.FileName := rptdirectory + '\Refreshing_Report1.rtm';
Template.LoadFromFile;
ShowAutoSearchDialog := FALSE;
PrinterSetUp.DocumentName := 'Rolling Report';
DeviceType := dtScreen;
Print;
end;
finally
Screen.Cursor := crdefault;
MainDataModule.AdvantageReport.IsConnected := FALSE;
end;
end;

If I use Reset in the third line, as shown, the report doesn't seem to free
the temporary files, and I get a file creation error on the first
AdsCopyTable. If I comment out all the AdsCopyTable lines the program runs
without errors but that is no good because the data is not being updated.
If I use Free instead of Reset in the third line, the AdsCopyTable lines
work fine, but I get an access violation on the line Template.New. If I
comment that out, I get access violations on subsequent lines. Please can
anyone suggest a solution?

--
Regards

Martin Houlton
P C Data Services

nicocizik

Hi Martin,

If you are just changing the data the report accesses, you do not need to
reload the same template before regenerating the report. Simply closing the
report, changing the data and reopening it should do the trick. It is also
possible to refresh the report from within the previewer (similar to how the
AutoSearch feature works). See the following example.

http://www.digital-metaphors.com/tips/RefreshReportAfterDataChange.zip

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

Thank you for your reply. The example you gave is interesting. However,
the requirements have changed and I now need to show three reports one after
the other in a slideshow. I downloaded the Developer's Guide from your
website and found the section 'Creating Reports in Code' on pages 83-85. It
seemed to me that this would be the solution because we can dynamically
create the second report after the first one has been freed, and so on.

I typed in the example from that section, exactly as printed, and it worked
fine. However we need the previewer to close as soon as the report is freed
on a timer, so I inserted the line:-
lReport.ModalPreview := FALSE; before the command lReport.Print; The
previewer closes but the program then halts with an access violation. Can
you tell me what I am doing wrong or what I am not doing? Thanks.

--
Regards

Martin Houlton
P C Data Services

nicocizik

Hi Martin,

I'm a bit unclear about when you are actually closing the preview form. You
need to be sure you are not freeing the report before the preview form has
completely closed. In other words, the following code....

ppReport.Print;
ppReport.Free;

Will cause an AV because the preview form has not fully destroyed before the
report object is freed. A work around for this is to creat another timer
and delay the call to free the report object.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico

I have made a lot of progress with this application. I have got rid of the
access violations using two timers, and the three reports display in turn.
Unfortunately when I first run the program the first report displays fine,
then it disappears and the second report takes approx. 14 minutes to appear.
The strange thing is that on the second and subsequent passes, the second
report appears in about 30 seconds. At first I thought the timers were at
fault, but I just changed the screen cursor to hourglass while the reports
are generating, and the hourglass cursor is visible for all 14 minutes the
second report is generating the first time.

I hope you will excuse me for posting a rather long 'code snippet' of the
routine to display the second report, and that you can shed some light on
this problem!

procedure TForm1.Report2(Sender: TObject);
var
lLabel1: TppLabel;
lLabel2: TppLabel;
lLabel3: TppLabel;
lLabel4: TppLabel;
lSysVar: TppSystemVariable;
lColumnDef: TppColumnDef;
lRowDef: TppRowDef;
lValueDef: TppValueDef;
begin
try
Screen.Cursor := crHourGlass;
lQuery := TAdsQuery.Create(Self);
lQuery.Name := 'tblCustomer';
lQuery.DatabaseName := 'AdsConnection1';
lQuery.SourceTableType := ttAdsADT;
lQuery.SQL.Add('SELECT Jobsheet.STATUS, Jobsheet.ENGINEER,
Jobsheet.JOBNO');
lQuery.SQL.Add('FROM Jobsheet Jobsheet');
lQuery.SQL.Add('INNER JOIN Contract Contract ON');
lQuery.SQL.Add('Contract.CONTRACT = Jobsheet.CONTRACT');
lQuery.SQL.Add('WHERE Jobsheet.STATUS IN
(''ENGREVISIT'',''PENDING'',''WAIT INSTR'',''WAIT PARTS'',''WAIT REP'')');
lQuery.SQL.Add('AND ( Contract.BRANCH = ''' + EditBranch.Text + ''')');
lQuery.SQL.Add('ORDER BY Jobsheet.STATUS, Jobsheet.ENGINEER');

lDataSource := TDataSource.Create(Self);
lDataSource.Name := 'dsCustomer';
lDataSource.DataSet := lQuery;
lDataPipeline := TppDBPipeline.Create(Self);
lDataPipeline.Name := 'plCustomer';
lDataPipeline.DataSource := lDataSource;

lReport := TppReport.Create(Self);

lReport.CreateDefaultBands;

lLabel1 := TppLabel.Create(Self);
lLabel1.Band := lReport.HeaderBand;
lLabel1.spLeft := 2;
lLabel1.spTop := 2;
lLabel1.Font.Size := 24;
lLabel1.Caption := 'Service Calls by Status - ' + EditBranch.Text;
lCrossTab := TppCrossTab.Create(Self);
lCrossTab.Band := lReport.DetailBand;
lCrossTab.UserName := 'CrossTab1';
lCrossTab.DataPipeline := lDataPipeline;
lCrossTab.InitAvailableDimensions;
lCrossTab.Stretch := True;
lCrossTab.Style := 'Standard';
lCrossTab.spLeft := 2;
lCrossTab.spTop := 2;
lColumnDef :=
lCrossTab.SelectColumnDef(lCrossTab.IndexOfAvailableDimension('STATUS'));
lRowDef :=
lCrossTab.SelectRowDef(lCrossTab.IndexOfAvailableDimension('ENGINEER'));
lValueDef :=
lCrossTab.SelectValueDef(lCrossTab.IndexOfAvailableDimension('JOBNO'));
lValueDef.CalcType := dcCount;
lCrossTab.OnFormatCell := ppCrossTab2FormatCell;

lLabel2 := TppLabel.Create(Self);
lLabel2.Band := lReport.FooterBand;
lLabel2.spLeft := 102;
lLabel2.spTop := 2;
lLabel2.Caption := 'Over limit - monitor';

lLabel3 := TppLabel.Create(Self);
lLabel3.Band := lReport.FooterBand;
lLabel3.spLeft := 327;
lLabel3.spTop := 2;
lLabel3.Caption := 'Risk - attention required';

lLabel4 := TppLabel.Create(Self);
lLabel4.Band := lReport.FooterBand;
lLabel4.spLeft := 577;
lLabel4.spTop := 2;
lLabel4.Caption := 'High Risk - immediate action required';

lSysVar := TppSystemVariable.Create(Self);
lSysVar.Band := lReport.FooterBand;
lSysVar.VarType := vtPrintDateTime;
lSysVar.spLeft := 2;
lSysVar.spTop := 30;
lSysVar := TppSystemVariable.Create(Self);
lSysVar.Band := lReport.FooterBand;
lSysVar.VarType := vtPageNoDesc;
lSysVar.Alignment := taRightJustify;
lSysVar.spLeft := (lReport.PrinterSetup.PageDef.spPrintableWidth -
lSysVar.spWidth) - 2;
lSysVar.spTop := 30;

lReport.ModalPreview := FALSE;

lReport.PreviewFormSettings.WindowState := wsMaximized;
if RadioGroup1.ItemIndex = 0 then
lReport.PreviewFormSettings.ZoomSetting := zsPageWidth
else
lReport.PreviewFormSettings.ZoomSetting := zsWholePage;
if RadioGroup2.ItemIndex = 0 then
lReport.PrinterSetup.Orientation := poPortrait
else
lReport.PrinterSetup.Orientation := poLandscape;

lReport.Print;
finally
Screen.Cursor := crDefault;
end;
end;

--
Thanks

Martin Houlton
P C Data Services

nicocizik

Hi Martin,

If you set your library path to RBuilder\Source and pause your application
during this 14 minute wait, are you able to determine what is taking so
long? If you execute the second report on its own, how long does it take to
generate?

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

If I add C:\RBuilder\Source to my library paths it stops my program from
working! Delphi says undeclared identifier: PreviewFormSettings and if I
comment out all references to that I get a message Property Outline Settings
does not exist. Without C:\RBuilder\Source in my library paths, when I
pause my program I just get a lot of CPU information that I can't make head
nor tail of.

If I execute the second report on its own, it takes about 14 minutes to
generate if it is displaying for the first time in this Windows session.
Subsequently, it only takes at most 30 seconds. I tried putting
lQuery.Active := False and lQuery.Active := True statements around the
generation of the query, but it makes no difference.

Sorry I couldn't get more information.
--
Regards

Martin Houlton
P C Data Services

nicocizik

Are you certain you have ReportBuilder installed in the root directory?
Typically the ReportBuilder source is located in the Delphi directory...
C:\Program Files\Borland\Delphi 6\RBuilder\Source\...

Which version of ReportBuilder are you using? When you add the source
directory to the library path, be sure you remove the entry pointing toward
the \RBuilder\Lib\... directory.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico

I set the library path to what you said, and removed RBuilder\Lib\. I put a
breakpoint into the code for the second report, and when I stepped over, the
problem seemed to be in the line lQuery.Active := TRUE; after defining the
SQL of the report. When I traced into, I found the function AdsExecuteSQL
in ACE was taking rather a long time.

I am using ReportBuilder v9.03.

--
Regards

Martin Houlton
P C Data Services

nicocizik

Hi Martin,

It sounds like something is taking a while in the DB. If you execute this
query directly from a Delphi app (without ReportBuilder) do you see it
taking a long time as well? If you execute the second report by itself does
it still take 14 minutes?

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico

I tried executing this query directly without reportbuilder and it still
takes 14 minutes. Yes, the second report by itself takes a long time. I
suppose I had better try a forum about the database. Thanks for all your
help.

--
Regards

Martin Houlton
P C Data Services