Averaging your Totals
By Tim Dexter-Oracle on Jul 10, 2008
Another totaling question on the forum a few days ago from user645621- not your average (excuse the pun, you'll understand once you read the question) totaling question I must say. Check out the other 'total' posts here.
I am trying to do something a bit obscure with BIP. My XML is something like:
Note the data has been flattened because we need to regroup in different ways. I am regouping as such Location (Master) then by Person (Detail) What I need to do is find the Billable % at Person Group which is Billable Hours / Available Hours (<?sum(current-group()/BILLABLE_HOURS) div sum(current-group()/AVAILABLE_HOURS)?> - which is OK, but at Location level I need the average of the billable % at the location level. I have tried assigning the calculation for the billable %age to a variable and then dividing by the number of records but this has not proven to work. My thoughts where to create a variable to hold the total pct for a location and then divide by the number of records (another variable). The problem is assigning the calculation to a variable ie <?xdoxslt:set_variable($_XDOCTX, ‘PCT_TOT', (sum(current-group()/BILLABLE_HOURS) div sum(current-group()/AVAILABLE_HOURS)) + xdoxslt:get_variable($_XDOCTX, ‘PCT_TOT’))?> Results in an error - An internal error condition occurred in the Template Builder
Some others have contributed to the thread, namely Srini but our mysterious user was not happy with the proposed solutions. I jumped in with two others - might not be right either and they raise another question I would be glad to hear from you on ... read on.
Assuming you have :
<?for-each-group:DETAIL;./LOCATION?> LOCATION Table of details - <?for-each:current-group()?> PERSON BILLABLE_HOURS AVAILABLE_HOURS % <?end?> <?end for-each-group?>Is this enough? Total Billable
<?sum(current-group()/BILLABLE_HOURS) div sum(current- group()/AVAILABLE_HOURS)?>If you really want the average of the percentages per group then: Create a variable just after the for-each-group LOCATION
<?xdoxslt:set_variable($_XDOCTX, 'AvgHrs', 0)?>putting it here resets it to zero at the beginning of every LOCATION group.Then in the percentage field you need:
<?BILLABLE_HOURS div AVAILABLE_HOURS?> --- show the line value <?xdoxslt:set_variable($_XDOCTX, 'AvgHrs', xdoxslt:get_variable($_XDOCTX, 'AvgHrs')+ (BILLABLE_HOURS div AVAILABLE_HOURS) )?>For the second part we set the variable value to itself + the current rows % value In the totals section
<?number(xdoxslt:get_variable($_XDOCTX, 'AvgHrs')) div count(current-group()/PERSON)?>Get the variable value (force it to be numeric) and div by the total number of folks in the location group Now in the data I created based on the data given, the first calculated total came out to the same value as the variable method. I think on a bigger data set it might be different ... you decide!