Archive | GP Reports code RSS feed for this section

SQL view for inventory items and dates

This view is a little bit of a twist on my previous SQL view for inventory quantities on hand. The results of this view will give you a list of your Microsoft Dynamics GP inventory items, current quantities and the last sales and purchase dates along with the vendor. For other SQL views on Dynamics GP data, please visit […]

Continue reading

Running Totals in Crystal Reports

Here is a tip from the Flexible Solutions GP Reports October newsletter on how to create a running total in Crystal Reports.  Running totals are a great way to show totals that have a different calculation than a subtotal without having to write additional formulas.  Let’s look at an example: You have a list of unapplied […]

Continue reading

SQL view for Payables payment apply detail in GP

I have published SQL views that return all posted Payables payments in Dynamics GP and Payables apply information to help identify what payments (or credits) paid off an invoice, however another frequent request is for a list of payments and their apply information.  Below is a view that returns a list of all posted payments with details on how […]

Continue reading

SQL view for all posted Payables transactions in Dynamics GP

Payables Transactions have been a popular request for reports lately. Below is a view that lists all posted Payables transactions in functional currency. Voided transactions are not excluded. For other SQL views on Dynamics GP data, please visit my GP Reports page. ~~~~~ CREATE VIEW view_PM_Transactions AS /*************************************************************** view_PM_Transactions Created Oct 12 2009 by Victoria Yudin – Flexible […]

Continue reading

SQL view for all posted Payables payments in Dynamics GP

This view is in response to more than a few inquiries I have had for a vendor check report. This view brings in all payments, not just checks, so if only checks are needed, add the following to the end of the WHERE clause: AND P.PYENTTYP = 0 For other SQL code, please visit my […]

Continue reading

SQL view for all unapplied Receivables transactions in Dynamics GP

Here is a SQL view that will return all unapplied Receivables transactions in Dynamics GP. This will calculate how many days overdue unapplied transaction are. If something is not overdue, or if it is a credit transaction (payment, credit or return) the Days_Past_Due will be zero. For more SQL code like this, please visit my GP Reports and […]

Continue reading

SQL view for all posted GL transactions in Dynamics GP

Below is a SQL script to create a view showing all posted General Ledger transactions in Dynamics GP. This has the common columns asked for on reports, but you can certainly add your own as needed. For additional SQL code, please visit my General Ledger SQL views page or the GP Reports page. A few notes on this view: Year-end […]

Continue reading

How to get next Monday’s date in Crystal Reports

From the Flexible Solutions’ GP Reports July 2009 Newsletter, here is a great new tip: how to calculate next Monday’s date in Crystal Reports. This comes up periodically when someone wants to have weekly columns on a report showing totals of something like sales, expenses or invoices due. So if you have a report parameter called […]

Continue reading

Suppressing blank lines in Crystal Reports

Here is a Crystal Reports tip from the GP Reports Viewer newsletters that can help when you are working with addresses that are stored in multiple fields. Dynamics GP (and probably many other systems) stores address components in separate fields, so if we look at a customer address window there are many individual fields that make […]

Continue reading

SQL view for inventory quantities on hand

I haven’t had much time to blog lately as we have added functionality to our GP Reports Viewer product that allows seamless replacement of SOP reports in Dynamics GP and the amount of interest has been overwhelming.  However, I just needed to create a new SmartList to show current inventory quantities on hand with their costs and I […]

Continue reading

Dynamically change decimal places in Crystal Reports

The May 2009 Flexible Solutions GP Reports Newsletter brings us a tip for dynamically changing decimal places in Crystal Reports. This can be really useful for creating Sales Order Processing (SOP) reports for Dynamics GP, where GP stores the number of decimal places for each line item in the SOP10200 and SOP30300 tables. Here are the […]

Continue reading

SQL view with all SOP line items in Dynamics GP

Here is a script that I have found useful on numerous occasions – it returns the lines items for all SOP (Sales Order Processing) transactions, both posted and unposted. This view will work with either SmartList Builder or Crystal Reports. For more scripts like this, take a look at my GP Reports page. ~~~~~  CREATE […]

Continue reading

Adding watermarks to Crystal Reports

Here is the latest tip from the Flexible Solutions GP Reports Newsletter: how to add a watermark to your Crystal Reports.  I am going to demonstrate with an example. I have created a statement report, but it’s a little bland, especially when there are only a few lines of data to show:   First step is to […]

Continue reading

SQL view with all posted Receivables transactions

Below is code to create a SQL view that returns all the posted Receivables transactions in Dynamics GP. I have had an abbreviated version of this view on my GP Reports page for a while, but I cleaned it up a bit and gave it some more friendly column names. I also added values for the commonly used […]

Continue reading

Alternating shading for lines in Crystal Reports

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

SQL view with security resource details for Dynamics GP

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 reading

SQL view to show security roles and tasks in Dynamics GP

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

Searching for strings in SQL Server using LIKE

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