SSRS Subtotal to exlude rows with a blank field

Posted on 2011-10-26
Last Modified: 2012-05-12
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%.

Open in new window

Question by:dplowman
    LVL 10

    Accepted Solution


    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)


    Author Closing Comment

    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)))

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now