An Intro to SmartList Designer

Who owns Smart List Designer?

Any customer running Microsoft Dynamics GP 2013 SP2 or higher!

How do I use Smart List Designer?

1

User will need access to Smart List Designer Tasks, these tasks need to be assigned to a role and the role assigned to the user…

  • GP2015 R2 Offers a workflow to approve Smart List Designer object before they become accessible to all users.
  • Once the object is created, uses need to be granted access to the Smart List Designer object.
  • Open Smart List and click on NEW or Modify, yes, you can modify!

How is Smart List Designer different from Smart List Builder? Which is the ‘right’ tool for me?

3
Let’s Create a Smart List Designer object with a SQL View!

Premise:

COO wants Payroll pay information for all employees and all pay codes. Would like to see the pay rate value per hour. COO does not have access to Dynamics GP.

Solution:

  1. Create a SQL View to gather appropriate information
  2. Grant COO access to SQL view
  3. Create a Smart List Designer Object using the SQL View
  4. Publish the SQL view to Excel Reports
  5. Pretty up Excel Report for the COO

View?

In database theory, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This preestablished query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested.

Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view. In some NoSQL databases, views are the only way to query data.

Views can provide advantages over tables:

  • Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
  • Views can join and simplify multiple tables into a single virtual table.
  • Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.
  • Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.
  • Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.

What does my view include?

USE [TWO]
GO /****** Object: View [dbo].[BMI_Employees] Script Date: 06/15/2012 08:36:03 ******/ SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create VIEW [dbo].[BMI_Employees] AS select Distinct([‘Payroll Master’].[EMPLOYID]) as ‘Employee ID’,
rtrim([‘Payroll Master’].[LASTNAME]) as ‘Last Name’,
rtrim([‘Payroll Master’].[FRSTNAME]) as ‘First Name’,
rtrim([‘Payroll Address Master’].[ZIPCODE]) as ‘Zip Code’,
[‘Payroll Master’].[BRTHDATE] as ‘Birth Date’,
Left(rtrim([‘Payroll Address Master’].[PHONE1]),10) as ‘Phone 1’,
rtrim([‘Payroll Master’].[SOCSCNUM]) as ‘Social Security Number’,
rtrim([‘Payroll Pay Code Master’].[PAYRCORD]) as ‘Pay Record’,
[‘Payroll Pay Code Master’].[PAYRTAMT] as ‘Pay Rate’,
from [UPR00100] as [‘Payroll Master’] with (NOLOCK) left outer join [UPR00102]
as [‘Payroll Address Master’] with (NOLOCK) on [‘Payroll Master’].[EMPLOYID] = [‘Payroll Address Master’].[EMPLOYID] and [‘Payroll Master’].[ADRSCODE] = [‘Payroll Address Master’].[ADRSCODE] left outer join [UPR00400]
as [‘Payroll Pay Code Master’] with (NOLOCK) on [‘Payroll Master’].[EMPLOYID] = [‘Payroll Pay Code Master’].[EMPLOYID]
Where [‘Payroll Master’].[INACTIVE]=0 AND [‘Payroll Pay Code Master’].[INACTIVE]=0

Contact InterDyn BMI today to learn more about SmartList Designer and Microsoft Dynamics GP.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s