Posts Tagged ‘SmartList Builder’
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
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.
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.
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:
- SmartList to look at Request Resolution results.
- Step by step article on how to do it in Excel Report Builder as well.
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.
Here’s an example of a multiple company bank account list:
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”
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
These are the results:
If you have SmartList Builder and several GP company databases, you should try this out.