Microsoft Dynamics SL 2018 & Web Apps Available Now!
Dynamics 365 2019 release wave 2 plan Discover the latest updates to Dynamics 365.Release Plan | Weekly Deployment Notes
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 and Operations TechTalks | Customer Engagement TechTalks | Talent TechTalks
I am a developer for a company where Dynamics SL is used(I don't have any proir experience in SL). We have a cloud based BI application for executive dashboards and other collaborative reporting purposes. I am trying to export some key data from SL to build some kind of dashboard outside SL. I have full access to SQL server database, but the number of tables and views are overwhelming. I really don't know how to start looking for any kind of data. Here are some reports I am planning to build.
Total Ap - this month & prev month
Total AR - this month & prev month
Invoices - Overdue, Paid, Open
Vendor performance / Vendor Spend analysis.
Open balance by vendor, Vendor expenses YTD
Purchase Order - POs by vendor, Open POs by Period
I really need a starting point. Could anybody share some SQL queries I can use to start digging? I tried searching many forums. I can see queries for GP, AX.. but i am not getting anything useful for SL.
Any help is really really appreciated.
I know how you feel. I inherited Dynamics SL too. We only use the AR module. Tables most important for AR module are Ardoc, Artran, and Aradjust. In Ardoc the CuryOrigDocAmt field gives you the original amt, the perpost field gives you the month it was posted, the doctype field gives you the type like IN - invoice, cm - credit memo etc.., and docbal field gives you the outstanding balance.
You can link the ar tables by refnbr and batnbr. I think the tables for Ap are the same.
I hope this helps you a little.
In the Dynamics SL client click Help, Help Topics and scroll down to schema and it will list all the fields in all of the tables with somewhat of a description as to what they mean.
Here some information:
APDoc Documents Header (All Documents Vouchers, Adjustments, Checks)
APTran Documents Detail
APAdjust Relations between documents
ARDoc Documents Header (All Documents Invoices, Adjustments, Payments)
ARTran Documents Detail
ARAdjust Relations between documents
PurchOrd Purchase Orders
PurOrdDet Purchase Orders Detail
POReceipt Receipt Headers
POTran Receipt Detail
If you see once you see the names is not complicated. Check also the views that start with QQ this were built the be used by the final user.
Business Applications communities