Below is a view I have put together to get the monthly Fixed Assets depreciation amounts in Dynamics GP. For more detail on the Fixed Assets module, please visit my Fixed Assets Tables page. If you’re looking for more SQL code, you can find it on my GP Reports page. ~~~~~ CREATE VIEW view_FA_Depreciation AS /******************************************************************* view_FA_Depreciation Created on […]
Continue readingCheckboxes in Crystal Reports
This Crystal Reports tip comes from the January edition of our GP Reports Viewer Newsletter. There are often times when a report is printed so that someone can go down the list and verify items or see where information may be missing. While it was a little more timely in January, at the time of the newsletter, confirming […]
Continue readingSQL view with AR apply detail
This has been a hot topic in the newsgroups lately and several people have asked me if I have any code for Receivables (AR) apply information in Dynamics GP. Below is a view that should help you get started if you’re building a report to show AR apply information in Dynamics GP. For more views like this, check out […]
Continue reading
SQL view with all GL distributions for AR transactions
As a corollary to my SQL view with all posted Receivables transactions, below is a view that will add all the General Ledger distributions to the AR transactions. I made a few changes to the original AR transactions view, primarily to remove some columns that are typically not needed and add underscores to the column names so […]
Continue readingSelective highlighting in Crystal Reports
The Crystal Reports tip from Flexible Solutions GP Reports November newsletter shows how to selectively highlight values in a list without having to write a formula. Let’s say you have a customer list with balances: Now what if you wanted to make all customers with balances of $50,000 or higher more visible? Here are the steps to […]
Continue readingSQL 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 readingRunning 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 readingSQL 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 readingSQL 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 readingSQL 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 readingHow 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 readingSQL 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
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 readingAdding 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 readingSQL 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 readingHow to use a SQL view in SmartList Builder
Something I have been asked more than a few times is how to use a SQL view in SmartList Builder to create a new SmartList. I am going to walk through this step by step using Dynamics GP 10.0 SP 3 and SQL Server 2005 SP 3: STEP 1 – create SQL view To start […]
Continue reading
April 7, 2010 



Recent Blog Comments