- Overview:
Reports are used to display information from a database. Earlier, there were two types of report layouts, i.e., RDLC and WORD. A new type of report layout called Excel Layout has been added to the Business Central 2022 release wave 1 plan. Excel’s layout helps us create a basic report and leaves it to the end-user to make further changes for designing purposes in Excel, like charts and pivot tables. Excel reports in Business Central allow you to export data from various pages and tables within the system into an Excel file. This can be useful for creating financial reports, inventory reports, sales reports, and more.
- How to create an Excel report:
To create an Excel report, you can either use an existing report or create a new one from scratch. Once you have exported the data to Excel, you can use Excel’s powerful tools to analyze and visualize the data. This includes creating charts and graphs, using formulas and functions to perform calculations, and applying formatting to make the report easy to read and understand. Excel reports in Business Central are highly customizable, allowing you to create reports that meet your specific needs. You can add or remove fields, change the layout and formatting, and even create custom formulas and calculations.
For demo purposes, let’s take a simple example, i.e., create an Excel report to analyze the sum of the amounts of purchase orders.
- For calculating the sum of the amounts of purchase orders, add the data item and fields as shown below:
2. After the building report, a new Excel layout (.xlsx) file will be created, which will be as shown below:
3. Now, after publishing the report on Business Central, you can see data for specific columns and add filters and expressions to summarize, calculate, and analyze the data.
4. We can use many features from Excel like charts, and pivot tables. Users can filter and calculate as needed by inserting a pivot table to analyse the sum of amounts.
- Create a new worksheet for Datasheet:
- Now, add fields in specific areas as required:
- Adding document no. in the filter area for filtering as per PO no.
5. Now apply the type of calculation on fields from the value area, as shown below:
- Applying the type of calculation as Sum,
6. Now, we can easily calculate sum of amount of specific Purchase orders by applying filter on PO No. as shown below:
- Some guidelines for Excel reports:-
- Don’t change the name of the Datasheet, Data table, or columns from the Excel file.
- You can delete or hide columns in an Excel file.
- Don’t add any columns in the Excel file unless they’re included in the report dataset.
- Close Excel file before publishing the report from V S Code
- On the request page, can’t preview it like RDLC / Word Layout, we have to download the Excel template.
Overall, Excel reports in Business Central provide a powerful tool for analyzing and presenting data in a way that is both meaningful and actionable. Whether you’re a small business owner or a financial analyst, Excel reports can help you make informed decisions based on the data available in Business Central.
Thank you!
Samiksha S. Mhatre
D365 Business Central
Addend Analytics