Hi,
I'm trying to run a report that spans a lot of records over several months in the Item Ledger Entry table. The report failed with a "PivotTable2 invalid reference". When I clear that error, there #VALUE cell also clears, so there's nothing to debug.
I tried refreshing the report a few days at a time until I found the date that was causing trouble. Then, I refreshed the report using that date and the day immediately following, and it refreshed with no issue.
I'm worried that I've hit a limit with either Jet or Excel. Any thoughts on where or what to check?
Date
Votes
7 comments
-
Jet Reports Historic Posts Hello Jaycen,
In the Jet ribbon choose 'Application Settings' > 'Performance' > Disable 'Enable batch function execution'.
Run the report again to see if all of the information is returned properly.
Best regards,
MWilson -
Jet Reports Historic Posts Hi, MWilson!
When I go to Application Settings>Performance I only find the following:
Cache function results
Enable Excel automatic calculation when restoring formulas
Disable Excel multi-threaded calculation when running reports
All three checkboxes are currently checked. -
Jet Reports Historic Posts Hello Jaycen,
What type of Data Source are you utilizing?
Open the Jet Data Source Settings and click on the drop down button in the top left corner.
Best regards,
MWilson -
Jet Reports Historic Posts Dynamics NAV 2013 and later (Web Services)
-
Jet Reports Historic Posts Hello Jaycen,
If you are utilizing Jet Express 2015 Update 1 and you have an on-premise SQL Server where your NAV database is stored I would strongly recommend switching over to the "Dynamics NAV 2013 and later (SQL Server)" Data Source type.
The "Dynamics NAV 2013 and later (SQL Server)" Data Source is generally 50-60% faster than Web Services and may resolve the issues you are experiencing.
Below you will find the configuration instructions (I know they say Jet Essentials, but Jet Express is the same configuration).
Configuring a data source to NAV 2013 or later through SQL Server:
Requirements for using the Dynamics NAV 2013 SQL Server Connector: http://kb.jetreports.com/article/AA-00915
Configuring Jet Essentials for the Dynamics NAV 2013 SQL Server Connector: http://kb.jetreports.com/article/AA-00902
Best regards,
MWilson -
Jet Reports Historic Posts Unfortunately, the server is not on-site. We also have multiple locations with the need to have off-site users refresh these reports.
I was thinking of pushing to purchase Essentials to get past this problem, but it sounds like maybe we're going to have the same issue, even with the paid version(s). -
Jet Reports Historic Posts Hello Jaycen,
Not necessarily.
With Jet Essentials you have much more power in terms of creating reports that filter down to just the data you are looking for and run less queries against the database.
You also have the ability to use a new Data Source type (which is also available in Jet Express 2015 Update 1) called the Jet Remote Data Service Data Source.
Jet Remote Data Service Data Source requires a valid SSL certificate and is specifically made for hosted/cloud based environments where users are not on the same domain that their NAV database is.
The Jet Remote Data Service Data Source - Admin Guide: kb.jetreports.com/article/AA-01053
A typical set of PowerShell commands to configure the instance would look like:
New-JetRemotetDataService -Port 8084
New-JetNavRemoteDataServiceInstance -InstanceName "JRDS" -Server "servername\instancename" -Database "Demo Database NAV (7-1)" -AuthenticationType 2 -UserID "sa" -Password "password"
It sounds as though this is the only report causing you any problems though, so it may be worth starting from scratch and restructuring the report to return the fields in a different order and take a look at the filters you are utilizing.
You can also try increasing some of the values in the Data Source Settings > Advanced tab for your Web Services Data Source to see if that may help the report run without #VALUE.
Best regards,
MWilson
Please sign in to leave a comment.