I've attached what appears on the tables for the NL formula below which is not returning the correct value. I tried checking the knowledge DB but couldn't find anything that would give me the correct solution.
I want the formula to pick up the correct PBN based on the set “EXP_DATE” of 1/31/16 so that I can get the correct “PREMIUM” of $29.00. The formula below is giving me a “PREMIUM” of $19.33. The common factors in the two tables are the “GRP_NBR”. What is the correct link formula to return the correct premium?
=NL("first","pbn","PREMIUM","DataSource=","Oracle","GRP_NBR",$L3,"INSURER",$E3,"PLAN_TYPE",O$2,"Link=","GRP_PLAN","GRP_NBR","=GRP_NBR","PLAN_TYPE",O2,"EXP_DATE",N3)
Date
Votes
5 comments
-
Jet Reports Historic Posts To start, what happens if you add DATASOURCE= to your link?
=NL("first","pbn","PREMIUM","DataSource=","Oracle","GRP_NBR",$L3,"INSURER",$E3,"PLAN_TYPE",O$2,"Link=","GRP_PLAN","GRP_NBR","=GRP_NBR","PLAN_TYPE",O2,"EXP_DATE",N3,"DataSource=","Oracle") -
Jet Reports Historic Posts No that didn't work, it's not picking up the correct PBN number, which should be 2 based on the EXP Date of 01-31-2016 and PLAN_TYPE(PLAN) of CI on the GRP PLAN table. I want it to automatically be able to pick of the PBN number based on all the other criteria in the function. The only issue may be that the GRP Plan table says PBN and the PBN Table says PBN1. Don't know if this won't allow the correct PBN to be recognized. Can you link= on multiple criteria like the GRP_NBR & PBN?
-
Jet Reports Historic Posts Hello -
In order for LINK= to work, the contents of the fields on which the link is based must match *exactly*.
In the example you state (where the field in the PBN table contains "PBN1" while the field in the GRP_PLAN table contains "PBN") there would be no way to establish the link.
While it is possible to link two tables together based on more than one field (e.g., [table1].field1=[table2].field1 *AND* [table1].field2=[table2].field2), it sounds like you are asking about being able to link based on different *contents* of the the fields.
e.g., "If [table1].field1 is "xyz" and [table2].field1 is either "xyz" or "abc", I want these to match up".
To accomplish this, I would recommend using an NL("Filter") function in a separate cell, and then referencing that from your NL(Rows) function. This gives you a great deal of control over your filter values.
More information on NL("Filter") can be found here:
https://jetsupport.jetreports.com/hc/en-us/articles/115001652367
and
https://jetsupport.jetreports.com/hc/en-us/articles/218953168
I hope that helps -
Jet Reports Historic Posts Would it be better to using the NP function so that it can return the correct PBN based on the exp date and plan type, and then use the NL function to return the correct value. Is it possible to nest these 2 functions together? Thanks for all of the help!
-
Jet Reports Historic Posts I'm not exactly sure what you have in mind, but…
There are (obviously) multiple methods of achieving just about any result. If you have an idea that will will give you what you need… Go For It!
Please sign in to leave a comment.