After updating to Jet Essentials I’m having problems with ‘Advanced Dimensions” – specifically that I cannot return a Sum from the Purchase Line (see example formula below) which has worked fine for a few years in previous Jet versions (at least up to v9). “Jet cannot find any relationship between the ‘Purchase Line’ table and Advanced Dimensions…etc”
The report I’m having problems with has dozens of similar formulas using the purchase line table. It appears our Dimension Code “MAINT” will no longer work as a ‘field’ in the filter.
Example:
=NL("Sum","Purchase Line","Outstanding Amount","No.","3535|3270","Shortcut Dimension 1 Code"," ASSET","Maint","PT")+NL("Sum","Purchase Line","Outstanding Amount","No.","3274","Shortcut Dimension 1 Code","TCS|ASSET|PM","Maint","PT")
I have searched this forum and cannot find how to setup advanced dimensions in Jet Reports. The Jet Reports help also just has a small mention of it but no instructions. Could someone provide advice for changing the formula or activating in Jet whatever it is I’m missing.
Many thanks
Date
Votes
5 comments
-
Jet Reports Historic Posts Official comment Hi,
There isn't any setup for advanced dimensions in Jet. This sounds like something you should contact Jet Reports support about. I don't think anyone on the forum will probably be able to help you, since it may be something specific with how advanced dimensions are setup in your NAV database.
Regards,
Hughes -
Jet Reports Historic Posts Problem solved.
It turns out we were using a Dimension ‘Code Caption’ text of ‘Maintenance Code’ on our dimension ‘MAINT’ which is identical text to a NAV standard field name Maintenance Code on the purchase line table.
After renaming that bit of caption text all is well. :) -
Jet Reports Historic Posts Oh that is interesting. So in your formulas were you using "Maintenance Code" and then Jet was picking the standard field instead of the dimension? That is something to watch out for; I have seen the same thing happen with the AREA dimension since there is a field called area on some tables. Good catch.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes
No, the formula only referred to our Dimension Code ‘MAINT’ - no mention of Maintenance Code in there, so I guess Jet is using the ‘Code Caption’ text somewhere in the advanced dimension process.
Bizarrely, we also have a (bespoke) field named "Department Code" on that same Purchase Line table which I thought could clash in the same way because another Dimension Code we use has that same text “Department Code” in its “Code Caption” – but it doesn’t clash, presumably because it is not a NAV standard field? :?
Cheers. -
Jet Reports Historic Posts Jet really has no notion of whether a field is a NAV standard field or a custom field. The only time that might come into play is if you told Jet to use captions and you hadn't exported the captions for your custom fields. If you have 2 fields with the same caption, Jet would most likely just pick whichever one it found first. This might be the advanced dimension or it might be the built-in field. So really, the answer is that if you want Jet to work reliably, you shouldn't give advanced dimensions the same captions as fields. I'm glad the problem is resolved now.
Cheers,
Hughes
Please sign in to leave a comment.