Generating insights from F&O, is one of the single most important activities you will likely be undertaking once Live. It may well be the primary driver for adopting an ERP solution in the first place, facilitating a more data driven organisation, leveraging a consistent and comprehensive version of the truth. Whilst most people will initially think of Power BI dashboards and management reports, there are many ways you can leverage F&O to extract information adhoc, or more frequently to get the data (and insights) you need. This series of posts will explore each of the options in turn, starting with a birds eye summary of all available options. Not all reporting needs to be about insights though, sometimes you just need to get a list of records or transactions, so we will start small with simple data extraction and build from there.

Standard Views

The quickest and simplest option available, with literally hundreds if not thousands of standard views across records and transactions alike, standard views make a great first option. My colleague Paul Sinnott has provided a great summary of Filtering in D365 F&O which uses an example of the “all suppliers” view.

Standard Enquiries

Various specialised enquiry screens are available, providing filters, parameters and options to generate quick ad-hoc information. Examples of these include the Trial Balance enquiry within the General Ledger, and Project Statement within the PMA module. These enquiries often exist for frequently enquired data, often with an interactive drill through capability.

Standard Reports

Hundreds of standard, parameterised reports exist across all modular areas. These are typical paginated reports we are all familiar with, and exist for printing, non-interactive distribution or storage as part of accounting records such as the Customer ageing report shown below.

Office Integration (Excel Data Connector)

Often accompanying the Standard Enquiries is the ability to “Open in Excel” which provides a refreshable, personalisable and updatable Excel spreadsheet. These can be refreshed independently using the pre-installed Dynamics 365 Excel Data Connector Add-In, providing a great option for getting information out quickly.

Analytical Workspaces (Power BI Embedded)

Role based workspaces are often accompanied by an Analytical workspace (Analytics tab), providing embedded and interactive Power BI reports with multiple pages and charts. These Power BI reports provide an excellent foundation off the shelf, which can be used to tailor more personalised dashboards.

Financial Reporter

The Financial Reporter provides a dedicated and accounts aware tool for users to producing interactive and paginated financial statements from income statements (P&L) to balance sheets. The Financial Reporter provides the ability to fine tune the definition of columns, rows, filters and financial amounts, using familiar excel-like formula to define as many varieties of report as required, an example of the reporting output is shown below, which includes interactive capabilities.

Business Performance Analytics

Microsoft provides a foundation of Power BI based reports, and supporting data models designed to give you more power when combining third party data sources, and enriching your data with AI and other advance capabilities, as well as enhanced capabilities for off the shelf reporting. This additional module makes it easier to move into more advanced Power BI based analytical scenarios, whilst building on top of a strong and productised foundation.

Power Query (Power BI / Excel)

Power BI and Excel share the ability to leverage Power Query Editor to extract information directly from F&O using the Open Data (OData) API’s. Whilst this might seem quite technical, my colleague Mike Pearsall has written an excellent post about extracting a Trial balance using OData in Excel from F&O.

Azure Synapse Link for Dataverse

Whilst Business Performance Analytics provides means to start a more advanced analytical journey. Organisations with their own Enterprise Data Strategy and more advanced internal capabilities, may want to leverage a more tools based approach for ultimate flexibility. Azure Synapse link for Dataverse can be leveraged using the native connectors between F&O and Azure Data Lake Gen2 storage which is part of Synapse. This is a much more advanced topic to cover, and will likely be the subject of its own series of posts, but its worth mentioning at this time.

Electronic Reporter (ER)

Electronic reporter can be used to create highly configurable file outputs, these file outputs whilst traditionally for data interchange, such a remittance advice or BACS file export. Could provide a suitable solution for extracting a tailored file for use in onward reporting chains. This is certainly a more advanced option, requiring a high level of technical knowledge, so we will leave this one for last in terms of onward exploration.

Summary

There are a myriad of ways to extract information from F&O, and I highly expect you will only use a few of the above options on a regular basis. In the remaining posts on this topic, we will explore each of the reporting options in more detail and discuss the relative benefits of each, providing insight and context for how to extract the information you need.

Published by Jason Newbatt

Experienced commercial and technical Director for Dynamics 365 with a deep background in ERP across range of industries, having a strong complimentary understanding of the wider Dynamics 365 platform. Ex-Microsoft Technical and Commercial Specialist.

Leave a comment

Your email address will not be published. Required fields are marked *