| Author |
Message |
Tupacmoche
Groupie
Joined: 04 Apr 2007
Online Status: Offline
Posts: 52
|
  
Topic: Data lookup
Posted: 03 Mar 2008 at 7:09am |
I'm working on a Crystal Report against an Oracle db. There are several "island" of data all on Oracle db. From one of these data sources I retrieved a set of records that I am using in an expression in a report to select the records that I need. See below:
If {TAXPAYER_ID} in
["123456789", "123456789", "1234566789", "123456789", "123456789",
"123456789", "123456789", "123456789"]
Then
1
else
0;
So, if the taxpayer id is in the expression it returns true and is included in the data set. Now comes the question and problem I have. There is a field that I need to add to my report that does not exit in its tables. It is available in another data source. It includes the taxpayer id that I can use to link it to the above report. But I can't add this data table to the above source. I must do it through an expression or some sort of lookup in an expression.
To summarize, I have Field1 taxpayer_id and Field2 field_I_need. Taxpayer id can be used as a link field to bring in filed2. I cannot add these fields the easy way by adding a table that contains them to the report but must do it through an expression that can lookup the value of field2.
Any ideas how this can be done? 
|
|
Rob
|
 IP Logged |
|
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 03 Mar 2008 at 12:59pm |
|
Can you use a subreport to display this data? That way you can pass the linking field (expression) to the subreport and have it query the data based no this linking field that was calculated.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
Tupacmoche
Groupie
Joined: 04 Apr 2007
Online Status: Offline
Posts: 52
|
Posted: 04 Mar 2008 at 7:09am |
I suppose that I can do that. The point is, I still have about 3,000 taxpayer_id's and the second field that I need to bring into the report. If there is a simple way to link the taxpayer_id in a subreport why not simple do the linking on the main report, no extra work. So, the problem remains how can I get the info in. See sample below:
taxpayer_id other_field
123456789 4
836748342 5
836450126 2
730912456 3
Remember there are about 3,000 taxpayer_id's associated with the other_field that I want to add to the report. Simple put If this was a table I would relate tableA.taxpayer_id with tableB.taxpayer_id to bring in the other_field but the vendor, politics and many other reasons does not allow this.  |
|
Rob
|
| IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 04 Mar 2008 at 10:15am |
|
Unfortunately, I can't determine what the exact problem is. You want to relate the data in one table to the data in another table, but the linking field is not a data field. It is an calculation. So you need to link another table to an expression? If so, I would make that expression part of the original SELECT statement so that it is part of the raw data.
However, from reading these posts, I'm thinking that I really don't understand the problem and this solution is probably not what you are looking for. Sorry.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
Tupacmoche
Groupie
Joined: 04 Apr 2007
Online Status: Offline
Posts: 52
|
Posted: 06 Mar 2008 at 12:03pm |
I will try to simpify and be more clear. I have id's (like a SSN) and a second field which is a flag (about 3,000) that I get from one data source. This information has been exported into an Excel file for use in a Crystal Report that uses them in an expression, see below:
If {TAXPAYER_ID} in
["123456789", "123456789", "1234566789", "123456789", "123456789",
"123456789", "123456789", "123456789"]
Then
1
else
0;
Even thought this filed exist in a table in the CR, there are other fields that do not exit which are needed to create this set. By useing the above expression in the select expert for all 3,000 id's I get the records I want. This is no different than if I put id = "123456789" in the select expert and got back one record. This part I have done. Now I have a second field which is a flag that also came with the Excel file. It does not exit in the tables of this report. This report has 3,000 records and I have 3,000 flag fields. How do I add this information to the report.
If it were an Oracle table and I had the rights I would simple add the table with a field that could be related to a table in this report. But just like above these values are not in a table. I must find some other way of using these 3,000 values in the report. I hope this makes it clear and someone can help. 
|
|
Rob
|
| IP Logged |
|
|
|
