Percent Of Total Errors (0/1)

rbuser

We are attempting to write a report that lists Items by Location where
the report is to show each items revenue as a % of the Location Total
and are having a lot of difficulty getting the % of Total and
Cumulative % values to calculate correctly.
We are using Delphi 2007 and SQLDirect and these reports have been
tested under both ReportBuilder Enterprise versions 10.08 and 12.04.
We have been using RB and Delphi for many years and so did not expect
to have problems with this report. So please advise if we are missing
something obvious or if there are any work arounds for this problem,

I have attached a pdf file that shows the report as well as the .rtm
file.

Report to print is as follows:

Location Item Total % of Total Cumulative %
L01 1234 120.00 12% 12%
L01 1235 120.00 12% 24%
....
L01 1278 60.00 6% 100%

Location Total: 1,200.00 100%


We have read earlier news group articles on this topic and have
followed the recommendations where:
1. In Report.FirstPass at each Location LevelBreak Footer, we write
the location and revenue total away to a string list.
2. In Report.SecondPass at each Location LevelBreak Header, we
retrieve the total for the location into a variable and then use this
variable to calculate the % of Total and Cumulative %.
3. In the OnCalc event for the 2 variables that display the % of Total
and Cumulative % we calculate the values from the retrieved Location
Total..

What we are finding is that the first row of each location does not
calculate the values correctly. The Report has Two Pass enabled and we
have tried numerous ways to get this to work. What we eventually did
was to try and trace the sequence that the events are fired and have
found what may be the cause of the problem. The list of events in the
execute sequence are attached.

What we expected to find was:
ReportSecondPassStart
Location Level Break Header Before Print
Detail Band Before Print
Variable On Calc Events
Detail Band Before Print
Variable On Calc Events
Detail Band Before Print
Variable On Calc Events
Location Level Break Footer Before Print
Location Level Break Header Before Print
Detail Band Before Print
Variable On Calc Events
Detail Band Before Print
Variable On Calc Events
Detail Band Before Print
Variable On Calc Events
Location Level Break Footer Before Print

What we are finding is:
ReportSecondPassStart
Variable On Calc Events
Location Level Break Header Before Print
Detail Band Before Print
Detail Band Before Print
Variable On Calc Events
Detail Band Before Print
Variable On Calc Events
Location Level Break Footer Before Print
Variable On Calc Events
Location Level Break Header Before Print
Detail Band Before Print
Detail Band Before Print
Variable On Calc Events
Detail Bandk Before Print
Variable On Calc Events
Location Level Break Footer Before Print

During the SecondPass, at the Location Level Break Header, the detail
band OnCalc events are fired before the LevelBreakBeforePrint event
which means that the two % calculations that rely on the location
totals are been performed before the Location totals have been
retrieved. The following is a list of events that we recorded from the
report. Lines 3 and 4 should have been fired after line 7 and lines 14
and 15 should have fired after 19.

1. ReportOnEndFirstPass
2. ReportOnStartSecondPass
3. DetailLineOnCalc_varPercentOfTotal: LocationCode=EPOS,
ItemCode=1000826
4. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=EPOS,
ItemCode=1000826, Sub Total=109.05
5. L1BeforePrint: LocationCode=EPOS
6. L1BeforePrint: Retrieved from Location List: LocationCode=EPOS,
Total=176.78
7. DetailBeforePrint: LocationCode=EPOS, ItemCode=1000826, Sub
Total=109.05
8. DetailBeforePrint: LocationCode=EPOS, ItemCode=1000586, Sub
Total=109.05
9. DetailLineOnCalc_varPercentOfTotal: LocationCode=EPOS,
ItemCode=1000586
10. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=EPOS,
ItemCode=1000586, Sub Total=154.05
11. DetailBeforePrint: LocationCode=EPOS, ItemCode=FREIGHT, Sub
Total=154.05
12. DetailLineOnCalc_varPercentOfTotal: LocationCode=EPOS,
ItemCode=FREIGHT
13. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=EPOS,
ItemCode=FREIGHT, Sub Total=176.78
14. DetailLineOnCalc_varPercentOfTotal: LocationCode=FG,
ItemCode=1000152
15. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=FG,
ItemCode=1000152, Sub Total=700
16. L1BeforePrint: LocationCode=FG
17. L1BeforePrint: Retrieved from Location List: LocationCode=FG, 18.
Total=799.82
19. DetailBeforePrint: LocationCode=FG, ItemCode=1000152, Sub
Total=700
20. DetailBeforePrint: LocationCode=FG, ItemCode=1000001, Sub
Total=700
21. DetailLineOnCalc_varPercentOfTotal: LocationCode=FG,
ItemCode=1000001
22. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=FG,
ItemCode=1000001, Sub Total=790.73
23. DetailBeforePrint: LocationCode=FG, ItemCode=FREIGHT, Sub
Total=790.73
24. DetailLineOnCalc_varPercentOfTotal: LocationCode=FG,
ItemCode=FREIGHT
25. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=FG,
ItemCode=FREIGHT, Sub Total=799.82
26. DetailLineOnCalc_varPercentOfTotal: LocationCode=T01,
ItemCode=1002937
27. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=T01,
ItemCode=1002937, Sub Total=1450
28. L1BeforePrint: LocationCode=T01
29. L1BeforePrint: Retrieved from Location List: LocationCode=T01,
Total=1450
30. DetailBeforePrint: LocationCode=T01, ItemCode=1002937, Sub
Total=1450
31. DetailBeforePrint: LocationCode=T01, ItemCode=FREIGHT, Sub
Total=1450
32. DetailLineOnCalc_varPercentOfTotal: LocationCode=T01,
ItemCode=FREIGHT
33. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=T01,
ItemCode=FREIGHT, Sub Total=1450
34. DetailLineOnCalc_varPercentOfTotal: LocationCode=T04,
ItemCode=1002950
35. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=T04,
ItemCode=1002950, Sub Total=116.56
36. L1BeforePrint: LocationCode=T04
37. L1BeforePrint: Retrieved from Location List: LocationCode=T04,
Total=473.26
38. DetailBeforePrint: LocationCode=T04, ItemCode=1002950, Sub
Total=116.56
39. DetailBeforePrint: LocationCode=T04, ItemCode=1002951, Sub
Total=116.56
40. DetailLineOnCalc_varPercentOfTotal: LocationCode=T04,
ItemCode=1002951
41. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=T04,
ItemCode=1002951, Sub Total=216.79
42. DetailBeforePrint: LocationCode=T04, ItemCode=1003111, Sub
Total=216.79
42. DetailLineOnCalc_varPercentOfTotal: LocationCode=T04,
ItemCode=1003111
44. DetailLineOnCalc_varPercentCumulativeOnCalc: LocationCode=T04,
ItemCode=1003111, Sub Total=289.48
45. DetailBeforePrint: LocationCode=T04, ItemCode=1000020, Sub
Total=289.48

nicocizik

Hi Peter,

The events are firing as designed. I am unsure how your report is
designed (attachments are blocked from this newsgroup), but the proper
way to calculate a running percentage of a total value is by using the
method in the following article/example.

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

rbuser

Hi Nico,

I can't see how the events are firing as designed.

Did you actually read our message and look at the supplied example?

For our report, the expected approach was:
Report.FirstPass:
Save the LevelBreak Totals at the Level Break AfterPrint event.

Report .SecondPass
Retrieve the Level Break Total at the Level Break Before Print event.
Calculate the % of Total in the variable OnCalc events.

The above did not give the expected results. What we found was:
At the level break, the variable OnCalc events for the new row are
firing before the LevelBreak BeforePrint or DetailBand BeforePrint
events. On each row outside of the level break, the variable OnCalc
events fire after the DetailBand BeforePrint events (as expected). So,
as reported, the first row after each level break was showing the
incorrect information as it is doing its OnCalc before the Level
Break has been able to retrieve the reliant totals. Please see the
output of the event sequence provided.

Our frustration level is quite high - we have wasted a day trying to
find out why the reports we were working on were causing problems and
spent quite a while trying to provide this as feedback with recreated
example. Our reports are not embedded in Delphi, are self contained
and reliant on RAP and run from externally loaded .rtm files. The
error was able to be reproduced in the Report Builder designer for
both 10.08 and 12.04 versions. We use SQLDirect for our database and
use the DADE plugin as supplied. We have been using ReportBuilder for
many years now and have had a number of our Delphi developers review
this problem and the general consensus is that it does not look
correct.

We have managed to get our report working by not relying on the
variable OnCalc events. Will be happy to email you the .rtm file if
required.

On Thu, 21 Jul 2011 10:06:00 -0600, "Nico Cizik (Digital Metaphors)"

nicocizik

Peter,

I'm very sorry you are frustrated with this issue. Please understand
that simply describing a report as complex as yours does not give us
enough information to track down any issues that may arise or to
recreate it on our machines.

As I mentioned before, attachments are not permitted on these
newsgroups. If you would like to send us something, please send it in
.zip format to support@digital-metaphors.com.

What we really need is a minimal working example we can run on our
machines that uses only Delphi and ReportBuilder components.

rbuser

Hi Nico,

I have sent a zip file to the advised email address. I have created 2
simple reports using RB 12.05 and ADO and have attached the .rtm files
as well as related .pdf files that show the problems.

We are in the process of standardising on ReportBuilder and the
conversion of existing reports which reflect a % of Total and which
work fine in our other reporting engine but do not appear to work in
RB are causing some concern.

Any assistance will be greatly appreciated.

On Fri, 22 Jul 2011 08:03:15 -0600, "Nico Cizik (Digital Metaphors)"

rbuser

Nico,

Thanks very much for your response from last time.

I have applied the changes as listed in the RAP sample and am still
having difficulties.

There are 2 main calculations that need to be performed on the report:
1. Use the total from the current line and show this as a percent of
the total.
2. Show the above % as a running total %.

1 is working using the RAP but the first line on the new page from
pages 2 onwards shows a zero.
2 is working except on a new page the totals start again. The var
timing reset is end of report so the var shouldn't be resetting
itself.

I have also attempted to calculate 2 above using a DBCalc to show the
running total but am unable to calculate the running % from this.

This is driving me crazy and we are at a stage where we will have to
abandon our report conversion project as our system contains a number
of ranking reports as per the above which we can't get working.

The frustrating part is that we had most of the reports working except
where we found and reported that the page \ level break events were
not firing in the correct sequence - ie the OnCalc events for the
variables were firing before the Level Header BeforePrint and the
DetailBand OnPrint events.

Please advise how we can get the cumulative % showing correctly.

I have provided a sample of the report output showing the errors as
well as the .rtm file that we have used.

On Fri, 22 Jul 2011 08:03:15 -0600, "Nico Cizik (Digital Metaphors)"