{"id":41975,"date":"2022-06-30T09:07:20","date_gmt":"2022-06-30T08:07:20","guid":{"rendered":"https:\/\/arquiconsult.com\/?p=41975"},"modified":"2025-07-30T17:04:42","modified_gmt":"2025-07-30T16:04:42","slug":"excel-layouts-for-reports","status":"publish","type":"post","link":"https:\/\/arquiconsult.com\/en\/excel-layouts-for-reports\/","title":{"rendered":"#023 EXCEL LAYOUTS FOR REPORTS"},"content":{"rendered":"<div class=\"services_vertical horizontal_services row\" style=\"background-color: #f7f7f7; padding: 25px; border-radius: 5px; text-align: justify;\">\n<p>With Microsoft Dynamics 365 Business Central 2022 Wave 1 (BC20), a new feature is added to the application, that adds the capability to create custom report layouts in Excel with all the potential that Excel enables us \u2013 pivot tables, formulas, charts, etc.<\/p>\n<p>The Excel layouts work as do Word and RDLC layouts, meaning that they can be edited and imported back to Business Central, using only the Web Client and without the need to develop any functionality, if no dataset changes are needed.<\/p>\n<p><strong>Important note:<\/strong> Not all reports have an optimized dataset to be used with Excel.<\/p>\n<p>This feature is can be of major usage since it enables the extraction of rough data and gives the customer the power to design and perform its own custom calculations trough Excel.<\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><strong>To create an Excel layout for a Report:<\/strong><\/p>\n<p>1. Run the Report and choose <strong>Send To<\/strong>, and then on <strong>Microsoft Excel Document (data only):<\/strong><img loading=\"lazy\" class=\"size-medium wp-image-41979 aligncenter\" src=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Microsoft-Excel-Document-data-only-640x380.png\" alt=\"Microsoft Excel Document (data only)\" width=\"640\" height=\"380\" srcset=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Microsoft-Excel-Document-data-only-640x380.png 640w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Microsoft-Excel-Document-data-only-768x456.png 768w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Microsoft-Excel-Document-data-only.png 1333w\" sizes=\"(max-width: 640px) 100vw, 640px\" title=\"-\"><\/p>\n<p>2. The result is an Excel file with 2 sheets:<br \/>\na.\u00a0 \u00a0<strong>Data<\/strong>: Contains the Report dataset.<br \/>\nb.\u00a0 \u00a0<strong>Report Metadata<\/strong>: Contains the Report filters and report properties.<img loading=\"lazy\" class=\"size-full wp-image-41983 aligncenter\" src=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Report-Metadata.jpg\" alt=\"Report Metadata\" width=\"558\" height=\"325\" title=\"-\"><\/p>\n<p>3. Add a new sheet to the Excel file, design the layout and import back the Report layout to Business Central from the <strong>Report Layouts<\/strong> page by using the <strong>New Layout<\/strong> action:<img loading=\"lazy\" class=\"size-medium wp-image-41985 aligncenter\" src=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Business-Central-from-the-Report-Layouts-page-by-using-the-New-Layout-action-640x291.png\" alt=\"Business Central from the Report Layouts page by using the New Layout action\" width=\"640\" height=\"291\" srcset=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Business-Central-from-the-Report-Layouts-page-by-using-the-New-Layout-action-640x291.png 640w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Business-Central-from-the-Report-Layouts-page-by-using-the-New-Layout-action-768x349.png 768w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Business-Central-from-the-Report-Layouts-page-by-using-the-New-Layout-action.png 884w\" sizes=\"(max-width: 640px) 100vw, 640px\" title=\"-\"><\/p>\n<p>4. Run the Report from the <strong>Report Layouts<\/strong> page, using the <strong>Run Report<\/strong> action with the imported Excel Layout record selected:<img loading=\"lazy\" class=\"size-full wp-image-41989 aligncenter\" src=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Run-the-Report-from-the-Report-Layouts-page.jpg\" alt=\"Run the Report from the Report Layouts page\" width=\"564\" height=\"439\" title=\"-\"><\/p>\n<p>5. Open the Excel file (In this case the Excel report layout sheet contains only a Pivot Table that shows the Sales Amount per Customer):<img loading=\"lazy\" class=\"size-full wp-image-41992 aligncenter\" src=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2022\/06\/Open-the-Excel-file.png\" alt=\"Open the Excel file\" width=\"421\" height=\"237\" title=\"-\"><\/p>\n<p>&nbsp;<\/p>\n<!--themify_builder_content-->\n<div id=\"themify_builder_content-41975\" data-postid=\"41975\" class=\"themify_builder_content themify_builder_content-41975 themify_builder tf_clear\">\n    \t<!-- module_row -->\n\t<div  data-lazy=\"1\" class=\"module_row themify_builder_row tb_snt4897 tb_first tf_clearfix\">\n\t    \t\t<div class=\"row_inner col_align_top col-count-1 tf_box tf_w tf_rel\">\n\t\t\t<div  data-lazy=\"1\" class=\"module_column tb-column col-full first tb_376x897 tf_box\">\n\t\t\t    \t        <div class=\"tb-column-inner tf_box tf_w\">\n\t\t    <!-- module text -->\n<div  class=\"module module-text tb_13ta898    hide-desktop hide-tablet hide-tablet_landscape hide-mobile\" data-lazy=\"1\">\n        <div  class=\"tb_text_wrap\">\n    <h2>EXCEL LAYOUTS FOR REPORTS<\/h2>    <\/div>\n<\/div>\n<!-- \/module text -->\t        <\/div>\n\t    \t<\/div>\n\t\t    <\/div>\n\t    <!-- \/row_inner -->\n\t<\/div>\n\t<!-- \/module_row -->\n\t\t<!-- module_row -->\n\t<div  data-lazy=\"1\" class=\"module_row themify_builder_row tb_qi4f887 tf_clearfix\">\n\t    \t\t<div class=\"row_inner col_align_top col-count-1 tf_box tf_w tf_rel\">\n\t\t\t<div  data-lazy=\"1\" class=\"module_column tb-column col-full first tb_0xpg887 tf_box\">\n\t\t\t    \t        <div class=\"tb-column-inner tf_box tf_w\">\n\t\t    <!-- module text -->\n<div  class=\"module module-text tb_s2an887 arquitps-data  \" data-lazy=\"1\">\n        <div  class=\"tb_text_wrap\">\n    <p><script>\njQuery(\".tbp_post_month:contains(Janeiro)\").html(\"January\");\n\njQuery(\".tbp_post_month:contains(Fevereiro)\").html(\"February\");\n\n\njQuery(\".tbp_post_month:contains(Mar\u00e7o)\").html(\"March\");\n\n\njQuery(\".tbp_post_month:contains(Abril)\").html(\"April\");\n\n\njQuery(\".tbp_post_month:contains(Maio)\").html(\"May\");\n\n\njQuery(\".tbp_post_month:contains(Junho)\").html(\"June\");\n\n\njQuery(\".tbp_post_month:contains(Julho)\").html(\"July\");\n\njQuery(\".tbp_post_month:contains(Agosto)\").html(\"August\");\n\njQuery(\".tbp_post_month:contains(Setembro)\").html(\"September\");\n\njQuery(\".tbp_post_month:contains(Outubro)\").html(\"October\");\n\njQuery(\".tbp_post_month:contains(Novembro)\").html(\"November\");\n\njQuery(\".tbp_post_month:contains(Dezembro)\").html(\"December\");\t\n\n\n\n<\/script><\/p>    <\/div>\n<\/div>\n<!-- \/module text -->\t        <\/div>\n\t    \t<\/div>\n\t\t    <\/div>\n\t    <!-- \/row_inner -->\n\t<\/div>\n\t<!-- \/module_row -->\n\t\t<!-- module_row -->\n\t<div  data-anchor=\"sucesso\" data-hide-anchor=\"1\" data-lazy=\"1\" class=\"module_row themify_builder_row sucesso repeat tb_has_section tb_section-sucesso tb_zzqb832 tf_clearfix\" >\n\t    \t\t<div class=\"row_inner col_align_top col-count-1 tf_box tf_w tf_rel\">\n\t\t\t<div  data-lazy=\"1\" class=\"module_column tb-column col-full first tb_u8ac832 tf_box\">\n\t\t\t    \t        <div class=\"tb-column-inner tf_box tf_w\">\n\t\t    <!-- module buttons -->\n<div  class=\"module module-buttons tb_6v3t832 buttons-horizontal transparent popmake-24705 large circle tf_textc\" data-lazy=\"1\">\n    \t<div class=\"module-buttons-item tf_inline_b\">\n\t\t\t\t\t\t\t<a href=\"#\" class=\"ui builder_button tb_default_color\" >\n\t\t\t\t\t\t\t\t\t\t\t<span class=\"tf_inline_b tf_vmiddle\">Give as a Feedback and Suggest Questions<\/span>\n\t\t\t\t\t\t\t\t\t\t\t<\/a>\n\t\t\t    \t<\/div>\n\t<\/div>\n<!-- \/module buttons -->\n\t        <\/div>\n\t    \t<\/div>\n\t\t    <\/div>\n\t    <!-- \/row_inner -->\n\t<\/div>\n\t<!-- \/module_row -->\n\t<\/div>\n<!--\/themify_builder_content-->","protected":false},"excerpt":{"rendered":"<p>With Microsoft Dynamics 365 Business Central 2022 Wave 1 (BC20), a new feature is added to the application, that adds the capability to create custom report layouts in Excel with all the potential that Excel enables us \u2013 pivot tables, formulas, charts, etc. The Excel layouts work as do Word and RDLC layouts, meaning that [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":41999,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2099,2124,2132],"tags":[2076,1729,2160],"acf":[],"_links":{"self":[{"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/posts\/41975"}],"collection":[{"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/comments?post=41975"}],"version-history":[{"count":0,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/posts\/41975\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/media\/41999"}],"wp:attachment":[{"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/media?parent=41975"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/categories?post=41975"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/tags?post=41975"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}