Hello, I am new to Jet reports and I was trying to create a report where it will pull all sales for specific locations. Am getting the #Value error when I refresh the data. Looks like it is causing an error for the location with () in it. North Area (Canada). My filter for the location is is to include all the locations except the Head Office and the Regional Offices. Any ideas on how I can still use my existing filter (of excluding specific locations) and have the values for North Area (Canada) pull in?
Thanks!
Michelle
Date
Votes
2 comments
-
Jet Reports Historic Posts Hello, I am new to Jet reports and am modifying a report which pulls in all locations except for a few. My filter is currently set up as Location <> "<>*Area 1*&<>*Region 1*&<>*Head Office*" I noticed that some of the locations in the field use special characters - North Area (Canada). Am getting an error message whenever I run the report and North Area (Canada) - Invalid Filter "North Area (Canada) and the values are showing up as "#Value!". How do i go about fixing this one?
Thanks!
Michelle -
Jet Reports Historic Posts Hi Michelle -
Given the nature of this question, I have to assume that you are working with Dynamics NAV.
Because (as you noted) parenthesis are consider to be "special characters" by NAV, they require special handling when used in a filter. Please see this Knowledgebase article –> http://kb.jetreports.com/article/AA-00540
Because your filter uses <>, you won't be able to use the "@@" technique described in the top part of that article. The reason for this is noted in the paragraph toward the bottom of that article. By applying a "@@" at the front of the filter you mentioned, NAV would look for a Location with the *exact* match of "<>*Area 1*&<>*Region 1*&<>*Head Office*". Obviously, you don't have a location with that name.
Thus, I think you would somehow need to build the filter using the "single quote" method described in the second half of the article, so that you would have 'North Area (Canada)' enclosed within single quotes. The rest of your filter (which includes the * wild card symbol) would need to *not* include a single quote (since, again, you don't want NAV to look for an actual asterisk in the Location name).
I hope that helps get you pointed in the right direction.
Please sign in to leave a comment.