How to Create a Simple Sales By Customer By Item Report with Business Central
Reports are an invaluable tool for businesses. They provide decision makers with data needed to make correct decisions about the organization. Microsoft understands the need for reports and real-time data and that's why Microsoft Dynamics 365 Business Central's reporting functionality is head and shoulders above their competition. In fact, it's so advanced we're going to show you how anyone can easily build a Sales by Customer by Item report. Let's get started!
This report is simplified and is going to include the fields Sell to Customer Name, Posting Date, Document Number, Item, Item Description, Quantity, Sales Amount, Cost Amount, Gross Profit, and Gross Margin. If we were to do a more in-depth report we'd also include Posted Sales Return Receipts and Posted Sales Credit Memo. The main driver of this report is the Item Ledger Entries Report
Step 1: In Business Central navigate to the Web Services Page.
Step 2: Select New and create a new page.
Step 3: Search for the Object ID needed. For this report we need Item Ledger Entries.
Step 4: Enter a Service Name.
Step 5: Select Publish and a URL will populate.
Step 6: Copy the URL.
Step 7: In Excel, select Get Data, select Other Sources, and then select OData Feed.
Step 8: Paste the copied URL and select OK.
Tip: If you receive an error message that informs of you not being able to connect do the following and retry Step 7 and Step 8:
Select Cancel.
Select Get Data, select Data Source Settings, select Clear Permissions, and select Delete.
Select Get Data, select Query Options, select Clear Cache, and select OK.
Re-sign in with your credentials.
Step 9: We can now see the data. We want to run this report just for July so we're going to click Transform Data to add the correct filters. For this example we'll be adding the date filter between 07/01/2022 and 07/31/2022 as well as a filter for Entry Type to show only Sales for the month.
Step 10: Once we have chosen the proper filters we need to select Close & Load. This will refresh the Query with our live data.
Tip: We recommend renaming your Query because we're going to be working with more than one. You can do this by right clicking on the Query name located on the side bar within Excel and selecting rename. We're going to call ours Item Ledger Entries.
Tip: We recommend renaming your Excel tab. We've named ours Report and Item Ledger Entries.
Step 11: It's time to link the report to our Query data.
From the Item Ledger Entries Tab copy the first posting date box shown and paste it under the posting date in the Reporting tab. Do the same for Document Number, Item Number, Quantity, Sales Amount Actual, Cost Amount Actual.
Enter a Gross Profit calculation: Sales Amount + Cost Amount
Enter a Gross Margin calculation: Gross Profit / Sales Amount
Now we need to link the Item Description. We know Item Description exists on the Item Card and that means we'll need to create another Web Services Page for this by following steps 1-8 from above.
On the Web Services Card in Business Central, select New, select page, select Object ID, search for Items and select Items (ID 31), enter a Service Name, select Publish, and then Copy the URL.
In your Excel report select Get Data, select From Other Sources, and select OData Feed. Paste the copied URL and select OK.
We don't want to apply any filters on this query so we simply select Load.
We'll rename the Query and the Excel tab.
In your report under Item Description we need to enter a VLOOKUP.
=vlookup(D3,Items!A:B,2,false)
The field is now populated
Next we need to link the Sell to Customer Name and this will require us to create another Web Services Page.
On the Web Services Card, select new, select Page, select Object ID, search for Posted Sales Shipments (ID 130), Enter a Service Name, select Publish, and then copy the URL.
In your Excel report select Get Data, select From Other Sources, and select OData Feed. Paste the URL. Select OK.
Now we need to apply some filters. Let's select Transform Data and filter by posting date 07/01/2022 and 07/31/2022, select Close and then select Reload.
We'll rename the Query and Excel tab.
In our Report under Sell to Customer Name we need to do a VLOOKUP.
=vlookup(C3,PostedSalesShipments!A:B,2,false)
The field is now populated.
Step 12: Select and drag down to populate fields.
We have now created a Sales by Item by Customer report with Business Central. In the future to refresh this report and pull in updated real-time data we select the Data tab on the top ribbon in Excel and simply select Refresh All.
Do you have more Business Central reporting questions? Fill out the form below and one of our team members will be in touch with you shortly.
