How do you calculate percentage of hours worked at GroupHeader level

Toad Data Point
1

Hello,

I have a TDA report in which I need to calculate the percentage of hours worked by an employee for the assigned workstream, which is one of the many workstreams an employee may work for.

BACKGROUND INFORMATION:
During a four week period an employee is initially assigned to a workstream, but s/he may do work for other workstreams if a priority call for their special skills-set is agreed.

My SQL script obtains a summary of the total hours logged by employees for a given workstream during the period.

The DetailBand level of the report has columns:

AssignedWorkStream | Employee | Team | WorkStream | TotalHoursLogged

At the GroupHeaderBand1 level I have the AssignedWorkStream
At the GroupHeaderBand2 level I have the Employee

At Employee group level I have the following fields:

  1. Employee name
  2. Team
  3. Total Hrs logged against assigned workstream
  4. Actual BAU Hrs logged (excl Absence)
  5. Total w/s & BAU Hrs Worked
  6. Proj %
  7. Proj Variance (<0 is shortfall)
    n) …

Fields 3,4, and 5 are calculated fields with IIF() Functions to obtain the correct values. These three fields have the Summary Editor SUM function set to run at Group level.

My issue is with field 6 and 7.
Field 6 is a percentage calculation of the value in item 3 divided by the value in item 5.
Field 7 is a calculation based on (80% utilization subtracting the percentage value in item 6) multiplied by -1

In the Expression Editor have tried using the names of the calculated fields for 3 and 5.

calcAssignedWSHrs / calcTotalWS_BAUHrsWorked

I have even tried using the IIF() functions from both calculated fields.

Iif([AssignedWorkStream] == [WorkStream],[TotalHoursLogged],0.00) / Iif([AssignedWorkStream] == ‘BAU’,[calcBAUHrs],[calcAssignedWSHrs] + [calcBAUHrs])

But I am unable to obtain the correct percentage, even with the Summery Editor SUM function set and the formatting string = {0:0%}. With Summery Running at Group level.

Also tried selecting a different summary function [Avg, RunningSum, Percentage], but
still can not obtain the correct percentage.

Any help welcome.

Thank you in advance.

David

2

The calculcated expressions seems to only want to use real field values not other calculated fields. Have you tried creating each calculated expression based only on using your fields? It might be a large expression but should work better.

Debbie

3

OK I have adjusted the calculated expression for item 6 to be based only on my fields:

Iif([AssignedWorkStream] == [WorkStream],[TotalHoursLogged],0.00) / Iif([AssignedWorkStream] == ‘BAU’,Iif([WorkStream] == ‘BAU’,[TotalHoursLogged],0.00),Iif([AssignedWorkStream] == [WorkStream],[TotalHoursLogged],0.00) + Iif([WorkStream] == ‘BAU’,[TotalHoursLogged],0.00))

This still does not obtain the correct percentage?

I have the Summary Editor, Summary Function set on this table cell to SUM and Group level.

David