Posts Tagged ‘SmartList Builder’

Using SmartList Builder to Create Multicompany Reports

SmartList Builder is a great tool to use if you want to pull data from multiple company databases in Dynamics GP.

I recently recorded this video to show how to do this: http://youtu.be/72cRzWKOpNw

Take a look at this older post to get more details on it: http://gp.rosebizincblogs.com/2010/01/multi-company-smartlists-2.html

Blog >> SQL View >> Dynamics GP >> Excel

Retrieving information from Dynamics GP for reporting purposes is so easy to do because there are several great tools available and there are many sources of information on how to use the tools and specific code to make and build reports.

Victoria Yudin writes a blog in which she generously shares SQL code that allows you to easily create SQL views to retrieve information from Dynamics GP.  Victoria wrote this article this week, and in it includes code for creating a SQL View that retrieves information about payables invoices and associated purchase order line details.  This is fantastic.

You can easily copy the code from her blog and paste it into a new query using SQL Server Management Studio.  Execute the query and it will create the view.  The SQL view can be used in Excel and SmartList Builder to create SmartLists that all users can have access to.

I created this short video that shows how easy it is to do.

Creating SmartLists using SmartList Builder and SQL Views

There are a number of GP bloggers that often include SQL Query Language as part of their posts.  This is a rich source of specific problem solvers.

SmartList Builder allows you to easily take advantage of these resources to provide data inquiry and reports.

These are the steps to use once you’ve found a SQL Query that would be useful:

  • Create the new SQL View using the SQL Query
  • Grant access to the new view
  • Create the SmartList using the SQL View

I created this video to show you a simple example of how to do it.

Victoria Yudin is one GP Blogger that regularly includes SQL Queries in her posts.  Check it out.

This is the specific post I referenced in the video.

SmartList Builder

There are many ways to use SmartList Builder and many reasons why you should check it out.  SmartList Builder allows you to create your own SmartLists and to enhance existing SmartList views.

You can easily create a new SmartList to compliment the ones that are provided with GP.  And they are very easy to build.

Here’s an example of a simple SmartList I built to look at the MRP results from Manufacturing:

Here’s a simple example of a SmartList with two tables.

Multi-company SmartLists

Here’s a nice little option in SmartList Builder which I’m surprised few people know about.  SmartList Builder allows you to create SmartLists that can report information from multiple companies in one list.

We have several real estate companies and family office clients that have dozens of GP company databases from which they want to report on rather simple things, e.g. all bank accounts, all fixed assets.  SmartLists is perfect for this type of reporting.

To have a SmartList look at multiple companies, go to the “Options” button, check the “Multicompany SmartList” check box, and select the companies you want included.

SmartList1

Here’s an example of a multiple company bank account list:

SmartList2

The problem in this solution is to find a field in the data that identifies the company to which the results belong.  In my example, this is easy because the CM Checkbook Master table includes the Company ID field that can be linked to the Company Master table, to return the name of the company.

I found this query on CodeSnippets to find all the tables in the company database with the column name “CMPANYID”

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE syscolumns.name=’CMPANYID’

These are the results:

CT00001
FA49900
CT00002
SOPS113B
RM50103
CT00003
SY00600
POPS0231
JCPS0040
POP10100
PRPF0113
UPR70501
DD40400
QAPS0032
QAPU0032
AAG00906
POPF0113
POPS0132
CO010000
ECPS0000
COSS0401
ECPU0000
ENC40000
ENC40100
RM30701
POP30100
mops0100
MOPS0200
CM00100
EC090000
WCPS0100
MRP0200
BMPF0114
BMPS0114
SVC40500
CAM10210
GTM41000
SY04800
MPPS0230
CAM10200
MPPU0130
WCPF0100
TLPS0100
TLPU0100
RVP0100
RVPS0132
CPO40001
CPO40002
WPPS0114
WPPU0114
PAVW10600
RTPS0130
RTPU0130
PAVW30600

If you have SmartList Builder and several GP company databases, you should try this out.

Subscription Options:
Subscribe via RSS
Articles Categories