Clever Geek Handbook

Replacing empty values ​​with zeros

I know this question has been asked many times before, but following the previous solutions, I still cannot solve the problem:

I have an SSRS (2008R2) report that displays data in a matrix. There is a horizontal column group containing values ​​that may or may not exist: when the value does not exist, I would like to replace a blank cell with 0

. The report is currently displayed as follows:

enter image description here

and I would like these empty cells to have 0

in them. The cell cell was set like this:

 =Sum(Fields!number.Value)

so i tried

 =iif (IsNothing(Sum(Fields!number.Value)),0,Sum(Fields!number.Value))

and

=iif (IsNothing(Fields!number.Value),0,Sum(Fields!number.Value))

and

=iif (Sum(Fields!number.Value)>0,Sum(Fields!number.Value),0)

... but "empty" cells are preserved. I'm sure I'm doing something stupid ... but what?

EDIT: To better illustrate my situation, my request produces an output (in SSMS) similar to:

File | outcomeID     | number
A    |    Outcome1   |   2
A    |    Outcome2   |   1
B    |    Outcome2   |   2
C    |    Outcome1   |   1
D    |    Outcome3   |   2

... which will give the result in SSRS:

File | Outcome1 | Outcome2  | Outcome3
 A   |    2     |     1     |
 B   |    2     |           |
 C   |    1     |           |
 D   |          |           |    2

using a column group:

enter image description hereenter image description here

Even if I change the expression just:

=999

In the end

File | Outcome1 | Outcome2  | Outcome3
 A   |    999   |     999   |
 B   |    999   |           |
 C   |    999   |           |
 D   |          |           |    999

... i.e. a lot of spaces.

EDIT2: I have downloaded a very small example .rdl

file [REMOVED] using the above example data - it reproduces the problem

+3


source to share


2 answers


My first thought was to change the stored procedure, but since I had a similar report to quickly check something, I got an idea.

try this instead



=0+Sum(Fields!number.Value)
+7


source


I tend to use the following (in SSRS 2005, however) =CInt(Fields!number.Value)

. You can also use other transformations, for example CDbl

.



Alternatively, but a little longer, try it =IIf(Fields!number.Value Is Nothing, 0, Fields!number.Value)

.

+1


source






More articles:


All Articles