none
SUM with IIF RRS feed

  • Question

  • For some reason while trying to do a sum with iif....

    =SUM(IIF(Fields!InboundCalls.Value = 0 OR Fields!AgentFirstName.Value = "" OR Fields!AgentLastName.Value = "",0,Fields!InboundCalls.Value))

    this causes the dreaded #Error to occur, im not sure whats going on and from what i read the iif inside the sum is perfectly okay so does anyone know whats wrong with my custom erpression?

    Wednesday, August 6, 2008 8:59 PM

Answers

All replies

  • Hello,

    you can not aggregat. Make a View like this:

    Select InboundCalls , AgentFirstName, AgentLastName,

    CASE

    WHEN AgentFirstName = '' OR AgentLastName = '' THEN 0

    ELSE InboundCalls

    END as ClearedInboundCalls

    from table_test

    ---------
    You can now sum the Field ClearedInboundCalls


    HTH,

    BanditTreiber

    Thursday, August 7, 2008 6:47 AM
  • Hello,

    Try this

    =IIF(Fields!InboundCalls.Value = 0 OR Fields!AgentFirstName.Value = "" OR Fields!AgentLastName.Value = "",0,SUM(Fields!InboundCalls.Value))

    Shilpa (http://www.ggktech.com)
    Thursday, August 7, 2008 7:45 AM
  • @ShilpaReddy

    now you produce a logical error. IMHO


    Testdatas:

    InboundCalls

    AgentFirstName

    AgentLastName

    ClearedInboundCalls

    0

    A

    A

    0

    1

    A

    0

    2

    B

    B

    2

    4

    A

    A

    4

    5

    d

    0

    5

    c

    c

    5



    My sum:         11
    Your sum:      17
    Thursday, August 7, 2008 9:16 AM
  • Hello, Thank you all for the replies, however the query in theory is as simple as you put it but here is the real query...

     

    SELECT 
              SUM(IF(`CallType` = 2,1,0)) AS `InboundCalls`,  
              SUM(IF(`CallType` = 2,`DurationSeconds`,0)) AS `InboundCallSeconds`,  
              SUM(IF(`CallType` = 3,1,0)) AS `OutboundCalls`,  
              SUM(IF(`CallType` = 3,TIME_TO_SEC(TIMEDIFF(c1.`EndTime`,c1.`StartTime`)),0)) AS `OutboundCallSeconds`,  
              IF(`CallType` = 2,`TargetDN`,Extension ) AS `MyDN`,  
              IF(`CallType` = 2,`TargetFirstName`,(SELECT `PartyIDName` FROM `ConnectWHERE `CallTableID` = `c1`.`ID` AND PartyID=Extension LIMIT 1)) AS `AgentFirstName`,  
              IF(`CallType` = 2,`TargetLastName`,(SELECT `PartyIDLastName` FROM `ConnectWHERE `CallTableID` = `c1`.`ID` AND PartyID=Extension LIMIT 1)) AS `AgentLastName`  
              FROM `call` as c1 LEFT JOIN `queuecall` AS q1 ON  `q1`.`CallID` = `c1`.`CallID` WHERE (?Workgroups = ',,' OR ?Workgroups LIKE CONCAT('%,', `QueueDN` , ',%'OR `QueueDN`  IS NULLAND (`CallType` = 2 OR `CallType` = 3) AND (c1.`StartTime` BETWEEN ?StartDate AND ?EndDate OR q1.`StartTime` BETWEEN ?StartDate AND ?EndDate)  
              GROUP BY `MyDN` 

    The problem is that by doing it your way i would have to repeat the sub queries, and this is going on a database who has literally a thousand entries an hour. It has to be as lean as possible to avoid problems.
    Thursday, August 7, 2008 12:33 PM
  • And how about this:


    select InboundCalls,InboundCallSeconds,OutboundCalls,OutboundCallSeconds,MyDN,AgentFirstName,AgentLastName,
    CASE

    WHEN AgentFirstName = '' OR AgentLastName = '' THEN 0

    ELSE InboundCalls

    END as ClearedInboundCalls
    from(

    SELECT 
              SUM(IF(`CallType` = 2,1,0)) AS `InboundCalls`,  
              SUM(IF(`CallType` = 2,`DurationSeconds`,0)) AS `InboundCallSeconds`,  
              SUM(IF(`CallType` = 3,1,0)) AS `OutboundCalls`,  
              SUM(IF(`CallType` = 3,TIME_TO_SEC(TIMEDIFF(c1.`EndTime`,c1.`StartTime`)),0)) AS `OutboundCallSeconds`,  
              IF(`CallType` = 2,`TargetDN`,Extension ) AS `MyDN`,  
              IF(`CallType` = 2,`TargetFirstName`,(SELECT `PartyIDName` FROM `Connect` WHERE `CallTableID` = `c1`.`ID` AND PartyID=Extension LIMIT 1)) AS `AgentFirstName`,  
              IF(`CallType` = 2,`TargetLastName`,(SELECT `PartyIDLastName` FROM `Connect` WHERE `CallTableID` = `c1`.`ID` AND PartyID=Extension LIMIT 1)) AS `AgentLastName`  
              FROM `call` as c1 LEFT JOIN `queuecall` AS q1 ON  `q1`.`CallID` = `c1`.`CallID` WHERE (?Workgroups = ',,' OR ?Workgroups LIKE CONCAT('%,', `QueueDN` , ',%') OR `QueueDN`  IS NULL) AND (`CallType` = 2 OR `CallType` = 3) AND (c1.`StartTime` BETWEEN ?StartDate AND ?EndDate OR q1.`StartTime` BETWEEN ?StartDate AND ?EndDate)  
              GROUP BY `MyDN`  ) as t


    ---------------

    Hope this works....

    Thursday, August 7, 2008 1:19 PM
  •  
    willthiswork89 said:

    For some reason while trying to do a sum with iif....

    =SUM(IIF(Fields!InboundCalls.Value = 0 OR Fields!AgentFirstName.Value = "" OR Fields!AgentLastName.Value = "",0,Fields!InboundCalls.Value))

    this causes the dreaded #Error to occur, im not sure whats going on and from what i read the iif inside the sum is perfectly okay so does anyone know whats wrong with my custom erpression?



    ok your getting the #Error.... I just dealt with this myself.

    First off, the report viewer will evaluate both sides of an IF statement to see if it's possible.   Is your  "InboundCalls" field of a numeric type?  If it's not, you get the #Error


    wrap the inbound call value in this to convert it to a valid numeric value.
    CDbl(Fields!InboundCalls.Value)


    The first thing you need to go is get past this #Error problem, then you can properly evaluate the totals to see if they are coming out correctly.   I don't advocate doing totaling work like this in the SQL side when the application side can easily do it for you.


    Living my life at 123mph in 11.15 seconds
    • Edited by Blasty Thursday, August 7, 2008 2:07 PM edited
    Thursday, August 7, 2008 2:05 PM
  • Some things need longer.......



    This is the Solution:


    =SUM(cdbl(IIF(Fields!AgentFirstName.Value = "" OR Fields!AgentLastName.Value = "",0,Fields!InboundCalls.Value)))


    Monday, August 11, 2008 6:35 AM