Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cwildeman
Starting Member
40 Posts |
 Posted - 2011-07-08 : 13:15:01
|
| I have a Matrix report where I am attempting to sum a field called InjuryValue. This field is formatted as Nchar(1) with a value of either 1 or 0. The expression for this field in SSRS is =sum(Fields!InjuryValue.Value) which generates the #Error message. The column header is called TrxMonth and the Row field is called UnitName2. When I change the expression to =count(Fields!InjuryValue.Value)it returns a number but give me the count of records (0 or 1) rather than summing all of the ones. Can someone help?ChuckChuck W |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-08 : 14:46:53
|
Would using an expression such as this work?=SUM(Iif(Fields!InjuryValue.Value="1",1,0)) |
 |
|
|
cwildeman
Starting Member
40 Posts |
Posted - 2011-07-11 : 09:21:17
|
| Thanks for your help. That worked. Some of the values that are returned are 1 or greater than 1 while others are zero. There are some that are also null. Is it possible to modify this so that if there is a null value, it returns a zero? ChuckChuck W |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-11 : 10:24:45
|
| You can use the IsNothing function as in IsNothing(Fields!InjuryValue.Value).(I am simply looking in the expression editor to post my replies to you. IsNothing is under Common Functions -> Inspection category. |
|
|
|
|
|
|
#Error when summing a field in SSRS