Home General
New Blog Posts: Merging Reports - Part 1 and Part 2

Percent Of Total Errors (0/1)

edited July 2011 in General
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

Comments

  • edited July 2011
    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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited July 2011
    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)"
  • edited July 2011
    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.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited July 2011
    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)"
  • edited August 2011
    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)"
This discussion has been closed.