This entry is more for my memory than anything else, but I am sure someone else out there can use it.
Did you know that you can custom format a zero value in a report?
From what I have seen in the MSDN forums, not too many people do. Most people, and myself included, first set up a visibility formula like this:
=iif(Fields!Data.Value = 0, true, false) <-- Sets hidden to true if value is zero.
And that does work great, unless you have any kind of formatting or borders in your cell. Then it hides them too.
The better workaround is to use the Format property on the cell and use a custom format string.
The custom format string has 3 parts separated by semicolons:
Positive value format; Negative value format; Zero format
So if you wanted to hide the zeros in a decimal number cell, the format string would look like:
#,###.##;-#,###.##;””
You could also use that to further document the zero value:
#,##0.0;(#,##0.0);”Zero”
The only limitation that I have found, and I am still looking for a workaround is if you are writing a multi-locale report. If you change the Language property, the number formatting will not change.
I will post an update once I find a way to do this.
Till then…
Peace
5 comments:
Great, Thanks!
Nick
Very useful workaround, thank you
In cases where all else fails (and it nearly did in my case) manipulate the series data properties to make 0 values a null string - =IIf(Count(Fields!data.Value)>0,Count(Fields!Data.Value),"")
This indeed did the trick for hiding the zero values but leaving the borders!
My first attempt was to use the IIF(...), that hid the values as well as the borders.
Thanks for sharing the article.
Post a Comment