Subreport and Link Parameters

[tmccoy1]

I have a report that typically pulls a small amount of data per instance since it is run per client ID. The main reports runs very quickly. When adding a needed subreport the report then takes a long time and appears to cycle through the entire database, rather than being narrowed by the selection criteria set in the subreport links.

I have an older/simpler version of the report with the exact same subreport, and exact same subreport linking and it runs in a reasonable time. One thing I noticed is that if I view the sql query for this old report it prompts for the 2 parameters which are set to link to the main report and then the sql query shows that link parameter criteria with the entered values. In the new report, it too prompts for the parameters but the link parameter criteria is not shown in the sql query.

The main difference between the reports is that the old one was created entirely with the graphical database expert, and the new one is based entirely on a sql command table.

I have deleted and recreated the subreport, checked the link parameters, verified that think linking created a record selection formula, etc.

It appears as if the record selection formula is being ignored when the subreport runs, as it should only process through a small number of records, not millions.

Any ideas on how to resolve this?

 

[pmax9999]

From what you have said, my guess is that the parameters do not form part of the Command, but rather have been added to the Record Selection formula in the report, which means that the SQL Query will return every record and the report then will drop those records that do not meet the Record Selection requirements. To overcome this, add the Parameters to the Command and remove them from the Record Selection.

Cheers
Pete

 

[tmccoy1]

Thank you pmax9999. Your suggestion lead me to a solution that seems to work well. I found a post on another site that was an elaboration on your suggestion, and if it is of any help to anyone else it is:

"I've done this before. Took me a while to figure out how to get parameters into an SQL command within a subreport. It's true, the SQL you have there will fetch all the records because there is no where clause. If you are passing your parameters to get it in the Select Expert, you are filtering after you've retrieved the 10,000+ records. From the main report, the parameter has to get into the SQL command of the subreport in order for it to retrieve the specific record. Here's how to do it.

You can pass either the oiginal parameter or the field (if available) from the main report. Since your wrote "Pm-Command.PATID and Pm-Command.Episode_Number", you are passing the field. First, add a where clause to your SQL command

select
b.patid, b.episode_number, b.guarantor_name, Trim(b.guar_address_line1|| ' ' || ifNull(b.guar_address_line2, '')) address, b.guar_address_city || ', ' || b.guar_address_state || ' ' || b.guar_address_zip location, b.guar_phone_number
from
billing_guar_data b
where
b.patid = {?Pm-Command.PATID}
and b.episode_number = {?Pm-Command.Episode_Number}
Next, in the same window on the side parameter list, create 2 parameter

•Pm-Command.PATID
•Pm-Command.Episode_Number
Then in the main report bring up the Change Subreport links... The 2 parameters you are sending should still be there. Make sure the "Select data in subreport based on field" is unchecked on both parameters. If this is checked, it will send the parameter to the Select Expert. Because it's going to be in the SQL Command, it should be unchecked."

The original poster added the following which ultimately got things working for me:

"In the linking section I selected the parameter from the main query and moved it to the box on the right and then in the left hand drop down I selected the sub-report parameter...now when I am prompted for parameters I only get the main report ones and my report runs super fast! "