Quickly Grouping Data and Adding Totals in Dynamics 365 Finance & Operations

When you’re working with hundreds of transactions or inventory items in Dynamics 365 Finance & Operations, and you want to quickly find important information, the grouping and summing functionality is a great tool.

It can help you efficiently answer specific questions like “What is the total value of outstanding purchase orders for this SKU?” and “How many of this specific item do I have on hand in a certain warehouse?”

When I show this functionality to warehouse managers, buyers, or analysts, I see their eyes light up. If you have a large amount of transactions or inventory, there can be so much “noise” in the data — you need a simple way to drill down and get the specific, aggregated info that is important to you.

The best way to explain how to use this functionality is with specific examples. Click on the one that seems most relevant to your use-case.

Table of Contents:

• Summing Total Quantity On Hand
• Finding Total Open Sales Orders
• Grouping Open Purchase Orders By Vendor

Note: Even though most customers, commentators, and consultants still call it Dynamics 365 Finance & Operations or D365FO, Microsoft now technically licenses it as Dynamics 365 Finance and Supply Chain Management.

Summing Total Quantity on Hand for a Specific Item

Warehouse, purchasing, and sales managers often want to check the total quantity on-hand for a specific product, so they can see their levels for inventory control and purchasing needs, or to see if sales orders can be fulfilled. For instance, you may want to find out how many of a certain type of office chair you have available in your Seattle warehouse. In Dynamics 365 Finance & Operations, the steps to sum up the quantity on hand for a specific item is:

Step 1: From released products, go to Manage Inventory > View > On-Hand Inventory.

Step 2: Once in the On-Hand screen, make sure you bring in your dimensions, such as site, warehouse and location, so you can group and sum your totals by these as needed.

Step 3: To see the total of the Physical Inventory column, right mouse click on the Physical inventory column heading, and from the drop down, choose View Column Totals > Sum.

You can now see the total. This can be done for each of the columns where you want to see the totals to make decisions moving forward.

Finding Total Open Sales Orders

Sales and warehouse managers may want to know the total value of open sales orders. This helps to see what orders can be processed based on the availability of items you have on-hand or due dates of those orders.

The steps to quickly add up the total value of all open sales orders in Dynamics 365 Finance & Operations are:

Step 1: Go to the Sales and Marketing module, and select Sales Orders > Open Orders > Open Sales Order Lines.

Step 2: Once at the main screen, bring in any columns needed to carry out your totaling, such as Net Amount. You can see below that I have totaled the Net Amount of the open sales orders.

Step 3: To see the totals by sales orders, use the grouping function. Under the sales order column, right mouse click on the words Sales Order, and then select Group by this column:

The results then group each of the sales orders together, with a total for each one at the top of the grouping under Net Amount.

This grouping feature can be used to group customers or items together and show their totals as well.

Grouping Open Purchase Orders by Vendor

Purchasing and warehouse managers often want to group data for open purchase orders for vendors. This helps to determine when purchase orders are scheduled to arrive for a particular vendor.

You can use a similar process to group the purchase orders by other attributes such as purchase order numbers.

In Dynamics 365 Finance & Operations, the steps to see your open purchase order lines and be able to group the orders by vendor are:

Step 1: Go to Procurement and Sourcing > Purchase Orders > Purchase Order Follow-up > Open Purchase Order Lines.

Step 2: Remember to insert columns needed to do your totaling and grouping, such as Net Amount. In the example below, I did a sum on the Net Amount and Deliver Remainder.

Step 3: If you need to group the open purchase orders by vendor, go to the vendor column and right mouse click on Vendor Account and choose Group by this column.

You can now see each vendor’s purchase order has been grouped with the total added up in the net amount and deliver remainder columns.

To remove the grouping, go back to the vendor column, right mouse click on vendor account and choose Ungroup.

Increasing Your Efficiency with Dynamics 365

Companies using Dynamics 365 Finance & Operations often have large volumes of transactions and inventory to keep track of, and often over multiple locations and jurisdictions.

There are a wide variety of tools within the system that can help you get your work done more quickly and accurately. But it can be difficult to know the best way to handle each of your tasks.

At Encore Business Solutions, we offer support, training, implementations, and integrations of Dynamics 365 Finance & Operations and the rest of the Microsoft stack. You can view our full archive of F&O training articles or learn more about our work as a Dynamics 365 F&O Partner.