Set threshold for value based on group sum

Question

SSRS 2008 R2

I am creating an employee utilization report for one of our SSMS databases. If an employee has entered 40 hours of work in our system for a regular work week then they are at 100% utilization. However, we do not want to include overtime because we do not want the under utilization for an employee that did not enter their time to be offset by someone who reported overtime.

For example:

Steve has 32 regulars and forgot to enter his time for one day. he is at 80% utilization for the week.

Joe has 48 hours this week. 40 regular hours + 8 hours of overtime. Currently, the report is listing him as 120% utilization. This makes the total utilization for the group 100%, Ideally, Joe would be 100% utilized and therefore not compensate for Steve.

Here is what I have tried so far:

=SUM(iff(sum(Fields!HOURS.Value, "LABORNAME") > 40), 40, SUM(Fields!HOURS.Value, "LABORNAME")))

But I receive the following error message:

The Value expression for the textrun ‘Textbox105.Paragraphs[0].TextRuns[0]’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

This is my first time posting to stack overflow so if I have mis-labeled or have not followed the correct format please let me know and I will edit the post.

Answer 1

You need to set your expression like the following and format as a percentage

=iif(sum(Fields!Hours.Value) >= 40 , 1 , SUM(Fields!Hours.Value)/40)