One report's data as input to another report?

[hawkseyesys]

I have one report that uses complicated calculations and a specific sort to come up with the date each upcoming shop floor order on a machine should begin, based on the cumulative time required for the preceding orders. I want to use these order numbers and begin dates as input to another report that checks whether materials will be available at that time (MRP).

Is there a way to export selected field values into some format that could then be used as input to another report? I can't redo the calcs in the second report because the sort is completely different.

We are using CR/CE 10 and an Oracle 9i database.

Thanks for any help you can give!

 

[Turkbear]

Hi, Try inserting the second report as subreport and linking on the derived date in the first report.

 

[hawkseyesys]

Thanks, Turkbear. I was thinking that wouldn't work because the sort for the second report is completely different from the one in the first report. I really just need certain data that I can rearrange in the second report, combined with additional data from the Oracle db.

Maybe I'm overlooking something here?

 

[Turkbear]

Hi,
AFAIK, the sorting of the subreport in completely separate from the main report..
If you place the subreport in the report footer ( and optionally use a 'new page before' tag), it will 'look' like a new report following the main one.

If I'm missing something, maybe a sample output would clarify..

 

[hawkseyesys]

Maybe I'm beginning to see the light. I can see that your solution would work fine, IF I can get the output from each line of the main report into the subreport. If I were only calculating the date for a single order in the main report, that could be used in the report footer subreport. But I have hundreds of orders in the main report, each of which needs to provide data to the second report.

Here's an example, if it helps:

========= First Report ===============

MACHINE ORDER NUMBER START DATE MATERIAL QTY
A 12345 10/05/04 ABCD 100
A 23456 10/07/04 EFGH 150
A 34567 10/10/04 ABCD 125
<group break>
B 45678 10/02/04 IJKL 100
B 56789 10/02/04 IJKL 200
B 67890 10/04/04 EFGH 75
<group break>
C 78901 10/02/04 EFGH 175
C 89012 10/04/04 IJKL 100
C 90123 10/04/04 ABCD 75
<group break>

======== Second Report ========================

MATERIAL DATE NEEDED ORDER QTY BALANCE
ABCD 500
10/04/04 90123 75 425
10/05/04 12345 100 325
10/10/04 34567 125 200
<group break>
EFGH 300
10/02/04 78901 175 125
10/04/04 67890 75 50
10/07/04 23456 150 -100 <---STOCK OUT!!
<group break>
IJKL 200
10/02/04 45678 100 100
10/02/04 56789 200 -100 <---STOCK OUT!!
10/04/04 89012 100 -200 <---STOCK OUT!!

Thanks again for your help!

 

[lbass]

Try importing your first report as a subreport which is placed in a detail_a section and linked to the main report (your second report) on {table.ordernumber}. Do your calculation in detail_b, and then format the detail_a section to underlay following sections so that subreport results and the calculation appear on the same detail line.

To do the calculation, you will have to create a formula making material quantity a shared variable (and placing this on the subreport canvas instead of {@materialquantity}), which you then reference in your calculation for balance in the main report.

-LB

 

[hawkseyesys]

Thank you, lbass. This was an interesting idea I had to think through several times. However, I think I'm still not explaining the situation correctly.

The calculations I need to do on each order will give me the date the order will begin on a machine. Basically we need to calculate and accumulate the required time for all the preceding orders on that machine (no matter what materials they use). So more than each individual order would be needed in the Detail A subreport you describe.

It's those calculated order begin dates that are then applied to the material requirements planning report - they are only virtual data, which is my problem. Hence my question about storing them temporarily as input to the MRP report.

I appreciate everyone's suggestions, and hope I've stated the problem more clearly this time. Any ideas are more than welcome!

 

[lbass]

I think I was assuming that your "First Report" was the subreport placed in the main report ("Second Report"). From your example, it looks like the subreport could be placed in the detail section and linked by material, date, and order number, and that the subreport could provide the display for date, order, and quantity in the main report. Then the shared variable could be used to accumulate. In other words, your example doesn't suggest that you are accumulating anything in the subreport.

Maybe if you refined the example to show exactly what you mean, we could be more helpful.

-LB