SBX - Search With Button

SBX - Forum Post Title

Dynamics SL SQL Query for exporting data from Modules

Microsoft Dynamics SL Forum

sreeharivijayan asked a question on 1 Jun 2019 2:29 PM

Question Status

Unanswered

Hi Team,

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

current Aging 

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

Inventory transactions

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.

Thanks,

Reply
BarbaraF responded on 7 Jun 2019 9:06 AM
My Badges

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.

Reply
JohnTwohig responded on 17 Jun 2019 4:40 PM

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.

Reply

SBX - Two Col Forum

SBX - Migrated JS