Dynamics SL aged AR query

Posted on 2008-11-13
Last Modified: 2012-05-05
I need to develop a sql query that returns invoices that are more than 90 days overdue. I looked at the aged ar report but the query that it uses is a little confusing.  I also need to be able to export this data to excel so just exporting the aged AR report doesnt work well.  Can someone give me a query to return these invoice that can be used in SQL Server?

Question by:jlingg
    LVL 7

    Expert Comment

    by:James Glaubiger
    Please post a sample output of the tables that the current report uses.  We can then build a query.  

    1. Export a subset of data including column names to excel, use one sheet for each SQL table that we need to pull data from.

    I can then build you a query.

    LVL 1

    Expert Comment

    Hi, this is the SQL Query:

    select refnbr,custid,* from ardoc where doctype='in' and duedate >= 01/01/2008

    You must change the end date, in this sample this query will print all invoices that has a due date greater or equal than 01 january 2008.

    LVL 2

    Accepted Solution

    Since you need to be able to export this to excel, have you considered using sql reporting services?  One of the output formats is Excel.  Another option is to have the query used by SL transfered either into a procedure or a function.  I took the ps aged ar query and converted it into a sql function.  I took the standard aged ar and moved it into a stored procedure.  Both work great.  

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
    On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now