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

# Trouble with percent calc in SubSummary Report

### 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.

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

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.

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

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

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

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

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

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

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...

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

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.