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.
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.