Personalized Community is here!
Quickly customize your community to find the content you seek.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2022 Release Wave 1 PlanDynamics 365 release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.
2022 release wave 1 plan
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
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.
CREATE VIEW [dbo].[VIEWNAME]
--Your SQL View Here
grant select on [VIEWNAME] TO DYNGRP
Select a.COLUMN1, b.COLUMN2, a.COLUMN3, c.COLUMN4 from…..
Select a.COLUMN1 AS 'First', b.COLUMN2 AS 'Second', a.COLUMN3 AS 'Third', c.COLUMN4 AS 'Fourth' from…
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:
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!
Support Escalation Engineer
Microsoft Dynamics GP
Business Applications communities