In the old days there was a property named BlankZero which we could enable on a field in the section designer. In RDLC however there’s no such thing. To overcome this problem Microsoft has added a BlankZero function in the Code tab of a report. There’s a good explanation of how to implement this here: How to: Add BlankNumbers and BlankZero Functions

However, using functions in the Value property of a textbox causes the textbox to display Expr at design time, making it more difficult to see what is in there.

Did you know there's an alternative, without this negative effect?

Actually, Dynamics NAV already sends a format string in the dataset for every decimal field. It usually looks something like this: #,##0.00 or #,##0.#####, depending on your settings. This is known as a Custom Numeric Format String, and comes from .NET. You can find a more detailed description and examples here: Custom Numeric Format Strings.

When I was reading this documentation, and looking at the format properties of a textbox in RDLC, I noticed the following:

customformatstrings

Transparently there’s already a BlankZero foreseen in the Custom Numeric Format String implementation. So let’s make use of it.

I have created a dummy report with the following dataset:

customformatstrings2

As you can see, it contains a Constants section, using an integer data item to make sure only one row is added to the dataset. Here I will send my own custom format strings. In this example I’m using two of them:

  • FormatForDecimals: #,##0.#####;#,-##0.#####;”
  • FormatForDecimals_None: #,##0.#####;#,-##0.#####;’None’

The first one implements BlankZero and the second one replaces a zero with the word None. It’s the part in Red that does the BlankZero or ReplaceZero magic.

Then I add a table in the RDLC layout as follows:

customformatstrings3

The Nothing column uses no Format, the column after uses BlankZero and the last two use the custom format codes. Now when I run the report the result is as follows:

customformatstrings4

As you can see, it works using the custom format codes. So I was thinking, it should not be that difficult, for Microsoft, to implement a BlankZero property on a field in the Report Dataset Designer, because all that needs to be done is to add a small section in the format string which is currently sent to the dataset…

The example report can be downloaded here:

4 Thoughts on “How do I: Implement BlankZero or ReplaceZero in a Report?

  1. JonasA on June 29, 2015 at 12:21 said:

    Nice! Let’s hope Microsoft implements this.

  2. Pingback: How do I: Implement BlankZero or ReplaceZero in a Report? - think about IT - Dynamics User Group

  3. Pingback: How do I: Implement BlankZero or ReplaceZero in a Report? - Microsoft Dynamics NAV Community

  4. Pingback: How do I: Implement BlankZero or ReplaceZero in a Report? | Pardaan.com

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Post Navigation