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

example
dplowmanAsked:
Who is Participating?
 
itcoupleConnect With a Mentor Commented:
Hi

You can do conditional SUM I think it goes like that

=SUM(IIF(Fields!MyField.Value = "I do not want you", nothing, Fields!FieldtoSum.Value)

Regards
Emil
0
 
dplowmanAuthor Commented:
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)))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.