Using crosstab values in labels
Hi,
I have done a program showing three crosstab reports in sequence. They are
all generated in code. The basic crosstabs work fine, I just need to add
some columns of labels to the right of the crosstabs, showing the results of
calculations that cannot be done within the crosstab. I have tried the
following within the main routine that defines one of the reports:-
for i := 1 to 26 do
begin
lLabel5 := TppLabel.Create(Self);
lLabel5.Band := lReport.DetailBand;
lLabel5.spLeft := 650;
lLabel5.spTop := 36 + 27 * i;
lLabel5.Caption := lCrossTab.Matrix.Value[1, i];
end;
This gives me an access violation and the debugger shows that the error
occurred in ppCTCell, function TppMatrix.GetValue(aColumn, aRow: Integer):
Variant; at the line lRowList := TList(FColumns[aColumn]);
Could anyone tell me how to do this?
--
Regards
Martin Houlton
P C Data Services
I have done a program showing three crosstab reports in sequence. They are
all generated in code. The basic crosstabs work fine, I just need to add
some columns of labels to the right of the crosstabs, showing the results of
calculations that cannot be done within the crosstab. I have tried the
following within the main routine that defines one of the reports:-
for i := 1 to 26 do
begin
lLabel5 := TppLabel.Create(Self);
lLabel5.Band := lReport.DetailBand;
lLabel5.spLeft := 650;
lLabel5.spTop := 36 + 27 * i;
lLabel5.Caption := lCrossTab.Matrix.Value[1, i];
end;
This gives me an access violation and the debugger shows that the error
occurred in ppCTCell, function TppMatrix.GetValue(aColumn, aRow: Integer):
Variant; at the line lRowList := TList(FColumns[aColumn]);
Could anyone tell me how to do this?
--
Regards
Martin Houlton
P C Data Services
This discussion has been closed.
Comments
I have some more information. When I switch the reports around and display
Report1, Report3 and then Report2, Report2 displays okay, but then
afterwards Report1 gives an access violation, as before. So Report3 is not
tripping up the system like Report2 is. The code for the extra column in
Report3 is:-
procedure TForm1.ppCrossTab3FormatCell(Sender: TObject; aElement:
TppElement; aColumn, aRow: Integer);
var Value1: variant;
Threshold: integer;
lDrawText: TppDrawText;
lDrawShape: TppDrawShape;
lDrawLine: TppDrawLine;
subtract, divide: integer;
result: real;
begin
if (aColumn = 1) and (aRow = 1) then
begin
lDrawShape := TppDrawShape.Create(lReport.Engine.Page);
lDrawShape.Width := ppToMMThousandths(76, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawShape.Height := ppToMMThousandths(aElement.CrossTab.Matrix.RowCount
* 26.73,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawShape.Top := ppToMMThousandths(lReport.HeaderBand.spHeight + 26,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawShape.Left := ppToMMThousandths(509, utScreenPixels, pprtVertical,
lReport.Printer);
lReport.Engine.Page.AddDrawCommand(lDrawShape);
lDrawLine := TppDrawLine.Create(lReport.Engine.Page);
lDrawLine.Width := ppToMMThousandths(76, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawLine.Top := ppToMMThousandths(lReport.HeaderBand.spHeight + 52,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawLine.Left := ppToMMThousandths(509, utScreenPixels, pprtVertical,
lReport.Printer);
lReport.Engine.Page.AddDrawCommand(lDrawLine);
lDrawLine := TppDrawLine.Create(lReport.Engine.Page);
lDrawLine.Width := ppToMMThousandths(76, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawLine.Top := ppToMMThousandths(lReport.HeaderBand.spHeight + 79,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawLine.Left := ppToMMThousandths(509, utScreenPixels, pprtVertical,
lReport.Printer);
lReport.Engine.Page.AddDrawCommand(lDrawLine);
lDrawLine := TppDrawLine.Create(lReport.Engine.Page);
lDrawLine.Width := ppToMMThousandths(76, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawLine.Top := ppToMMThousandths(lReport.HeaderBand.spHeight +
(aElement.CrossTab.Matrix.RowCount * 26.73) - 2,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawLine.Left := ppToMMThousandths(509, utScreenPixels, pprtVertical,
lReport.Printer);
lReport.Engine.Page.AddDrawCommand(lDrawLine);
lDrawText := TppDrawText.Create(lReport.Engine.Page);
lDrawText.Text := '% Older';
lDrawText.Font.Name := 'Arial';
lDrawText.Font.Size := 10;
lDrawText.Font.Color := clBlack;
lDrawText.Autosize := True;
lDrawText.Width := ppToMMThousandths(56, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Height := ppToMMThousandths(18, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Top := ppToMMThousandths(lReport.HeaderBand.spHeight +
59,
utScreenPixels, pprtVertical,
lReport.Printer);
lDrawText.Left := ppToMMThousandths(521, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Alignment := taRightJustify;
lReport.Engine.Page.AddDrawCommand(lDrawText);
end;
if (aRow > 1) and (aColumn = (aElement.CrossTab.Matrix.ColumnCount -1))
then
begin
lDrawText := TppDrawText.Create(lReport.Engine.Page);
divide := StrToInt(aElement.CrossTab.Matrix.Value[aColumn - 1, aRow]);
result := 100 * divide /
StrToInt(aElement.CrossTab.Matrix.Value[aColumn, aRow]);
lDrawText.Text := FormatFloat('0.00', result) + '%';
lDrawText.Font.Name := 'Arial';
lDrawText.Font.Size := 10;
lDrawText.Font.Color := clBlack;
lDrawText.Autosize := True;
lDrawText.Width := ppToMMThousandths(56, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Height := ppToMMThousandths(18, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Top := ppToMMThousandths(lReport.HeaderBand.spHeight +
31 + INT(26.8 * aRow),
utScreenPixels, pprtVertical,
lReport.Printer);
lDrawText.Left := ppToMMThousandths(521, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Alignment := taRightJustify;
lReport.Engine.Page.AddDrawCommand(lDrawText);
end;
if aRow = (aElement.CrossTab.Matrix.RowCount -1) then
begin
aElement.Color := clWhite;
exit;
end;
Value1 := aElement.CrossTab.Matrix.Value[aColumn, 1];
if Value1 = 'Older' then
Threshold := 20
else if aColumn = (aElement.CrossTab.Matrix.ColumnCount -1) then
Threshold := 40
else begin
aElement.Color := clWhite;
exit;
end;
if (aRow > 1) and (aColumn > 0) then
begin
Value1 := StrToInt(aElement.CrossTab.Matrix.Value[aColumn, aRow]);
if Value1 > (Threshold + 20) then
aElement.Color := $0000A0FF
else if Value1 > (Threshold + 10) then
aElement.Color := clYellow
else if Value1 > Threshold then
aElement.Color := clAqua
else
aElement.Color := clWhite;
end;
end;
--
Regards
Martin Houlton
P C Data Services
My first guess would be that the column you are trying to access is nil.
Where are you calling this code?
I would suggest using the crosstab events such as the OnGetValueText event
to assign the values of the separate labels. See demo 127 located in the
\RBuilder\Demos\Crosstabs\... directory for an example of using the crosstab
events.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
I was calling this code from the Report2 procedure, which is called by a
Timer event, not from any crosstab event. I have now moved it to the
OnGetValueText event, as follows:-
procedure TForm1.CrossTabGetValueTextEvent(Sender: TObject; aElement:
TppElement; aColumn, aRow: Integer; const aDisplayFormat: String; aValue:
Variant; var aText: String);
begin
if aColumn = 1 then
begin
if aRow > CurrentRow then
begin
CurrentRow := aRow;
//ShowMessage(aElement.CrossTab.Matrix.Value[aColumn, aRow]);
lLabel5 := TppLabel.Create(Self);
lLabel5.Band := lReport.FooterBand;
lLabel5.spLeft := 650;
lLabel5.spTop := 36 + 27 * aRow;
lLabel5.Caption := aElement.CrossTab.Matrix.Value[aColumn, aRow];
end;
end;
end;
And I have put the line:-
lCrossTab.OnGetValueText := CrossTabGetValueTextEvent;
in the Report2 procedure.
This doesn't give me any errors but unfortunately the labels don't appear on
the report. The procedure is definitely firing, because when I uncomment
the ShowMessage statement I get messages. These show the correct values for
column 1 of the crosstab. Could it be that when this event takes place, it
is too late to define labels on the report?
Obviously here I am only trying to copy column 1 of the crosstab to some
labels, but I will put the calculations in when I have got the problems
sorted.
Any help you can give would be much appreciated.
--
Regards
Martin Houlton
P C Data Services
Have you tried setting the label caption equal to the aText parameter?
There should be no need to access the value of the cell using the Element
parameter.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Yes, I tried that, and I have just tried it again. The result is as before,
no column of labels to the right of the crosstab. When I uncomment the
showmessage line the messages appear over the top of the print preview
screen, so I really do wonder whether it is too late to define labels.
--
Regards
Martin Houlton
P C Data Services
Yes, sorry, it is most likely too late to create TppLabels at this time.
One option would be to directly create drawcommands and place them on the
page where you need them. Something like the following...
uses
ppDrwCmd;
lDrawText := TppDrawText.Create(Report.Engine.Page);
lDrawText.Text := 'Mytext';
lDrawText.Font.Name := 'Arial';
lDrawText.Font.Size := 18;
lDrawText.Font.Color := clBlack;
lDrawText.Autosize := True;
lDrawText.Width := ??;
lDrawText.Height := ??;
lDrawText.Top := ??;
lDrawText.Left := ??;
Note that the ??'s are represented in thousandths of millimeters or microns
relative to the entire page so conversions will need to be made. See the
ppUtils.pas file for conversion utilities.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
I have changed the OnGetValueText procedure as follows:
procedure TForm1.CrossTabGetValueTextEvent(Sender: TObject; aElement:
TppElement; aColumn, aRow: Integer; const aDisplayFormat: String; aValue:
Variant; var aText: String);
begin
if aColumn = 1 then
begin
if aRow > CurrentRow then
begin
CurrentRow := aRow;
lDrawText := TppDrawText.Create(lReport.Engine.Page);
lDrawText.Text := aElement.CrossTab.Matrix.Value[aColumn, aRow];
lDrawText.Font.Name := 'Arial';
lDrawText.Font.Size := 18;
lDrawText.Font.Color := clBlack;
lDrawText.Autosize := True;
lDrawText.Width := ppToMMThousandths(36, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawText.Height := ppToMMThousandths(18, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Top := ppToMMThousandths(lReport.HeaderBand.spHeight + 36 +
(27 * aRow), utScreenPixels, pprtVertical, lReport.Printer);
lDrawText.Left := ppToMMThousandths(650, utScreenPixels, pprtVertical,
lReport.Printer);
end;
end;
end;
Unfortunately I still don't get a column of numbers to the right of the
crosstab. I don't get any error messages, it is just that nothing happens.
As before, when I put a showmessage in this procedure I get message boxes,
so it is definitely firing. I have declared lDrawText as a global variable.
What am I doing wrong? Thanks.
--
Regards
Martin Houlton
P C Data Services
Sorry, I forgot to include code in my example to actually add the
drawcommand to the page
lReport.Engine.Page.AddDrawCommand(lDrawText);
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Thanks, I think I am really close to finishing this project. There is just
one last problem.
Report1 doesn't have any extra columns. I have drawn the extra columns next
to the crosstab in Report2 and that works fine on its own. However, when it
flips over to Report3 it gives an access violation.
When I get this access violation, the debugger highlights the line:-
else if (FTemplate.DatabaseSettings.DataPipeline = aCommunicator) then
near the end of the TppCustomReport.Notify procedure in the ppClass unit.
This is not one of the usual display-time based access violations because I
have tried allowing 15 minutes and the same thing happens. Also when I
change the program to go straight from Report1 to Report3, Report 3 works
fine. So I assume Report2 is doing something that upsets Report3.
I found that using OnGetValueText I couldn't access the Grand Total values
of the crosstab, which I needed, so I put everything in the OnFormatCell
procedure (it is mixed up with some code to colour the cells):-
procedure TForm1.ppCrossTab2FormatCell(Sender: TObject; aElement:
TppElement; aColumn, aRow: Integer);
var Value1: variant;
Threshold: integer;
lDrawText: TppDrawText;
lDrawShape: TppDrawShape;
lDrawLine: TppDrawLine;
subtract, divide: integer;
result: real;
begin
if (aRow > 1) and (aColumn = (aElement.CrossTab.Matrix.ColumnCount -1))
then
begin
lDrawText := TppDrawText.Create(lReport.Engine.Page);
divide := StrToInt(aElement.CrossTab.Matrix.Value[1, aRow]);
result := 100 * divide /
StrToInt(aElement.CrossTab.Matrix.Value[aColumn, aRow]);
lDrawText.Text := FormatFloat('0.00', result) + '%';
lDrawText.Font.Name := 'Arial';
lDrawText.Font.Size := 10;
lDrawText.Font.Color := clBlack;
lDrawText.Autosize := True;
lDrawText.Width := ppToMMThousandths(56, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Height := ppToMMThousandths(18, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Top := ppToMMThousandths(lReport.HeaderBand.spHeight +
31 + INT(26.8 * aRow),
utScreenPixels, pprtVertical,
lReport.Printer);
lDrawText.Left := ppToMMThousandths(721, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Alignment := taRightJustify;
lReport.Engine.Page.AddDrawCommand(lDrawText);
end;
if aRow = (aElement.CrossTab.Matrix.RowCount -1) then
begin
aElement.Color := clWhite;
exit;
end;
Value1 := aElement.CrossTab.Matrix.Value[aColumn, 1];
if Value1 = 'ENGREVISIT' then
Threshold := 20
else if Value1 = 'PENDING' then
Threshold := 20
else if Value1 = 'WAIT INSTR' then
Threshold := 10
else if Value1 = 'WAIT PARTS' then
Threshold := 10
else if Value1 = 'WAIT REP' then
Threshold :=10
else if aColumn = (aElement.CrossTab.Matrix.ColumnCount -1) then
Threshold := 40;
if (aRow > 1) and (aColumn > 0) then
begin
Value1 := StrToInt(aElement.CrossTab.Matrix.Value[aColumn, aRow]);
if Value1 > (Threshold + 20) then
aElement.Color := $0000A0FF
else if Value1 > (Threshold + 10) then
aElement.Color := clYellow
else if Value1 > Threshold then
aElement.Color := clAqua
else
aElement.Color := clWhite;
end;
if (aColumn = 1) and (aRow = 1) then
begin
lDrawShape := TppDrawShape.Create(lReport.Engine.Page);
lDrawShape.Width := ppToMMThousandths(66, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawShape.Height := ppToMMThousandths(aElement.CrossTab.Matrix.RowCount
* 26.73,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawShape.Top := ppToMMThousandths(lReport.HeaderBand.spHeight + 26,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawShape.Left := ppToMMThousandths(654, utScreenPixels, pprtVertical,
lReport.Printer);
lReport.Engine.Page.AddDrawCommand(lDrawShape);
lDrawShape := TppDrawShape.Create(lReport.Engine.Page);
lDrawShape.Width := ppToMMThousandths(66, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawShape.Height := ppToMMThousandths(aElement.CrossTab.Matrix.RowCount
* 26.73,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawShape.Top := ppToMMThousandths(lReport.HeaderBand.spHeight + 26,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawShape.Left := ppToMMThousandths(718, utScreenPixels, pprtVertical,
lReport.Printer);
lReport.Engine.Page.AddDrawCommand(lDrawShape);
lDrawLine := TppDrawLine.Create(lReport.Engine.Page);
lDrawLine.Width := ppToMMThousandths(130, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawLine.Top := ppToMMThousandths(lReport.HeaderBand.spHeight + 52,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawLine.Left := ppToMMThousandths(654, utScreenPixels, pprtVertical,
lReport.Printer);
lReport.Engine.Page.AddDrawCommand(lDrawLine);
lDrawLine := TppDrawLine.Create(lReport.Engine.Page);
lDrawLine.Width := ppToMMThousandths(130, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawLine.Top := ppToMMThousandths(lReport.HeaderBand.spHeight + 79,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawLine.Left := ppToMMThousandths(654, utScreenPixels, pprtVertical,
lReport.Printer);
lReport.Engine.Page.AddDrawCommand(lDrawLine);
lDrawLine := TppDrawLine.Create(lReport.Engine.Page);
lDrawLine.Width := ppToMMThousandths(130, utScreenPixels, pprtVertical,
lReport.Printer);
lDrawLine.Top := ppToMMThousandths(lReport.HeaderBand.spHeight +
(aElement.CrossTab.Matrix.RowCount * 26.73) - 1,
utScreenPixels, pprtVertical, lReport.Printer);
lDrawLine.Left := ppToMMThousandths(654, utScreenPixels, pprtVertical,
lReport.Printer);
lReport.Engine.Page.AddDrawCommand(lDrawLine);
lDrawText := TppDrawText.Create(lReport.Engine.Page);
lDrawText.Text := 'Count';
lDrawText.Font.Name := 'Arial';
lDrawText.Font.Size := 10;
lDrawText.Font.Color := clBlack;
lDrawText.Autosize := True;
lDrawText.Width := ppToMMThousandths(36, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Height := ppToMMThousandths(18, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Top := ppToMMThousandths(lReport.HeaderBand.spHeight +
59,
utScreenPixels, pprtVertical,
lReport.Printer);
lDrawText.Left := ppToMMThousandths(675, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Alignment := taRightJustify;
lReport.Engine.Page.AddDrawCommand(lDrawText);
lDrawText := TppDrawText.Create(lReport.Engine.Page);
lDrawText.Text := '%';
lDrawText.Font.Name := 'Arial';
lDrawText.Font.Size := 10;
lDrawText.Font.Color := clBlack;
lDrawText.Autosize := True;
lDrawText.Width := ppToMMThousandths(36, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Height := ppToMMThousandths(18, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Top := ppToMMThousandths(lReport.HeaderBand.spHeight +
59,
utScreenPixels, pprtVertical,
lReport.Printer);
lDrawText.Left := ppToMMThousandths(741, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Alignment := taRightJustify;
lReport.Engine.Page.AddDrawCommand(lDrawText);
end;
if aColumn = (aElement.CrossTab.Matrix.ColumnCount -1) then
begin
if aRow > 1 then
begin
lDrawText := TppDrawText.Create(lReport.Engine.Page);
if aElement.CrossTab.Matrix.Value[aColumn, aRow] <> Null then
subtract := StrToInt(aElement.CrossTab.Matrix.Value[aColumn, aRow])
else
subtract := 0;
lDrawText.Text := IntToStr(subtract - 40);
lDrawText.Font.Name := 'Arial';
lDrawText.Font.Size := 10;
lDrawText.Font.Color := clBlack;
lDrawText.Autosize := True;
lDrawText.Width := ppToMMThousandths(36, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Height := ppToMMThousandths(18, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Top := ppToMMThousandths(lReport.HeaderBand.spHeight
+ 31 + INT(26.8 * aRow),
utScreenPixels, pprtVertical,
lReport.Printer);
lDrawText.Left := ppToMMThousandths(675, utScreenPixels,
pprtVertical, lReport.Printer);
lDrawText.Alignment := taRightJustify;
lReport.Engine.Page.AddDrawCommand(lDrawText);
end;
end;
end;
I hope you can help with this. Thanks in anticipation.
--
Regards
Martin Houlton
P C Data Services
I'm unclear what you mean by Report1, Report2, etc. Are you running these
reports in succession or are these subreports in a main report? How do
these reports relate to eachother and how are you loading them.
If you are loading templates you could be loosing event handlers.
--------------------------------------------
Article: Troubleshooting Lost Event Handlers
--------------------------------------------
Let's assume you have created a report in Delphi and assign an event
handlers to the OnPreviewFormCreate event of the report. The event is
generated by Delphi as:
procedure TForm1.ppReport1PreviewFormCreate(Sender: TObject);
You then save the report to an RTM file 'Report1.RTM.' The events are
stored as references only, and so the RTM contains:
object ppReport1: TppReport
.
.
OnPreviewFormCreate = ppReport1PreviewFormCreate
end
You then go on to work on a different report. Saving it with under then
name 'Report2.RTM'. Only this time, before you save the report you
change the report component name to: rptOrders. Delphi automatically
updates the event declaration for OnPreviewFormCreate event to:
procedure TForm1.rptOrdersPreviewFormCreate(Sender: TObject);
You then create two buttons on the form, one to load Report1 and
preview, the other to load Report2 and preview. When you run the app
and click Report1, you an error. This is because the Report1.RTM file
contains a reference to ppReport1PreviewFormCreate, a method which no
longer exists (at least with this name) in the form.
One answer is to load all your rtm files into the report component you
will be using for loading. Fix any errors, reassign any events that get
cleared. This will update your rtms to contain the proper event handler
names.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
I am running these three reports in succession, then starting again at the
first one. Each one is defined in code. The reports are independent of
each other. An example of how I am loading them is:-
procedure TForm1.Report1(Sender: TObject);
var
lLabel1: TppLabel;
lSysVar: TppSystemVariable;
lColumnDef: TppColumnDef;
lRowDef: TppRowDef;
lValueDef: TppValueDef;
MonthYear1: string;
begin
MonthYear1 := LeftStr(EditMonthYear.Text, 3) + '01' +
RightStr(EditMonthYear.Text, 5) ;
lQuery := TAdsQuery.Create(Self);
lQuery.Name := 'tblCustomer';
lQuery.DatabaseName := 'MainAdsConnection';
lQuery.SourceTableType := ttAdsADT;
lQuery.SQL.Add('SELECT DIARY.JOBNO, ');
lQuery.SQL.Add('CASE WHEN (MONTH(DIARY.MONTH_YEAR) = 12) THEN ''12
December''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 11) THEN ''11
November''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 10) THEN ''10 October''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 9) THEN ''09
September''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 8) THEN ''08 August''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 7) THEN ''07 July''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 6) THEN ''06 June''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 5) THEN ''05 May''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 4) THEN ''04 April''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 3) THEN ''03 March''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 2) THEN ''02 February''');
lQuery.SQL.Add('WHEN (MONTH(DIARY.MONTH_YEAR) = 1) THEN ''01 January''');
lQuery.SQL.Add('ELSE NULL END AS MONTH, ');
lQuery.SQL.Add('CASE WHEN (SYS_TYP.GROUP_CODE = ''A'') THEN ''Access''');
lQuery.SQL.Add('WHEN (SYS_TYP.GROUP_CODE = ''C'') THEN ''CCTV''');
lQuery.SQL.Add('WHEN (SYS_TYP.GROUP_CODE = ''E'') THEN ''Em Ltg''');
lQuery.SQL.Add('WHEN (SYS_TYP.GROUP_CODE = ''F'') THEN ''Fire''');
lQuery.SQL.Add('WHEN (SYS_TYP.GROUP_CODE = ''G'') THEN ''Gates''');
lQuery.SQL.Add('WHEN (SYS_TYP.GROUP_CODE = ''I'') THEN ''Intruder''');
lQuery.SQL.Add('ELSE ''Other'' END AS SYS_GROUP');
lQuery.SQL.Add('FROM PERIODS PERIODS LEFT OUTER JOIN DIARY DIARY ON
(DIARY.DUE = PERIODS.DESC1) '
+ 'INNER JOIN SYS_TYP SYS_TYP ON (SYS_TYP.CODE = DIARY.SYSTEM)');
lQuery.SQL.Add('WHERE DIARY.MONTH_YEAR <= ''' + MonthYear1 + '''');
lQuery.SQL.Add('AND ( DIARY.BRANCH = ''' + EditBranch.Text + ''')');
lQuery.SQL.Add('ORDER BY DIARY.MONTH_YEAR, PERIODS.LINENO, SYS_TYP.CODE');
lQuery.Active := TRUE;
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 := 'Overdue Maintenance Visits By Month - ' +
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('SYS_GROUP'));
lRowDef :=
lCrossTab.SelectRowDef(lCrossTab.IndexOfAvailableDimension('MONTH'));
lValueDef :=
lCrossTab.SelectValueDef(lCrossTab.IndexOfAvailableDimension('JOBNO'));
lValueDef.CalcType := dcCount;
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;
Timer1.Enabled := TRUE;
lReport.Print;
end;
This example routine doesn't have a OnFormatCell routine. I will appreciate
it very much if you can help with this.
--
Regards
Martin Houlton
P C Data Services
Where in this code does the AV occur? Try tracing into the RBuilder source
and see why the AV is occuring. Also, you may want to try altering your
second report to try to isolate the specific code that is causing the AV in
the third report.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
The program just started working! Thanks for all your help.
--
Regards
Martin Houlton
P C Data Services