Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

1.  PO Smartlist - Performance too slow

Posted 4 days ago
Hi There,

We have few smartlist that are related to Purchase orders, Performance of having results to display on screen from that smartlist is way too slow.
Is there anything I could do to improve the browsing of these smartlist?

Sunaina  ​

Sunaina Sharma, Business Application Analyst(Admin)
Oakville Enterprise Corporation

2.  RE: PO Smartlist - Performance too slow

Posted 4 days ago

​Hi @Sunaina Sharma

It sounds like you guys may have a tremendous amount of data.  Even if you limit the data with filters the PO SmartLists will run slowly.

If you need to keep them in SmartLists someone else on here might have a solution, but I'm going to recommend you use Refreshable Excel Reports - the tend to run much faster for things such as the PO information.

There's quite a few articles out there on how to set them up.  But, in a nutshell you'll either write the query directly in an Excel SQL connection or write a view and deploy it and then query the view from within Excel.

This can dramatically increase the speed.

With this said, the slowness could be due to a number of issues from space in SQL, space on the Server to the network connection joining your workstation or even the CPU of your workstation.   There are too many varied configurations to troubleshoot that from the information you've provided.


Jo deRuiter
"That GP Red Head"
Senior Financial Systems Consultant
Heartland Business Systems, LLC
Milwaukee, WI
770-906-4504 (Cell)

3.  RE: PO Smartlist - Performance too slow

Posted 3 days ago
HI @Sunaina Sharma,

As Jo suggested, Excel refreshable reports would be a much speedier alternative.. That being said, It sounds strange that your SL report takes so long to display the PO data.. I'm wondering, are you archiving your closed PO's on a regular basis..? this is one of the factors that can lead to slow display of the data..
Completed PO's are not automatically moved to historical tables, which may be the cause for your slowness.. you need to run this routine from time to time :
However, it could also be the query that you use to retrieve your PO's.. is it a default SL report or some home-brewed SL ? Some of the default SL's are based on SQL views that retrieve both the historical & current data.. which in the case of large amount of PO's can take more time. The last question is also about how many records are your returning in your SL's ? by default, GP returns 1000 records, but you can change that and save it with the SL.. in some cases if someone changed the default from 1000 to 10000, that could take significantly more time.. ​

Beat Bucher
Business Analyst, Dynamics GP SME
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14

4.  RE: PO Smartlist - Performance too slow

Posted 3 days ago
The problem with many Smartlists is that they want to bring in the entire table's or query's worth of data so that subsequent sorts run faster.  It really defeats the purpose when there is lots of data in the first place!  A couple of suggestions:

1.  Use an SSRS report instead of a Smartlist.  The SQL Server database engine can do a much better job of getting and retrieving information than the Dynamics platform can.  Use it.  You can publish all the Dynamics GP reports as SSRS reports.  Then have someone (preferably on your team) tweak the published report definition to give you what you need and put in better filters (date range, etc.).

2.  Create a view-based Smartlist with an inherent date filter (last year, etc.) and build your Smartlist off that.

Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID

5.  RE: PO Smartlist - Performance too slow

Posted 3 days ago
Edited by Gary Siebenlist 3 days ago

Hi Sunaina,

Have you tried adding the following line to your DEX.INI settings file?


This setting uses a different method to export data to Excel. It's much faster, but certain things do not export correctly, some foreign currency symbols for one. Also it strips off leading zeros. Test it first before deploying.

It made a significant improvement in speed for exporting Smartlists to Excel from my experience. 

Gary Siebenlist
Sempra Infrastructure
San Diego CA

6.  RE: PO Smartlist - Performance too slow

Posted 3 days ago
​If you are using large data sets Smartlist has performance issues as it works thru Dex and older Excel models.    Additionally it doesn't aggregate so you have to bring in every line of data.

Another option is BI360 Report Composer which has built in Dynamics GP Metadata and allows groupings and calculations.

There is a Free BI360 Report Composer Offer.
Solverglobal remove preview
BI360 is enabling world-class decisions one user at a time. In the spirit of spreading the word and giving you the tools you need, we are giving away 5,000 team licenses (up to 20 users each) as of September 18, 2017!
View this on Solverglobal >

Jim Hummer
Los Angeles CA