Hi,
I have a report from our accounts team I am trying to run, which gets a set of rows from our Oracle datasource, and then for each reference, it also retrieves information from our Dynamics NAV SQL datasource.
Once the run is completed, all Oracle data appears correctly, and some NAV data appears correctly. However, most rows of data for the NAV side of things just shows #VALUE! all over the place.
There are no errors when the run completes, and if I select a single cell, and click debug, it changes to the value it should be.
Is this likely an issue with Jet, or an issue with the report?
Many thanks
Eds
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hi CC,
After opening a case with Jet support, they have advised the same.
I have increased the connection timeout on our NAV SQL datasource from 30 seconds to 300 for testing, and it works perfectly.
Still odd that two different machines using the same central datasources and datasource settings exhibit different behaviour, but at least now I know why it has happened and how to resolve :)
Eds -
Jet Reports Historic Posts Hello Eds.
It sounds like something is interfering with the communications between Excel/Jet and the NAV database.
You may want to run this one by Jet Reports Technical support. I'm guessing that they'll have you create a log file to provide them with more in-depth info. -
Jet Reports Historic Posts As I suspected.
Posted in the forum in case anyone had come across before and had a quick fix.
Interestingly, if I run on one of our 2012 R2 servers, with the same version of Jet, it works without a problem.
Will see what Jet support say.
Eds -
Jet Reports Historic Posts Hello eds,
What type of data source are you using to connect to your NAV database?
Web Services?
Direct SQL?
NAV 2009 R2 or earlier?
When a Jet function results in an error, #VALUE! will appear in the worksheet cell containing the function.
Debug helps diagnose problems with these Jet functions as you have found.
Generally, when clicking debug corrects the issue, it appears as though some of your queries in the report may be timing out.
There are a few potential fixes for this issue.
1) Disable "Enable batch function optimization" from the Jet ribbon > Application Settings > Performance tab.
2) Upgrade Jet Essentials/Jet Professional to a newer release (if not already on the latest)
3) If using Web Services data source, increase the "Request timeout (seconds):" in the Data Source Settings > Advanced tab.
4) If using direct SQL Server data source, increase the "Command timeout (seconds):" setting in the Data Source Settings > Connection tab.
Increase the "timeout (seconds):" value incrementally, testing the report after each increment.
e.g. 60 > 120 > 240 > etc… until you no longer see the #VALUE! errors in the report.
If the above does not help, I'd recommend submitting a Support ticket.
Cheers,
Please sign in to leave a comment.