crystal report - linking subreport parameters

Question

I am trying to add a subreport and pass the parameters from my main report to the subreport. When I link everything up the subreport runs through thousands of records looking for insurance information on my test subject but there is no record.

If I go to a query tool and run the query with the WHERE clause that I expect to be added on the Crystal report query it takes milliseconds to return no rows.

When i run the preview in Crystal reports it takes minutes to run through all the records. Why doesn't the query return no records like it should? Or just the one specific one I am looking for if there is a record? I don't want all 10,000+ records returned to crystal just the ONE or ZERO that I should get based on the parameters I am passing from the main report!!

Thanks for any guidance, Leslie

Edit: I have a query on the main report that needs PatID and Episode_Number. I have a query on my subreport that I would like to filter on the SAME PatID and Episode_Number. The sub-report query is:

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

I have not added any "extra" parameters onto this Command. I have these parameters that have been added by this linking: Pm-Command.PATID and Pm-Command.Episode_Number

which I picked from the Linking page when I created the sub-report

Have you got parameters in the subreport? Have you linked them to the main report? Are the parameters actually used in the "where clause" of the subreport? — vice
I have tried to do this...I have parameters on the sub-report but I didn't put them there...they appeared after I did the linking. — Leslie
It shouldn't be this hard. I want to use the same parameters on all the queries in my report. I want to select one person and one episode and have all the data I need. I want the selected/entered PatID and Episode_Number passed through to every query I have. — Leslie

ca_wan ca_wan · Accepted Answer · 2012-10-27T08:46:51

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.

crystal report - linking subreport parameters

2 Answers