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:
…and replace it with this line:
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.
4 thoughts on “Zero Values in Word Reports”
Thank you, exactly what I was looking for 😀
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.
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.