Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sum with condition not working Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 7:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:24 PM
Points: 33, Visits: 180
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.
Post #1372733
Posted Monday, October 15, 2012 8:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 2:21 PM
Points: 132, Visits: 557
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
Post #1372741
Posted Monday, October 15, 2012 8:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:24 PM
Points: 33, Visits: 180
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
Post #1372745
Posted Tuesday, October 16, 2012 2:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 2:21 PM
Points: 132, Visits: 557
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
Post #1373075
Posted Wednesday, October 17, 2012 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:15 PM
Points: 6, Visits: 38
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))).
Post #1373911
Posted Monday, October 29, 2012 11:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:24 PM
Points: 33, Visits: 180
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.
Post #1378360
Posted Monday, October 29, 2012 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:24 PM
Points: 33, Visits: 180
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
Post #1378367
Posted Tuesday, October 30, 2012 4:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 2:21 PM
Points: 132, Visits: 557
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
Post #1378653
Posted Tuesday, October 30, 2012 7:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:24 PM
Points: 33, Visits: 180
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'
Post #1378755
Posted Tuesday, October 30, 2012 7:28 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:30 PM
Points: 572, Visits: 2,070
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.
Post #1378767
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse