Working with Business Central Custom Report Layouts
When we do Microsoft Dynamics Business Central or Dynamics NAV development, one of our goals is to minimize the work required during an upgrade. When it comes to report customizations, we can handle this by using a custom report layout instead of modifying an existing report. It’s easier to upgrade, and it’s not any harder than modifying the existing report layout. I’ll talk you through custom report layout limitations, how you can make a custom report layout, and why you would want one.
The first thing to know is that custom report layouts are only a feature in NAV 2015 and later and in Business Central. Older versions of NAV cannot use a custom report layout. The entire feature rests on the SQL Server Reporting Services engine used to handle reporting in modern versions of NAV.
The second thing to know is that custom report layouts have a major limitation: If you need to add a field to a report that is not already in the dataset for the report, and the field cannot be calculated from data that’s already in the dataset for the report, you will not be able to use a custom report layout. If you have to add a field, you’ll need to modify the report object itself. Custom report layouts work because SQL Server Reporting Services separates the underlying data of a report from the presentation layer of a report (a.k.a., the layout). The entire idea of a custom report layout is that you’re giving an existing set of data a different presentation layer to work with – sort of like having the data put on different clothes to go on a date than it would to paint a house.
If your desired report change passes those two tests, then congratulations! You can use a custom report layout to meet your needs. Otherwise, you should probably make a modification to the report object itself.
To start with, you’ll need to open up NAV and look in Departments – Administration – IT Administration – Reports. The two things you’ll need here are Report Layout Selection and Custom Report Layouts.
Figure 1 – Report Layout Selection and Custom Report Layouts under Departments – Administration – IT Administration – Reports in Microsoft Dynamics Business Central/NAV
These two features are key to using custom report layouts. Both are intertwined, and they work together to get you the layout customization you need. Let’s talk about what they do and when you use each of them.
The Report Layout Selection page shows you all of the NAV reports, and it allows you to choose the layout you’ll be using. The Custom Report Layouts page will show you all of the custom layouts that have been created for a report. You can press the Custom Layouts button from the Report Layout Selection screen to see all the available custom layouts for a specific report. You can also go straight to the Custom Report Layouts page to change an existing layout or to create a new one.
One report can have multiple layouts, and all of the layouts for every report live in the Custom Report Layout table. Layouts can be created in RDLC or in Word. What’s the difference? An RDLC layout is created and edited using SQL Server Report Designer, while a Word layout is edited using Word.
I’ve heard that the goal of Word layouts is to let less-technical users make changes to a report layout, while the RDLC reports are intended for power users and developers. Personally, I find the Word layouts to be nearly inscrutable and harder to modify than the RDLC layouts. However, I am a developer, and I often find “user friendly” tools to be frustratingly limited if I know a more complex tool well, so your mileage may vary. (I find that XMLports are much easier to create than RapidStart packages, also.)
Let’s walk through an example and see if we can’t add a new RDLC custom layout for sales orders, then modify it by changing some fields, and then set it up as the new layout for the sales order.
First, go to the Report Selection – Sales page and find out which report you’re using for Sales Orders. In the CRONUS company in NAV 2018, that’s report 1305 Sales – Confirmation. The base report looks like that shown in Figure 2.
Figure 2 – 1305 Sales – Confirmation report in the CRONUS company in Microsoft Dynamics NAV 2018
Let’s say that you want to modify the Sales – Confirmation report so that, in addition to the customer address and the company address, it also includes the customer’s shipping address as found in the Ship-to Address fields on the Sales Header. And while we’re in there, let’s fix that weird problem where the “Amount Subject to Sales Tax” and “Amount Exempt from Sales Tax” labels that should be in the report footer are appearing with the report lines instead.
- Go to the Custom Report Layouts page and make a new RDLC custom layout. On the page, select New, and the window in Figure 3 appears.
Figure 3 – Window that appears after creating a new RDLC custom layout in Microsoft Dynamics Business Central/NAV - Fill in the Report ID with the object ID of the report you’re modifying, which is 1305.
- Check the “Insert RDLC Layout” box and click OK to create a new Custom Report Layout that’s a copy of the built-in layout. In the Custom Report Layouts page, your new layout should look like Figure 4.
Figure 4 – New custom report layout in Microsoft Dynamics Business Central/NAV - Rename your custom layout to something you’ll remember, like “Ship Address and Footer Fix”.
- Highlight it and press the “Export Layout” button to make an RDLC layout to work with.
- Save the layout to your local machine when prompted.
- Find the RDLC file you saved and open it up with SQL Server Report Builder. It should look like Figure 5.
Figure 5 – Saved RDLC file, opened in the SQL Server Report Builder - Move the sales tax labels and amounts down and add the shipping address fields into the header. This is just like modifying an existing NAV report, so I’m not going to go into the details. When you’re done, it should look like Figure 6:
Figure 6 – Report modified with the sales tax labels and amounts moved down and the shipping address fields added into the header - Once you’ve made the necessary modifications, save the RDLC file and close out of the SQL Server Report Builder. Take note of where you’ve saved the RDLC file, since you’ll need it in a minute.
- Go back to NAV, find the Custom Report Layouts page, and select your newly-created record for the Sales – Confirmation report.
- Press the “Import Layout” button as shown in Figure 7, and then choose your custom RDLC file to be imported into NAV. NOTE: When you press the “Import Layout” button and choose a RDLC layout, you will not receive any confirmation message that the import was successful.
Figure 7 – Import Layout button in the Custom Reports Layout page in Microsoft Dynamics Business Central/NAV - With the custom layout imported, go to the Report Layout Selection screen and find report 1305 Sales – Confirmation. Change the “Selected Layout” value to “Custom Layout”, and then select your newly-created layout from the Custom Report Layouts screen that appears.
Figure 8 – Report Layout Selection, “from Layout” selected in Microsoft Dynamics Business Central/NAV
That should be all you need to do to get your new layout loaded. Be aware that in some versions of NAV prior to 2018, I have seen issues where the Custom Report Layout or Report Layout Selection records needed to be created by directly accessing the appropriate table from the Object Designer instead of using a page. If that is the case, you will still need to visit the Custom Report Layouts page and run “Import Layout” to get your layout into the system.
- With the layout imported, it’s time to verify that the results look good. Go to the Sales Order and press the “Print Confirmation” button, and your printed results should look like Figure 9.
Figure 9 – New report layout in Microsoft Dynamics Business Central/NAV
You can see that there is a shipping address now and that the sales tax amount fields have been moved down into the footer section of the report.
Hopefully this blog entry has shown you something new about making a customized report layout. Just remember that it only works when you want to add or move fields that are already in the report dataset; if you need to add a new field that isn’t in the dataset being exported for the report, you’ll need to do that with an actual customization to the report. Thanks for reading!
NERDY STUFF UPDATE: If you’ve read other blog entries I’ve done, you know that I write stuff at the end about nerdy things I’m doing outside of work. I used to write about Street Fighter a lot, but I’ve mostly retired from fighting games, and now I play Magic cards instead. Many people consulted about this change have confirmed that this is a lateral move that has conferred minimal additional social status on me, and I may, in fact, be spending money on literal cardboard that will severely depreciate in value. These people are probably on to something, but I am having fun, and I do enjoy having fun.
If you have any questions about reports or other Dynamics NAV or Business Central questions for any version, contact ArcherPoint.
Read more “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics Business Central or NAV.
If you are interested in NAV/Business Central development, check out our collection of Business Central and NAV Development Blogs.