3 Replies Latest reply on Jan 8, 2013 12:29 PM by philmodjunk

    Sub Summary Report Issues

    c.wagner1

      Title

      Sub Summary Report Issues

      Post

            

           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.

           Thanks,

           Chris

            

            

      report.png

        • 1. Re: Sub Summary Report Issues
          philmodjunk

               You need summary fields defined in LineItems instead of Sales.

          • 2. Re: Sub Summary Report Issues
            c.wagner1

                 Well, that makes sense, but I can't figure out how to get a summary field in lineItems to add the totals of a field in Sales.  (Sales::InvoiceTotal)  or for that matter get a summary field in lineItems to add the total of a field in ShippingInfo

                 Chris

            • 3. Re: Sub Summary Report Issues
              philmodjunk

                   One of the summary fields can compute the total of lineItems::extendedPrice.

                   For the shipping cost, you'd have to get quite a bit more creative.

                   Define a calculation field, cFreightCompany as:

                   ShippingInfo::FreightCompany

                   Another, cShippingCost as:

                   ShippingInfo::freightCost

                   Define a summary field sCount as the Count of some never empty field in LineItems.

                   Define a summary field sShippingCost as the total of cShippingCost.

                   Set up cShippingCostFraction as:

                   cShippingCost / GetSummary ( sCount ; cFreightCompany )

                   Modify your sub summary settings to use cFreightCompany instead of ShippingInfo::FreightCompany as the "sorted by" or "break" field.

                   For your grand total shipping cost, use this calculation:

                   sShippingCost / sCount

                   Then make sure that you sort your records by cFreightCompany instad of ShippingInf::FreightCompany.