The Ultimate Guide to Microsoft Dynamics 365 Business Central Reporting

After nearly two decades maintaining multiple ERP code base, Microsoft has aligned its future product roadmap around two core ERP products. For larger enterprises, the company will be carrying forward it’s new cloud offering called Microsoft Dynamics 365 Finance & Supply Chain Management for Microsoft Dynamics AX customers. Microsoft offers Dynamics 365 Business Central as its new cloud ERP for small and mid-sized businesses as a new offering for legacy Dynamics GP, SL, and NAV customers.

Although relatively new to the market, Microsoft Dynamics 365 Business Central is making waves among small to midsized growing businesses (SMEs) around the world. Using Microsoft’s NAV code base as a foundation, the company has built out a new ERP product to serve as an “all-in-one” business management solution for SMEs. Microsoft has aligned D365 BC with its larger technology vision to create a platform for managing financials, automating the supply chain, and optimizing operations. Like nearly all the Microsoft Dynamics ERPs before it, Business Central claims to have all the native reporting functionality required to meet the financial management needs of small businesses. Unfortunately, the reporting tools that come with Microsoft D365 BC don’t always necessarily provide the flexibility and ease of use that SMEs truly need.

Solid financial reporting provides the foundation upon which good business management practices can flourish. In today’s uncertain economic environment, managers need timely access to information more than ever before. As conditions change rapidly–sometimes over the course of just a day or two–business leaders are relying more than ever on real-time reporting to help them steer their businesses in the right direction.

Reporting in Business Central

Microsoft D365 BC comes with a number of different off-the-shelf reporting features, including Account Schedules, Microsoft Excel report templates, RDL reports (using Visual Studio or Microsoft SQL Server Reporting Services), custom XML in Word, and Power BI with Business Central content packs for basic Finance, Sales, and CRM reports.

In many respects, this array of different tools can be confusing, and potentially even overwhelming. The Microsoft tools will give you access to the data you need to chart financial performance, analyze data, and forecast trends, but you will need experienced, technical resources to get you there. Which tool is most effective for any given task? What kind of specialized expertise is required? Which additional Microsoft platform components should your IT environment include, and how should you architect them?

Even with all these tools available, Business Central has some significant limitations that customers need to be aware of in advance. Here are four key reporting challenges in Business Central you should know about:

1. Pre-defined report formats are very limited.

No matter what kind of business you run, you need the ability to format reports to your company’s unique standards and personal preferences. Very often, banks and other external agencies require specific formats for greater transparency, debtor compliance, regulatory reporting, and operational efficiency.

Unfortunately, Microsoft D365 BC provides limited options out-of-the-box. For example, there are only six predefined Excel reports for financial statement analysis, and they provide little to no room for any customization. To address that limitation, Microsoft offers something called Account Schedules, but those are rigidly structured columnar reports driven by proprietary programming within the Business Central application itself. In other words, it is difficult to modify them.

Because of this significant lack of flexibility, most organizations running Microsoft D365 BC end up spending a great deal of time and money developing reports that are modified versions of Business Central’s system reports, or building new report formats entirely from scratch. That leaves many users in a difficult position because getting the information they want in the format they need turns out to be an expensive and time-consuming proposition.

Many users end up building out reports in Microsoft Excel by exporting data or saving reports to a file, then copying and pasting the resulting information into a spreadsheet. That is a slow and tedious process. It also tends to introduce errors, and it precludes the possibility of generating reports quickly, on an as-needed basis. Unfortunately, that prevents managers from achieving the kind of business agility that is essential in today’s rapidly changing economic environment.

2. Manual processes result in poor accuracy.

Microsoft Excel is a great tool for financial reporting and analysis because it is powerful, flexible, and familiar to virtually every finance and accounting professional on the planet. Unfortunately, there are limits to Business Central’s out-of-the-box integration with Excel.

Though it is possible to export data from the Microsoft D365 BC client to Excel, the formatting is in a standard layout and it is not possible to define formatting when exporting data to Excel using that method. Any customized formatting takes extra time and effort.

The content of the resulting Excel workbook is static data and there is no association back to the ERP. So, it is not possible to refresh the report. The only way to update the data is to send a fresh export to Excel, which means that any custom formatting will need to be redone. Further, because the resulting Excel workbooks is static data only, there is no drilldown capability to see underlying transactional data.

3. It is not possible to consolidate reporting across multiple companies.

Many SMEs need to run multiple legal entities within Microsoft D365 BC. This is especially true in this era of increased global trade, as many organizations operate separate companies in the various countries where they do business.

Unfortunately, Microsoft D365 BC can only report against one company at a time. For businesses with single entities and basic needs, this shouldn’t pose a problem. For others, though, this limitation significantly complicates the process of producing consolidated financial statements. In the end, finance teams resort to the same time-consuming manual processes described earlier.

This can be especially frustrating during the month-end or year-end closing process, as members of the finance team make adjusting entries and then need to view updated versions of the financial statements on a fairly frequent basis throughout the process. Each update to the report consumes additional staff time, and results in additional delays.

4. It requires specialized technical expertise.

Whenever you are rolling out a new software system, there is a significant amount of adjustment to the way people experience their day-to-day work. The process can be challenging enough for both clients and staff, without having to pay for expensive outside consultants or train valuable internal IT resources to work with complicated reporting tools.

Business Central is compatible with a range of Microsoft reporting tools, many of which are capable of addressing the needs that D365 BC’s built-in capabilities can’t meet. Unfortunately, though, they aren’t very user friendly. RDL reports, for example, allow more options for report formatting and access to multiple companies. Working with RDL requires advanced programming skills, though. You will need access to Microsoft Visual Studio or SSRS Report Builder, along with the requisite skill set to create or modify reports.

To be efficient and make data work for your business, reporting needs to be something that every member of your finance and management team can do without expert help. The process of creating, modifying, and sharing reports should be a self-service exercise.

Reporting in the Multi-Tenant Cloud

As you make the transition to cloud ERP, it is important to understand some of the technical differences between the old on-premise paradigm and a multi-tenant cloud scenario. Traditionally, finance teams have built ERP reports using tools that directly query the ERP database, normally through a standard ODBC (“open database connectivity”) connection. That works quite well in an on-premise scenario, in which you have complete control over your database server, knowing that no one else has access to it. That kind of direct connection to the database is powerful, but it also presents the potential for misuse. For that reason, direct database connections are no longer allowed in the cloud.

When you’re running a cloud-based ERP system like Microsoft D365 BC, a single database server environment houses your data with data from other tenants. For security reasons, it no longer makes sense to allow reporting tools to use a direct database connection. Microsoft has supplied a set of application programming interfaces and Power BI queries that allow access to the underlying ERP data. This makes it possible to block your ability to access other clients’ data, and vice versa.

Unfortunately, though, there are limits to Microsoft’s out-of-the-box queries. One very significant aspect of Business Central’s off-the-shelf reporting queries is that they only provide visibility into standard business objects that exist within Microsoft D365 BC. If you have created extensions within Microsoft D365, or if you are using third-party products that add functionality that your business needs, the standard reporting tools will have no visibility to them.

In practical terms, that means you will need a specialized technical expert to develop custom queries that can access your third-party data and/or your extensions. Whenever you add a new extension or third-party product, you will need to develop custom queries to make the data from those extensions visible in your reports.

Simplified Reporting and Analysis for Microsoft D365

Whether you are looking to transition to a new ERP system or simply migrate your existing Microsoft Dynamics software to the cloud, there is a fast, easy, and affordable way to fill the reporting gaps in Microsoft Business Central. You need a reporting solution designed for Microsoft D365 BC. Jet Reports is available for download through Microsoft AppSource. It enables you to build and run custom financial reports directly in a flexible and powerful tool that everyone already knows how to use – Excel. Jet Reports comes with a library of Microsoft D365 BC pre-built reports that provide immediate value for Microsoft Dynamics 365 customers.

Jet Reports addresses many of the shortcomings discussed above. Jet Reports allows you to continue using your preferred reporting tools like Microsoft Excel or even Power BI, but it makes the process vastly easier, more accurate, and more reliable.

Jet Reports dynamically reads metadata from Microsoft D365 BC, providing full access to the data created and maintained through extensions and third-party products. That means you can quickly and easily act to develop reports that provide information on your entire ERP system, not just the off-the-shelf components. It requires no additional programming.

With Jet Reports, you can create unlimited financial reports, in any format desired, without specialized IT knowledge and training. Jet Reports empowers finance and accounting professionals to make full use of Excel features such as charts, formulas, and images to easily customize and enhance reports and to drill down on any value in an Excel report back to the original source transaction in Business Central. Jet Reports enables you to access live data from Microsoft D365 in real time using custom Excel functions. You can refresh reports dynamically, directly inside of Excel, getting access to the most up-to-date information from your ERP system.

In addition, Jet Reports allows you to build consolidated financial reports from multiple companies, with unlimited dimensions. When you use Jet Reports in combination with Jet Analytics, you can even access data from external systems beyond Microsoft D365 BC, creating visibility to what is happening throughout your organization, regardless of where you store the information.

Jet makes it easy to develop custom reports without specialized IT expertise. You can get non-technical users up and running immediately with an intuitive wizard and leveraging their existing Excel skills, allowing IT to focus on activities that add more value to your organization. By putting business users in control over their reporting, you also reduce the need (and expense) of outsourcing this process to third-party contractors.

Get more visibility into your financial business performance by extending the reporting capability of Microsoft D365 BC with Jet Reports. With multiple companies and dimensions for consolidations, external data input and calculations, and adaptable report formatting directly inside Excel, your users will be able to easily create the reports they need in record time. Download our Jet Solutions brochure, built for Microsoft Dynamics 365 to learn more.