Inventory analysis reports are a newer functionality that gives Dynamics 365 Business Central users greater insights into key inventory reporting metrics for their business. This functionality
- Provides businesses advanced inventory reporting capabilities.
- Is a built-in standard reporting tool within Business Central (no extension required).
- Is the inventory equivalent of account schedules.
- Provides a seamless reporting output of sales, costing, and pricing data from your inventory subledger.
I’ll explain how to set up and use inventory analysis reports, as well as how to push the data to and from Excel.
How to Create an Inventory Analysis Report
- Decide what you want to analyze. You can analyze many aspects of your inventory and sales costing, such as
- Total inventory adjustments over a given period,
- Total inventory cost amounts over a given period, or
- Inventory sales amounts compared over different periods.
For this example, I will walk through how to generate a year-to-year inventory sales comparison between 2020 and 2021.
- Search for “Inventory Analysis Column Templates” within Business Central.
- Create a new inventory analysis column template record.
- Generate columns for your inventory analysis report. Give each column a number, header, and unique description. Those will appear in your reporting later. You can also create comparison period formulas here to analyze net changes and balances at dates with inventory subledger transactions.
You also need to select an analysis type code as part of each column. There are a few default analysis type codes based on the inventory subledger that you can use and modify to build the analysis columns. In this example I used a default analysis type code.
- Set up your inventory analysis lines. Search for “Inventory Analysis” and select the line templates page.
- Define a name and description for your line template as well as an analysis view. (I’ll explain the setup for analysis view in the next section..)
- Set up your inventory analysis view code by searching for it in Business Central.
- Update your analysis view to pull the most recent inventory subledger information based on the dimensions and the date ranges you wish to track.
- Create a new analysis view card and populate the dimensions to track within the inventory subledger as well as the lines and columns of your view.
- Now it’s time to generate your inventory analysis report! Search for “Inventory Analysis Reports” in Business Central.
- Populate your inventory analysis report name and description and select the analysis line template and analysis column template that you set up in the previous steps. Once you’re done, select “Edit Analysis Report” on the ribbon.
How to Export Your Inventory Analysis Report to Excel
- Select “Show Matrix” on the Inventory Analysis Report page to generate the matrix report output within Business Central.
- Select “Open in Excel” once the matrix is displayed to export the report results to Excel.
- The Excel matrix inventory report output is now exported as an Excel file.
What if I want another way to get an Excel report from my inventory analysis report?
No problem there. Select “Create New Document,” and that will generate a new Excel workbook file.
You can also update an existing Excel document by selecting “Update Existing Document.” From there, select the document from your desktop by clicking the “Choose” button.
How can I publish my data from Excel back into Business Central?
Just select “Edit in Excel” on your Inventory Analysis matrix view to get a dynamic Excel file that you can publish back into Business Central.
How do I change the date range I want to export in my inventory analysis view?
No problem. Just change the column set by selecting “Previous Set” or “Next Set” in the matrix option view range.
Happy inventory report building!
For more information on inventory and reporting within Business Central, you may also find these resources helpful:
- Physical Inventory Year-End Process (Video)
- 6 Ways to Automate Financial Reports (Video)
- Sales and Inventory Forecast
To get support on reporting or any other aspect of Business Central, please contact us.
Subscribe to receive our monthly newsletters with the latest updates all in one place! Get important product information, event recaps, blog articles, and more.