Averaging your Totals

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:

<DETAIL>
 <PERSON>Bloggs, Joe</PERSON>
 <LOCATION>XYZ</LOCATION>
 <BILLABLE_HOURS>1</BILLABLE_HOURS>
 <AVAILABLE_HOURS>1</AVAILABLE_HOURS>
</DETAIL>
<DETAIL>
 <PERSON>Bloggs, Joe</PERSON>
 <LOCATION>XYZ</LOCATION>
 <BILLABLE_HOURS>5</BILLABLE_HOURS>
 <AVAILABLE_HOURS>5</AVAILABLE_HOURS>
</DETAIL>

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)?>
Total Available
<?sum(current-group()/AVAILABLE_HOURS)?>
%
<?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!
Comments:

My son and daughter both love the dick and jane series. I remember beginning with them when i was little too!

Posted by Rigoberto Steffel on September 25, 2010 at 08:17 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today