Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2020 release wave 1Discover the latest updates and new features to Dynamics 365 planned through September 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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.
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.
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.
Is there a way to draw this information out of NAV into excel without the use of a ODBC Connection to SQL?
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.
“…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?
Business Applications communities