After subreport is supressed the report returns no data

[montgomery29]

My original problem was I have a subreport with a shared variable that needs to show on the main report. All works fine and I've created a running total for the group - again all works fine. Now I only want to see the group totals and not the detail. Subreport (all fields are supressed in the subreport) is in Detail A and the main report detail is in Details B. When I supress Detail B, the report takes about 7 mins to run but has the blank lines for the records in Detail A "Subreport".

After reading many threads on the subject, I found that to supress a subreport go to format subreport and check off "suppress blank subreport". When I do that, the report never stops running. I let it run overnight and it was still "reading subreport records". Any help is appreciated.

 

[lbass]

You can't suppress the subreport object or the section it is in, if you want the shared variables to pass to the main report. To make the sub not appear, you can suppress all sections within the sub, format the sub to "suppress blank subreport", remove the borders on the sub, and then format the detail_a section to "suppress blank section."

The fact that the sub is running so long is because it is in the detail section, so it is executing for every detail record in the main report. The question is how the sub is linked to the main report. If it is unlinked, it could be evaluating many records every time it executes. It may be that the sub should be in another report section, depending upon its purpose.

-LB

 

[montgomery29]

It is linked by Order and by item #. If I move the subreport out of the detail section, then the running total on the shared variable does not calculate.

 

[lbass]

I would go into the subreport record selection formula and make sure both links are represented in the selection formula. Without any more information, it seems like you have it linked correctly. After implementing my suggestion, are you successfully getting the shared variable results in the main report?

-LB

 

[montgomery29]

Yes, I am getting the results of the shared variable as well as the running total but only when the subreport is in detail A and yes subreport selection formula is represented by the links. I thought more of your earlier suggestion and removed the Item # link and only left the Order #. Then I ran the report for only January's results. The report did finally produce data. Now I am trying to run the report for July and the report has been running now for 2 hrs and still "reading records" from the subreport. Also, and I don't know if this is important, but this is a YTD and Prior YTD report so I'm sure it's having to go through a ton of data. The weird thing is that if I stop the report, it does after several minutes of "generating data from subreport" finally show incomplete data.

 

[lbass]

I think you need both links. If you have two years of orders and the report is checking every item, that is likely to be a long report. Are you sure the subreport is really necessary? How fast does the report run without the subreport?

It could be that your report is not optimized for speed regardless of the subreport. Have you checked to see if all of your selection criteria are represented in the SQL query (database->show SQL query)?

-LB

 

[montgomery29]

I verified the SQL Query as you suggested and yes all is represented. If I do not format the subreport to supress blank sections, then the report takes 7 mins. to run. However, I get blank spaces in between each Product Line Group Total. I do need the subreport for I am having to extract total price from the Invoice History table and Costs from the Purchase Order table to calculate Profit Margin by Product Line. I did a test and found the report for the January data runs about 1 hr longer if I have both Order # and Item Code linked vs. just Order #. I'm going to now run the report for June, let it run all night and hopefully see data in the morning. I have also set "Null" Fields to default and unchecked "use server or indexes" which in the past as improved performance. This report is also being run on a Terminal Server.

 

[lbass]

If you do not need to display the subreport, you can suppress all sections WITHIN the subreport. The "suppress blank section" is for the section in the main report in which the sub is located--again, if you don't want the sub itself to show. The links are not optional--they must be driven by the logic. If you don't link on item code, then the entire order will process for each detail section in the main report (assuming your groups in the sub are on Order # and then on Item Code). If you link on both fields, the sub will return values for that order#-item code combo, if you link only on order#, the results will be for the order # as a whole.

I would generally check "use indexes or server for speed."

All of your tables are linked, correct? In the Show SQL Query you are not seeing two separate queries, are you?

-LB

 

[montgomery29]

The report finally produced data and the data is correct. It took 12 hrs to run. I went back and checked off the "use indexes or server for speed" and now doing another test run to see if it speeds up the processing.

Not sure how to answer your SQL Query question. Here is what it shows for this report


SELECT "AR_InvoiceHistoryHeader"."SalesOrderNo", "AR_InvoiceHistoryHeader"."InvoiceNo", "AR_InvoiceHistoryHeader"."InvoiceDate", "AR_InvoiceHistoryDetail"."ItemType", "AR_InvoiceHistoryDetail"."QuantityShipped", "AR_InvoiceHistoryDetail"."ProductLine", "AR_InvoiceHistoryDetail"."ExtensionAmt", "PO1_PurchaseOrderEntryHeader"."PurchaseOrderNumber", "AR_InvoiceHistoryDetail"."ItemCode"
FROM "AR_InvoiceHistoryDetail" "AR_InvoiceHistoryDetail", {oj "AR_InvoiceHistoryHeader" "AR_InvoiceHistoryHeader" LEFT OUTER JOIN "PO1_PurchaseOrderEntryHeader" "PO1_PurchaseOrderEntryHeader" ON "AR_InvoiceHistoryHeader"."SalesOrderNo"="PO1_PurchaseOrderEntryHeader".
"PurchaseOrderNumber"}
WHERE (("AR_InvoiceHistoryDetail"."InvoiceNo"="AR_InvoiceHistoryHeader".
"InvoiceNo") AND ("AR_InvoiceHistoryDetail"."HeaderSeqNo"="AR_InvoiceHistoryHeader".
"HeaderSeqNo"))