BI Publisher RTF template - totaling a calculated field

Tkaser

Summary

BI Publisher RTF template - totaling a calculated field

Content

Hello,

First and foremost, I appreciate any help I may receive.

I am new to BI Publisher, our company has to prepare to leave Crystal Reporting behind and I have been teaching myself a bit.  I have a connected query XML datasource that provides me with information in order to do a weighted average revenue per hour calculation.  I am nearly there, just cannot get the totaling of the weighted average field to work.  Each individual logs time to projects.  Each project has a total of revenue, contractor expense, and hours associated.  In order to get the project level revenue per hour, I have a calculation: <?(REVENUE+CONTRACTOR) div TIME)?>.  We multiply this project level revenue per hour by the % of that individuals totals hours logged that went towards the individual project: <?(EMPLOYEE_TIME div sum(current-group()/EMPLOYEE_TIME))?>.

I made a third field that multiplies my rate by the project level revenue per hour: <?(EMPLOYEE_TIME div sum(current-group()/EMPLOYEE_TIME))*((REVENUE+CONTRACTOR) div TIME)?>, works wonderful on the line level.  When I attempt to put a sum field outside the table, I get multiple errors.  I thought it may be a grouping issue so I have played with groupings and can recreate individual line outputs outside the table.  Can't get the darn totaling to work.

When using <?xdoxslt:sum((EMPLOYEE_TIME div sum(current-group()/EMPLOYEE_TIME))*((REVENUE+CONTRACTOR) div TIME))?> I receive an error that 'sum' is not a recognized function.

When using <?sum((EMPLOYEE_TIME div sum(current-group()/EMPLOYEE_TIME))*((REVENUE+CONTRACTOR) div TIME))?> I get an error that it cannot convert the nodeset to numbers, despite the fact that there are no null values in my table.

I am at a loss right now and any guidance would be appreciated.

TK