AnsweredAssumed Answered

Trouble with percent calc in SubSummary Report

Question asked by jdevans on Jul 14, 2015
Latest reply on Jul 15, 2015 by philmodjunk

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

Outcomes