I am looking to build a report using a Count if either of two fields is equal to a value. Is there a way to do this without using NP(Union) and listing them all out in a Rows replicator statement?
Date
Votes
8 comments
-
Jet Reports Historic Posts Official comment Aha. That makes perfect sense. Thank you.
-
Jet Reports Historic Posts Are you using Navision? If so, you should be able to do this with a calculated field something like this:
=NL("Count","Table",,"=OR(NF(,""Field1"")=""value"",NF(,""Field2"")=""value"")",TRUE)
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hello,
I'm new to Jet and am trying to do the exact same thing, but I couldn't get your solution to work. Please see the following arbitrary example:
=NL("Count", "Customer",, "City", "Toronto") produces 18
=NL("Count", "Customer",, "No.", "X*") produces 10
=NL("Count", "Customer",, "City", "Toronto", "No.", "X*") produces 0
therefore
=NL("Count", np("Union", NL("Filter", "Customer", "No.", "City", "Toronto"), NL("Filter", "Customer", "No.", "No.", "X*"))) produces 28, as expected.
However,
=NL("Count", "Customer",, "=OR(NF(,""City"")=""Toronto"", NF(,""No."")=""X*"")", TRUE) produces 18, not 28.
Am I missing something? -
Jet Reports Historic Posts Hello!
My first guess is that there is some sort of overlap between the 2 sets. In theory though, this overlap would show up in both the NP(Union) example and the calculated filter field example. I'm not sure what could be going on there. Have you tried replicating out your results with NL(Rows) and looking at the City and No. fields to see the actual records being included by each of your functions? This is what I would do to try and figure out why particular records might be making it into one function and not another.
Regards,
Hughes -
Jet Reports Historic Posts There are no overlaps whatsoever. I've verified it. I've replicated out the results. Everything looks correct. It looks to me that the =OR function just isn't working. Are you able to produce proper results?
-
Jet Reports Historic Posts Yes I have verified that it works and I just spotted the problem in your formula. You have NF(,""No."")=""X*"". Keep in mind that the calculated filter field is evaluated as an Excel function. Excel has no idea what X* means. That's a Jet filter which you're trying to mix with an Excel function and that won't work. If you want to get results starting with X in an excel function, you'd have to do something like this:
UPPER(LEFT(NF(,""No.""),1))=""X""
Regards,
Hughes -
Jet Reports Historic Posts Unfortunately, I am not using Navision so a Calculated field won't work.
-
Jet Reports Historic Posts I believe the only other way to do it if you aren't using Nav (other than the ways you enumerated in the post to begin with) would be to use SQL= and create a custom SQL statement.
Regards,
Hughes
Please sign in to leave a comment.