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.
Business Applications communities