7 Replies Latest reply on Aug 26, 2013 3:34 PM by leftear13

    GetSummary Issue

    leftear13

      Title

      GetSummary Issue

      Post

           I have a report that summarizes Total Supplier Sales (Total Sales), Cost of Goods (Label Net), Margin (Gross Margin), shipping costs (Ship Expense) and Net Margin, after shipping costs (Net Margin).

            

           Totals Sales, Label Net, Gross Margin and Ship Expense are summary fields while Net Margin is a calculation using the GetSummary function.

           Everything works great except the GetSummary field at the bottom which provides totals of the data by customer for each vendor.

            

           My report includes three sub-summary parts.  The first is sorted by VendorID, the second is sorted by CustomerID and the third is sorted by VendorID.  

            

           How can I get the calculation to calculate correctly when summarizing customer totals?

            

            

      Capture.JPG

        • 1. Re: GetSummary Issue
          philmodjunk

               GetSummary requires the same Break field as you specify for the sub summary (when sorted by) layout part.

               Assuming that the circled field is inside a sub summary part that is specifies VendorID as its break field, then the getSummary function call should also specify VendorID as its break field. From the numbers shown, I would guess that you currently specify CustomerID as the break field for this funciton and thus you are getting the total immediately above the circled value instead of a total based on the entire group with the same VendorID.

          • 2. Re: GetSummary Issue
            leftear13

                 Yes, the circled field (the field with the Get Summary Calculation) is in a sub-summary part that specifies VendorID as the break field. And, my getSummary function used the CustomerID as the breakfield.

                 I tried specifying the break field in the getSummary function with the VendorID, but when doing so, I lose my customer total (at the bottom) for respective VendorID

                 Here is what the report looks like now after changing the getSummary function to VendorID

                  

                  

            • 3. Re: GetSummary Issue
              leftear13

                   Right now I have the subsummary with the VendorID as the break field. The getSummary function also has the VendorID as the breakfield, but now the field is blank.

              • 4. Re: GetSummary Issue
                philmodjunk

                     Sounds like you should be using two different calculation fields with two different getsummary function calls--one for use in the VendorID based sub summary part and one for use in the CustomerID based sub summary part.

                • 5. Re: GetSummary Issue
                  leftear13

                       That's how I have it now, but no go, the field still comes up blank.  I have two fields with the same calculation except that one calls the customer ID (which is placed in the subsummary with break field of Customer ID) and the other that calls the VendorID (which is placed in the subsummary with break field of VendorID). 
                        

                       Just to give you additional insight on how the layout is setup:

                       ---Header

                       ---Subsummary (with Vendor ID and VendorName)

                       ---Subsummary (with customer ID, the four summary fields and the getSummary calc field calling the CustomerID)

                       ---Subsummmary (with the four summary fields and the getSummary calc field calling the VendorID). This is the part that totals all customers by Vendor.

                        

                       Weirdness... I am stuck on this one.  Any other ideas by chance?

                        

                       Thanks!

                        

                        

                  • 6. Re: GetSummary Issue
                    philmodjunk

                         What I have described should work. What are the "sorted by" fields for each sub summary part?

                         I have assumed that you have

                         Sub Summary (sorted by VendorID )
                         Sub Summary ( Sorted by CustomerID )
                         Sub Summary (Sorted by VendorID )

                         Have you checked to make sure that you have the correct "context" table occurrence specified for your calculation field? It should match that of the table occurrence name selected in Layout Setup... | Show Records from.

                         And if you have multiple occurrences of this table, make sure that you have also selected your field for this layout from the correct table occurrence or it may be empty.

                    • 7. Re: GetSummary Issue
                      leftear13

                           Nailed it, Phil!  The Vendor_ID wasn't in my Reporting table, which is the table used for this report.  The VendorID was from my Products table.  The Products table and Reporting Table are related via the ItemNo.   So, I created a VendorID field in the Reporting Table and related it to the Vendor_ID field in my Products Table.  Then, in the getSummary field calculation, I replaced the Vendor_ID from the Products table with the newly created VendorID in the Reporting table.  Lastly, I replaced Products::Vendor_ID with Reporting::VendorID when sorting by in the summary part. 

                           Boom!  The calculation works!  See what happens when you use the correct fields and  table occurances ? winklaugh

                            

                           Thanks a lot!