If you’ve implemented the advanced warehousing functionality in Dynamics 365 Finance and Operations, you probably have come across the requirement to report on cycle counting accuracy and several other cycle counting related metrics. Unfortunately, the out of the box reporting on cycle counting is limited to one cycle counting report called “Counting statistics”.
As you can see the report only shows the overall count accuracy report. There are not details, dates, quantities, user IDs, or anything that would be required for a detailed report. After doing some digging, I was able to find an excellent workaround that met all the reporting requirements for cycle counting completely out of the box. In this article I am going to show you what I used and how to utilize it.
Note: This approach only applies if you are using the advanced WMS functionality in Dynamics 365 Finance and Operations for conducting cycle counting.
Cycle Count Work Data Entity
When cycle count work gets created it will appear in the “Open cycle count work” list page with a status of “Open”. The user will then be able to log into the WMS mobile application and complete the cycle count work. When a cycle count work record is completed, the status is set to “Closed” and the results of the cycle count are captured in a table called “WHSWorkLineCycleCount”. This table contains a number of important details that are relevant for most cycle counting reporting requirements.
Fortunately this table has a data entity associated with it called “Cycle Count Work” which means we can use the “Open in Excel” functionality to not only extract the data to excel but we can connect to the data entity and remove some filters to see this data for all cycle count work records.
This data entity is not listed in the data entities list. I think the reason for that is because the WMS mobile application needs this data entity to perform updates, create operations through the entity but you would never really use the entity for data migration.
- To access the data entity navigate to Warehouse management > Cycle counting > Closed cycle count work.
Click on the first record in the list page and click the “Cycle counting” button in the “Work” tab in the ribbon.
This will take you to the “WHSWorkLineCycleCount” table. Here you can see all the details of the cycle count record.
From here you can click the Microsoft Office icon in the right corner and click the “Open in Excel” option. For more information and a detailed user guide on the “Open in Excel” functionality see my article How to Use Excel Add-In: Dynamics 365 Finance and Operations.
Click the “Download” button and the Excel file will download. Open the data from the counting record you were on will populate into the spreadsheet.
In the next section I am going to show you how to build the report and get all the data not just the data under the cycle count record you exported.
How to Build the Report
Now that we have the Excel file connected to the data entity, we want we need to get the rest of the cycle count data instead of just the data associated to this one cycle counting record.
To do this we will use the filter button to remove the filter for this specific work ID and open it up to all cycle counting work.
- Click the filter button icon in the “Data connector” pane.
Click the trashcan icon next to the “Work ID” filter.
Next click the “Done” button.
Then click the “Refresh” button. This will refresh the data entity and bring in all the cycle count work results.
From here we can just use standard Excel to build any cycle counting report we want. The first one we will build is the inventory accuracy report.
- To do this first click on the select all triangle in the top left corner of the sheet. This will ensure that all data refreshed into the sheet will be included in our pivot tables.
After the data is selected click the “Insert” tab in Excel and click the “Pivot table” button. Click “Ok” to create the pivot table.
Drag the data elements to the respective “Rows”, “Columns” and “Values” sections in the pivot table builder.
Now you can see we have a much more detailed report on cycle count accuracy that includes by date, the user that conducted the count, and the number of counts performed on each item and at each location.
If you still need additional details you can add additional data sources and bring things in like item cost, to give you cycle count accuracy with a monetary value to give you the financial impact.
You can also add calculated fields to the entity connection so that you can not only include it in your pivot table, but it will also be refreshed the next time you refresh the data entity. For a step by step guide to adding a calculated field to a data entity from a entity connected workbook see my article How to Use Excel Add-In: Dynamics 365 Finance and Operations.
Takeaways
I hope I have provided you with a possible out of the box solution to use instead of the standard basic report associated to cycle counting, and have given you the tools to expand the report to meet any unique reporting requirements your business may have.
