- Example:
We have the “Job List” report with report ID 10126. Let’s design a new Excel report with an existing report to create charts, a pivot table, and graphs, using formulas and functions to perform calculations to make the report easy to read.
- Steps to create an Excel layout with the help of an existing report:
- Open the “Job List” report, On the request page, click on the “Send to” option, then select “Microsoft Excel document (data only);
2. Now, one Excel file will be downloaded. After opening that file, suppose we want to delete or hide unnecessary columns or insert a pivot table to calculate the sum, count, or product of specific fields. Then we can add formatting changes of our choice as shown below.
- Inserting Pivot table
3. After making changes, save that Excel file, and on Business Central, search for the “Report Layouts” page.
4. On the “Report Layouts” page, click “New Layout” to add a modified Excel layout to an existing report.
5. After that, there will be an option to choose an Excel layout file, as shown below.
6. Now, choose the modified Excel file that we want to display, and a new entry will be entered in the “Report Layout” page with the same report id. You can run the report from the highlighted option to see the modified layout.
7. In case we want to display the Excel layout as the default while printing the report, we need to fix the layout type to Excel in the “Report Layout Selection” page and select the custom layout that was added in the “Report Layout” page.
8. Now, by default, we can see the Excel layout on the request page. We can’t preview the report for display; you’ll need to download it as shown below:
9. Here is the result: we have deleted unnecessary fields and added a pivot table, as shown below.
Overall, we can create the Excel layout of any report to make the report easy to read.
Thank you!
Samiksha S. Mhatre
D365 Business Central
Addend Analytics