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
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.