Calculating Variables from different sub report

rbuser

Hi,

I have a report as follows:

- Main Report
- Detail Band
- Sub Report 1
- Sub Report 2
- ppRegion.


In both of the SubReports I have TppDBCalc components in the Summary
band that calculate the sum of a certain field.

In the ppRegion section I have a number of tppVariable components, where
on their onCalc event I want them to obtain the sum from the tppDBCalc
components off the Sub Report bands.


It seems as though the components in the ppRegion are executing their
oncalc event before the SubReports are firing.

The code I'm using on the onCalc event is:

value := SubReport1ppTotal1.value;

value := SubReport2ppTotal1.value;

value := SubReport1ppTotal1.value - SubReport1ppTotal1.value;

I have set the CalcComponent on the ppRegion.tppVariable components to
the ppRegion component.

The ppRegion component is set to "shiftRelativeTo" the subreport above
it, and likewise the subreport above that.

I have set the datatype of the tPPVariable components to dtCurrency, but
still can't get this to work.

Can someone please help / advise?

Thanks & Regards

Adam.

nicocizik

Hi Adam,

Instead of using DBCalc components in your subreport, try using
TppVariables to calculate the sum(s) manually in the OnCalc event. Then
while updating the sum calculation for the subreport, update the main
report variable's value as well.

http://www.digital-metaphors.com/rbWiki/Delphi_Code/Calculations/How_To...Display_Subreport_Totals_in_the_Main_Report

rbuser

Hi Nico,

Thanks for that. I was just wondering if there's an alternative solution
as things are going to get quite messy behind the scenes.

In my original post you can see that there is a third variable on the
main page, that calculates off the first two variables values. (My
report has more than this going on, but this would be at it's most basic
level).

From your suggestion, to calculate the 3rd variable, I'm guessing I
would need different TppVariable.OnCalc events fired to add, subtract,
multiply, etc the values - but I won't know what those total values are
until they are all calculated to work with - making the calculations
much more complicated.

I did get the individual tppVariables to 'copy' the values from the
subreports by using the tppDBCalc.onPrint event to set the main
ppVariarables values, but I still couldn't calculate off those
tpPVariables for others.

(ie:

procedure TMyReport.ppVariable3Calc(Sender: TObject; var Value: Variant);
begin
value := ppVariable1.value - ppVariable2.value;
end;

As you can see, this is a simple subtraction, but as things get more
complicated on the main form with calculations (multiplications,
divisions, with additions and subtractions), I'm going to need to access
the values of those ppVariables directly to calculate others.

Thanks & regards

Adam.

rbuser

Hi Nico,

Sorry - I realised that my last post might be more confusing than
anything else, so I'll try to re-explain.

On my main report, I have numerous tppVariable components. I'm at a
stage where I can populate these components from values within the
SubReport bands. :-)

However, I have other tppVariable components I wish to calculate using
values from previous tppVariable components (on the same data band).


To keep it basic and example of this is:

ppVariable1 - Calculates from subreport 1. (Working OK)
ppVariable2 - Calculates from subreport 2. (Working OK)
ppVariable3 - Calculates from ppVariable1 and ppVariable2 values.

it's ppVariable3 that I'm having much difficulty with at the moment.

When I try to use ppVariable3's oncalc event to grab values from
ppVariable 1 and 2, it grabs the values from the previous record - not
from the current one. (It lags behind by one record each time).

I've tried changing ppVariable3's CalcComponent to teh detail band, to a
group footer, even to ppVariable2 - but it doesn't seem to make a
difference.

Thanks & Regards

Adam

rbuser

Doing lots of playing with the CalcComponent and CalcType events, the
only way I've been able to get this to behave is by setting the
CalcComponent to SubReport2, and the CalcEvent to veDataPipelineTraversal.

However if the 2nd Subreport doesn't print at all (there's only data in
the first Subreport, and none in the 2nd), it doesn't behave correctly -
most likely because it's not triggered in that instance.

There's got to be a magic combination that will get the tppVariable
component to calculate after the other information has been populated,
but I can't seem to find it.

nicocizik

Hi Adam,

Thanks for the further explanation. This is a classic timing issue of
variable calculations and traversal.

I recommend using a two pass report with look-ahead values to get the
correct values for your final calculations. Similar to the
example/article below.

http://www.digital-metaphors.com/rbWiki/Delphi_Code/Calculations/How_To...Use_a_Look_Ahead_Value_in_a_Calculation

I performed a quick test with a report similar to yours (2 subreports
with DBCalcs in the summary band and 3 variables in the main report's
summary band) and it functioned correctly. Below is a snip of my code...

function TForm1.GetLookAheadTotal(aCalc: TppDBCalc): Variant;
var
lsTotal: String;
begin

{retrieve the look ahead total and convert from Text to Float value.
note: the lookahead values are cached as Strings. The content of
the string
is exactly what is rendered to the page. Therefore it is a
formatted value }
lsTotal := aCalc.GetLookAheadValue(ppDBCalc1.GetLookAheadKey);

lsTotal := StringReplace(lsTotal, FormatSettings.CurrencyString, '',
[rfReplaceAll]);
lsTotal := StringReplace(lsTotal, FormatSettings.ThousandSeparator,
'', [rfReplaceAll]);

Result := StrToFloat(lsTotal);

end;

procedure TForm1.ppVariable1Calc(Sender: TObject; var Value: Variant);
begin
//Retrive sum from subreport 1
if ppReport1.SecondPass then
Value := GetLookAheadTotal(ppDBCalc1);
end;

procedure TForm1.ppVariable2Calc(Sender: TObject; var Value: Variant);
begin
//Retrieve sum from subreport 2
if ppReport1.SecondPass then
Value := GetLookAheadTotal(ppDBCalc2);
end;

procedure TForm1.ppVariable3Calc(Sender: TObject; var Value: Variant);
begin
//Add subreport 1 and subreport2 totals
if ppReport1.SecondPass then
Value := GetLookAheadTotal(ppDBCalc1) + GetLookAheadTotal(ppDBCalc2);
end;

rbuser

Hi Nico,

Thanks for that. Unfortunately I'm getting an Access Violation in
ppctrls.pas on line 4031:

while (liIndex < Count) and (Result = nil) do

Count appears to be undefined.

The report is set to twopass, and I have the if statement to only
execute this if ppReport1.SecondPass is true.

Thanks & regards

Adam.

rbuser

Sorry - I should probably add that this is with version 15.01 in Delphi
2007.

I've put a try / except around the call but nothing seems to get
populated. It seems the error is raised each time.

Best Regards

Adam.

nicocizik

Hi Adam,

Are you able to run the example in the article I referenced without an
error?

Please upgrade to RB 15.05 and retest. I am not getting any errors with
RB 16.01.

rbuser

Good Morning Nico,

I'm able to successfully run the example in your article on both 15.01
and 15.05.

I notice that your demo does not contain any subreports, or any
master/detail link - which is where I need to grab the values from in my
example, so I'm wondering if that's where the difference lies?

Just to clarify, my report is as follows:

- Main Report

- Detail Band - ppDBPipeLineMaster

- Sub Report 1 - ppDBPipeLineSub1 - (Contains ppDBTotal1)

- Sub Report 2 - ppDBPipeLineSub2 - (Contains ppDBTotal2)

- ppRegion. (Contains ppVarTotal1, ppVarTotal2, and ppVarTotal3).

(ppRegion is located in the Main Reports Detail Band)

the DBPipeline for Subreport1 and 2 are linked to the Detail Band's
datasource as a master/detail relationship.


I need:

ppRegion.ppVarTotal1 := ppDBTotal1 (OK)
ppRegion.ppVarTotal2 := ppDBTotal2 (OK)

ppRegion.ppVarTotal3 := ppDBTotal1 / ppDBTotal2

or

ppRegion.ppVarTotal3 := ppVarTotal1 / ppVarTotal2

I can get ppVarTotal1 and ppVarTotal2 to display the correct values
using numerous methods, but I can not get ppVarTotal3 to calculate
correctly - whether I attempt to calculate from ppVarTotal1 & 2's Value,
or otherwise.

In either instance, ppVarTotal3 refers to the prior Detailband's record
values.

In some cases, either SubReport1 or SubReport2 may not contain any
records at all depending on the Main Reports current record.

Thanks & Regards

Adam.

nicocizik

Hi Adam,

Please create a simple example that demonstrates this behavior (possibly
using the DBDEMOS database) and send it to support@digital-metaphors.com
in .zip format.

rbuser

Hi Niko,

Thanks. the DBDemos database doesn't contain the same relationship as my
database, so I've used a query to try and simulate a similar situation
to what I have.

I've emailed this through to support. Hopefully it's not too difficult
for you to find a solution. :-)

Thanks & Regards

Adam