I have upgraded from an older version of Jet Reports (7.1.2) to Jet Essentials 2015 Update 1. We are in the process of converting from an older Dynamics NAV 2009 R2 Classic Client on a native Navision database to a Dynamics NAV 2015 SQL database. These are on separate servers so I currently run both side by side. I have created separate data sources for these two databases but am having the exact same issue on both:
All of our financial reports pull information from the G/L Account table. When I do an NL function to replicate rows for my G/L accounts for some reason the No. field in the G/L Account table populates with the data from the Div. Code field. Therefore, my Filter using the No. field to pull the G/L accounts I want does not work.
So basically, it seems the table is mapped wrong and the No. filed is being populated with the information from the Div. Code field. How do I correct this?
Date
Votes
8 comments
-
Jet Reports Historic Posts This is strange behavior indeed. What happens when you use the Field Numbers instead of the Field Names? In the G/L Account table the No. field should be Field Number 1. Open your function in the Jfx and replace No. with 1. Do you get the correct data now?
-
Jet Reports Historic Posts If I put the 1 in, the filter appears it is going to work as I can Evaluate the Function and actually get a result. However, when I run the report, every cell comes up with a black diamond with a white question mark in it (never seen that before). When I drill down, I get the following error:
Dynamics NAV C/Front reported the following error:
The sum of the values in the Amount field cannot be calculated because the filter contains question marks (?), asterisks (*), or 'at' symbols (@).
Filters: G/L Account No.: ?, Global Dimension 1 Code: 01, Posting Date: 07/01/15..07/31/15
Table: G/L Entry -
Jet Reports Historic Posts Update from last post using 1 in place of No.
I realized the first replicated row had a G/L account with a zero balance. I changed my account range so that the first row would pull in an account with a balance. This time, I got information back but it was all wrong. The G/L accounts it pulled in seemed to be random; some accounts we don't even use and some accounts we do use were missing. Then, for the g/l amounts it seemed to take a really random group of entries and added them together and that was the same amount posted for every line. So basically I just got garbage.
WORKAROUND: in the G/L Account table there is a column for "No. 2". In our table that field was blank so I copied over the G/L numbers and used No. 2 as my filter in Jet rather than No. This works and now my new reports match my old reports. I'd still like to fix the issue with the No. field since now when we add G/L accounts the No. 2 field will have to be copied over as well, but at least it's working… -
Jet Reports Historic Posts This is really strange and doesn't seem to be working correctly. I would suggest you contact Jet Support directly (https://support.jetreports.com/) to see if they can help you out.
-
Jet Reports Historic Posts I will try that. My account is pretty old and our domain and therefore my email address has changed since I signed up. I can't change that in my account settings for some reason so I put that note in my ticket. Hopefully I'll get a response but I figure there is a good chance any response will go to my old email address.
-
Jet Reports Historic Posts If you include your new email address I am sure they can update it for you - or you could create a new login with your new address.
-
Jet Reports Historic Posts Wow, glad I contacted support. Not long after I submitted my ticket, Matt from support messaged me asking for some screen shots. Shortly after I sent them, he gave me a call and joined onto my computer to take a look around. What I was working on for many hours trying to fix, they had solved in about 5 minutes!
It ended up being pretty simple and I can't believe I didn't catch it. For some reason we had a dimension created that was named "No.". When I looked at the values of that dimension the only value was our G/L account for phone expense so I'm guessing the dimension got created by accident at some point. Needless to say that dimension was conflicting with the "real" No. field. They had me change the code caption from "No." to "No. Code" and sure enough all is working.
Thank you for your help Kloser, and thank you Jet Support! -
Jet Reports Historic Posts That totally makes sense! I didn't even think about an advanced dimension being the issue. Thank goodness Matt was awesome and figured it out!
Happy Jet Reporting!
Please sign in to leave a comment.