Add Fields to a Word Report in Business Central
Well, how do I…where is the… alright, I will checkout a tutorial first. This was my reaction when someone asked me to add fields to a Word Report and I opened a Microsoft Word layout for the first time.
What You Will Learn
In this post you will learn how to add a field to an already existing standard report invoice in Visual Studio Code. My examples are done in a docker container with Business Central 15.
Update 13.11.2020: The approach taken in this tutorial is still valid and can also be used for RDLC reports, except of step 5.
I assume that you already have set up an environment with business central. You will also need the extensions AZ AL Dev Tools/AL Code Outline by Andrzej Zwierzchowski and ALRunner by Tobias Fenster. Having installed Waldo’s CRS AL Language Extension is optional but also recommended.
What We Will Do
Basically we need to take these steps to accomplish our goal:
- Create a new A/L Project with our running sandbox
- Copy standard report to a new number and name
- Add a new column to the dataset
- Include layout in the project
- Add the new field in the layout
- Publish report
- Enjoy
1. Create a new A/L Project with our running sandbox
First, we need to create a new A/L Project in Visual Studio Code. If you have not setup a running sandbox container yet, you can checkout this post. For the next steps, I will assume that you have a running container and already downloaded the symbols.
It is also a good idea to set “launchBrowser” in your “launch.json” to “false”.
2. Copy Standard Report To a New Number and Name
We use the AZ AL Dev Tools to open the base application in the AL Object Browser. Rightclick on the Base Application and select “Open in AL Object Browser”.
Update 13.11.2020: You do not have to use the right click anymore. Just left click on the Base Application once.
This window will show up:
Select the object type “Reports” and search for ID “1306”.
- Right click the result. Select “Go to definition”.
- Use Ctrl + A to select the whole file text. Copy it.
Right click in the empty space below your last file. Select “New File”.
I will call my file “StandardSalesInvoiceCopy.al”.
Open the created file. Paste the copied text from the standard report into the blank file. If you now hover over the first line, you will see this error:
“An application object of type ‘Report’ with name ‘Standard Sales – Invoice’ is already declared”.
That is because we are using the same name and ID as the standard report. So let’s change the ID to 50100 and the name to “Standard Sales – Invoice Copy”.
This step is optional. We are all good people. So let’s stick to the file naming conventions. Waldo’s CRS AL Language Extension has a wonderful renaming feature. Press Ctrl + Shift + P. Search for CRS: Rename – Current File. Rename the file and checkout the result:
Update 25.04.2020: We could also have used the Reorganizing functionality of the CRS Language Extension. This would have created a separate folder for our report file. If you want to find out more about the CRS Language Extension and its useful features, you can read more here.
3. Add New Column To the Dataset
Uh yes. The fun part starts here. Our next step is to include our new field in the dataset. We want to see the field “VAT Bus. Posting Group” in our invoice. Not the regular customer requirement, but let’s go with it for demonstration purposes.
Navigate to the last dataset column of the header and place your new field below it. You can use the “Add multiple fields” feature from the dev tools in order to add “the VAT Bus. Posting Group” to the dataset.
Why add the column after the last one? We place it below the last one because it is easier to see later that this field is a customization. If you always place your new code “somewhere” in the middle of already existing code and other people do it as well, you get some good old coleslaw. Coleslaw not good for developers. It should be easy to see what is the standard code and what is customization code.
Long talk, no walk. Here is where we are now:
4. Include Layout in The Project
So far we have only been working with the .al file of our report. But our report does not only consist of an “.al” file. It also needs a “.docx” file if we want to “see something”. So let’s go get the layout.
Open your sandbox in the web client. Search for “Custom Report Layouts”. You can use the reading glass or ALT + Q to search:
Search for Report ID 1306.
Select the first report. Click on “Layout” -> “Export Layout” to export the Word layout.
Update 25.04.2020: If you cannot find your report in the Custom Report Layouts, you can create a new record by clicking “New” and filling out the necessary fields. It is also possible to export RDL layouts – just in case you were confused that you can only see Word layouts in this screenshot.
Navigate to the folder of the downloaded file.
Drag and drop the file from the Windows Explorer to your Visual Studio Code Project:
Yes. We are getting forward. This is where we are now:
Right click on your al file. Choose “Rename”. Copy the file name except the file ending (.al).
Use the copied file name to rename the downloaded layout:
There is one last thing we have to do. Right now, our new “.al” file is not linked to our new “.docx” layout. So let’s fix it. Change the WordLayout in our “.al” file from this:
… to this:
Use Ctrl + Shift + B to run the build task. If you do not do this, then our new field in the dataset will not be accessible in our layout:
5. Add New Field in the Layout
Do you smell the finish already? We are almost there. Our field is included in the dataset. Microsoft Word is already waiting for us. If you have never made changes to a word layout yet, be ready for a little surprise.
Right click on the “.docx” file and select “Open Externally”.
Microsoft Word launches. Activate the showing of paragraphs. This will make it easier to find spots where we can insert text:
Check if you can see the Developer Tab.
If not, you are doomed. No, just kidding. You can make it visible by following the steps in this tutorial.
Navigate to your “Developer” tab and open the XML Mapping Pane.
Find the report XML part.
Search for our “VAT Bus Posting Group” field below the “Header” DataItem :
Click in the space where we want to add our new field. We want it below the Work description:
Right click on our VATBusPostingGroup. Select “Insert Content Control” -> “Plain Text”.
Result:
Save and close the file.
6. Publish Report
Now start your project without debugging (Ctrl + F5) to publish the report:
If you have specified in your launch file to launch the browser, ignore the now opened browser.
Go to the first line of your report .al file. Run the selection with “Alt + R”. This will run the report in your sandbox:
Filter for a “Posted Sales Invoice” No. and click “Preview”:
7. Enjoy!
This is our result. Our field “VAT Bus. Posting Group” is being printed:
Congratulations. Have a nice week 🙂
18 thoughts on “Add Fields to a Word Report in Business Central”
Is it possible to add field on standard report
Hey Siva,
what exactly do you mean? Add a field like a page extension without copying the original report? I’m afraid that this is not possible at the moment. At least I have not heard of any way to accomplish that.
Cheers
Hi with this post, I am able to add fields which are standard, but I am not able to add Custom field in this report.
Please help.
Hey annivasu,
if you want to add fields that are not standard, then you first have to add them in your table. In this tutorial, we used the sales invoice. So you would need to create a table extension of the Sales Invoice Header and add the desired field there before you can add it to your report.
You can also add a global variable in your report and add it to your dataset if you do not want to store the information in a table.
Hope this helps.
Kind regards
Waldemar
Thanks for your reply, I have already added the custom field in sales invoice header table and added in dataset as well, also available in word XML mapping too, but when I am saving this docx file and importing as a layout in custom layout and then trying to run this report, it giving me message and not showing that field in report preview.
Why exactly are you trying to import the layout in custom layouts?
You do not have to import anything back to the custom layouts.
Please try this instead:
1. Build and publish your extension.
2. Go to Report Selection – Sales. Select Usage – “Invoice” and pick your created report ID.
3. Go to your Posted Sales Invoices and print.
Hie
I have been following your tutorial but after I opened the base app in AL Object browser and searched for my report and right clicked on it and select Go to definition, it only showed me the variables not the other source code. How do I deal with this situation?
Hey Tatenda,
which Business Central version are you using? Versions prior to Business Central 15 do not show the source code properly.
If you are using a version prior to 15, you have to get the source files from the installation DVD or from your docker container.
Check this blog post if you do not know how to get files from your installation DVD or docker container.
Kind regards
Thank you, I got them from the installation DVD.
Cheers!!!
There are already some tutorials that are dedicated to the topic of translations. But I haven’t found one yet that is specifically for reports. That’s why I will take on this task today.
For reports, we currently have the special case that we do not create “empty” objects, but often copy standard reports and customize them for customers. At least this is the case now, maybe this will change in 2021 with the announced report extensions.
Unfortunately, the copied reports are not automatically translated, because you change the IDs – that is, you create a “new” report based on the existing standard report. The translation files of the base application are ID-bound and translate only the standard objects .
In this tutorial we will therefore look at how to create translations for our copied reports using the translation files of the Base App.
How To Translate a Copied Report
Get Translation Files Get Base App Translation Files.
Download Poedit and Load Translation Files Into Memory Download Poedit. Install and run it. Go to “preferences”. Import translation files in tab “Translation Memory”.
Install XLIFF Sync Install XLIFF Sync by Rob van Bekkum.
Prepare AL Project Create a new AL Project (AL: Go!). Copy a standard report. Activate feature: TranslationFile in app.json.
Compile the Project Compile the project so that a translation file is being generated.
Create Target Translation File Use “XLIFF: Create Target Translation File” to create a new translation file.
Translate Target Translation File in Poedit Open your Target Translation File in Poedit (Open Externally). Pretranslate the file. Save the file.
Continue Developing Your Report Make the changes you need for your report. Use “XLIFF: Synchronize Translation Units” to update target translation files for new captions.
Get Translation Files
To translate our copied reports, you need the translation files of the Base App for our target language. In our example I would like to have the German translations. If you do not know how to get the translation files, you can read about it in this post.
Download Poedit and Load Translation Files Into Memory
Download Poedit. Install it and run it. Go to “File” -> “Preferences”.
Click “Manage” in the tab “Translation Memory”.
Select “Import translation files…”
Select your target translation file, in my case that’s the german translation file.
You should now see the progress and the values for “Stored translations” and “Database size on disk” are increased.
Install XLIFF Sync
Search for the extension XLIFF Sync by Rob van Bekkum and install it.
Alternatively, download it from the Visual Studio Code Marketplace.
Prepare AL Project
I assume that you are able to create an AL project. If not – learn how to get started here. Also you should already have copied a standard report that we can work with. If not, follow the instructions in this tutorial to copy a report.
I created a simple AL Project called TranslateStandardReport and downloaded the symbols. Then i copied the report 1305 “Standard Sales – Order Conf.”.
Next, activate the feature “TranslationFile” in your app.json.
Compile the Project
Compile the project so that a first translation file is generated.
Create Target Translation File
Use the XLIFF Sync function “XLIFF: Create New Target File(s)” to create a new target translation file.
If you receive this error…
…use the function Developer: Reload Window and try again.
Select the default workspace folder.
Next, choose your target language file.
A new translation file will be created for your target language.
Translate File in Poedit
Right Click on your created target translation file and select “Open Externally”. The xliff file should open in Poedit.
If it does not, open your project folder and change the settings of your translation file so that it opens with Poedit.
Use the function Pretranslate in Poedit.
A new dialog opens. Check “Only fill in exact matches” if it is not already checked! If you do not check it, then inaccurate translations may also be filled in.
You will be notified that the operation was succesful.
Save the file in Poedit. The standard translations are now in your translation files.
Continue Developing Your Report
If you continue to change something in your report and there are new captions to be translated, you will need to synchronize your translation units so that your target translation file gets updated.
That’s it. Have fun.
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!
Thank you for the detailed guide on adding fields to a Word report in Business Central. Your step-by-step instructions are incredibly helpful for anyone looking to customize standard reports. As a company specializing in Dynamics 365 Business Central implementation, this information is highly relevant and valuable. Your insights into using Visual Studio Code and the various extensions streamline the process, making it accessible even for those new to customizing reports. We appreciate the clarity and thoroughness of your tutorial, which will undoubtedly assist us in delivering enhanced reporting solutions to our clients.
Thank you!
Hi Waldemar,
I’m trying to follow your steps but stopped at adding the new field in the report. I can’t find my fields in the XML mapping.
In my case, I want to add new fields in Purchase – Order report (Report ID: 1322), then I changed the ID to 50100. However, in the XML Part link only shown 1322 hence I think that’s why I can’t find my custom fields. Fyi, I already did the table extension as well. Now I am not sure which part I’ve missed out. Really appreciate your help. Thank you in advance!
Hi Syuhadah,
have you changed the line in your .al file that links the layout file to your .al file? I am talking about these 2 steps. Maybe you can search for the text and check the steps in the tutorial again. If you have done these 2 steps, the layout file should have the correct XML Part link.
1)
“There is one last thing we have to do. Right now, our new “.al” file is not linked to our new “.docx” layout. So let’s fix it. Change the WordLayout in our “.al” file from this:”
2)
“Use Ctrl + Shift + B to run the build task. If you do not do this, then our new field in the dataset will not be accessible in our layout:”
Hope this helps.
Kind regards