Small and medium business | Business Central, N...

How do we look at detailed historical sales by month from an entity?

(0) Share

Report

Posted on by FJR

For our case it would be a government entity. Customers go to a government site, choose the items they want to purchase, then we get sent an order notification from the government site, and we fullfill that order.

Basically, can we spit out a report (in Excel) that shows lines for each Item sold in a given period (monthly) from a specific entity, fields below are examples:

1. Manufacturer Name
2. Manufacturer Part Number
3. Unit Of Measure
4. Quantity of Item Sold
5. SIN Number
6. Price Paid per Unit

Categories:

Dynamics 365 Business Central

All responses (3)

Answers (0)

Suggested answer

Dallefeld 11,425 User Group Leader on at

Like (0)

Report

RE: How do we look at detailed historical sales by month from an entity?

You might also consider using Excel Layouts. The Item Sales by Customer is a great report and gets you a long way to what you want. Don't forget that you can use all Excel tools like pivot tables and filters.

For the fields not on the Item Sales by Customer, you might have to do a secondary report to Excel and then a third to combine the two.

Its a bit of work up front by then the data is repeatable as needed.

I might do what Inge suggests and get a developer to program a report but the above is an option if you don't want to contract for a customization.
Suggested answer

Inge M. Bruvik 32,748 Super User 2024 Season 1 on at

Like (0)

Report

RE: How do we look at detailed historical sales by month from an entity?

I think you should look at the data you have in the item ledger entry table. Combined with the item table this will give you the information you are looking for.
Suggested answer

MahGah 15,428 on at

Like (0)

Report

RE: How do we look at detailed historical sales by month from an entity?

Hi

I think the easier way is to hire developer and then design it in BC.

If that is not option and you have Jet Report use that. You need the following tables in the next option.

If you do not have any of those then this is what I recommend (for BC SaaS) go to Web Service then Action -> New Document -> Create Data Set then publish below pages (one page at the time). Also take a note of each page name that you name

Page 131: Posted Sales Shpt. Subform

Page 130: Posted Sales Shipment

Page 31: Item List

Page 5737: Item Reference Entries

Then use this guideline and use these pages in Excel. Use Power Query then try to join them together. after that you have your report.

community.dynamics.com/.../getting-d365-business-central-data-to-excel-using-odata-feeds

Loose logic

For p[age 130 filter for customer no and date range then use that to filter Page 131 to find all Items and their QTY and price paid . From there use Item No to filter Page 31 or 5735 to get MFG info.