7 Replies Latest reply on Aug 3, 2015 1:21 PM by jdxxs

    Calculation using sub summary fields

    jdxxs

      Title

      Calculation using sub summary fields

      Post

      Here's my (simplified) problem:

      I have a report that creates a sub summary of sales_cost and sales_price for each customer when sorted by customer_name. What I need are two more fields on each sub summary row that provide sales_profit (sales_price - sales_cost) and the sales_profit_percent (sales_price - sales_cost / sales_cost * 100).

      Thanks for any solutions.

      Jeff

        • 1. Re: Calculation using sub summary fields
          philmodjunk

          Sub summary fields do not exist, only summary fields. I'm not just nitpicking terms here. When you refer to a summary field in a calculation, the value returned (if summary field and calculation fields are defined in the same table), evaluates as though the summary is in a footer or grand summary part. It computes a "Grand Total" type value. The design of a layout to include sub summary layout parts has no effect on how a calculation field evaluates.

          But there is a function that you can use to get the sub total value shown in that sub summary layout part: GetSummary ( SummaryField ; BreakField ).

          The key detail is that your "BreakField" should be the same field as your "sorted by" field specfied for the sub summary layout part. On important difference between getsummary and subsummary layout parts is that the Break field used with GetSummary must be from the same table as the summary field.

          • 2. Re: Calculation using sub summary fields
            jdxxs

            Thanks, Phil.

             

            That led me to a partial solution.

            The problem is that my layout has buttons that allow the report to be sorted by six different fields - date, customer, unit cost, etc. I can create 6 different fields for the calculated value depending on the sort field if this is necessary but the the main problem is that one of the sort fields - customer name - comes from a different (related) table. Not being able to calculate and display these summary fields for each customer is MAJOR problem...

            Jeff

            • 3. Re: Calculation using sub summary fields
              philmodjunk

              You can work around this limitation by defining a calculation field in the same field as the summary field that copies the value of the related field. You then use this calculation field as your break field and include it in your sort order in place of the related field.

              A bit of a Kludge, but works.

              • 4. Re: Calculation using sub summary fields
                jdxxs

                That was a very useful tip. Just didn't occur to me..!

                Problem solved - kludgy or not..!

                Thanks.

                By the way, is it my machine or do others experience the cursor going to the top of the page and creating a new paragraph when the 'enter/return' key is pressed when posting here???

                Jeff

                • 5. Re: Calculation using sub summary fields
                  SteveMartino

                  By the way, is it my machine or do others experience the cursor going to the top of the page and creating a new paragraph when the 'enter/return' key is pressed when posting here???

                  It does it all the time when I try my one and only post for the day from an iPad.  Won't be a problem in a couple of weeks

                  • 6. Re: Calculation using sub summary fields
                    philmodjunk

                    This is a characteristic of using the Safari browser with this web site. It does not occur with other browsers. You can also put at least one character, such as a space to the right of the cursor and then it won't jump to the top when you hit return.

                    • 7. Re: Calculation using sub summary fields
                      jdxxs

                      Very weird - but thanks for the 'explanation'.

                      Jeff