Error while calculating the grand total of a column in SSRS

0
votes

I have an SSRS report which is grouped on one a column. Here i have four columns on the report. First column is the grouped column, second is description, and third and fourth columns are those whose grand total i want in my report. Below is how my report looks enter image description here

And below is how my data would be shown and this is the error that im getting if any of the row is blank. The logic is if the value is positive i have to show that in Record 1 column and if it is negative i have to show that in Record 2 column. For this i have added a expression on [Record 1 Value]

=IIF(Fields!My_Value.Value > 0, Fields!My_Value.Value,"")

Now my requirement is if the value is greater than zero it should else the value should be blank, i dont want to display zero.

Another condition is that if there is no blank row in the [Record 1 Value] then the summation does gets calculated perfectly just like below.

enter image description here

How can i overcome this blank or null column situation. I have tried to add zero in the iff else condition but showing zero on report is not my requirement.

Below is the error that i get if the value is blank enter image description here

1
ssrs-2008ssrs-2008-r2ssrs-2012ssrs-tablixssrs-grouping
Did you try set the cell visibility to an expression, that checks if the value is zero? – alejandro zuleta

1 Answers

0
votes

Instead of using an Expression for your value, use the Format property of the text box and use blank to suppress negatives and zeroes.

#; ;

For Record 2:

 ;-#;

Make sure to include the leading space.

For you totals, you would need to SUM them with a custom expression:

=SUM(IIF(Fields!My_Value.Value > 0, Fields!My_Value.Value, 0))

=SUM(IIF(Fields!My_Value.Value < 0, Fields!My_Value.Value, 0))