Posts Tagged ‘smartlist’
There are the easy exports where they just publish the report in text mode and then open the same in excel and I’ve seen customers that love formatting data in excel, spending countless hours formatting and at the end they have to do it again because data was refreshed.
There are others that are more complicated but yield better results, like Excel Reports, SSRS or plain T-SQL extracts and just very few who take advantage of the Export Solutions but that will be a subject of a future post so stay tuned.
Smartlist is a very useful tool within GP but some customers find it confusing because of the filters and how data is stored in GP.
One of the questions that made me write this post for you is, How can I sort data in Smartlist using more than one field, so I don’t have to do this in Excel?
The solution is simple but believe it or not, I learned that not many people know about this, I queried my own colleagues and I was shocked.
Here is my revelation for you, hope I can save you some time …
Use the Order By feature in Smartlist. Let’s go thru a sample:
Step 1.- Open Smartlist
Step 2.- Open Financial > Account Transactions
Step 3.- Double click on the * named smartlist
Step 4.- In the Search Account Transactions window click on the Order By button
Step 5.- Add the sort rules/levels from the list on the left, by using the Insert button
in my sample I am sorting by Account, then by the amounts, but I want all debits from highest amount to the lowest, then I want all Credit postings same rule and lastly I want to sort by the Series.
And here is the result, so no more single column sorting!!!
Step 6.- Click OK twice and see your results
Here is another sample where I am sorting by Journal entry then Highest Debit Amount to lowest and lowest Credit Amount.
Until my next post!!!
Francisco G. Hillyer
I know people that use Smartlist extensively, they really use it but the pain they have is sorting, I have witnessed many exports to Excel and even some people were some kind of creative that they made their own “Export Solution” to do what? you may ask, and yes you have the answer, to SORT by other columns.
So when I show them this trick they just think that I’ve been hanging around with MGomez, FHamelly, DPitcher, the famous SChapman, MPolino and many more just I wont finish the list.
Here it is… how about if I tell you I can show you how to sort Smartlist by using multiple fields, different sorts and even with fields not being displayed on the smartlist itself?
Lets open Smartlist….
Double click on one of your smartlist objects, I am choosing the “All” in Account Transactions.
Once the Search dialog appears click the button on the bottom called “Order By”
Then the Select Order By window appears, note on my example that I sorted my list by Account, then by Series (Descending), by Transaction Date and by the User Who Posted.
The User Who Posted is a field that is not being displayed in the screen, even in this example the user who posted is not doing us any good report wise as we cannot see the value, it’s a clear example that you can use a non displayed field to sort.
Once you have chosen the available columns and moved them to the Order by section you click OK to go back to smartlist search dialog, then OK again to have the search executed.
As you can see the result is ordered by the Account number, then by the series int value, then by date.
Don’t forget that once you define your sort, save your smartlist definition so you don’t have to re-work.
I hope I can save you sometime with Excel macros and ease your job directly from Dynamics GP Smartlist.
Until my next post!!
Francisco G. Hillyer
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.
Patrick over at Developing for Dynamics has a great solution for those of you using Dynamics GP 10 and beyond.
I tested this out on my stinky old PC. I downloaded 20,000 rows in about 50 seconds after making the dex.ini switch. Without the switch I quite after a 1 min and 15 seconds and had only downloaded 2000 rows. Extrapolated that would have taken 12.5 mins to do 20,000.
This has been a limiting aspect of smartlist in the past so was pretty syched to see this solution.
What a great find.
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.
If you’re using SmartLists on a regular basis to report on information in Dynamics GP, you’ll want to learn how to use the Export Solutions functionality within SmartLists to add charts, pivot tables, calculated fields, and additional formatting to your data.
Export Solutions allows you to run an Excel spreadsheet macro on the data that SmartLists exports to Excel.
These are the basic steps to set up the macro:
- Export a SmartList to Excel
- Start and name a new macro in Excel
- Perform the formatting you want in the Excel spreadsheet
- Stop the macro
- Delete any worksheets you may have added during formatting
- Delete the data that was initially exported to Excel
- Save the Excel spreadsheet as a template
These are the steps to attach the spreadsheet with macro to SmartLists:
- Navigate from SmartList: SmartList >> Export Solutions
- Document: Select the Excel spreadsheet that contains the macro you wish to execute
- Works for Favorites: Select the SmartList(s) you want the Export Solution to be available with.
Once the Export Solution has been attached to the SmartList(s), you can use it when you wish. Just make sure to select it from the menu drop down, and not “Quick Export”:
I’ve created a companion video to this on YouTube.