Formula running Totals 2

[DevMerl]

Hi there.
I have a problem creating a total, using a formula.
I am no crystal expert, but have some experience of it in the past.
The problem is this.

The data.
AccountID BookingID SubBookingID PAX
123 1 1 5
123 2 1 3
123 2 2 3

I want to sum the pax count, but as you can see in the last row of data, the pax count is repeated for each booking, even if is the same booking with another subbookingid.
I can get a formula field to display the data correctly, (i.e. for account 123 and booking 2, the total is 3. and the account pax total is 8.(NOT 5+3+3, BUT 5+3)
but only in the drill down views. I cannot create a running total based on the formula field.
If anyone has any suggestions???
I will continue to update this today, as it may not be entirely clear as to what the problem is.
I have tried a few things, but with no joy.
Many thanks for any help, or suggestions.
Regards
Merlin

 

[DevMerl]

I am now looking at using global variables to handle the totals of the pax number, which once again works fine in the drill downs, but i am thinking that as the global varible is not populated until after the records have been read. This causes an issue as i canno therefore display the total (sum of pax), as there is no data until the report has fully loaded.
Thanks for any help.
Regards
Merlin

 

[lbass]

I'm assuming you are grouping on {table.AcctID}. Add another group on {table.bookID}, insert summaries on the detail section, and drag them into the group header. Then create three formulas:

//{@reset} to be placed in the group 1 (AcctID) header:
whileprintingrecords;
numbervar sumpaxtot := 0;

//{@accum} to be placed in the group 2 (Booking ID} header:
whileprintingrecords;
numbervar sumpaxtot := sumpaxtot + sum({table.pax},{table.bookID})

//{@display} to be placed in the group 1 footer:
whileprintingrecords;
numbervar sumpaxtot;

If you want a report level total, insert another variable, e.g., grtot, into the accumulation formula, but not in the reset, and create a display formula that uses grtot instead of sumpaxtot, and place it in the report footer.

-LB

 

[DevMerl]

Thanks lBass for your suggestions there,
That has given me a running total, and shown a diferent approach to the problem. I have formula which sums the pax total a we want it (only sums one paxcount value from a booking, if there are multiple Sub-bookins.

whileprintingrecords;

global numbervar lPAXCOUNT;

if onfirstrecord then
lPAXCOUNT:= tonumber({spRPTBranches;1.PAX});

if {spRPTBranches;1.BookingRef} <> previous({spRPTBranches;1.BookingRef})
then
lPAXCOUNT:= lPAXCOUNT + tonumber({spRPTBranches;1.PAX});

,
which works a treat, calculates the total correctly, but i can only get the total to be displayed at the group footer, not on the header as needed!

Thatks for your post.
Merlin

 

[lbass]

You could save the report under a different name and then import it as a subreport. You could then suppress all sections in the subreport except the group footer and place it in the group header of the main report. The subreport would be linked to the main report on the group.

This will slow the report, but I'm not sure there is another way around it, if you need the results in the group header.

-LB

 

[DevMerl]

Thanks, lBass.
I will have a go.
One other option that i am looking at, is changing the shape of the data, which will remove the problem. (all the data is from a sp, so i will fix the pax issue.
I will remove the pax count from all records bar the first on, within the booking group, so the data will be thus:
AccountID BookingID SubBookingID PAX
123 1 1 5
123 2 1 3
123 2 2 0

- see the 0 pax count
- rather than

AccountID BookingID SubBookingID PAX
123 1 1 5
123 2 1 3
123 2 2 3

This, although not the best situation, will have to do for now.
Thankfully, this is a stand alone reporing system, with a data table being populated every night - so I can add an update procedure, which will sort the problem.
This is an issue I will have with nearly all the reports, so it is worth the change.

Not Ideal, but will have to do.

Thanks All

Merlin²

 

[DevMerl]

HI all, Still working on it..... Heck
The data update choice, while it does work, there is a massive amount of data (1.7m+ rows), which gets replaced entirely every night.... Could still consider it but not good.
Have done it using sub reports, but it is now uslessly slow..... For each record in group 1 it goes and does a full table scan, (which is why did not experiment with sub reports at first.....), so the report, instead of being fast and furious, is instead slow and clumpy....
Will continue on, attacking it from as many angles as possible.
Ammending the data still could be the way to go... would have snappy reports at least.
Have not used cr for four years now, and you wonder why?



Im trying.... will get there in the end......