Variables in Summary Band: calctype/resettype?

rbuser

Stymied:

I have variables in the summary band. They get calculated in their
oncalc events.

If I set resettype=veReportend and calctype=veReportend, the calc runs
at the right time, calculates correctly, and then prints zeros - I guess
it's getting reset to zero before it can print.

If I set resettype=veReportend and calctype=veTraversal, it calculates
sort of correctly, but the calc runs on every record.

I have also tediously tried a lot of other combinations (like resetting
at report start), to no avail.

How the heck do I set these so that it just calculates once at the end
of the report and doesn't set itself to zero before it prints?

This has to be much easier than it looks... I'm certainly overlooking
something obvious.

rbuser

PS: These do not need to be accumulated on each record/row, by the
way... they are just simple percentage calculations across the band.
Other dbcalcs on the band are counting records, and I need to show the
percentage of one over the other.

After 4 1/2 hours of trying everything I can think of - even placing a
bogus shape control at the end of the summary band and trying
desparately to get RB to wait to reset all my fields to zero on that
instead of before my variables print - I'm exactly nowhere.

And when I let the summary band calc on every freaking row, they're
wrong anyway the first time the report is run, but right the second
time. Somehow one of the dbCalcs is showing a different (smaller)
number than it prints on the report, even though a higher number is used
on the group band footer of the final group's footer.

Of course, in order to analyze this, since the summary band is now
firing on every row, I have to trace through about 10 firings before I
get to the summary band.

This is my worst reporting nightmare - the numbers are different based
on how many times you run the same report.

The only thing I can think of now is to ditch all the dbCalcs an
variables and replace them all with labels, and then do a new sql query
at the end of the report and just re-get the numbers.

Argggghhh... what a night.

rbuser

Setting it to two-pass doesn't stop RB from printing the wrong numbers
the first time around.

Setting the calc component of the variable to dbcalc doesn't do it either.

I guess I'll just tell the users to run it twice if they want the
numbers to be right...

I suppose RB has never been able to correctly calculate percentages
based on calculations across rows. This goes back years. I remember
being told that the calcorder etc. would make this possible.

For some reason, I supposed that this is such a common requirement that
it had been fixed and that the calcorder etc. and all the trigger
components now made it work.

I'm now stripping out all the dbCalcs and using the report merely as a
formatter, and doing all the real work in the bandevents, and then
stuffing the results into labels.

It's a shame because I had managed through a great deal of tedious work
and trial and error to get the group footers calculating percentages
across rows based on dbcalcs.

I just could not get the summary band to work. I think RB needs some
additional events like veSummaryBand like it has for the groups - that
would make it all work.

rbuser

I realize my frustration is obvious and yes, I'm pissed off at the moment.

This problem has caused me to miss a deadline, which is rare.

Also, I almost never end the day in failure-mode, and that's got me
bummed out as well.

I imagine I will find out tomorrow (unless DM is gone for the upcoming
US holiday-weekend, in which case next Tuesday) that either yes it can't
be done that way or that there's some hidden setting on a right-click
inspector, or a property that's mis-documented, or demo 9,983,323,322
shows how to do it.

For which I will be immensely appreciative I'm sure...

rbuser

Hi Richard,

for the case it may help I send you demo 'PercentageOfGroupTotal.zip' via
private email.

HTH,
Chris Ueberall;

rbuser

The example you sent is not even close to the problem I described, and I
think I covered it pretty well in the 4 messages above.

There is no calculation anywhere in my report that needs to do a
percentage in a group band or detail band based on the report total.
That's a cool thing but what I need is so much simpler than that yet I
can't get RB to do it.

1 - I can do percentages of a dbCalc using a variable in a group footer:

This is simply:

Total Count Total Count Red as
Group Red % of Group
dbCalcTot dbcalcRed variablePct

code on variable calc event is simply

if dbCalcTot.value = 0 then
variablePct = 0
else
variablePct = dbcalcRed.value/dbcalcTot.value ;

2 - This doesn't work in a summary band. I have spent about 7 hours
on this now and it still doesn't work. Just this one thing.

When I trace the code, it runs exactly once at exactly the right time
and produces in the code exactly the right result.

Then RB prints zeros on the report.

Because there is no veSummaryStart to use to trigger the calc and
because there is no veASummaryEnd to use to reset the values AFTER they
are printed, I'm stuck.

I tried many combinations of trying to make RB either reset the variable
values in the summary band after another component or make it calculate
after a component, but nothing works.

When I leave it as resettype=veReportEnd and calctype=traversal, the
thing fires on every row when it only needs to print at the end, but
what's far worse, the numbers are WRONG the first time the report is
printed (to screen) and then correct after that. 2-pass doesn't fix it.

I am actually reduced to thinking that when the user clicks run I could
run the report to a text file without a preview and then run it again
normally.

And that's a pretty gross hack just to make RB calculate some numbers.

If you have an example of caculating a simple percentage using a
variable calc based on dbcalcs all in the summary band, I'd love to see it.

digitalmetaphors

My first thought: don't use OnCalc. Just use the BeforeGenerate event of
the summary band and assign the Value property of the variable.

--
Cheers,

Tom Ollar
Digital Metaphors Corporation

rbuser

But in the beforegenerate event I would assume the dbCalcs will not have
calculated?

if dbCalcTot.value = 0 then
variablePct = 0
else
variablePct = dbcalcRed.value/dbcalcTot.value ;

rbuser

Tom Ollar (Digital Metaphors) wrote:

Hmmm... do you store values to variables when not using calc events? I
usually use labels together with band events. Is there any reason to
use one versus the other?

rbuser

FYI, where there's a will, there's a way:

I added to my query's select:

select ..., '1' as dummy_group

Then I added an outside (top)-level group to the report on dummy-group.
Since's always '1', it only has one group and one group-footer.

I just dragged my stuff from the summary band up there, then put the
properties to the same settings I'm using on the group footers:

calc component: ppGroup1
calc type: veOnBeforeGroupFooter
reset type: veReportEnd

and it all works.

This issue can be moved from a support issue to a wish-list or bug-fix,
depending on your point of view (mine is obviously bug-fix) for the next
version.

I most strongly believe there should be veBeforeSummaryBand and
veAfterSummaryBand events.

Then any variable in the summary band can easily be set:

calc component: ppSummaryBand
calc type: veOnBeforeSummaryBand
reset type: veOnAfterSummaryBAnd

and everything will work nicely and *intuitively*, without summary bands
being a special case - (special cases are the source of much programmer
grief and hair-pulling as evidenced here).

I guess that wraps it.

digitalmetaphors

Labels can be real killers for calculations, because they are not cached at
the start of each page.

Variable.Value (as long as it is not a string variable) is cached. This is
why it is good to use variables for calculations, but not labels.

DBCalcs save the numeric value at the start of each page, so of course,
these are fine too. Your calculation, as I understand it, doesn't even
cross pages though, so this is all academic.

From what I understand you have these two grand totals:

dbCalcTot dbCalcRed

and you want to calculate dbCalcRed as a percentage of dbCalcTot. In the
summary band BeforeGenerate you can code:

VariablePct.Value := dbCalcRed.Value / dbCalcTot.Value;

Make sure the variable DataType is set to Double and use the appropriate
percentage DisplayFormat. That should be it...

Cheers,

Tom Ollar
Digital Metaphors Corporation

rbuser

Because the other groups on this report already were using variables
with calcs, I decided to use the other work around (a dummy group) so
that the report summary would not be different style code than the other
group bands, but in the future I will not use oncalc but only band events.

Thanks for the tip on the efficiency of using variables - in the past I
have always stored to strings. I guess from what you're saying the
value of caching is if the user goes forward and back or previews and
then prints the report? But wouldn't the band's generate event fire
anyway? Or does it "know" it only has variables in it and no labels so
it doesn't do them?

TIA...

digitalmetaphors

The engine 'knows' it has variables in play, and restores them to their
previous value as of the start of each page. Then the variables calculate
again as records are traversed for that page.

An additional technique that some developers find useful: set
Report.CachePages to True. This causes the pages to be generated only once
(for OnePass) or twice (for TwoPass.) - which can make it easier to code
calculation related event handlers.

Definitely though: labels and (even worse) Delphi form variables. Real
killers because they cannot participate in the engine's caching scheme.

--
Cheers,

Tom Ollar
Digital Metaphors Corporation

rbuser

Thanks - I'll use all that info.