Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

Dynamics SL aged AR query

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?

thanks
0
jlingg
Asked:
jlingg
1 Solution
 
James GlaubigerCo-FounderCommented:
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.

0
 
DarkAztecCommented:
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.

0
 
Philippe BoulosCommented:
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.  
fn08611.sql.txt
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now