Optimized Excel Integration for Business Central

Despite the emergence of powerful Business Intelligence (BI) tools such as Microsoft Power BI, many finance professionals still turn to Microsoft Excel to perform their reporting tasks.

The most valuable data for finance professionals is stored in their ERP solution, and their challenge is often in making sure they can easily transfer this data into Excel. This is why it is vital for Microsoft Dynamics 365 Business Central (BC) users to make sure that data can flow optimally to Microsoft Excel.

Better leveraging of BC data in Excel with ForNAV

BC users know that, while BC does support Excel report layouts, this solution has a few limitations, for example:

  • Excel must be launched from Business Central rather than using refreshable OData data sources
  • The data moved to Excel is produced by the Business Central reporting engine, which by design loops all involved records without using the power of SQL queries
  • The Excel table created with BC data contains a flat dataset that requires extensive clean up
  • The table header names in Excel are inherited from the column names in the AL-report dataset, which can be difficult to understand for non-developers. Changing it requires coding

This is why ForNAV came up with an enhanced Excel integration that lets you take advantage of your BC data and the excellent functionality in Excel to perform your reporting tasks. The ForNAV Excel integration simply transfers BC data in Excel where needed, so it is immediately ready to be used.

Four unique ForNAV features that transform the way you handle Excel reporting

While other providers also offer an integration between Excel and Business Central, ForNAV takes it a step further with four unique features:

  • ForNAV transfers aggregated data to Business Central. This means that data transfers are done faster. Aggregated data is great, but what if you need the details? No worries. With ForNAV, you can also drill down to Business Central to see all the details behind the data displayed in Excel
  • When you transfer data from BC to Excel, ForNAV makes sure that your captions are coherent. For example, your table headers contain a title that makes sense, is the same as you have in BC, and is displayed in your local language. No need for extra coding to make this happen
  • ForNAV allows you to save your Excel reports inside BC so that they are stored in one place where other BC users in your company can access them. Best of all, you can keep on making changes to improve these reports again and again – without any problem
  • In the ForNAV Designer, you can easily create a dynamic dataset where you specify hierarchy and the data you would like to use – this is done with a simple drag-and-drop function. This gives you access to full BC data in Excel, without having to write a single line of code

These special ForNAV features make creating and editing your report layout in Excel simple and straightforward.

Get started faster with a set of out-of-the-box Excel reports

The ForNAV Customizable Report Pack contains a set of out-of-the-box Excel reports to enable exporting aggregated data from the Business Central Ledger tables. Read more about how to use them in this ForNAV Excel reports Knowledge Base article.

Learn more about how and where to download the ForNAV Customizable Report Pack here.

Top eight benefits of using ForNAV to integrate BC with Excel

  • Optimized integration with Excel. A fully secure solution that transfers BC data to feed your Excel reports and lets you drill back down to Business Central to see where the data displayed in Excel originates from
  • Fast data transfers. Provides a very fast transfer of data and report creation. Using the ForNAV queries, you can create Excel reports with BC data in no time. Data is aggregated in Business Central rather than in Excel, avoiding large data transfers and optimizing transfer time
  • Full access to BC data. Gives you access to any table and field inside BC – including flow-fields
  • Reliable data. The BC data you get in Excel is clean, live and ready-to-use, removing the need for manual and error-prone data clean-up
  • The right captions, in the right language. Provides captions in your local language, eliminating the need for extra translation or clean-up tasks. Your table header names will be the same as the translated captions in Business Central
  • Leverage Excel functions. Allows you to leverage all fields, sheets, and functions available in Excel to create the desired layouts
  • Simple access. Lets you use credentials and permissions from BC
  • Centralized report storage. Allows you to save your Excel report layouts in Business Central. All your reports are stored in one central location, eliminating the need for data-filled Excel sheets spread across various file shares

Video tutorials: create and edit Excel reports using BC data

Watch these short Coffee Break videos to learn more about how to handle the ForNAV Excel reports:

  • Creating new ForNAV Excel reports. This 11-minute video teaches you how to create a new Excel report from the ForNAV Designer, populate it with BC data, and add data from related tables to the source table data sources and secondary Data Item. Watch it here: https://www.youtube.com/watch?v=Y3HSjwZPGTc&t=381s
  • Editing the ForNAV Excel reports layout. This 16-minute video takes a close look at the layout of the ForNAV Excel reports and how to edit them – from a simple swap of table style to creating new table styles or making changes to the worksheet. Watch it here: https://www.youtube.com/watch?v=W4y-zIb_rSM&t=71s

Microsoft, Microsoft Excel and Microsoft Power BI are trademarks of the Microsoft group of companies.