12-08-2017 03:38 PM
We have a few "self-service" reports which essentially contains several filters and a single List Table. The purpose of these reports is to allow our users the ability to export as much or as little data as they would like to MS Excel and then work with it in ways that are not available in other reports (in other words, this is for the "do it yourselfer"). Obviously, these List Tables are very wide with many columns. The problem that I am encountering is that the number of rows returned is around the 5,000 range...and I need for this to be much higher. Also, when I attempt to export this data from SAS VA 7.4 modern viewer report to MS Excel, the export pop-up forces me to select a range of 1 to "much less than 5,000" rows.
Can these limits be increased? If so, how? I have searched the web and looked thru the settings for VA in Management Console, but nothing is standing out to me as being the obvious values to change. Any help would be greatly appreciated.
12-08-2017 05:33 PM
According to the documentation the limit for rows returned is 50,000 not 5,000:
Is there any additional processing done on your List Table object like for example sorting? This may be subject to further constraints.
12-11-2017 10:36 AM
Thank you for the reply. I had already stumbled across that document...and I too thought that the 5,000 records seemed very low. However, your reply did prompt me to start looking at other things...and after more digging, I think that I stumbled across the cause for our issue. As I mentioned, the dataset being returned is very wide...as in 106 columns wide. It appears that in addition to the High-Cardinality thresholds, we are also bound by the properties of the Visual Analytics Transport Service...particularly, the property "viewerservices.default.max.cells.produced". This property dictates the number of data cells that can be delivered to a mobile device for a single data query...and the default value is 250,000. Thus, when our List Table contains 106 columns, the maximum number of rows that can be returned is 2,358...which is exactly the behavior that we are seeing.
12-11-2017 02:11 PM - edited 12-11-2017 02:19 PM
Thanks @rgreen33! I wasn't aware of that limitation so much appreciated for pointing it out. Just goes to show both the poster and the postee learn on the SAS Community.
The only workaround I can think of for exporting to Excel would be using a SAS stored process in VA. This is not something I have tried, but since a stored process contains SAS code it wouldn't be subject to VA limitations.
12-14-2017 07:46 AM
Thanks again for the info. I had not thought about the idea of using a stored process for this (even though I am using a stored process to pull my job schedules from Metadata to a SAS VA Report). That might just work. :-) Another option that I found would be to leave the data in SAS and use SAS Visual Analytics Explorer. Since this tool is not included in mobile bi, you are not bound by the 250,000 cell limit. In reality, the user could probably do everything that he/she wants to do in SAS Visual Analytics Explorer.
We are fairly new to SAS, so we are just learning the capabilities of all of the tools...and doing a slow rollout of each of them. So, SAS Visual Analytics Explorer may become a part of our toolbox soon.