by Nicholas Jackson, TrinSoft Senior Dynamics Consultant
Dynamics 365 Business Central (BC) is a powerful tool, enabling users to track invoices, create sales orders, check inventory levels, and more all with a few clicks. But once clients have used BC for a while and have a good dataset in BC, the next question is how to organize and retrieve the data to make business decisions from it. Fortunately, BC has many options for reporting, and this post will highlight the 3 main options: native reports, refreshable Excel reports, and Power BI dashboards.
Precursors to Reporting
Before we dive into the options for reporting in BC, there are 3 questions that must be answered before any reporting exercise can begin:
- What is the problem you are trying to solve? This answer will provide a good direction for choosing where to build your reports and what to include in them. Remember, reports are a means to an end, not the end themselves.
- Do you have the required data? Good reporting is based on good data. That means if the data does not exist, neither can the reports. This may seem obvious, but it is a question worth asking.
- Where is the data stored? Assuming you do have the data required, the data must be located. For the sake of this blog post, we will assume the data is in BC, but for other reports, that may not always be the case. In some scenarios, a data warehouse may be worthwhile to aggregate your data before consuming it in a report.
Once these questions have been answered, the next question is where to build the report.
Dynamics 365 BC Native Reports
BC provides hundreds of native reports out of the box. A simple search on the word “inventory” yields over 27 results alone.
Each one of these reports has a host of options and filters to yield the exact results desired. They also have options of exporting to multiple sources, such as PDF, Word, and Excel (data only or data and format).
Pros of Dynamics 365 BC Native Reports
Native reports have one major benefit: they are native. That means you can access your report inside of BC without leaving and going to another application. BC also enables you to schedule native reports, enabling you to get an emailed copy of the report on a regular basis (e.g., receive your top 10 customer list delivered to your inbox every Monday morning at 8 AM).
Cons of Dynamics 365 BC Native Reports
Native reports do have some customization options in terms of what data is displayed, but they are largely hardcoded into the system. Any customization to what fields are displayed requires development, putting it out of reach of most end users. The same goes for creating entirely new reports, as they also require development. Lastly, the scheduling feature of native reports is limited in that it can only support a single filter set. This means that you can schedule a sales report with the date filter for the first week in February, but if you schedule it, you will always receive a copy of the report in the same timeframe; there is no native way to use dynamic date ranges.
Refreshable Excel Reports
Refreshable Excel reports are a powerful option for users wishing to translate data before using it for reports. The other benefit is that it leverages a tool most BC users are already familiar with: Excel. Users can create their own web services to be consumed by Excel, and even leverage tools like Power Query for powerful formula-free data manipulation.
Tip: When generating your web service, make sure to grab the lowest-level data possible. For example, if you want sales data, grab the customer ledger entries instead of the customer card. This gives you much more power and flexibility in building your reports.
Pros of Refreshable Excel Reports
Refreshable Excel reports are flexible, powerful tools that bring your data into Excel. If configured properly, they are refreshable, meaning with a click of a button, the data will be refreshed with the latest entries and your reports will be updated automatically. There are few tools faster for aggregating data than pivot tables, allowing for quick and easy summarization of your entries.
Cons of Refreshable Excel Reports
While refreshable reports are powerful and flexible, there are a few limitations. First, web services require a slightly more technical understanding of BC compared to native reports. Web services are also built upon the page data source instead of the table, meaning if a field is not available on the page, it will not be available in the web service. Refreshable Excel reports connecting to BC as a data source are not currently supported on Excel for Mac (though support is currently in preview). Lastly, depending on your data source size, refreshable Excel reports connected to BC can be slower than other data sources. There are some advanced options for filtering your data before it reaches Excel, but large reports can take minutes to refresh due to the large amount of data and complex transformations that need to take place on your machine.
Tip: For the power users out there looking to increase the performance of their refreshable Excel reports, look into OData URL filtering. BC web services are built on a protocol called OData, which enables filtering and selecting your data before it even reaches Excel, often speeding up slower queries.
Power BI
For those who are looking for a visual representation of their data, Power BI is the go-to tool for analysts across the world. Few tools are more successful than Power BI when it comes to creating interactive visuals with ease. Power BI is free for personal use, enabling you to test building of visuals without any financial commitment.
Pros of Power BI
Power BI takes the complexity out of building visuals, placing the power in the hands of the user. An intuitive interface makes building reports a breeze. Nearly all the visuals are interactive, enabling the consumer to drill down and filter the data live. No other tool can create visually stunning dashboards like Power BI. These dashboards can be put directly into applications like BC or Microsoft Teams, placing them right where the consumers spend their time.
Cons of Power BI
Power BI does have a slight learning curve for those looking to build reports from scratch. Setting up the data model requires an understanding of basic data relationship principles, but once configured, the visuals practically build themselves. Publishing and sharing a Power BI report does require a license, but the cost is minimal and is even included in Microsoft E5 licenses. Lastly, as is the case with any reporting tool, Power BI is not always the right tool for the job. If you are needing a tabular report or something without visuals, Excel or native reports would be a better option.
Microsoft provides many choices for reporting on your data in BC. There is not a one-size-fits-all solution for reporting, but this post should help you decide where to build your next report.
Not sure where to build your report? Need help organizing your data model or assessing your need for a data warehouse? Contact TrinSoft today and we will gladly assist you with all your reporting needs.
