Feeding parameter value into command object 3

[lynchg]

I have a subreport that has a Crystal command as it's datasource, this command is a union query. I would like to pass a parameter from the main report into the WHERE clause of the first select of the union query. I can't get anything to work.

Let me explain a little about what I am trying to do. I am producing a report that displays immunization history for kids. The users want to see all of the age increments for each immunization whether it was administered or not. Since there is no data for a non-occurrence of an immunization I want to union the actual data records to query that returns all possible age increments for immunizations, these age increments (like 2m, 4m, 12m) are not part of the data in any table.

The report should display like this:

ID Immunization Age Date
123 Measles 2m 1/1/07
123 Measles 4m 3/1/07
123 Measles 12m

123 IPV 2m 1/1/07
123 IPV 4m 3/1/07
123 IPV 12m 1/1/08

I want my union query to look like this:

Select ID, Immunization, '' as Age, DateOfService as DoS
From MyTable
Where ID = ParameterFromMainReport
Union
Select ID, Immunization, Age, DoS
From qryFillerRecords

The SQL for qryFillerRecords is like this:

Select '' as ID, 'Measles' as Immunization, '2m' as Age, '' as DoS
Union
Select '' as ID, 'Measles' as Immunization, '4m' as Age, '' as DoS
Union
Select '' as ID, 'Measles' as Immunization, '12m' as Age, '' as DoS
Union
Select '' as ID, 'IPV' as Immunization, '2m' as Age, '' as DoS
Union
Select '' as ID, 'IPV' as Immunization, '4m' as Age, '' as DoS
Union
Select '' as ID, 'IPV' as Immunization, '12m' as Age, '' as DoS

My problem is that Crystal doesn't seem to let you pass a parameter directly into the SQL command, but I can't use a similar union query on the database side(without the Where clause), as the datasource for the subreport because the parameter linking the subreport to the main report will filter out all of the filler records when applied to the entire union query.

I am using Crystal 9 and DB2 backend database.

 

[lynchg]

lbass, in thread thread 766-1419039 you mention that you can use parameters in the SQL in a command. I was hoping there would be an example of the syntax, but there isn't.

That is exactly what I am trying to do.

If someone could post an example of that I would really appreciate it.

Thanks

 

[cmmrfrds]

I can't remember in CE9, but in CE10 and XI to the right of where you type in your SQL is an option to create parameters. You give the parameter a name such as ParameterFromMainReport and since it is an ID you don't need quotes around it in the query.

Select ID, Immunization, '' as Age, DateOfService as DoS
From MyTable
Where ID = ?ParameterFromMainReport

I am not at work to look at an example and I can't remember but it may have brackets around the parameter - you can experiment.

 

[lbass]

Create the parameter within the command, add it to each where clause in the union query, and then link the subreport to the main report by linking the parameters to each other (by using the dropdown in the lower left corner of the linking screen to select {?parm}, not the default {?pm-?parm}. In the command, after creating the parameter on the right hand side, place the cursor where you want the parm to appear in the where clause, and double click on the parm to add it. String parameters must be placed in quotes like this:

'{?parm}'

-LB

 

[lynchg]

Thanks for your help. But I am still having some trouble getting this to work. I followed your instructions to create the parameter in the command, double-clicked on it and it was properly placed in my where clause. I closed out of the command editing window and the Database Expert, the newly created parameter was not listed under the parmeter fields in the field explorer of the subreport.

I went out to the main report tried to set up the link to my subreport, my only choice was the default {?pm-?parm}. I tried that, not expecting it to work, it didn't. I backed out of that, went back to my subreport, explicitly added a new parameter (same name as the one I added in the command), in the field explorer, went back to my main report, linked on that parameter, and then ran the report.

I get all of the filler rows but none of the actual data rows, leading me to believe that there is a disconnect between the parameter I created in the command and the parameter I created in the field explorer. Like I said in my original post, the Where clause is only on the first Select in my union, that is where I want to filter the returned rows to only include the data for a specific person. The second half of the union is the filler rows to ensure that all age increments appear on the report regardles of whether there is data for that person for that age increment.

 

[lbass]

The parameter should have appeared in your field explorer in the subreport. Please post the contents of your command.

I justed tested this, and the parameter appeared in the sub and was available in the dropdown box in the lower left of the linking screen as the subreport link. Note that it must be of the same datatype as the parameter in the main report.

-LB

 

[lynchg]

Here it is:

SELECT CORP_MBR_ID, MEASURE_CLASS, MEASURE_NAME, MIN_REQUIREMENT, SRVC_FROM_DATE, DISPLAY_CODE, NUM_TO_DISPLAY, AGE_MONTHS
FROM DM101.PED_WORK
WHERE CORP_MBR_ID = '{?pCORP_MBR_ID}'
UNION ALL
SELECT CORP_MBR_ID, MEASURE_CLASS, MEASURE_NAME, MIN_REQUIREMENT, SRVC_FROM_DATE, DISPLAY_CODE, NUM_TO_DISPLAY, AGE_MONTHS
FROM DM101.QPHS_FILLER

 

[lbass]

Okay, I guess that didn't really help. When you clicked okay to the command, did you enter a value for the parm just so the command could compile? Is the parameter a string in the main report as well and with the same format? Did you check the dropdown in the linking screen? When you open the command to edit, do you still see the parameter in the list on the right of the screen?

-LB

 

[lynchg]

lbass,

I did not originally enter a default value for the parameter and so the parameter didn't "stick" in either the command window or the field explorer. I went back into the command window, entered a default value, saved it, saw that the parameter now appeared in the field explorer along side the other that I had created. I deleted the first field explorer parameter, went back into the command window, got rid of the default value (which was forcing the reports for all people to display the same data), relinked the real parameter to the main report and it works great.

I had always avoided using parameters in commands because I didn't relize that you initially had to enter a default value to get the command to compile.

Thanks a lot.

 

[lbass]

I never enter a default value in the parameter set up within the command. I just enter a valid value when I say "OK" after finishing the query so that it compiles, and then the parameter appears in the field explorer. Oh well, glad you got it to work.

-LB