Summary field not working as it should

[JamieChriso]

Hi - please be gentle, I have never posted anything on a forum before and am a newbie! I am having problems with a report and think it is a simple solution.

I have a report for a number of different tours and summarise the amount of people on each tour:

CountILPax = If (TourNameRefined = "Island Life" ; PaxNumbers)

 

I then have a summary field:

SummaryILPax = Total of CountILPax

 

I have a calculation to determine the number of Island Life tours on a particular day:

CountILTours = Case ( GetSummary ( SummaryILPax ; TourNameRefined ) ≤ 6 ; 1 ; GetSummary ( SummaryILPax ; TourNameRefined ) ≥ 18 ; 2)

 

The CountILTours calculation works in the Sub Summary Part which is sorted by TourNameRefined

 

In the Grand Summary Part I have a summary field:

SummaryILTours = Total of CountILTours

 

This field does not calculate as I want it to - it should be the total number of Island Life tours over the report but it comes up with a random number.

 

I also tried to put a calculation field in there to see if that would work:

GetSummaryILTours = GetSummary ( SummaryILTours; TourNameRefined )

But that only calculated the number of Island Life records in the report

 

I have attached a copy of the layout.

 

Thanks in advance for any help you can provide.

ILReport.pdf

[David Head]

Hi Jamie

It would be useful to know your data structure -- what tables and relationships you have.

And also an example of what you actually want as the report.

You may be drawing the report from the wrong table but I cannot tell/

[JamieChriso]

Hi David,

Thanks for your reply. All but one of the fields are in the same table, which is the field Booking Status (shown as travelled on the pdf). Booking Status is from the Bookings table which is related d to this current table (Tour Day), by booking number. Basically, what I want to be able to do is produce a report to look at forward tours so that we can manage our stock ordering more efficiently.

I hope this is enough information.

thank you

[David Head]

Hmmm, not really enough information there yet.

 

So you want a report summarised by date?

 

And you want to know how many pax for each date -- which you have working.

 

For this field: CountILPax = If (TourNameRefined = "Island Life" ; PaxNumbers)

If all records are for Island Life, then surely CountILPax = "Total pax of above tour type on date" ?

 

For this field: SummaryILPax = Total of CountILPax

This makes sense and seems to be working.

 

For this field: CountILTours = Case ( GetSummary ( SummaryILPax ; TourNameRefined ) ≤ 6 ; 1 ; GetSummary ( SummaryILPax ; TourNameRefined ) ≥ 18 ; 2)

This seems to work as written but does not account for numbers between 7 and 17?

 

What happens if you put the CountILTours field into the Trailing Grand Summary?

 

It would be helpful if you mocked up a report that you wanted. It seems there is a lot of redundant/repeated data in the report you have shown.

 

Cheers, David