Skip to main content

Notifications

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

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

Posted on by 75

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
  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 11,425 User Group Leader on at
    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 Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    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 Profile Picture
    MahGah 15,428 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,228 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans