1 2 Previous Next 15 Replies Latest reply on May 7, 2016 11:44 AM by justinc

    Subsummary Calculation

    satcowitz

      I have a summary field that totals hours. This number is displayed on subsummary parts.

       

      I want to multiply this by cost/hour (a field in a related table) and display on the same subsummary part. So I can have the total cost summarized at two different levels.

       

      I've tried using the GetSummary command but it returns blank. (I think because the fields are not in the same table.)

       

      I've tried other ways that use this summary field, but in doing so the number I get is always the total of the hours in the found set, not the subsummary.

       

      Seems like this should be simple. How can I get this to display the proper calculation?

       

      Thanks!

        • 1. Re: Subsummary Calculation
          erolst

          You already found the issue:

          satcowitz wrote:.

          I've tried using the GetSummary command but it returns blank. (I think because the fields are not in the same table.)

          Create calc fields that reference the related fields and use them as break & sort fields and in GetSummary()

          • 2. Re: Subsummary Calculation
            satcowitz

            OK. I created a reference to the related field in the current table, and put that reference in the GetSummary break field, but it still doesn't work. The layout parts are sorted by fields in different tables. When I redefine a layout part to sort by the new, referenced field in the current table, the part disappears. Do I need to create the layout again from scratch with all the break and sort fields in one table? (I hope not, as that would be a big step backwards.) Thanks!

            • 3. Re: Subsummary Calculation
              Oodua

              Can you make a sample of this file available?

              • 4. Re: Subsummary Calculation
                erolst

                satcowitz wrote:

                 

                OK. I created a reference to the related field in the current table, and put that reference in the GetSummary break field, but it still doesn't work. The layout parts are sorted by fields in different tables. When I redefine a layout part to sort by the new, referenced field in the current table, the part disappears.

                 

                You need to redefine the break field in GetSummary(), and change the sort order. You also need to redefine the sub-summary part setup so you have break field, sort field and sort order in sync.

                 

                To reiterate this for a field Rate::costPerHour

                 

                In your report layout table, create a calc field, as, say, cRate_CostPerHour; then change all references to the related field to this new calc field:

                 

                • the relevant sub-summary part's Setup dialog

                • the break field of your GetSummary() calc, AND

                • the sort order

                 

                satcowitz wrote:

                Do I need to create the layout again from scratch with all the break and sort fields in one table?

                You only need to bring over the fields that are used in a GetSummary() calculation; and you don't have to re-create the layout – simply change its sub-summary part(s) definition(s).

                • 5. Re: Subsummary Calculation
                  satcowitz

                  This is very helpful. Seems like the sort order is the problem.

                   

                  How can I change the sort order of the summary fields? I don't see how to do it in layout mode and when in browse mode I can change them, but the changes aren't permanent.

                  • 6. Re: Subsummary Calculation
                    erolst

                    satcowitz wrote:

                    How can I change the sort order of the summary fields? I don't see how to do it in layout mode and when in browse mode I can change them, but the changes aren't permanent.

                     

                    You need to understand how summary fields work.

                     

                    They don't have a definable sort order, because summary fields work on records that are broken down (or not) by sort fields; so you use a sort order to control these breaks …

                     

                    … and sub-summary parts only appear if their break field is included in the sort fields, because otherwise it wouldn't make sense for them to appear.

                     

                    Now if you place a summary field into different parts – e.g. a sub-summary part and a grand total – you will see that it yields different results: in the s-s part per group, in the gt for the entire found set.

                     

                    Note that all this works with any mix of native and related fields; it's when you want to manipulate these summary results programmatically that you need GetSummary(), and where both function arguments must be native fields.

                     

                    Note also that sub-summary parts are merely a visualization; to work with GetSummary(), all you need is a summary field, a found set and a sort order – no actual parts (sub-summary or otherwise) are required.

                    • 7. Re: Subsummary Calculation
                      satcowitz

                      Yes, that makes sense. But when I created the layout/report (this is a report (ah, perhaps that is important)) I was prompted to sort the subsummary parts by field and now it seems I'm stuck with those fields. Can those fields be changed?

                      • 8. Re: Subsummary Calculation
                        BillisSaved

                        Good afternoon satcowitz,

                         

                        I hope your day is going well. If I'm not mistaken, you should be able to double-click the sub-summary part label while you're in layout mode, and then choose a different break field. Have a great weekend!

                         

                        God bless,

                         

                         

                        Bill

                        • 9. Re: Subsummary Calculation
                          satcowitz

                          Thanks, Bill,

                           

                          I've done that. But when I click on the sort button (in browse mode) it shows that the fields I'm sorting on are the ones from the related table, not the ones in the current table. I can change these fields to the ones I want, but the changes don't get applied.

                          • 10. Re: Subsummary Calculation
                            electon

                            Take a look at this file.

                            It makes an assumption that there is only one record in the related table for cost per hour.

                            • 11. Re: Subsummary Calculation
                              justinc

                              You need to read Bill's comments again:  when in LAYOUT mode, DOUBLE CLICK the sub-summary part label on the left.  OR, you can right click on empty space in the Layout, and choose 'Part Setup', then click on the Sub-summary part that you want to change.

                               

                              Once in this window to change a sub-summary part, you can redefine what field the sub-summary is based on.  Or you can create a whole new sub-summary part, if you want to leave the other one in place.

                              Screen Shot 2016-05-06 at 16.49.33 .png

                              Screen Shot 2016-05-06 at 16.52.50 .png

                              • 12. Re: Subsummary Calculation
                                satcowitz

                                Yes, I've done that. I understand Bill's comments. I have the subsummary parts set up as he describes. When I do the those parts disappear from my layout. When I then click on the sort button in browse mode it shows me that the sort order is using the fields I originally chose when setting up the report, not the ones that I just changed in the subsummary part. When I then change these fields the report displays partially correctly, but oddly, only some parts. When I run the report again and look at the sort (browse mode) it has reverted back to the original fields. It seems I need to get those to change. At this point it's taking me longer to trouble shoot this problem than to just recreate the layout from scratch, but using the break fields in the current table from the start. It really seems there ought be a way to get the original sort order to change in a report, but that just doesn't seem to be the case.

                                 

                                Thanks to everyone for their input.

                                • 13. Re: Subsummary Calculation
                                  BruceRobertson

                                  "It really seems there ought be a way to get the original sort order to change in a report, but that just doesn't seem to be the case."

                                   

                                  In your report script, you need to choose the sort order you want and use that in the script that runs the report.

                                  It is very hard to imagine why you don't take control of this. If you know the sort order you need; use a script to perform that sort.

                                  • 14. Re: Subsummary Calculation
                                    satcowitz

                                    "In your report script"

                                     

                                    Yes! That's it. Some parts still not displaying properly, but the script needed to be changed.

                                     

                                    "It is very hard to imagine why you don't take control of this."

                                     

                                    Well, mostly because I'm still pretty new at this.

                                     

                                    Thanks for your help!

                                    1 2 Previous Next