UPDATE:

How do I: Create a Report Extension to add a field to the dataset of a Report? – think about IT

 

 

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.

 

UPDATE:

How do I: Create a Report Extension to add a field to the dataset of a Report? – think about IT

 

17 Thoughts on “How Do I: Add fields to a Report in Business Central?

  1. Drew K on April 10, 2019 at 16:25 said:

    Does this process require a development license?

    • To be able to export an object to .txt you need a DEV license indeed.
      When publishing an extension to an On Prem BC environment, you also need the objects included in your license, in BC Cloud/Saas the 50k-90k range is included for free.

  2. Tom Verlinden on August 8, 2019 at 11:47 said:

    Hi,

    How can I enable emailing from Business Central for custom reports? Is there some code that needs to be added to the AL-file?

    Thanks,
    Tom

    • Hi Tom,

      Yes. Emailing has only been implemented for some document reports in BC. To enable it for other (and custom) reports you can use these emailing objects (pages, tables, codeunits).

      A good starting point are:
      page 9700 Email Dialog
      table 9500 Email Item

      I’m going on vacation now and will be back in a couple of weeks. Then I will create some blogposts on how you might be able to do this 😉

      • May, Tang on August 23, 2019 at 04:45 said:

        Hi,
        I had developed some custom reports using VSC and surely would like to know how to enable the email function. Awaiting for your blog for this add on.

        Thank you inadvance.

  3. That would be very much appreciated!

  4. Hi,

    have you had the change to look at this?

    Best regards,
    Tom

  5. Hi Tom,

    Not yet. I’m back from holiday, but delivering trainings, doing consultancy and preparing for my Directions Emea sessions and workshops. As soon as I find the time I will post…

    Best regards,
    Steven

  6. Thanks Srenders, very helpful!

  7. Aqurius Hnin on September 15, 2020 at 11:26 said:

    i created custom fields in item table. But i can’t find these custom fields in item dataset when i create fields in custom report for Inventory List report. I really appreciate for your reply. Thanks.

  8. When you create a table extension for a table and then use that table as a dataitem in a report, the new fields should be available in the report.
    Can you share a copy of your code?

    • No, that’s not the way it works. As with Pages, if you extend the table, the field does not instantly appear in the page. Since Report Extensions have not yet been implemented (a requested feature), you will have to copy the report’s source and modify it.

      • I did not mean that the new field appears by itself in the dataset, I mean that you can add it in the dataset, when you create a custom report, as Aqurius describes…

  9. Vani Vashisht on November 21, 2020 at 15:14 said:

    Hi all,

    I want to insert a custom field in a report. I followed below mentioned link to create field:

    https://www.archerpoint.com/blog/Posts/adding-field-extension-microsoft-dynamics-business-central-or-nav
    I create a table extension and created a new custom field named as Notes of text datatype for Sales Invoice. and then I created a page extension and placed custom field in that page.

    Published the application on Business Central. Downloaded the symbols again. then tried to create a report.al file for the RDL file creation.

    When I created report.al file to place the fields in the business central it is showing me an error “The name ‘Notes’ does not exist in the current contextAL(AL0118)”.

    Can anyone help me with this?

    Thanks in Advance

  10. Vani Vashisht on November 23, 2020 at 06:40 said:

    Hi all,

    I want to insert a custom field in a report. I followed below mentioned link to create field:

    https://www.archerpoint.com/blog/Posts/adding-field-extension-microsoft-dynamics-business-central-or-nav

    I create a table extension and created a new custom field named as Notes of text datatype for Sales Invoice. and then I created a page extension and placed custom field in that page.

    Published the application on Business Central. Downloaded the symbols again. then tried to create a report.al file for the RDL file creation.

    When I created report.al file to place the fields in the business central it is showing me an error “The name ‘Notes’ does not exist in the current contextAL(AL0118)”.

    Image

    Can anyone help me with this?

    Thanks in Advance

Leave a Reply

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

Post Navigation