subreports

[aurunner611]

CR11, on Accpac 5.5 data.

Hello,
I have an issue that I'm not sure how to tackle. I'm trying to create a report that displays two different sets of data, and then puts that data into a report where i can run calculations on it. Basically I'm taking 2 reports that we already have created and trying to combine the data.
the first report contains our last 4 quarters sales (which are grouped on OESHHD.ITEM) getting a total sold per item for the 4 quarters.
the second report is then taking our current inventory vs orders (whats on PO, sales orders, Qty on hand, etc) running calculations on that and this is grouped by ICSTATI.ITEMNO.

Basically what i need to do is get some data (the data totals) from each report and use it to run more calculations for another report. This final report will make projections on what we need to re-order.

what is the best way to accomplish this? do i have to create a new report and add the 2 existing reports as sub-reports? I've created a new report and the only thing in it so far are the 2 sub-reports. I've linked the main reports parameters to the sub-reports' and put the sub-reports in the main reports details section. but the data doesn't display there, basically when i run it it shows a blank report. if i move the reports to the report header they automatically run and show data. if i put it in the footer the sub-report shows and i can double click it to view the data. I really don't care if i can see the sub-report data in the main report since i only need to cherry pick data i want for the main report, but how do i pass just this data to the main report? and once it's in the main report i need to group it by ICITEM.SEGMENT2, is this going to be a problem since the sub reports are grouped differently?


thanks for your time.

 

[lbass]

You can't group on a field that is shared from a subreport. There isn't much to go on here, but if the sales and inventory tables have item numbers in common, and if we assume that the inventory contains all potentially sold items, and if we assume the field you want to group on is inventory-related, you could use the inventory tables in a main report and add the sold items in a subreport. You might want a second group on item number where you would place the subreport, linked to the main report on item number. You could then pass information from the sub to the main report using shared variables, so you could use the data in calculations.

This is just a guess though, given limited information about your data and what you are trying to do.

-LB

 

[aurunner611]

lbass, thanks for the reply.

i believe you are pointing me in the right direction. i re-did the report in the following manner. took one of the existing reports and added the 2nd report to it as a sub report, linked the parameters from the main report to the sub-report. and then setup shared variable to bring the one item over from the sub-report that i need to use in the main report. however, in the main report the shared variable is only giving me the very last data in the sub-report for each item (gives data from last item calculated, not each individual item). (main report is grouped on ICSTATI.ITEMNO, sub-report is grouped on OESHHD.ITEM, not sure if that has anything to do with it however, OESHHD.ITEM is the same as ICSTATI.ITEMNO.)
could you give me any hints on what to look for so this will give me data for each item?

sorry i know i'm probably not explaining this very well. CR is not my forte, just had this handed to me.

 

[lbass]

You need to link the item fields to each other (not just the parameters). Go into edit->subreport links to make the link. Then place the sub in a group section based on item. Also note that calculations must occur in a section below the one in which the sub is placed, e.g., in GH_b if the sub is in GH_a.

-LB

 

[aurunner611]

LB,
thanks that did the trick.

I do have another question, unrelated to the ones above though. On another report i have a formula doing some calculations from the OESHHD table, i placed the formula in group header2. i can add anything from the OESHDD table and that formula factors correctly.
however, if i add a field from another table then it seems to double almost all of the results in that formula. (it doesn't double everything, but this may be because there was no data for the particular item in the second table???)

here is the way this report is grouped.
group header #1-ICITEM.SEGMENT2
group header #2-ICITEM.ITEMNO
group header #3-OESHHD.ITEM


here are how the tables are linked for the report.
ICITEM.ITEMNO LINKED TO ICSTATI.ITEMNO, ICILOC.ITEMNO, OESHHD.ITEM
i would think it would have to do with how it's grouped, but i'm not really sure. do you have any ideas why this might be happening?

thanks again for your time!

 

[lbass]

By adding the field you are activating the table, and if you have a many to many link, then you will get row inflation--records in one table will duplicate for each instance of a matching field in the other table. You can either use running totals in the group footer that evaluates on change of some field such that duplicates are eliminated, or if you are not grouping on a field from the new table, you can add the new table to a subreport, so that it doesn't cause the row inflation.

Please in the future start a new thread when you have a new topic.

-LB