Passing Parameter to Subreport

[kskid]

CR XI
Oracle 8i using Oracle ODBC Driver

I receive a parameter from the main report for use in a subreport. No problem there. Question there is how can I incorporate the parameter into the Add Command of the subreport.

I tried to use the same parameter name but that didn't work. What I am getting is a complete union of both tables. I just want specific records from each table

Select
person_id
from
tablea
where
person_id = {?pm-parm}
union
Select
person_id
from
tableb
where
person_id = {?pm-parm};

I stil get prompted for the parameter rather than using the parameter that was passed.

If I leave the parameter off then I get a union of both table (3 million in Table a and 500K in table B) before the record selection uses the parameter.

Any ideas?

-lw

 

[lbass]

I'm confused. Are you showing us your actual subreport command (which is a union), or are you trying to explain the results you are getting?

-LB

 

[NC17]

Create the paramters in your command like usual. then go back to your main report & select subreport links under edit. select the field to link to. the subreport parameter field to use should contain the parameters you created in your command. I've done this many times using Crystal 9, 10, or 11. I did have a problem with Crystal 10 initially, but a patch was available on the support site.

 

[lbass]

Just tested this, and if you create a parameter within a main report command and then also create a parameter within a subreport based on a command, you can then link the main report parameter to the subreport parameter by using the dropdown in the lower left of the subreport linking screen to select the subreport parameter (instead of the default {?pm-?parameter}). The problem I see here is that as far as I know, parameters developed in commands do not allow multiple values. You could set the parameters up so that you could add a string of values in the parameter selection screen and then link the parameters as before, but this is not an intuitive way of entering a parameter option. You would have to set up the command using:

where {table.field} in {?stringparm}

And then enter the fields like this:

('Field1','Field2','Field3')

-LB

 

[kskid]

Hmmmm... I must have done something wrong. I'll look into it and see if I can figure what I did wrong.

Thanks for the tip. Will get back and post the results

-lw

 

[kskid]

I double checked and I still can't get the subreport parameter to pick up the main report parameter.

Maybe it's because my main report is also created from an Add Command. Thare are the same table but from two different databases, one active(tableb) and one inactive (table70).

SELECT DISTINCT
Table70.LNAME,
table70.FNAME,
table70.MNAME,
table70.PER_PERSON_ID,
table70.DOB,
table70.SSN
FROM
tableb@OLD.WORLD table70
WHERE
table70.LNAME LIKE UPPER('{?LName}%') AND
table70.FNAME LIKE UPPER('{?FName}%')
UNION
SELECT
tableb.LNAME,
tableb.FNAME,
tableb.MNAME,
tableb.MN_ID,
tableb.PER_PERSON_ID,
tableb.DOB,
tableb.SSN
FROM
tableb
WHERE
tableb.LNAME LIKE UPPER( '{?LName}%') AND
tableb.FNAME LIKE UPPER('{?FName}%') AND
tableb.NME_TYP_NAME_TYPE_CODE = 'P'

From here I am grouping by name and then personid. everything below is hidden so when I pick the personid I want, the subreport should be triggered taking the personid from the main report as input.

I create a subreport with another Add Command and want to link both of them

From the main report, I right click on the subreport and click on Change Subreport links... I get ?pm-Command.personID on the lower left but no parameter link is visible on the lower right for the subreport. I have to edit the subreport and go into the record selection expert to pick up the subreport parameter.

When I go back to the main report and refresh, I am prompted for all three parameters, the last name and first name from the main report and the personid from the subreport.

Any ideas? I am stumped.

-lw

 

[lbass]

Is the subreport using one of the same tables as the main report? Did you create the same last and first name parameters in the subreport command? Have you verified that the parameters are set up as the same datatype?

-LB

 

[kskid]

I did use the same two table in the subreport but using the personid parameter instead of the last name first name parameter in the main report.

I'll try using different tables in the subreport.

Yes, the parameters have the same number datatype.

-lw

 

[lbass]

WHAT personID parameter? I don't see one in your first command. If you want to use a personID parameter, try adding a {?personID} parameter to both commands, use it in the where clause of each, and then link the two {?personID} parameters.

-LB

 

[kskid]

Problem solved

From my main report, right click on the subreport and clicked on change subreport links I clicked on the Command.personid as the link parameter. This creates a parameter name of Pm-Command.personid that appears in the lower left hand corner.

I uncheck "select data in subreport based on field". Besides, ?personid doesn't show up on the drop down list anyway.

I right clicked on the subreport and clicked on edit subreport. I clicked on Database Expert and edited the add command. I clicked on Personid parameter on the right side and clicked on modify. I changed the parameter name from "personid" to "Pm-Command.personid". Likewise, I changed the parameter name in the SQL statements on the left side to {?Pm-Command.Personid}.

It was that simple. Make the parameter name of the subreport identical to the parameter field you are passing from the main report.

-lw