April 25, 201213 yr 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 :)
Create an account or sign in to comment