Excel Report Layouts, new in Busness Central 2022 Wave 1 (v20)

[@gonzaloriosley2]

Hi Erik, you probably already figured it out, but in case somebody else watch this. To add new columns in your dataset after the first generation of the excel layout, go to the Data sheet, go to the table and manually add the column with the same name from your dataset colum in AL. It finds the column and puts the data there. Some other checks the AL compiler makes are that the Data sheet exists, and thta it contains a table called Data.

[@anada1124]

Thank you, Erik. Being able to update the layout when there is any change to the dataset is a missing feature that Microsoft needs to enhance with. It should be like updating Word layout.

[@kieransweeney3768]

Erik, Thanks for another great BC video. I found a bit more flexibility with the Excel data when using the data table as a reference in Power Query. I can transform the query dataset as needed to populate my own worksheets and then simply hide the data tab. The only annoyance I find with this route is that users must enable content and manually refresh their data before any of the reports or pivots will show anything. I would love it if Microsoft could add some logic on report generation to refresh all locally scoped data sources and queries before the file exports.

[@kennienybo]

Love the video. I will post a few comments in here on various gotchas and the rationale behind them

[@sotirisdragonas4737]

For updating the dataset on changes, there's two problematic scenarios. The first is that Excel is a bit possessive in regards to open files. It holds a lock on the layouts if a user is looking at it and prevents the live update. The second issue is that layouts also support the ability to remove columns. You can remove some of them or re-order them in data and they will be filled in correctly. Due to this we cannot just patch in the entire dataset. The generation of the dataset was added more as a starting point, to easily get a template to work on, although it's obvious it could be improved to be more intuitive. It's also obvious that more documentation is needed on how to design layouts. Like what's the easiest way to refer to data and what's supported. All in all though, great feedback and a good run through

[@Hougaard]

Thanks for the clarifications.... a few counter comments: 1. Open file is really not an issue, there's several other cases where the compiler barfs out of you got specific files open. 2. From my perspective, the "data" sheet is offlimit, so you can just delete it from the template file and re-populate it. (Just like the XMLPart in Word templates are wiped and regenerated). 3. Documentation yes, there's basically no documentation on this right now..

[@kennienybo]

A few comments 1) The dataset that is generated is the same as it always has been. We could do a change to Excel layouts where you can specify in metadata that you do not want a cross join of data items but instead want multiple data tables on the data worksheet. 2) Note that with Excel layouts, both the developer and the end user can author new layouts. So, whatever funky stuff we add to the compiler to support the developer, we also need to do in the UI of the client. 3) The runtime supports that the data worksheet in the Excel layout does not have all fields in the dataset. This makes layouts stable towards report extensions and also makes it possible for the layouter to remove columns that are not needed. 4) In the current version (v20) of Business Central, in case the developer is both the author of the layout AND is adding new fields to the dataset, the simplest solution is probably to copy the new fields from the AL code into the data worksheet of the layout. No need to delete the file and start over. Another way is to run the report request page with the "Excel data (no layout" option and just copy the new fields from the data worksheet of the generated file.

[@Hougaard]

1) Yes Please :) 2-4) But with Word layout the metadata (xmlpart) CAN be updated with new fields when the dataset gains new fields - There's even a "Update Layout" in Custom Report Layout that enables endusers to perform that. (In a non-destructive way) - I believe there's an opportunity here to improve the product.

Great and interesting. Maybe, you need to modify the Excel file like when you work with an Word layout, via development, or something like that. Thank you very much for sharing your knowledge.

[@throwawaybaylan-kz1wu]

For some reason the new page we create, the Report one, does not update when opening the downloaded file, I need to click on "Enable editing" to make it appear... Any idea how I can solve this? Thank you!

[@bieloliver524]

How can i put captions or translations for the column name. I have an extension called XLIFF sync. ?

[@Hougaard]

Excel layout don't support translations yet... or maybe they do in 23.1.....

[@bostjanles]

Nice new feature. Still "some" room for improvment. Data worksheet is the same as Dataset in RDLC layout.

[@crisvdwijst]

And thank you again sir !

[@dividuttnamboodiri3283]

at 4.13 can we show the balance sum in the same sheet without creating a new sheet?

[@Hougaard]

Never in the sheet that Microsoft populates.

[@dividuttnamboodiri3283]

@@Hougaard can we create duplicate sheet then populate in that sheet is that possible?needed total of that column in the same sheet so

[@navnav3134]

Dear Eric, After last update of visual studio code, Vscode breadcrumbs in report doesn't show the full path of the code in a function. Note: This only in a report, for other types the breadcrumbs is normal and it is shown perfectly. Is there any idea how to show it on the report. Best Regards, Nav

[@Hougaard]

No idea, sorry

[@persetrue]

if you go into the configuration packages, is it possible to make a report for US.ENU.EVALUATION and is it generally possible through vs code or is it impossible? P.S: This can be found by searching on Dynamics

[@Hougaard]

Not sure what you're asking about?

[@persetrue]

@@Hougaard sorry, I'm bad at explaining. I need to make an excel report for the US.ENU.EVALUATION table, which is located inside the Configuration Package. I can make a report manually by selecting Export to Excel. This table can only be reported in this way? In Visual Studio Code, can I also make a report for this table? It's just that when I do it through create report, it is not there when specifying the source table.

[@Hougaard]

@@persetrue that's not a table, but a package with records inside it

[@persetrue]

@@HougaardOK thanks. Is it possible to create an excel report with this package of records using VS Code?

[@sendit2jason]

This sort of models the Dataset only choice if you were to preview any report. No real surprise. So therefore it's a mindset change to use this report output. Hey Erik if only you live streamed this we could be sharing our thoughts as you're learning.... brainstorming ideas with your KZbin channel live. Scary huh?

[@Hougaard]

But Microsoft promotes this as something you can extend existing reports with, so there's a clear assumption that it should be usable on "normal" created datasets.

[@Hougaard]

Live streams, been thinking about it, but....

[@navnav3134]

Dear Eric, Please can you do a video how to export customers to excel from business central and edit in excell then save, and save in table directly using excel business central add-i s? Best Regards, Nav

[@Hougaard]

Just click on "Edit in Excel" from the customer list?

[@navnav3134]

@@Hougaard yes dear it is on BC 14, but in advanced version when you click edit in excel, the file open but you cannot save the data directly to business central. Microsoft did an extension in excel you can connect directly to business central. If you have an idea about it. Please can you make a video how to configure it step by step. Best Regards, Nav

[@ara3n]

I'm not going to touch this until it's usable from development perspective.

[@wojtekmoc97]

Hey, can u show us again how to perpare Visual studio code and buisness central sandbox and connect? Because i have problem, that i cant download symbols, and i dont have .alpackages after AL:GO! Thanks!

[@Hougaard]

Check the video on launch.json

[@wojtekmoc97]

@@Hougaard Thanks for quick response, i checked out the video, but i still have an error message: Your license does not grant you the following permissions on TableData Published Application: IndirectRead. i tried change those settings in bc sandbox site but it doesnt work for me, also i cant create new account for a fresh start. Thanks for help, Greetings from Poland :D

[@Hougaard]

That sounds like a sandbox without a BC license (or you as a user without a BC license), when sandboxes get into that state, delete them, and create a new.

[@wojtekmoc97]

@@Hougaard so, after hours and hours of research i finnally got my first Hello World published app. First mistake was create account on my own domain, not from Azure, so for everyone,, start with azure tentant and new user with global admin permission, and ALWAYS check United States for country - that was my second mistake. In every Country label i picked Poland, and i cant even get microsoft bc with error : We’re sorry, but Dynamics 365 Business Central isn’t currently available for your account. Please check back later. After hours of searching all internet i found an article, where they mention about checking US in every single time when azure or Microsoft BC ask for counry. Good luck everyone, hope you find this earlier than me :P