Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Sales Orders Created $...
Microsoft Dynamics NAV (Archived)

Sales Orders Created $ Per Month Report

(0) ShareShare
ReportReport
Posted on by Dave Traves

In the middle of trying figure out how to create a report that shows how many sales orders and total $ value of all sales orders created for a given month.

Now before you point me to any of the built in reports (e.g. Outstanding Sales orders / sales orders status etc..) let me mention that I'm looking for Booked Sales Orders, not outstanding.

In other words:

If the Sales order was created in Jan it's total value should be on the report. Even if it's shipped and/or invoiced (which built in reports don't show because their not sales orders anymore)

If S.O. is shipped and/or invoiced yet was created in Jan, it needs to show.

SO#-23443     Status-SalesOrder     Total-$23,432

SO#-23435     Status-Shipped          Total-$43,215

SO#-52443     Status-Invoiced          Total-$34,232

Something like the above, don't need status, but might be a nice touch and easy once the other info is gathered.

If a user wanted to see Booked orders, they'd have to reference something like:

Sales Header/line tables for Outstanding Orders

Sales Shipment receipt Header/line tables for all other orders (shipped/invoiced)

Problem with above is that some orders in the shipment receipt tables might have been booked in Dec or previous and shipped in Jan. You wouldn't want to include these, so you'd probably have to archive the Sales Orders first (only work on information going forward obviously if this setting isn't on.), and then cross reference items in the sales shipment receipt tables with this archive information to ensure you only had entries that belonged in January's bookings.

One reading the report must understand that this information once reported each month may change as customers could cancel orders / change orders / may not be shipped till next month etc..., so it will definitely not match sales reports.

Anyways... I believe the term booked orders means orders "taken" that month regardless of their status after.

Any ideas about the following would be greatly appreciated.

I'll most likely just test the best scenario first with excel via odbc sql data, so any reference to the best tables to get this information from would be helpful.

I thought Item Ledger Entry Table at first, but it doesn't record prices and you'd have to cross-reference it with value postings? I think.

Shipment receipt line records unit price and item charge base amount (which is the extended price including discounts), so you could get all relevant information from there and sum the items with the same Order No_ (sales order no)

that's where I'm at right now with it. Biggest problem with this report is items booked in Dec shipped in Jan.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Sales Orders Created $ Per Month Report

    “…easier to validate results and get them in Excel and then build a report rather than messing with the beautiful report builder built into NAV (2009)”

    No report writer in any version since then. So, if you’re not a $500/hr, 8-hr minimum per report, ‘partner’, your only choice is Excel, and:

     “…I do a vlookup on the sales order #

    "…I do vlookups on document date

    "…I do SUMIFS sums on the line tables

    "…now subtotal your sums”

     

    Wouldn’t a real report writer be the better solution? So that in your absence someone else could run it, without all the manual interventions needed in your Excel solution?

     

    “Instead of using ODBC and the RDLC reporting tool, you could've also build your report in NAV and have it export to Excel using Excel buffer.”

    And how would a regular user, without programming knowledge, or a developer’s license do that exactly?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Sales Orders Created $ Per Month Report

    I actually implemented something like this many years ago in an old (3.6 !)  Version.  I don't remember all of the details, but we created a separate "booking" table (or tables) and functions that populated it with order values based on changes to open orders.  So a new record was created any time the order value changed.  In that way, we were able to report by period.  For example, if a $1000 order taken in January was updated to become $1200 in February, monthly bookings would show $1000 in January and $200 in February.  Similar kind of arithmetic for reductions and cancellations.  I had to convince management that "bookings" had to be considered the value of an order at the time that value changed.

    Conceivably, archive records could be used instead, but you have to handle the arithmetic differently, and I think add some automation to be sure archiving occurred at the right time (standard function only archives on posting).

    Hope that helps.

  • jregan Profile Picture
    jregan 674 on at
    RE: Sales Orders Created $ Per Month Report

    Is there a way to draw this information out of NAV into excel without the use of a ODBC Connection to SQL?

  • Alex Chow Profile Picture
    Alex Chow 4,481 on at
    RE: Sales Orders Created $ Per Month Report

    Booked meaning the date that the order is taken right? Shouldn't you be using the Order Date instead of Document Date?

    Instead of using ODBC and the RDLC reporting tool, you could've also build your report in NAV and have it export to Excel using Excel buffer.

    All you really need  are 2 tables. Sales Line and Sales Invoice Line. Create flowfields to the Order Date on the header and you're ready to go.

  • Suggested answer
    Dave Traves Profile Picture
    Dave Traves on at
    RE: Sales Orders Created $ Per Month Report

    Might as well answer my own question here:

    NOTE: I used ODBC Connection to SQL to draw from the tables directly to Excel to solve this problem as it's much quicker/easier to validate results and get them in Excel and then build a report rather than messing with the beautiful report builder built into NAV 2009 <sarcasm>. You can apply the same results in NAV.

    I'm drawing my information from the following tables:

    Sales Header Table / Sales Line Table

    (gives me my open/released Sales Orders and my shipped not invoiced orders)

    Sales Invoice Header Table / Sales Invoice Line Table

    (gives me the Invoiced documents)

    Using the data connections I first created 4 worksheets and connected each above table to a worksheet.

    Then using some code on workbook_open() I added all of the Sales order No.'s from Sales Header Table in column A and the Sales Order No's from the Invoice header table in the same column (there will be some repeats if orders have been partially invoiced.. for me that's ok because I'll only show the amount invoiced on the one line and the amount back ordered on the other so#)

    In Column B the code adds either Order or Invoice text to the cell to tell me which table to sum the amounts in.

    before the on Open event runs (and adds all the SO#'s), the data connections refresh to give me up to date info.

    Once the code adds the SO#'s, it's all just excel formula's from here.

    I do a vlookup on the sales order # if type is invoice and put the invoice # in another column (can't reference the sales invoice line table by so#)

    then I do vlookups on document date (something to filter monthly by) remember I want to know montly bookings, and the document date is created when the order is created and copied to the invoice header assuming the user doesn't change it. (it should be outside their allowable posting range in my case)

    Once I have document dates I do SUMIFS sums on the line tables: (ensure your not double accounting for items on the invoice and sales order) sumifs all amounts on invoice as items not invoiced show as 0, on the sales order line, you have to make sure to not include qty's that have been invoiced.

    now subtotal your sums and your done.... much easier than I anticipated.

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,309 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans