Dear Community,
I need your help I am all new to Jet Report, and have been setting up the interface so it fetches data. However, when I try to set an date filter (in the GL window) I get the error that the filter is invalid. Here is a little background info on my setup:
Client:
Excel 2013 (32-bit) Danish
Regional Settings on Windows; danish (dd-MM-yyyy)
Server:
NAV2013 (English, United States) running on a English Windows Server 2008R2
Regional Settings on Windows Server; Swedish (yyyy-MM-dd)
MS SQL 2008R2 (collation: Finnish_Swedish)
I have tried every possible way (I could think of) to alter the date in the "GL Windows", however, no luck. Can anybody help? I would prefer NOT changing the regional settings on windows.
Hope you can help, BR
Frederik
Date
Votes
14 comments
-
Jet Reports Historic Posts Hello Frederik,
Microsoft Excel/Jet Essentials use the date format set in the Windows Control Panel > Region and Language settings.
If you are set to dd-MM-yyyy (e.g. 31/12/2014) then you must use that format in the Microsoft Excel/Jet Essentials.
After making any changes in the Windows Control Panel > Region and Language settings you must restart Microsoft Excel before it will see those changes.
Best regards,
MWilson -
Jet Reports Historic Posts Dear MWilson,
Thank for your reply and clarification. I am indeed using the format in Windows (dd-mm-yyyy), however, I get the error that it is not a valid date. Here is what I do:
In design Tools tab I click "GL" and gets the "Jet Function Wizard". From here, I choose "balance" from "what" and an account from "accounts" - and then "Evaluate Function" to see if it fetches data - it does. If I then continue to the "start date" field and choose a date from the "drop down" I get the error immediately - So I cannot see how I can avoid this error.
This is why I guess I have done something wrong in the setup of either the client or the server (or both).. -
Jet Reports Historic Posts Hello Frederik,
Which type of NAV 2013 Data Source are you utilizing?
SQL Server Direct
Web Services
Jet Remote Data Service (JRDS)
Can you attach screenshots of your Data Source Settings and your Windows Region and Language?
Best regards,
MWilson -
Jet Reports Historic Posts Dear MWilson,
Thank for your reply
Data Source Type: Dynamics NAV 2013 and later (Web Services)
Attached is screenshots from client side (only 3 as this is the maximum)
Please let med know if you need anyting further and thank you very much for your help so far
Best,
Frederik -
Jet Reports Historic Posts Hello Frederik,
Can you send a screenshot of what it looks like when you select the date and press "Evaluate Function" in the JFX?
Is the SQL Server your NAV database is stored in a local, on-premise SQL server?
If so, have you tested the "Dynamics NAV 2013 and later (SQL Server)" Data Source rather than the Web Services Data Source?
Best regards,
MWilson -
Jet Reports Historic Posts Hello MWilson,
Once again thanks for your reply - Your help is highly appreciated.
Screenshot attached below. The server that has both NAV and SQL installed is a hosted Virtuel Private Server. I have tested the "Dynamics NAV 2013 and later (SQL Server)" connection, however, it failed to connect.
FYI: The error message is in Swedish (Which I find wird, since I have a danish OS and the server has and English OS). It says: "The filter 12/07/2015.. is not valid for the field postingdate in tabel General Ledger entires. It is not possible to evauate the value 12/07/2015 to the type of Date." In my NAV client I can choose between English (United States) or Swedish. Furthermore the JFX write the date choosen in the dropdown as MM/DD/YYYY, where regional settings would imply DD/MM/YYYY - don´t know if this have anything to do with the problem… -
Jet Reports Historic Posts Hello Frederik,
As a test, can you switch your region and language settings to Swedish (yyyy-MM-dd), reopen Excel and test the GL() function again?
I am trying to determine if for some reason the Web Service is only expecting a date format in Swedish (yyyy-MM-dd).
Best regards, -
Jet Reports Historic Posts Hello MWilson,
Thx for a fast reply.
Sorry, but the result is exactly the same… Errorscreenshot attached. However, reading through another post I saw something about 32- Vs 64-Bit installation. My OS is 64-bit, but my Excel is 32-bit version, could that have anything to do with it? And if so, it it possible to get hands on the "old" installation files where the user could choose bit-version in the installation proccess (just to test)?
Al the best,
Frederik -
Jet Reports Historic Posts Hello Frederik,
This would not have anything to do with the bitness of Jet Express or Excel.
Jet Express is meant to match the bitness of Microsoft Excel not the bitness of Windows.
e.g.
If you have 32bit Excel installed you must install 32bit Jet Express.
If you have 64bit Excel installed you must install 64bit Jet Express.
The latest installation procedure does not have separate 32bit/64bit installation packages because it automatically detects the bitness of Microsoft Excel that is installed and installs the appropriate version of Jet Express.
My assumption is that this has something to do with the local client, NAV server and SQL server all having different region settings/collations.
I have not been able to reproduce this locally as even changing my local PC to Swedish and Danish I can still use any format without issue (against a NAV server in English) via Web Services.
My assumption is the same issue occurs when using Table Builder and setting report options for a date field, is that correct?
Best regards, -
Jet Reports Historic Posts Dear MWilson,
We have figured it out (had a developer to help me out). You have to change the language ID of the NAV user to 1033 in the page User Personalization. On top of that, you have to change the Service Language to en-US in the NAV Administration Panel. Better create a second web service instance so you do not have to touch existing ones.
It seems like Jet Reports builds date filters in English format (MM/DD/YYYY), but our NAV service expected the universal format (YYYY-MM-DD). Changing around locale settings in Excel or Windows does NOT change the way Jet Reports build the date filters.
Hope this helps everybody who encounters this problem.
And thank you very much for your help MWilson - Highly appreciated!
All the best,
Frederik -
Jet Reports Historic Posts Hello Frederik,
That is great news!
I am glad that you were able to resolve the issue and we always appreciate when you share the resolution with the community.
I am sure that this will help other users in the future that may encounter a similar issue.
When using Web Services we do always recommend having a separate instance of NAV specifically for Jet Express with only the Jet Codeunit published.
There are some other helpful hits in the following knowledgebase article (some of which are specific to Jet Essentials) which may help with the performance of Web Services.
Essentials Performance with NAV 2013-2015 & Web Services: http://kb.jetreports.com/article/AA-00808
Best regards,
MWilson -
Boris Krstic Hi.
I have the same problem - the only difference is that my customer is from Norway.
I've tried everything except changing the Service Language to en-US (current is nb-NO). Obviously it didn't help.
So my question is should I just make a new instance (this is a recommended solution if using Web Services) with English service language or there is maybe a different solution?
In addition, I am getting "Settings.xml is corrupt" error when starting the Excel.
-
Harry Lewis Hello Boris -
I will leave your first question to anyone who may have found a different solution.
Let's look at the message about the Settings.xml file.
Assuming that this is the settings file used by the Jet Excel add-in, I would recommend renaming that file and see what happens.
With *all* instances of Excel closed, start Windows Explorer and navigate to the Jet Reports app data folder:
Then, rename the SETTINGS.XML file to something else (e.g., oldsettings.txt)
Then, restart Excel to ensure that the message is no longer displayed. You may have to check your Application Settings and reset any that you may have previously changed.
-
Boris Krstic @Harry Lewis
Thank you, this helped with the settings file.
Let's hope I will get some suggestions regarding the date/filter problem.
Hi again. I would just like to say that changing the service language to ENU helped. And it seems that is the only thing we can do about this in order to make it work.
Please sign in to leave a comment.