FSI Logo
GP Reports Viewer Newsletter

August 2017

GPRV Banner
GP Reports Viewer for Collections Management
New feature - GP Reports Viewer now works with Collections Management to email invoices
Do you use Collections Management from Professional Advantage? Now you can email your custom SSRS or Crystal Reports invoices from Collections Management using GP Reports Viewer. 
For the administrator, the setup is quick and easy. For users nothing changes at all in the steps they use to email invoices. Except now you have the ability to replace the out-of-the-box Dynamics GP invoices with your custom ones. You can even use different reports for Sales Order Processing and Receivables Management documents if you want.
GP Reports Viewer works with Collections Management in the following versions of Microsoft Dynamics GP:
  • GP 2016 and GP 2016 R2 (new GP Reports Viewer build 4)
  • GP 2015 and GP 2015 R2 (new GP Reports Viewer build 10)
  • GP 2013 and GP 2013 R2 (new GP Reports Viewer build 19)
For details about setting up and using GP Reports Viewer and this new feature, please see our User Guide.
If you have any other upgrade questions, please let us know.
To see more details about GP Reports Viewer functionality, please visit our website or email us for a demo.
Insert Pictures in Crystal Reports
How to add pictures dynamically to your Crystal Reports 
We recently created a Purchase Order report for a customer and they wanted to show their item pictures next to each line item. This turned out to be surprisingly simple in Crystal Reports.

To do this, first we had them add the location of the picture file on the Dynamics GP Internet Information window for each item. To add the pictures in the Crystal report we followed the steps below:
  • Insert a picture on the report - use a small picture, so it doesn't unnecessarily blow up the size of your report
  • Right click on the picture and select Format Graphic
  • On the Picture tab make sure that Use Original URL is not selected and then click on the formula icon next to Graphic Location
  • On the formula window, select the field in the table (or your SQL code) that points to the item picture
  • On the Common tab, click on the formula icon next to Suppress and add a formula similar to the one below, so that if there is no picture it shows as blank: 
    {Picture_File} = ""
While this example was for an item picture, you can use the same method to add any pictures dynamically to your Crystal reports. 
Date from Month and Year in SQL Server
How to calculate a date from month and year in SQL Server code 
We often see reports where the parameters are a month and a year, so all you have are numbers for both, for example, 8 and 2017. You can sometimes compare the month and the year to the ones in your data, but for some situations it may be easier to turn these into an actual date that can be used for comparison with other dates that are in date or date/time format.
Sometimes we need to turn the parameters into the beginning of the month, other times the end of the month or the first day of the following month. Below is the SQL code to accomplish all three of those. This code uses @month and @year for the parameters:
First day of the month:
CAST( CAST (@year as varchar(4)) + RIGHT('0' + CAST (@month as varchar(2)), 2) + '01' as datetime)
Last day of the month:
DATEADD(d,-1, DATEADD (m,1,( CAST ( CAST (@year as varchar(4)) + RIGHT ('0' + CAST (@month as varchar(2)), 2) + '01' as datetime))))
First day of the following month:
DATEADD (m,1,( CAST ( CAST (@year as varchar(4)) + RIGHT ('0' + CAST (@month as varchar(2)), 2) + '01' as datetime)))
If you prefer to get date instead of datetime , just change the " datetime " in the code above to " date ". Also, if you are on SQL 2012 or higher, you could use the EOMONTH function for the last day of the month:
EOMONTH ( CAST ( CAST (@year as varchar(4)) + RIGHT ('0' + CAST (@month as varchar(2)), 2) + '01' as datetime))
Looking for a solution to manage Crystal, Excel and SSRS reports inside of Dynamics GP quickly and easily? An online demo of GP Reports Viewer is available anytime on our demo web page.

If you would like to schedule a live demo of GP Reports Viewer with us, please call 212-254-4112 and choose option 1 for Sales or e-mail sales@flex-solutions.com.

You can also download a fully functional trial for the Dynamics GP sample company. Expiring registration keys are available upon request if you would like to test GP Reports Viewer with your company data. 
Victoria Yudin
Dynamics GP MVP (2005-2018)
Flexible Solutions, Inc.
In This Issue
GP Reports Viewer News
Crystal Reports Tip
SQL Server Tip
Quote of the Month
Quick Links

Quote of the Month
The only place success comes before work is in the dictionary.

~ Vidal Sassoon