Hello everyone,
i am a a bit new to JetReports, and ran into a problem, which maybe you could help me with.
Currently using Jet Reports 7.12, connected to Navision 5.0
My problem is combining filters from different fields with a logical OR (instead of AND) within my NL function.
We are evaluating our course leaders, and save the results as values to a table in Navision.
Now i want to select all course leaders from this table, which have exceeded a certain value.
The problem is, that i have multiple fields with values, and i want to select all course leaders, that reached certain values in Field 1 OR Field 2 OR Field 3.
So far, as a test, i built a function that selects all course leaders that reached a certain value in ALL of the fields (normal AND operation in NL)
=NL("Rows";"Evaluation";;"No.";Options!$D$5;"+Surname";"*";"Skill";">=2,81";"Material";">=2,81";"Knowledge";">=2,81")
This works, but is not what i wanted, since i want to select everyone who has reached the value in either of the three fields.
How can i connect these 3 Filterfields with a logical OR?
I tried messing around with NP(Union) or NL(Filter), but found no real good documentation that explained it well or showed some usable examples.
I found some threads dealing with problems similar to mine on this board, but trying to recreate the functions giving there and adjusting them to
my tables/fields didn't work at all.
Could you fill me in with a clue or two, how to approach this problem?
Thanks in advance!
Mike.
Date
Votes
1 comment
-
Jet Reports Historic Posts Hi Mike,
I believe you do need to use NP(Union) and NL(Filter) to achieve what you are trying to do. Assuming that the "No." field is the primary key of the "Evaluation" table, you could do this something like this:
B2: =NL("Filter";"Evaluation";"No.";"No.";Options!$D$5;"Skill";">=2,81")
B3: =NL("Filter";"Evaluation";"No.";"No.";Options!$D$5;"Material";">2,81")
B4: =NL("Filter";"Evaluation";"No.";"No.";Options!$D$5;"Knowledge";">2,81")
B6: =NL("Rows";NP("Union";$B$2;$B$3;$B$4))
The NL(Filter) functions give you the sets of values of the No. field for each of your filter conditions and then you combine those sets of values (eliminating duplicates) with NP(Union) which is the equivalent of logical OR. Then we replicate the results with NL(Rows). Of course the only issue with this vs. what you have is that it will be sorted by No., not by Surname. We could do a slight modification on it to sort by surname like this though:
B6: =NL("Rows";"Evaluation";;"+Surname";"*";"No.";NP("Union";$B$2;$B$3;$B$4))
Here instead of replicating the results of the NP(Union), we use those results as a filter on the No. field and thus we can perform the necessary sort. Does this work for you?
Regards,
Hughes
Please sign in to leave a comment.