subreport search by main details 1

[XrayboX]

Help (I failed again to figure out search question)

my problem is, how do I pass all {so_item_trans.soit_id} from main report and use them as search criteria for the subreport?

crystal reports XI, ODBC connection to an informix database.
main tables
po_hdr
poln
so_item_trans

main joins
{po_hdr.poh_id} inner join to {poln.poh_id}
{poln.poh_id} left outer join to {so_item_trans.soit_hdr_id}
{poln.pol_id} left outer join to {so_item_trans.soit_ln_id}

main report filtered by {?PO} returns matching {po_hdr.poh_nbr}
grouped by {po_hdr.poh_nbr}
details {so_item_trans.soit_id} (1 to many lines)

main example
Header
{po_hdr.poh_nbr}= LF045
details
{so_item_trans.soit_id}= 17229
{so_item_trans.soit_id}= 17230
{so_item_trans.soit_id}= 17231
{so_item_trans.soit_id}= 17232


In the subreport

subreport tables
po_hdr
rec_hdr
ioq_hdr
so_item_trans

subreport joins
{so_item_detail.soit_id} inner join to {so_item_trans.soit_id}
{po_hdr.poh_id} left outer join to {so_item_trans.soit_hdr_id}
{rec_hdr.rch_id} left outer join to {so_item_trans.soit_hdr_id}
{ioq_hdr.ioqh_id} left outer join to {so_item_trans.soit_hdr_id}


subreport grouped by {so_item_detail.soit_id}

subreport example

header
{so_item_trans.soit_id}= 17229
details
{po_hdr.poh_id}
{ioq_hdr.ioqh_id}
{rec_hdr.rch_id}
header
{so_item_trans.soit_id}= 17230
details
{po_hdr.poh_id}
{ioq_hdr.ioqh_id}
{rec_hdr.rch_id}
header
{so_item_trans.soit_id}= 17231
details
{po_hdr.poh_id}
{ioq_hdr.ioqh_id}
{rec_hdr.rch_id}

ect...
thanks in advance for looking.

 

[synapsevampire]

Why not just link the reports by the same PO parameter?

-k

 

[XrayboX]

the sub report list everything about so_item_trans.soit_id} not just the PO

Sorry I should have described it better with less detail

I lookup a PO, that has items.
I then want to see what happend to the items on that po(receipt,po,order,invoice), sorted by item.

 

[lbass]

Please explain where you want to display the subreport--in what report section. You could link the subreport to the main report on {so_item_trans.soit_id} and place the subreport in a detail_b section. If you want the subreport to appear in a group section or the report footer, then there would be a different solution.

-LB

 

[XrayboX]

I need to Link the subreport from group header.

thanks

 

[lbass]

If in the main report the selection for {?po} is what is determining the {so_item_trans.soit_id} results that are displayed, then, as SV suggested, linking the subreport to the main report on the {?po} parameter should give you the results you want in the subreport. Or are there criteria in the record selection formula that you have not shared?

-LB

 

[XrayboX]

[tt]After finding the {so_item_trans.soit_id} associated with the {?po}
I need to search based on all those {so_item_trans.soit_id} .

I want to find all transactions that use those {so_item_trans.soit_id}, receipt(s) order(s) invoice(s) po(s) , not just the one po .

each {so_item_trans.soit_id} has a {so_item_trans.soit_hdr_id} that is unique to the transaction (not transaction type)
example
transaction
Type Number Id soid
PO LF045 9625 17229
17230
17231
...
rec LF045-1 10570 17229
17230
17231
...
Ord 506948 110847 17229
17230
17231
...
Inv 5069481 111813 17229
17230
17231
...
[/tt]

 

[lbass]

The simplest approach would be to move the subreport to a detail section and link the subreport ONLY by {so_item_trans.soit_id}.

If you really need all {so_item_trans.soit_id} clustered together in one subreport per PO, then I think it would have to be in the group footer so that you could accumulate the IDs in the main report to be passed as a shared variable to the subreport. Let me know if you need to use this second approach.

-LB

 

[lbass]

There is a way to use one subreport per PO in the group header as long as you don't have a lot of {so_item_trans.soit_id} per PO, so it would help to know the maximum number per PO.

-LB

 

[XrayboX]

LB
RE simplest:
I was able to link the subreport in the details section by {so_item_trans.soit_id}, but thanks for the suggestion.

RE Footer:
Since first posting I've found this thread 767-474541 and have been reading cr_arrays.pdf
if this is the wrong tangent or you have code in mind I would appreciate it.

RE One subreport per po in group header:
the system supports 9999 per PO, but as yet the max seems to be 40.

 

[lbass]

I'm confused. Did you mean to say "I was NOT able to..."? And of course you could do that linking if the resulting display was what you wanted.

Re: the other solutions. What version of CR are you using?

-LB

 

[XrayboX]

LB
In the detail section I Can link the subreport to One {so_item_trans.soit_id}, not all of them.

I'm using CR XI

 

[lbass]

I see from another post that you are using CRXI. To use the subreport in the group header, you would need to insert it in a GH_b section (You can format GH_a to "underlay following section" if you wish). In the main report, you would create a formula {@soitIDs} like the following (you can't use a variable in the NthLargest function, so this has to be done manually):

numbervar j := distinctcount({so_item_trans.soit_id},{po_hdr.poh_nbr});
shared stringvar x := "";
stringvar k := totext(NthLargest(1,{so_item_trans.soit_id},{po_hdr.poh_nbr}),0,"") + ", ";
stringvar m := if j > 1 then totext(NthLargest(2,{so_item_trans.soit_id},{po_hdr.poh_nbr}),0,"") + ", ";
stringvar n := if j > 2 then totext(NthLargest(3,{so_item_trans.soit_id},{po_hdr.poh_nbr}),0,"") + ", ";
stringvar p := if j > 3 then totext(NthLargest(4,{so_item_trans.soit_id},{po_hdr.poh_nbr}),0,"") + ", ";
//etc up to NthLargest(40,{so_item_trans.soit_id},{po_hdr.poh_nbr}),0,"") + ", ";
x := k + m + n + p; //add additional variables

Then you can use this formula as the main report field for the subreport linking and uncheck "Select data based on this field". Then in the subreport selection formula area, add the following:

totext({so_item_trans.soit_id},0,"") in split({?Pm-@soitIDs},", ")

If {so_item_trans.soit_id} is already a string field, remove the totext(,0,"") in the above formulas.

If you are unsure of the maximum number of IDs and you are willing to display the subreport in the group footer, you could use the following three formulas in your main report:

//{@reset to be placed in the group header:
shared stringvar x := "";

//{@accum} to be placed in the detail section:
shared stringvar x;
if instr(x, totext({so_item_trans.soit_id},0,"")) = 0 then
x := x + totext({so_item_trans.soit_id},0,"")+ ", ";

//{@displ} to be placed in the group footer:
shared stringvar x;
left(x, len(x)-2)

Then use {@displ} for linking to the subreport and follow the steps above to create the subreport record selection formula.

-LB

 

[lbass]

I missed your last post. If you link {so_item_trans.soit_id} to the same field in the subreport, and place the subreport in a detail section, each subreport execution will return all info for that particular {so_item_trans.soit_id}. Only one link is necessary to accomplish this.

If you are saying you want each firing of the subreport to contain all {so_item_trans.soit_id}s for that PO, that is the same as saying you want the subreport to be executed in a group section. My last post shows a method for using the subreport in a group header section, and a different method for using one in the group footer section.

Please note that for the first solution the main report formula should be placed in GH_a, and the subreport in GH_b. For the second solution, {@display} should be placed in GF#1a, and the subreport in GF#1b.

-LB

 

[XrayboX]

Lb
Here is A star For you.

As good as you Folks are, I am Amazed At your abilities and speed of reponse.
I used your GH_a / GH_b sugestion, and if I had read ALL your comments I wouldn't have ripped my hair out trying to modify the NthLargest line with a variable.

(you can't use a variable in the NthLargest function, so this has to be done manually):

I take it there is no way to convert {@soitIDs} to a numeric array (that can be used in record selection Formulas) to speed up the search? ({so_item_trans.soit_id} is an indexed field)

 

[lbass]

I suppose there is a way to accumulate the values into a numeric array--I just don't have a facility with arrays.

-LB