Sub-Summary Report Help

[Ron Cates]

Ah the fun part. Describing my db and my problem. Well here goes...

We print calendars with advertising on them.

As the image below shows, i have a pretty typical invoice type solution although it has grown to be a monster, so in the screenshots I erased all the irrelivant tables.

Note: Although my actual TOs don't have underscores in the names I used underscores in the write up below for ease of reading.

The task;

Create a report that shows a summary of all the invoices ( tickets in my db ) that were paid after the calendar was printed. Also show percentages of total tickets and total amounts.

So far;

I pulled the job FK from tickets into a field in Payments so I could create a TO with a direct relationship between jobs and payments where the payment date is greater than the date printed. ( Payments_After_Print )

Used an existing TO of Tickets ( Job_Breakdown_Tickets_Active ) that is related to Jobs by Job Id and using an unstored calc to populate a multikey that is a list of what we consider to be "Active" status. ( I currently use this TO to pull summary fields for total_tickets, total_amount, total_paid etc. of "Active" tickets only, from the Job_Breakdown_Tickets_Active TO and display them on the Jobs layout ).

Created a subsummary report based on the new Payments_After_Print TO.

Sub-Summary part when sorted by JobName

Put summary fields from Payments_After_Print for total tickets paid, and total amount.

Put summary fields from Job_Breakdown_Tickets_Active giving me total active tickets and amounts per job

---- Everything is golden up to this point --------------------

Created a Trailing Grand Summary

Put the same summary fields in the Grand Summary but the summaries from Job_Breakdown_Tickets_Active do not total...

The problems;

1. Need to figure out how to show percentages.

2. In the Grand Summary part I get grand totals for the summary fields from Payments_After_Print

But Not for the summary fields from Job_Breakdown_Tickets_Active

( as illistrated in the report screen shot )

What I've Tried;

For percentages

I tried creating calculation fields in Payments_After_Print for the percentages. The calcs being for example,

Payments After Print::total_tickets / Job_Breakdown_Tickets_Active::total_tickets

In the Sub-Summary part I could see the correct totals for Payments After Print::total_tickets and Job_Breakdown_Tickets_Active::total_tickets but the calc field showed results that were way off.

I tried changing the "Evaluate this calculation from the context of:" in the Specify Calculation dialog to Payments_After_Print - No Good ( no change )

I tried using getSummary - Payments After Print::total_tickets / GetSummary ( Job_Breakdown_Tickets_Active::total_tickets ; jobName ) - No good ( blank )

For Grand Totals

Tried creating a calc field using getSummary

total_active_tickets = GetSummary ( Job_Breakdown_Tickets_Active::total_tickets ; jobName ) - No Good ( blank )

My Thoughts

I am perplexed at why the summaries from Job_Breakdown_Tickets_Active show accurately in the Sub-Summary part but do not total up in the Trailing Grand Summary?

I suspect that the answer to getting this done is dependant on the correct usage of getSummary() but I can't seem to wrap my head around it?

To Summarize The Task;

I need the report below to include percentages representing the percent of tickets paid after printing

I need the Trailing Grand Summary to show the Grand Totals and percentages

Ok, I think that about lays it out. Let me know if more information is needed and thanks for any assistance rendered :)