Link to home
Start Free TrialLog in
Avatar of dplowman
dplowman

asked on

SSRS Subtotal to exlude rows with a blank field

I'll do my best to explain my situation. I have an ssrs report that calculations utilization per associate. Utilization is the total time spent on inbound and outbound calls divided by hours worked. My issue is that an associate may have hours worked, but may not have any inbound or outbound calls that day. So I do not want to include these hours in the subtotal utilization field. The code and screenshot are below. For instance, Grace Eliasen subtotal shoudl be 154%, not 77%.
=(sum(Fields!Inbound_Connect.Value)+sum(Fields!outbound_Connect.Value)+sum(Fields!Inbound_Waiting.Value)+sum(Fields!outbound_Waiting.Value)+sum(Fields!Inbound_ACW.Value)+sum(Fields!outbound_ACW.Value)+sum(Fields!Inbound_Deassign.Value)+sum(Fields!outbound_Deassign.Value))/(sum(Fields!hoursworked.Value)*60*60)

Open in new window

User generated image
ASKER CERTIFIED SOLUTION
Avatar of itcouple
itcouple
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dplowman
dplowman

ASKER

Thanks for the help. I altered the formula a bit, here is the result!

=iif(Fields!Inbound_Calls.Value+Fields!outbound_Calls.Value = 0, 0, (sum(Fields!Inbound_Connect.Value)+sum(Fields!outbound_Connect.Value)+sum(Fields!Inbound_Waiting.Value)+sum(Fields!outbound_Waiting.Value)+sum(Fields!Inbound_ACW.Value)+sum(Fields!outbound_ACW.Value)+sum(Fields!Inbound_Deassign.Value)+sum(Fields!outbound_Deassign.Value))/sum(iif(((Fields!Inbound_Calls.Value)+(Fields!outbound_Calls.Value)) = 0,nothing, (Fields!hoursworked.Value)*60*60)))