8 Replies Latest reply on Oct 24, 2012 4:22 PM by brentjohn

    Subtracting Summary Types on a Report

    brentjohn

      Title

      Subtracting Summary Types on a Report

      Post

           I am creating a Balance Sheet (Assets = Liabilities + Owners Equity).  I am using a report and have the Assets listed first, followed by Liabilities.  On the “Sub-summary” Part, I have a Summary type calculation to total Assets and Liabilities.

           I need to subtract the Summary Liabilities from the Summary Asset, thus providing the Owners Equity.

            

           How can I do this?  Attached is the Report

      Report.jpg

        • 1. Re: Subtracting Summary Types on a Report
          philmodjunk

               If you refer to a summary field in a calculation, you get the "grand total" instead of the "sub total". The way around this is to use the getSummmary function to refer to the summary field. Use the same "break" field in the getsummary function as you specified as the "when sorted by" field for your sub summary layout part.

               Get Summary ( Summary field1 ; WhensortedByField ) + GetSummary ( Summary field2 ; WhenSortedByField )

               would then give you the sum of the subtotals for these two summary fields.

          • 2. Re: Subtracting Summary Types on a Report
            brentjohn

                 Phil,

                 Sorry I posted on another posting.

                 You responded:

                 "Make sure of the following details:

                                The field should beof type calculation, don't use a number field with an auto-entered calculation

                                The "break" field in the get summary function is the same exact field that you specified as the "sorted by" field in your sub summary part

                                The records must be sorted in an order that includes the break field."

                                 

                                That worked.

                                However, I am still confused on your original post:

                                "Get Summary ( Summary field1 ; WhensortedByField ) + GetSummary ( Summary field2 ; WhenSortedByField )"

                                How do I seperated the Summary field "Amt_Total" (the summary field I have on my database) into the 2 Summary fields you listed (Summary field 1, Summary field 2)?

                  

            • 3. Re: Subtracting Summary Types on a Report
              philmodjunk

                   I assumed that you had one summary field for each. (I can't see anything that tells me much in your uploaded screen shot.)

                   Do you have just one summary field and you sort your records into two groups, one for assets and one for liabilities? And you need to subtract these two sub totals? Is there a field with a value that identifies each record as either an asset or a liability? If so, what values appear in that field?

                    

              • 4. Re: Subtracting Summary Types on a Report
                brentjohn

                     Phil,

                     I should have been clearer - yes I have one summary field and sort my records into 2 groups: one for assets and one for liabilities.  And yes, I would like to subtract these 2 groups.  The value that identifies whether it is an asset or liability is "1" & "2".

                • 5. Re: Subtracting Summary Types on a Report
                  philmodjunk

                       I see more than one sub summary group in your layout. Do you have multiple groups here, each in pairs of asset, liability grouping?

                       If not, then you need a grand total difference between the two...

                       In either case, define a pair of calculation field to separate the amounts by asset and liability.

                       If ( TypeField = 1 ; amountField )

                       If ( TypeField = 2 ; amountField )

                       Then you can define a pair of summary fields to compute the total of these two calculation fields.

                       For a grand total difference, you can just subtract the two summary fields. For sub total differences (if you have pairs of asset and liability groups in your report), use the get summary function as described previously.

                  • 6. Re: Subtracting Summary Types on a Report
                    brentjohn

                         Phil,

                         With a little tweaking, it WORKS GREAT!!!

                          

                         Can I push my luck?  Is it possible to create a Report in Columns?

                         Ex: Left-side Assets  /  Right-side Liabilities.

                    • 7. Re: Subtracting Summary Types on a Report
                      philmodjunk

                           It's possible, but will take a significant amount of restructuring to do.

                           Will there be corresponding rows in the report?

                           This then becomes what is known as a "cross tab" report and can be set up with portals (often filtered) and possibly some sub summary layout parts.

                      • 8. Re: Subtracting Summary Types on a Report
                        brentjohn

                             Phil,

                             I figured as much - no worries.

                             Again, thank you for all your help!!!