Subreport problem

[greg52]

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.

 

[synapsevampire]

You on't need a subreport, and they should generally be avoided.

You need to pivot your data.

Given the format, a cross-tab doesn't exactly match the requirements, so group by the emp_id and then the date, and then create formulas in the details:

whileprintingrecords;
numbervar PC1;
if {table.paycode} = 1 then
PC1:=PC1+{table.amount};

whileprintingrecords;
numbervar PC2;
if {table.paycode} = 2 then
PC2:=PC2+{table.amount};

etc.

Then suppress the details and in the date group footer use:

whileprintingrecords;
numbervar PC1

This will display the sum of the PC1 values for that day. Create as many as you need for the amount of paycodes.

-k

 

[greg52]

That's it! Thank you!

-Greg