Post reply

Sum with condition not working

  • October 15, 2012 at 7:47 am

    #263882

    Hopefully someone here can help me solve this problem.

    What I have so far is a list with two tables in it. They are grouped on the same value, that way when this report is exported to excel both tables show up on the same tab.

    The expression I am having trouble with is in the second table. The table has the following groups:

    My expression looks like this:

    And the result I get is this:

    So in summary, what I am trying to do is total the sales by month and only for the years 2009 - 2011.

    Hopefully the pictures are not too hard to follow.

    Thanks for the help.

  • October 15, 2012 at 8:00 am

    #1549141

    For 2nd table, what is the expression for showing year where error occurred ?

    I mean there are 3 years from 2009 to 2011. So which of them are you trying to show in the year column ?

    And also the sales values in 2nd table for those 3 years don't match with the 1st table ?

    BI Developer
    SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
    Please visit... ApplyBI

  • October 15, 2012 at 8:05 am

    #1549144

    H4K (10/15/2012)

    For 2nd table, what is the expression for showing year where error occurred ?

    I mean there are 3 years from 2009 to 2011. So which of them are you trying to show in the year column ?

    And also the sales values in 2nd table for those 3 years don't match with the 1st table ?

    The year column has the following expression:

    =Sum(IIf(Fields!Year.Value > 2008 And Fields!Year.Value < 2012 , Fields!Sales.Value, 0))

    It is poorly titled, the column should be called '2009-2011 Sales'. I want to get the expression working first before I worry about properly titling my columns 🙂

  • October 16, 2012 at 2:27 am

    #1549407

    I don't know if it works or not. But you can try to create a calculated field with the 'iif' expression.

    And include sum(new calculated field) into the textbox...

    BI Developer
    SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
    Please visit... ApplyBI

  • October 17, 2012 at 9:43 am

    #1550112

    I believe you are having a data type mismatch issue. The sales value is a decimal and specifying "0" is an integer. You should be able to change it to Sum(IIf(Fields!Year.Value > 2008 And Fields!Year.Value < 2012 , Fields!Sales.Value, 0.00)) or else explicitly cast both values such as Sum(IIf(Fields!Year.Value > 2008 And Fields!Year.Value < 2012 , CDbl(Fields!Sales.Value), CDbl(0))).

  • October 29, 2012 at 11:02 am

    #1553816

    Tim Paulsen (10/17/2012)

    I believe you are having a data type mismatch issue. The sales value is a decimal and specifying "0" is an integer. You should be able to change it to Sum(IIf(Fields!Year.Value > 2008 And Fields!Year.Value < 2012 , Fields!Sales.Value, 0.00)) or else explicitly cast both values such as Sum(IIf(Fields!Year.Value > 2008 And Fields!Year.Value < 2012 , CDbl(Fields!Sales.Value), CDbl(0))).

    No luck. Still does the same thing.

  • October 29, 2012 at 11:10 am

    #1553823

    H4K (10/16/2012)

    I don't know if it works or not. But you can try to create a calculated field with the 'iif' expression.

    And include sum(new calculated field) into the textbox...

    I changed the expression to this:

    =IIf(Fields!Year.Value > 2008 And Fields!Year.Value < 2012 , Sum(Fields!Sales.Value), Sum(0.00))

    And this is what I got:

    Just to do some more testing I removed the second condition. I changed the expression to this:

    =Sum(IIf(Fields!Year.Value > 2008, Fields!Sales.Value, 0.00))

    And this is what I get:

    This is just not making any sense to me :crazy:

  • October 30, 2012 at 4:15 am

    #1554063

    I think "0.00" did provide some part of the solution but you need to look for NULL's and handle them which is giving you the error, i guess.

    Use the function IsNothing to handle NULL.=:

    iif( IsNothing(Fields!Sales.Value), 0.00, Fields!Sales.Value)

    Create a calculated field with the above expression.. just to check ...

    BI Developer
    SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
    Please visit... ApplyBI

  • October 30, 2012 at 7:16 am

    #1554143

    H4K (10/30/2012)

    I think "0.00" did provide some part of the solution but you need to look for NULL's and handle them which is giving you the error, i guess.

    Use the function IsNothing to handle NULL.=:

    iif( IsNothing(Fields!Sales.Value), 0.00, Fields!Sales.Value)

    Create a calculated field with the above expression.. just to check ...

    I added the suggestion above and I got the same results 🙁

    I need to get this report finished so I just modified my query. I added this line to my select statement:

    SUM(CASE WHEN YEAR(INVOICEDATE) > 2008 AND YEAR(INVOICEDATE) < YEAR(GETDATE()) THEN LINEAMOUNT ELSE 0 END) AS 'Past Sales'

  • October 30, 2012 at 7:28 am

    #1554154

    Following H4K suggestion can you handle the NULLs i the script instead if in the expression? if not maybe break the expression down and handle the NULL in a calculated field. then use the calculated fields in you expression.

  • October 30, 2012 at 7:30 am

    #1554155

    Also what is the error that you get in the Vis Studio output window when you run the report?

  • October 30, 2012 at 7:31 am

    #1554156

    Steven.Howes (10/30/2012)

    Also what is the error that you get in the Vis Studio output window when you run the report?

    There was no error.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply