7 Replies Latest reply on Jul 15, 2015 6:33 AM by philmodjunk

    Trouble with percent calc in SubSummary Report

    jdevans

      Title

      Trouble with percent calc in SubSummary Report

      Post

      I've been following Chris Ippolite's Lynda lesson on Cross Tab reports using a pair of fields per column in order to create a crosstabular report. I have the whol number's subtotaling like they need to, and seems to work well, but I also have need of another number under each column, a percentage field.

      My Cross-Tab Report is on a layout based on Line_Items.  I may be missing something key in how this can possibly work, but I have each "row" of my subsummary sorted on employee name. The columns are the Accounts they worked on. The data under the column heading for each employee is Hours worked. I also want to create a Percentage field that instead of working as a fraction of the Account total, rather as a fraction of the EMPLOYEE's total FOR that account.

      Example:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                     

      Name

                     
                     

      Totals

                     
                     

      Acc1 Hrs

                     
                     

      Acc1 %

                     
                     

      Acc2 Hrs

                     
                     

      Acc2 %

                     
                     

      Joe Smith

                     
                     

      40

                     
                     

      30

                     
                     

      75%

                     
                     

      10

                     
                     

      25%

                     
                     

      Meg Lewis

                     
                     

      40

                     
                     

      10

                     
                     

      25%

                     
                     

      30

                     
                     

      75%

                     
                     

      Stan Fields

                     
                     

      40

                     
                     

      8

                     
                     

      20%

                     
                     

      32

                     
                     

      80%

                     
                     

      GrandTotals

                     
                     

      120

                     
                     

      48

                     
                     

      40%

                     
                     

      72

                     
                     

      60%

                     

      So, as you can see above, the Acc1, and Acc2 columns are being totaled downward to the Grand total, but the percentages are being calculated horizontally against the Employee's OWN total. Then the Grand Total Row works the same way, percentage-wise.
      Here is a look at the Relationship Graph. I have 5 base table-coccurrences, and 5 matching "working" TO's.

       

      How could I go about getting the percentages to calculate based on the employee's total, rather than the account's total, all on the same report? Or is this possible? I've looked at other methods of CrossTabular Design, but haven't found anything outside of Ippolite's work that I understand.

       

      Rel_graph.png

        • 1. Re: Trouble with percent calc in SubSummary Report
          philmodjunk

          If I understand this method (not how I set up crosstab reports, but that doesn't mean that it won't work), You need to take the subtotal of hours for an employee and use it to compute a percentage of total employee hours for each account column.

          The trouble here is that when you refer to a summary field in a calculation. that expression knows nothing of your layout design. It does not know about your sub summary layout part. It then evaluates as a "grand total" type of value based on your current found set, not a sub group that serves as the basis for a sub summary part.

          The solution is to use the getSummary function. GetSummary ( SummaryField ; BreakField ) will return a sub total from SummaryField when the current found set is sorted by BreakField. That means that "breakField" and your sub summary layout part's "when sorted by field" should be one and the same if you want this function to return the same value that you see in that summary field when it's placed inside that sub summary part.

          The one limitation for getSummary that does not exist for a sub summary layout part is that the summary and break fields must both be defined in the same table. That limitation sometimes requires a bit of creativity in order to get the correct results.

          • 2. Re: Trouble with percent calc in SubSummary Report
            jdevans

            Thanks I will try that. I had forgotten about Get(Summary).

             

            • 3. Re: Trouble with percent calc in SubSummary Report
              philmodjunk

              But it's GetSummary, not Get ( Summary ). wink

              • 4. Re: Trouble with percent calc in SubSummary Report
                jdevans

                Yes, I know. The "same table" limitation may be the party pooper in this case.

                • 5. Re: Trouble with percent calc in SubSummary Report
                  philmodjunk

                  It's easily worked around in most cases.

                  If your layout's sub summary is sorted by a field from a related table, you can get the same sort order by defining a calculation field in the layout's table that simply copies the value and data type of the field from the related table. You then update your sub summary part to refer to this field and sort by this field instead of the related field.

                  • 6. Re: Trouble with percent calc in SubSummary Report
                    jdevans

                    Yes, once you told me I needed all these fields in one table occurrence, I set out to do just that. I created an auto-enter by calculation field that copies the fk_employee_id from the parent table into my join table, then sort the Subsummary by that, and also set it as the break-field in my GetSummary definition. I ended up using the GetSummary function twice in the percentage calculation, since both my numerator and denominator are both Summary fields. But dang-it if it don't work. And it does... smiley

                    • 7. Re: Trouble with percent calc in SubSummary Report
                      philmodjunk

                      Be careful of auto-enter in this context. I specified an unstored calculation for a reason. If you use an unstored calculation, changes in the related table won't update the value of the field with the auto-enter calculation. So you get a faster sort, but risk having data out of synch.