Report layouts in Excel? Yes!!!

by Jul 4, 2022Development, Reports

Home 9 Development 9 Report layouts in Excel? Yes!!!

Everyone is surely aware of how strong Excel is as a software. It has been deeply ingrained in corporate operations worldwide, whether it be for assessing stocks or companies, creating budgets, or organizing customer sales lists.

Usage of Excel Layout

Due to the fact that it comes with its own unique set of functions, the layout is considered as a separate tool from the others. The following are some examples of how to use Excel’s layouts:

  1. Users can generate dynamic reports using graphics such as slicers.
  2. Users have the ability to access original data from the report database, which can assist in better comprehending how the study functions and the origin of the data displayed on graphics.
  3. Make use of built-in tools of Office to perform post-processing just on generated reports, such as:
    1. Safeguarding the spreadsheets (password & other standard Excel protection).
    2. Implementing labels for levels of sensitivity.
    3. Including additional remarks and notes.
    4. Prediction and investigation are included.
  4. Utilize already installed add-ins and app integrations, such as Power Automate flows.

How to Use Excel Layout

When it comes to the configuration of an Excel layout for a report, there are fundamentally two steps:

Step 1. Create a file for Excel Layout

There seem to be three approaches that the developers propose using in order to work with an Excel layout file, and each of these approaches will be broken down further in the following section of this article. Regardless of the type of layout currently being used, you can utilize the techniques shown here to generate an Excel layout of any document. The only things that will be included in the Excel layout are still the necessary Datasheet and column, as well as a page for the report’s information.

  • Select the “Tell me” by, type in “Report Layouts“, and afterward select the relevant link.
  • List of all existing reports loads up and displays a comprehensive list of all the formats that are presently accessible for use with any report.
  • Choose the one report you want to create layout for from the available options, and then click the “Run Report” button.
  • (other way): Just open the report request page using any other process you know that opens the right report.
  • On the request page of the report, choose to “Send to…” — “Microsoft Excel Document (data only)” and then click “OK“.
  • In this stage, an Excel workbook that holds the report data is downloaded to your computer.
  • Excel must be used to make any necessary edits to the downloaded file (it’s strongly recommend to not use any other editors from Microsoft Office).

Step 2. Update Excel Layout in your Business Central

After you have updated the Excel layout document, the following step is to update the available Excel layout in your system.

  • Select the “Tell me“, type in “Report Layouts“, and selecting the relevant link afterward.
  • Choose the “New Layout“.
    • Choose proper report in the “Report ID” field.
    • Add new “Layout Name” & “Description
    • Select “Excel” in “Format Options
    • Choose “OK”
    • Locate the Excel file you want to publish, then click the Open button. The layout receives the specified file, and after that, you are sent back to the page that lists the Report Layouts.
  • Choose the new format from the menu, then choose “Run Report” to see how the report will be created.

How Excel Layout Works

When you just begin developing new Excel layouts or modifying existing ones, there are a few aspects you should be familiar with and think about. A “Data” sheet, as well as a “Report Metadata” sheet, are essential components that should always be included in any Excel arrangement. These pieces define data on Business Central, which you can interact with, and as a result, they serve as the foundation of the layout.

Recent Articles from the category

Connect to Azure Function in BC 2022 wave 2 (v21)

Connect to Azure Function in BC 2022 wave 2 (v21)

The new version of the Microsoft Dynamics 365 Business Central brought a new system module "Azure Functions" that makes integration with Azure Functions much easier and straightforward. The typical scenario, why to use Azure Functions together with Business Central,...

read more
Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use? This is the second part of my new article series about Collectible Errors. Let's check out the first part here: Collectible Errors?! | Microsoft Dynamics 365 - Ing. Tomáš Kapitán (kepty.cz) or you might be interested in my...

read more
Collectible Errors?!

Collectible Errors?!

Collectible Errors?! It has been already almost a year since ErrorInfo datatype & CollectibleErrors were introduced (I already have an article about basic structure: ErrorInfo data type & Collectible Errors). This article was released for the first time in...

read more
Substituting standard reports

Substituting standard reports

Report objects cannot be extended in versions previous to the Business Central 2021 release wave 1 version when ReportExtensions object was introduced. Since then, many of changes we need to do in reports can be done without creating a new copy of object. On the other...

read more
Isolated events

Isolated events

With Business Central 2022 wave 1, a new setting for event publishers was introduced. Until this version, any error in any event subscriber caused interruption to the current running process and stopped the whole activity. In some cases (such as log-in), this is...

read more
Custom Filter Tokens

Custom Filter Tokens

As a user of the Business Central you have some constants you can use to filter or insert values. These constants contain useful values for data manipulation such as t / today for date field - return current dateq / quarter for date field filters - return range of the...

read more

Sign Up for News

Certifications

Highest certification
Microsoft Data Management and
also in D365 Business Central

Microsoft Certified: Dynamics 365 Business Central Functional Consultant Associate

See other certifications here