After you calm down, have your coffee, chai latte or coconut water to relax, you are now ready to jump back on your cubical chair saddle and whip that metal horse back into submission. You take the time and effort to try to work through the issue only to find out when you think you have set up everything right and that it is all good, that you are back to where you started from, if not worse off…..leaving you to feel like this!
At this point, screaming fire through your Apple or Android bullhorn, you decide to call your support provider, hoping they can put the pieces of this mysterious puzzle together and get you back on track. Well you are not alone, recently, I had a client that was using Jet Reports, Jet Essentials with many Excel reports for multiple NAV companies. Even though the reports were separated on different servers for each company and scheduled to run at different times, based on the last report that ran, that data source and company was used on the next report that was scheduled to run. That resulted in a report with blank output due to the fact that the queries in the report were pointing at the wrong data source/company. Although the reports had the data source and company set correctly on the Jet ribbon in Excel, the last scheduled report dictated the settings for the next report.
To resolve this issue Jet Reports suggested using variables and updating each query in the report to pull the variable values. This would ensure that each query in the report would correctly use the values. This was a tedious effort for some reports that had an excessive amount of queries, but in the end resolved the issue.
To accomplish this perform the following steps:
1) In your report create a hidden column with the cells for data source and company:
2) In the next hidden column, enter the actual name for your data source and company with the values for these fields from the Jet->Settings in the ribbon (I have used generic names for this example):
3) Name the value cells (created in step 2) from the default name i.e. B6 to a meaningful name like rptDS and rptCo.
4) Finally, in each query in the report (via Function Wizard in the ribbon) add the data source and company as parameters to the query and reference the named value cells created in step 3:
This process left my client and I both feeling like this!