Zero Values in Word Reports

Zero Values in Word Reports

Today we look at how to hide zero values in Word Reports. Sometimes it makes sense not to print the value 0 in reports to protect the user from a flood of information.

With the help of two small procedures we can achieve this behavior in Word Reports. If you are familiar with RDLC reports, you will know the function BlankZero. In this post, we are going to implement something similiar.

BlankZero in RDLC Reports

With RDLC Reports, we have the possibility to use this code to not print 0 values in our reports.

Public Function BlankZero(ByVal Value As Decimal)
     if Value = 0 then
         Return ""
     end if
     Return Value
End Function

BlankZero in Word Reports

So far I have not discovered any possibility to implement something similar in the Word layout itself. But with little manual work we can make sure that the desired value is already passed in the dataset of the .al file. Our big advantage is that we don’t have to touch the layout at all and our changes are easily tracable.

Example: Regular Sales Invoice

Let us first look at a posted sales invoice. This invoice contains a 0 price (“Unit Price Excl. VAT”).

If we print out this invoice, we see that a value of 0.00 is displayed as “Unit Price Excl. VAT”. Our customer does not want “0.00” to be printed, but simply left “empty”. At this point, let’s neglect the question of whether this makes sense. This example is for demonstration purposes only.

The Word layout belonging to the sales invoice looks like shown in the following screenshot. The field “UnitPrice” is printed from the dataset.

In the dataset, the field is transferred as follows:

In fact, it is not the UnitPrice itself that is transferred here, but the “FormattedUnitPrice”. Now we could look at the procedure in which the UnitPrice is formatted, put our dirty fingers inside and somehow try to change the formatting from there.

But we don’t want to do that because it’s too complicated. Instead, we’d like a “general” approach. We decide to take the already formatted value “FormattedUnitPrice” and throw it into our own procedure.

Example: Sales Invoice with BlankZero

For this purpose we create a new codeunit “Report Document Mgt”. Why do we create our own codeunit and do not create our funny procedure in the report? Because it is very likely that we want to use the function in other reports as well.

In our codeunit we add two procedures:

  • BlankZero(Number. Decimal): Text
  • BlankZeroFormatted(NumberFormatted: Text): Text

We can use the BlankZero procedure if we have a numeric value. It checks whether the value is not equal to 0. If the value is not 0, it is formatted, otherwise we get an empty string back.

Our second procedure BlankZeroFormatted on the other hand receives a formatted text, in our case “NumberFormatted”. It tries to convert the text to an integer. If this works and the value is 0, it returns an empty string.

In our report we create a global variable “ReportDocumentMgt” for our codeunit.

We comment out this original line in the dataset:

column(UnitPrice; FormattedUnitPrice)

…and replace it with this line:

column(UnitPrice; ReportDocumentMgt.BlankZeroFormatted(FormattedUnitPrice))

The result of this witchcraft is that nothing is printed in our invoice at “Unit Price Excl. VAT” if the value is 0.

If, on the other hand, the value is not null, it will be printed in the usual way:

I hope this helps some of you. To be honest, I don’t know if there is perhaps a simpler and more sensible method to not print zero values in Word Reports. Therefore I am happy to receive suggestions.

If you are wondering where I get the Word layouts and the .al files for the reports, take a look at this post:
Adding a Field To a Standard Word Report in Business Central

Have a nice week.

Keep well!

4 thoughts on “Zero Values in Word Reports

  1. Hi,

    Maybe a silly question but what is the definition of “formatted Unit Price”?
    Is this a variable in the report being “text”?

    Thank you in advance.

    1. Hey Timo,

      FormattedUnitPrice is a variable of type text in the report.
      It is being set in codeunit “Format Document” like this:

      FormattedUnitPrice := Format(UnitPrice, 0, AutoFormat.ResolveAutoFormat(AutoFormatType::UnitAmountFormat, CurrencyCode));

      Hope that helps.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: