Quick Query Export to Excel Returns Fewer Rows than QQ Results
Posted by Tiffany Bennett, Senior Consultant on September 7, 2017
Have you ever found when you export a Dynamics SL Quick Query to Excel, you don’t get all the rows of data? Here’s some general information and options to fix that problem.
In Dynamics SL, you can turn on paging for your Quick Query screen, as well as limiting the number of records returned. To do so, you add the following lines to your Solomon.ini file on the SL application server:
[QuickQuery] Paging=Yes MaxSQLRows=100
In this example, the settings will allow paging of the Quick Query results on the screen and limit the number of rows returned to be 100.
However, when exporting the results to Excel, there are more records returned than in your Dynamics SL Quick Query results. There is a known bug related to Paging and Quick Queries. The issue first occurred in Dynamics SL version 2011 SP1. The problem is corrected in SL 2015. There’s a little bit of information online if you Google KB 2724313.
There is no issue when there are less rows in your resulting query than the maximum SQL rows setting. However, when there are more rows then your restriction, then the export to Excel is incorrect. In the example below, if the MaxSQLRows was set to 200, then when exporting to Excel, you would see all 138 rows. However, if your setting was set to 100, the screen would show 100, but exporting would show 138.
Prior to SL 2015, the workaround is to sort on any column in the Quick Query, before exporting to Excel. This will export the same amount number of rows as shown in the Query. Otherwise, the solution would be to upgrade to SL 2015.
I hope this helps to clarify a few details. If you have further questions, or need assistance, our team of Dynamics SL experts can help you out. Email us at Support@Crestwood.com.