Here is a new Crystal Reports tip from the GP Reports Viewer newsletter collection. I have added an example with some screenshots to better illustrate. Want to spruce up your Crystal report a little by adding alternate color shading to the lines? Here is how. (Click on the screenshots to see them clearer.) As an example, let’s start with […]
Continue reading
Tag Archives: GP Reports code
SQL view with security resource details for Dynamics GP
March 20, 2009 
38 Comments
As a corollary to my view showing the Dynamics GP security roles and tasks assigned to users, I have created another view that adds the security resource details. Thank you very much to Tim Foster for pointing me to a newsgroup post by David Musgrave detailing how to populate the SY09400 table in SQL which holds […]
Continue readingSQL view to show security roles and tasks in Dynamics GP
March 12, 2009 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 readingHow to get a definition of your Crystal Report
February 19, 2009 2 Comments
Here is the latest Crystal Reports tip from the Flexible Solutions GP Reports Newsletter: Ever try to delete a parameter from a Crystal Report and get a pop-up message saying the field is in use on the report and then discover that you can’t find where it is actually used? Or need to be able […]
Continue readingSQL view to show items with SOP POP link
February 4, 2009 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 readingDynamics GP Bank Rec table information
January 16, 2009 4 Comments
A new page is now available with table information for the Bank Reconciliation module. I have reorganized all the pages a little so they are now alphabetized on the sidebar to the right and hopefully easier to find. All of the available pages as well as other resources for GP Reports are listed on my GP […]
Continue readingHow to get rid of the blank page at the end of a Crystal report
January 12, 2009 50 Comments
Here is a tip from the Flexible Solutions GP Reports newsletter: Often when creating a Crystal report for something that needs a new page for each group (like Invoices or Purchase Orders) we use the New Page After option for the Group Footer in Crystal’s Section Expert. This works great, however it prints a blank […]
Continue readingSQL view to show customer pricing
January 7, 2009 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 readingReporting on Extender data in Dynamics GP
December 15, 2008 24 Comments
Dynamics GP Extender is one of those modules that has so much in it, most people don’t use even half of it. One of the most underused features is Extender Views. Anyone that has worked on reports using data entered into Extender windows will tell you that getting data directly out of Extender tables is a challenge. All […]
Continue reading SQL view for Payables GL distributions in Dynamics GP
November 28, 2008 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 readingDynamics GP Fixed Assets table information
November 20, 2008 1 Comment
I have posted up a new page with Dynamics GP Fixed Assets table information. This is part of my ongoing library of Dynamics GP reporting information. A directory of all the information I’ve posted up so far is on the main GP Reports page.
Continue readingSQL view for Crystal Reports to show open AP invoices from multiple companies in GP
November 10, 2008 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 readingSQL view to show open AP invoices from multiple companies in GP
November 8, 2008 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 readingSQL Server – how to get date differences
October 27, 2008 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 readingDynamics GP Inventory table information
October 23, 2008 0 Comments
I’ve posted up a new page with GP Inventory table information. You can see the entire list of modules available so far on the main GP Reports page. That page also has links to additional resources and the GP SQL views I’ve posted recently. I’ve gotten some requests for other modules, so I’ll work on putting those together next. Is there […]
Continue reading SQL view to show AP apply information in GP
October 22, 2008 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 readingSQL view to show what check paid a Purchase Order
October 17, 2008 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 readingDynamics GP GL table information
October 13, 2008 0 Comments
A new page has been posted up with the General Ledger table information. A list of all the various pages available can be seen on the main GP Reports page. I have also included links there to some additional reporting resources published by fellow Dynamics GP bloggers.
Continue readingMy Last Book
Dynamics GP 2013 Implementation
My Previous Book
Dynamics GP 2010 Implementation
-
Using credit cards to pay vendors in Dynamics GP
-
SQL view for Payables GL distributions in Dynamics GP
-
SQL view for all posted GL transactions in Dynamics GP
-
SQL view for Payables payment apply detail in GP
-
Sales Transaction Entry GL distributions in Dynamics GP
-
SQL view to show AP apply information in GP
-
Year end close in Dynamics GP
- SQL view with AR apply detail
-
Granting access to a new SmartList in Dynamics GP
-
SQL view with all SOP line items in Dynamics GP
-
SQL view for unposted checks in Dynamics GP
-
SQL view for rolling 12 months of sales by item by site in Dynamics GP
-
Dynamics GP sales amounts and quantities for rolling 12 months
-
Updating Dynamics GP data directly in SQL
-
Did you know you can change Void dates on Payables transactions?
-
Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP
-
SQL code to show top X percent of vendor data
-
Discount from Packt Publishing
-
Refreshable Excel report for historical GL trial balance in Dynamics GP
-
Refreshable Excel report for monthly GL budget in Dynamics GP
-
Victoria Yudin: Hi Matthew, This is a limitation in GP. "Why" is ...
-
Matthew: Why does "Sales" type distr...
-
-
Kevin Mackol: Does this still work on new...
-
-
Alex: Hi Victoria, Hi Victoria, Hi Victoria,Thank you so mu...
Victoria Yudin 
Recent Blog Comments