January 7, 2011 at 5:07 am
I have a column with Values, then a sum of the value, but when the columm contains a value of 0.00 the sum shows an error.
Any one advise?
January 7, 2011 at 5:11 am
What's the error?
What's the data type of the column?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2011 at 5:29 am
Hiya
its just shows as '#error' in the cell.
It is pounds values
January 7, 2011 at 5:32 am
January 7, 2011 at 5:37 am
What is the data type of the column?
What query are you running?
Please remember we can't see what you're doing. The more info you give, the more likely it is that someone will be able to solve this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2011 at 5:38 am
this sounds like Excel... If there was a problem with the field data, I would image SQL to throw an error versus displaying #Error in the result grid. Are you using SQL Management studio or is this in an application? If the query isn't casting the 0.00 to an int and this is an application, then the application is likely just handling the error being thrown by SQL and displaying #error.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
January 7, 2011 at 6:08 am
Could also be SQL server with Access as front end....
That message is always a cast error or an error in the formula.
January 7, 2011 at 6:11 am
Its is in BIDS(Business Intelligence Delvelopment Studio) 2008. Reporting Services tool.
The report is grouped by a field called costcode, then I have fields such as Supplier, Customer, Value.
each cost code has a summary for the total value of each cost code. i noticed the summary throws an error when the value has 0.00 values in it,.
Does this make sense?
January 7, 2011 at 6:20 am
It is starting to make more sense. Gail's questions are important though. What is the data type of the column? What is the underlying query? If the SUM is happening in the query and the field type isn't numeric I believe you'll get an error in the implicit cast.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
January 7, 2011 at 6:24 am
its a calulated field.
=iif(Fields!valuehome.Value>0,Fields!valuehome.Value,"0.00")
Not sure where to define what type of field it is
January 7, 2011 at 6:27 am
rkaria (1/7/2011)
its a calulated field.=iif(Fields!valuehome.Value>0,Fields!valuehome.Value,"0.00")
Not sure where to define what type of field it is
Does this work for you?
=VAL(iif(Fields!valuehome.Value>0,Fields!valuehome.Value,"0.00"))
You could also try cdbl instead of val.
January 7, 2011 at 6:30 am
rkaria (1/7/2011)
its a calulated field.=iif(Fields!valuehome.Value>0,Fields!valuehome.Value,"0.00")
Not sure where to define what type of field it is
Assuming valuehome is a numeric type, then wrapping 0.00 in quotes would be trying to put a string value in a numeric field, no?
Try
=iif(Fields!valuehome.Value>0,Fields!valuehome.Value,0.00)
January 7, 2011 at 6:38 am
Thanks but Taking the quotes of 0.00 worked for the sub total for each cost code, but not the grand total.
January 7, 2011 at 6:39 am
This worked a treat for all the totals that i am using....thank you so much for your help.
January 7, 2011 at 6:39 am
Yeah, since you are doing the work in SSRS, then you need to remove the quotes. I am curious about the formula though. Can the value be less than zero or is the formula being used to display zero for null values? You could do that in the underlying SQL using the ISNULL() function on the field and then you would not need this formula at all.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply