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

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

MahGah 15,529 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,111 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Dallefeld 211 User Group Leader on at

Like (0)

Report

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates