SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sum with condition not working


Sum with condition not working

Author
Message
tfendt
tfendt
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 342
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.
H4K
H4K
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 567
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
tfendt
tfendt
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 342
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 :-)
H4K
H4K
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 567
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
Tim Paulsen
Tim Paulsen
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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))).
tfendt
tfendt
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 342
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.
tfendt
tfendt
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 342
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
H4K
H4K
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 567
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
tfendt
tfendt
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 342
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 Sad

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'
Steven.Howes
Steven.Howes
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 Visits: 2346
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search