In Business Central’s first major release of 2022, Excel reporting was announced. It’s now available to use so I thought it’d be a useful thing to cover. This feature essentially allows you to make predefined templates specific to any report type. The way in which you will want to display data will differ from report to report.
For those of you who have had to create RDLC reports, you’ll be aware of how cumbersome and how time-consuming they can be due to their technical and fiddly nature. Microsoft Excel is a familiar tool to many so this is a welcome change. It opens up the door for end users to design their own reports, something less possible before.
This is another example of the integration between Microsoft products improving. Recently, we covered the integration with Teams, a strong development which we only expect to improve over the coming years.
What does it do?
The introduction of Excel reporting to Business Central gives users an additional option in how they choose to view their data. Prior to the recent Business Central update, it wasn’t unusual for people to export their ERP data to Excel to present it in an alternative means. Excel is a familiar, yet very powerful tool.
How to make an Excel document a standard report layout for Business Central data
The starting point for something like this is knowing what data you want to show. By that, I mean selecting the report that you want to create a new layout for. Once you’ve done that, navigate your way over to that report. In my case, I will demonstrate using Excel for an Inventory Valuation report.
Once you’ve reached the relevant report, you can apply any filters to your data. After this, download the Excel file. You can do this by clicking ‘Send to’. There’s now new options when you click this. The options we are interested in are those related to Excel: ‘Data and layout’ and ‘Data only’. For our purposes, we are going to stick with ‘Data only’. After this, click ‘Ok’.
After you have downloaded the file, you should get an Excel download.
In Excel
Notice at the bottom, you should have at least two worksheets. The ‘Data’ sheet is the important one for us. Make sure you don’t change any data or delete this sheet.
Next, create an additional worksheet. Name this new worksheet appropriately. In this new worksheet, you can choose how you want to display the data. For example, you might choose to use a pivot table or graph. You can use any of the functionality Excel has available. This includes graphs, charts, tables and a diverse colour pallet. The ability to have multiple sheets for each report is a great benefit.
However you choose to display the data, make sure the ‘Data’ worksheet is our source of information. This is why I have populated the ‘Table/Range’ field with the ‘Data’.
On the area where you select fields, drag the field to where you want it. As I’m using a Pivot Table for my example, I will typically want one field name in the ‘Rows’ section and another field for ‘Values’.
Back in Business Central
When you’re happy with the way your data’s presented, save the file. Then in BC, go to the Report Layout page (not to be confused with Custom Report Layouts). Here, click ‘New Layout’. Select the relevant report ID. Once you’ve done this, click the three dots in the page’s header. Here, you can choose the new report layout to be your standard layout.
One thing to iron out is the difference between Word and Excel report layouts. They have a different purpose. Whilst Word’s report layouts are meant for customer and vendor facing documents, Excel’s reporting are more geared towards internal use and large-scale analysis of data. You can use Excel reports to analyse large sums of data covering lots of fields. Word is more tailored towards providing small amounts of data.
Closing remarks
As always, thanks for reading! Hopefully this new reporting functionality helps spark some ideas as to what might be possible when it comes to presenting data. As always, if you have any comments or queries, don’t hesitate to get in contact with us. The best way to never miss when we post is to follow us on LinkedIn.