AnsweredAssumed Answered

Sub Summary Report Issues

Question asked by c.wagner1 on Jan 8, 2013
Latest reply on Jan 8, 2013 by philmodjunk


Sub Summary Report Issues



     Hi Guys -

     I have been fighting this for hours now and don't see the way to get it to work easily.  I know there should be an obvious answer but i'm not seeing it.

     I have a pretty typical set up for our company's invoicing system.  The three tables we are working with are Sales, ShippingInfo, and lineItems.  Sales have the unique key invoiceNumber.  ShippingInfo  (Which contains trackingNumber,  freightCompany, and freightCost) relates to invoiceNumber.  lineItems contains itemDescription, quantity, and extendedPrice.  The Sales table has two calculation fields, invoiceTotal which is sum(lineItems::extendedPrice) and shippingCost which is sum(shippingInfo::freightCost).  The Sales table also has 2 summary fields.  totalSummary = Total of invoiceTotal and freightSummary = Total of shippingCost.  Again a pretty typical set up.

     The report I am trying to generate should look something like the attached picture.

     What I did to create this is create a layout in the scope of lineItems.  It has the parts:

     1. sub-summary when sorted by ShippingInfo::freightCompany

     2. sub-summary when sorted by Sale::invoiceNumber

     3. body

     4. sub-summary when sorted by ShippingInfo::freightCompany

     5. Trailing Grand Summary

     sub-summary 1 has the field ShippingInfo::freightCompany

     sub-summary 2 has the fields Sales::invoiceNumber, Sales::invoiceTotal, and Sales::shippingCost

     the body has line the field lineItems::Description

     sub-summary 4 and the trailing grand summary both contain the 2 summary fields Sales::totalSummary and Sales::freightSummary

     In this set up, everything works right except for the summary fields.  They only display the information contained in the last Sale Record.  If I switch the scope of the layout to the Sales table, the summary fields display the correct information, but the body part only shows the first record from lineItems.