Tag Archives: SQL code

SQL view to show security roles and tasks in Dynamics GP

March 12, 2009 by Victoria Yudin 25 Comments

Dynamics GP 10.0 has brought about a drastic change in GP security. Not only is the security pessimistic now, so by default no permissions are granted, but setting up and administering security is quite different from what many of us are used to after working with previous versions of GP for many years. And, of course, reporting on […]

Continue reading →
Crystal Reports, Dynamics GP, GP 10.0, GP 2010, GP Reports code, GP SQL scripts, System/Setup SQL code
, , , , , , ,

Searching for strings in SQL Server using LIKE

March 2, 2009 by Victoria Yudin 7 Comments

Many times when searching for data in SQL Server we need to search for strings and use wild cards. One common wild card is %. As an example, I am going to use the Customer Master table (RM00101) in the Dynamics GP sample company, Fabrikam, Inc. If I want to all customers with ‘USA’ in the Class ID, […]

Continue reading →
GP Reports code, SQL coding, SQL Server
,

SQL view to show items with SOP POP link

February 4, 2009 by Victoria Yudin 15 Comments

Here is a view to show all Sales Order Processing line items with the SOP-POP link in Dynamics GP.  You can use this with either SmartList Builder Crystal Reports, as I have made sure not to put any spaces in the column names.  I maintain a list of all the SQL code I’ve published on […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, POP SQL code, SOP SQL code
, , , ,

SQL view to show customer pricing

January 7, 2009 by Victoria Yudin 18 Comments

Here is a view to show customer specific item pricing in Dynamics GP when using standard pricing.  This will work either with SmartList Builder or Crystal Reports as I have made sure not to put any spaces in the names.  If you are using SmartList Builder this means you may want to go down the […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code
, , ,

Useful SQL Scripts series from Developing for Dynamics GP

December 1, 2008 by Victoria Yudin 0 Comments

The Developing for Dynamics GP blog has started a new Useful SQL Scripts series.  This is brought to us by David Musgrave and a very impressive team of Microsoft Dynamics GP support engineers.  Many of these engineers have helped us tremendously over the years and I am looking forward to new additions for this series. Important note – the links will […]

Continue reading →
Dynamics GP, GP SQL scripts

SQL view for Payables GL distributions in Dynamics GP

November 28, 2008 by Victoria Yudin 174 Comments

Here is a view that will return the General Ledger distributions for all posted or unposted payables transactions.  Unposted is also called ‘work’ and posted includes any open or historical transactions.  If you’re going to use this with SmartList Buidler, make sure to include the section at the end to grant permissions to DYNGRP. Disclaimer: I […]

Continue reading →
Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts, Payables SQL code
, , , ,

SQL view for Crystal Reports to show open AP invoices from multiple companies in GP

November 10, 2008 by Victoria Yudin 0 Comments

In my experience Crystal Reports really doesn’t like it when you have spaces in your field names. I’ve gotten a request for a ‘Crystal’ version of the Multicompany Open AP view I posted up a few days ago, so here it is. Please follow the in-line directions in green to change the generic company names […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , ,

SQL view to show open AP invoices from multiple companies in GP

November 8, 2008 by Victoria Yudin 0 Comments

If you have multiple companies in GP and want to create a quick view to show a combined list of all the open AP invoices, you can use the example below.  I am not doing anything fancy here, just hard coding the company names and database ID’s.  In my experience this is a set list for most […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , ,

SQL Server – how to get date differences

October 27, 2008 by Victoria Yudin 8 Comments

Often, when writing a report, we need to calculate the difference between two dates or the difference between a date and today. For example – how old is an invoice? How many days overdue is an invoice? Below is some SQL code that will help. First, if you need to dynamically get the current date […]

Continue reading →
GP Reports code, GP SQL scripts, SQL coding, SQL Server
,

SQL view to show AP apply information in GP

October 22, 2008 by Victoria Yudin 108 Comments

Here is a corollary to the view I posted last week. The view below will show you what payments/credit memos were applied to payables transactions in GP. If you have SmartList Builder you can easily incorporate this SQL view into a new SmartList.  [Update: You can also use this in Crystal with the changes I made on […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , ,

SQL view to show what check paid a Purchase Order

October 17, 2008 by Victoria Yudin 14 Comments

There is no easy way in Dynamics GP to see what check or credit memo was applied to a particular Purchase Order. Here is a SQL view that can be used in SmartList Builder or any other reporting tool you choose to see this information. For other SQL scripts please visit my GP Reports Page. ~~~~~ […]

Continue reading →
GP Reports code, GP SQL scripts, Payables SQL code
, , , ,
Newer Entries