How do I: Create an Excel Layout Report?

Not so long-ago Excel report layouts were added in Business Central.

With Excel layouts for Business Central reports, users can now create and edit report layouts simply by using the full palette of capabilities in Excel such as sliders, diagrams, charts, and pivot tables.

On one hand an end user can easily add Excel layouts to existing reports, and on the other side developers can also provide, one or multiple, Excel layouts in the report object.

Personally, I see an ideal collaboration here.

Developers create reports with datasets optimized to be consumed in an Excel layout and users that create the Excel layouts in Business Central.

Recently Renato Fajdiga (@rfajdiga) / Twitter and I also hosted a workshop about at Directions Emea: Workshop Business Central Excel Layout Reports Demystified.

In this workshop we look at Excel layouts from the point of view of a developer:

and then from the point of view of a user in Business Central:

And, if you don’t have a Business Central trial environment to try this out, we also explain how to set one up:

Interested to give the workshop a try?

You can find it here: https://github.com/srenders/blog/tree/master/ExcelLayouts/DirectionsEmea

In the workshop we create 3 new reports:

Finance
Item Availability
Shipments

which, imho, are good candidates for Excel layouts.

You will also learn how as a developer you can provide calculated columns, to be consumed in Excel pivot tables/charts, but also how you as a user can achieve the same effect using Power Query in Excel.

And some of the Excel AI capabilities: like Analyze Data, are also covered:

During the workshop we also did some demos on how you can import data in an Excel layout coming from Power BI Dataflows. This is not in the workshop file, but I will blog about that soon too! Because before you can import your Dataflows in Excel, you need to know how to create those Dataflows 😉

Any feedback is also highly appreciated and welcome of course.