SmartList Designer Hacks for the SQL Minded

SmartList Designer is a great tool that makes it easy to do a basic join on tables through the User Interface for pulling quick sets of data.  But what about the more complex sets of data where the join you want to make is not straight forward or involves manipulation of the datatypes or trimming to be successful?  These scenarios can be difficult and, in most cases, impossible to do through the User Interface. 

For those of us that know SQL well, it can be frustrating when you know exactly what you want to do and how you would do it in SQL, but you are limited through the UI with SmartList Designer.  I am going to let you in on a little secret.  (My favorite SmartList hack)  If you can create a query in SQL that pulls the exact data that you want.  Then you can save that query as a SQL View and it will be available to select in SmartList Designer with very little effort!

The following links are great resources for this process:

KB 4014658 - How to create a SmartList Designer report using a SQL View

Microsoft already has a bunch of samples out there for you to use or build on.

Microsoft Dynamics GP SQL Views for SmartList Designer to Enhance Reporting - Microsoft Dynamics GP Community

Plus, many Microsoft Partners have SQL views out there as well that they share publicly on the web that you can find with a simple Bing search! 

CLICK HERE TO START YOUR SEARCH!

Below I have created a quick Video Training walking you through this process, and outlining some tips and tricks. 

Below are a few of the tips that I shared in the video. 

1. Include Granting select writes to DYNGRP users so that all GP users can run selects on your new SQL View with in SmartList without having to alter any additional SQL Permissions.

CREATE VIEW [dbo].[VIEWNAME]

AS

--Your SQL View Here

go

grant select on [VIEWNAME] TO DYNGRP

go

2. Don’t use ‘Select *’ to pull all columns from all tables in your view. You will see much faster performance when your SmartList populates with data if you only select the specific columns that you need to see in the SQL view to start with rather than filtering down your columns in SmartList afterwards:

Select a.COLUMN1, b.COLUMN2, a.COLUMN3, c.COLUMN4 from…..

3. Name your SQL View’s Columns with the display name that you want to see in the User Interface.

Select a.COLUMN1 AS 'First', b.COLUMN2 AS 'Second', a.COLUMN3 AS 'Third', c.COLUMN4 AS 'Fourth' from…

4. Along the same lines, convert your currencies, dates, or document status in SQL so they display the way that you want them to on the SmartList by default.

 a.  Convert your dates to exclude the Timestamp and display in MM/DD/YYYY format:

SELECT CONVERT(VARCHAR, SOP10100.DOCDATE, 101) AS 'DOC_DATE'

b. Use a CASE when selecting Document Types:

SELECT CASE

               WHEN RM20101.RMDTYPAL = '3' THEN 'Debit Memo'

               WHEN RM20101.RMDTYPAL = '7' THEN 'Credit Memo'

               END                     AS 'DOCTYPE'

c. Convert your Dollar amounts to 2 decimal places:

SELECT CONVERT(DECIMAL(15, 2), PM10300.appldamt) AS 'APPLY_AMT'

For more Videos and Tips check out the 'Are you "ON TOP" of your game when it comes to Microsoft Dynamics GP?' Blog Post.  

I hope you find this helpful!  If you have any questions or there are more features that like this that you would like our Support Team to create videos or blogs on, please let us know in the comments and we can consider them in a future series!  

Isaac Olson

Support Escalation Engineer 

Microsoft Dynamics GP