How Do I: Add fields to a Report in Business Central?

That’s a question I hear frequently from Customers and I see frequently on several fora. And if you are new to Business Central, without any prior knowledge of Dynamics NAV Development, it’s not an obvious answer…

(Dynamics NAV is the product Business Central is based upon, and it has a development environment called C/SIDE)

The first thing you need to know is that a Report object in Business Central had one dataset and one or two built-in layouts: an RDLC and/or a WORD layout. Both of these layouts are built on top of the same, common, dataset. Then, at run-time, in the application, you can add many more custom layouts to the same report object.

Furthermore, when you create a new run-time layout, it will be a copy of the built-in RDLC or WORD layout, which you can modify.

This means that the question: “How Do I: Add fields to a Report in Business Central?” breaks down into two other questions:

1. How Do I: Add fields to a Report Layout in Business Central?
2. How Do I: Add fields to a Report Dataset in Business Central?

How Do I: Add fields to a Report Layout in Business Central?

To modify a report layout, you go into the application, Business Central, and look for the page: Report Layout Selection:

It might take a couple of seconds for this screen to load, because it will fetch all reports in your tenant, and display them in a list. You can filter this list, as shown in the next screenshot, where I apply a filter to see all Invoice layouts:

Let’s have a look at the report 1306 Standard Sales Invoice:

In the column SELECTED LAYOUT you can see which layout is currently selected as the default layout for the report:

As you can see it might be the built-in RDLC, built-in WORD or one of the custom (run-time) layouts.

To add a new run-time (custom) layout click Process, Custom Layouts:

Then click New to create a new custom layout:

In the popup select Insert Word Layout and/or Insert RDLC Layout:

Then select Export Layout to export the RDLC or WORD file to edit:

Then in the layout you can see the dataset, and if required, add a field from that dataset into the layout:

But, there’s a restriction: you can not modify the dataset, you have to work with the available fields, provided by the developer. As a user in Business Central, you can only add or modify custom layouts.

How Do I: Add fields to a Report Dataset in Business Central?

This is a very simple question, with a very simple answer: YOU CAN NOT!

At this moment, you are not able to modify the dataset of any report, in Business Central.

So what’s the solution? Well, the solution is to create a new report. Using the VSCode development environment, you can build an extension, that contains one (or more) new report objects. There’s no Report Extension object, only a Report object. I hope, maybe one day, it might become possible, but at this moment it is not, so you are stuck and need to develop a completely new report.

From scratch? No, not from scratch… If you are clever, then you can use C/SIDE to export any of the existing reports of Business Central to TXT, then convert it to AL and then modify it.

So how do we do this?

The first thing you need is a Business Central on premises database, including C/SIDE. You can do this by downloading and installing Business Central On Premises, as described here:

How Do I: Install Dynamics 365 Business Central On Premises?

Or, as an alternative, you can pull a docker image, and use that. Just make sure you use the correct build, a build that corresponds to the build you are using in your Business Central Saas version:

What is my build no in Business Central?

Once everything is installed, go and have a look here:

C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\130\RoleTailored Client

You should find a finsql.exe file and a Txt2AL.exe file.

You use the finsql.exe (C/SIDE) to export objects to .TXT, and you use the Txt2AL.exe to convert the TXT file to an AL file.

More documentation is available here:

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-txt2al-tool

As an example, I will do this, on my PLATAAN database, which is a BE version of Business Central On Prem:

finsql.exe Command=ExportToNewSyntax, File=”C:\temp\ExportToTXT_ConvertToAL\TXT\R1306.txt”, Database=”PLATAAN”, ServerName=”THINKABOUTIT\SQL2017″ ,Filter=Type=report;ID=1306

In a command prompt (running as an Administrator) execute the above command. This will export a TXT file:

Then run the following command:

txt2al –source=”C:\temp\ExportToTXT_ConvertToAL\TXT” –target=”C:\temp\ExportToTXT_ConvertToAL\AL”

This will convert the TXT file into an AL file, including files for the layout(s) and translations:

Now you can create a new project, as described here:

How Do I: Develop an Extension in Business Central Saas?

And the copy the report file into your new project:

And voila, you can now use VSCode, to add fields to the dataset of this new report, which started as a copy of the original report.

The rest of the process depends on what exactly you need to do, and your knowledge of VSCode, AL and the Business Central data model.