5 Replies Latest reply: May 15, 2014 9:58 AM by Mike Urbonas RSS

    Adding up values from Appends

    mpschein _

      My report prints 1 or more detail lines with summary info above, which I am grabbing using the Append template.  There is a numeric field in the summary info that I want to sum for the entire report, and it's a field that is NOT included in the detail below, so I can't ignore it and simply sum the info from the detail lines.

       

      So the problem is that the value from the summary appears with each detail line, and when I sum up that field, my sum is way too high because I only want to include one value per summary level, not each detail line.  Easy fix?

        • Adding up values from Appends
          Steve Caiels

          Hmm.  I've not tried this and I don't have a report handy to try it on, so give me a "virtual slap" if it doesn't work.

           

          Try building the summary and double click on the summed field.  Then change the calculation to average.  I think this should do it.

           

          If not,add the count as a summary field, then at least you can take it into Excel and divide the sum by the count.

           

          Regards

          Steve

          Datawatch Europe Ltd

          • Adding up values from Appends
            mpschein _

            Thanks for the suggestion.  Actually, I had already done that, but the grand total was giving me an average instead of the actual grand total, so I dropped that idea.  It's dollars and therefore critical to have the total dollars for the field.

             

            I was getting set to export to Excel, and if no one else has an idea, that's where I'll head.  Thanks again.

            • Adding up values from Appends
              Mike Urbonas

              Hang on...give this a try:

               

              Set up your summary as follows:

               

              • Add the Key Field you want

              • Include the append value in question as a SUM summary field

              • Include "count" as a SUM field (this will count the number of times your Key Field appears in your Table View)

              • Include the append value in question again[/b] as a summary field.  Double click on this and change the Field Calculation to a Ratio:

                   -- Click on the "Ratio" radio button

                   -- Click on the drop-down menu and select "count"

                   -- Click OK all the way out

               

              You should now have a Summary with:

               

              (Col. 1) The Key Field you want,

              (Col. 2) The append value you want (but repeated/summed several times which we don't want),

              (Col. 3) the count column (count of # times the Key Field appears in Table)

               

              and finally...

               

              (Col. 4) "Ratio" column dividing col. 2 by col. 3...SHOULD be the figures you are looking for!

               

              Please reply if this works...I think I solved it

              :cool:

               

              Regards

              Mike

              • Adding up values from Appends
                mpschein _

                Worked exactly as you laid it out; pure genius, yet it looks so simple.  Thank you so much (except now I have to get back into that project!).

                • Adding up values from Appends
                  Mike Urbonas

                  Originally posted by mpschein:

                  Worked exactly as you laid it out; pure genius, yet it looks so simple.  Thank you so much (except now I have to get back into that project!).[/b][/quote]:D