I am using CR v8.5 and have limited experience with it. Here is what I trying to accomplish:
The SQL table structure:
Employid EndDate PayCode Amount
12345 03/17/2006 2 100.00
12345 03/17/2006 3 50.00
67890 03/17/2006 2 30.00
The report output structure I'm after is the following
Employid EndDate PayCode1 Amount1 PayCode2 Amount2
12345 03/17/2006 E 100.00 M 50.00
67890 03/17/2006 E 30.00 M 0.00
Since not all of the employee may have a PayCode '3' SQL row, I want to print 0.00 in the Amount2 column and conversly, there may not be a PayCode '2', so I want to print '0.00' in the Amount 1 column. The problem lies in trying to get '3' PayCodes and its Amount on the same line as the '2's. I have tried creating a report for the 2's and a subreport for the 3's and then attempted to link the subreport using Employid and EndDate fields. A 3rd linked field is a variable created in the PayCode2 column to reference the Amount on the subreport.
Nothing that I have tried has worked. What am I missing with this strategy or is there an easier way? Thanks.
The SQL table structure:
Employid EndDate PayCode Amount
12345 03/17/2006 2 100.00
12345 03/17/2006 3 50.00
67890 03/17/2006 2 30.00
The report output structure I'm after is the following
Employid EndDate PayCode1 Amount1 PayCode2 Amount2
12345 03/17/2006 E 100.00 M 50.00
67890 03/17/2006 E 30.00 M 0.00
Since not all of the employee may have a PayCode '3' SQL row, I want to print 0.00 in the Amount2 column and conversly, there may not be a PayCode '2', so I want to print '0.00' in the Amount 1 column. The problem lies in trying to get '3' PayCodes and its Amount on the same line as the '2's. I have tried creating a report for the 2's and a subreport for the 3's and then attempted to link the subreport using Employid and EndDate fields. A 3rd linked field is a variable created in the PayCode2 column to reference the Amount on the subreport.
Nothing that I have tried has worked. What am I missing with this strategy or is there an easier way? Thanks.