Join two reports data

[dasiga222]

Hi

I am using Crystal Reports 10

I am having two reports
The two reports are got from two different tables

Each employee is given two types of tests
The two tests results are stored in two different tables
emp_Test1 and emp_test2

The employee takes the test1 2 times and takes the test2 5 times

The table structure of emp_test1 is

Empno Test_marks
---------------------

01 10
01 15

02 20
02 20

03 15
03 20



The structure is similar for Emp_test2 table

Empno Test_marks
---------------------

01 14
01 12
01 14
01 10
01 10


02 10
02 15
02 20
02 25
02 11

03 10
03 18
03 17
03 15
03 15


The first report emp_test1 will display the result of Test1

I grouped the results of test1 by empno and got the result

Same is the case with the emp_test2 report
I got it from emp_test2 table
I grouped all the marks for a given employee by empno
The grand total is shown in the group footer grouped by empno

So I have two reports with two different test results
My objective is to link emp_test1 report to emp_test2
so that I can add the cumulative marks of Test1 to test2
for a given employee

For example if the grand total of emp_no 01 for test1 is
25 and grand total of emp_no 01 for test2 is 60... ( as shown above)

then the total marks the employee got is 25 + 60 = 85

How can I make this happen
I am taking emp_test2 as a sub report to emp_test1..but I am unable to pass on values

Actually both the tables contain hundreds of employee records..but for this question.. I am showing only 3 employees .

The total number of employees in both tables are same..but the records vary in ratio of 2 : 5 as test1 is taken 2 times
and test2 is taken 5 times

Could some one please suggest the remedy
Can I pass string of values from report2 to report1

Thanks

 

[lbass]

Group on {emp_test1.empno}, and right click on and insert a summary (sum) on {emp_test1.test_marks}. Insert a subreport which uses the emp_test2 table. Repeat the steps you did in the main report, and then create a formula {@test2}:

whileprintingrecords;
shared numbervar test2tot := sum({emp_test2.test_marks},{emp_test2.empno});

Place this on the subreport somewhere. Insert the subreport in the group (empno) header_a of the main report, linking the subreport to the main report on the empno field. In group header_b (insert this section), add the following formula:

whileprintingrecords;
shared numbervar test2tot;

test2tot + sum({emp_test1.test_marks},{emp_test2.empno})

This formula should display the total correctly for each employee.

-LB

 

[dasiga222]

Thanks for your response

There are hundreds of records in the report2 which I am passsing as a sub report

Placing this sub report in the main report is causing this subreport to be repeated in every change in group value
So it is consuming hundreds of pages

Also , evne though I am using the formula

whileprintingrecords;
shared numbervar test2tot;

test2tot + sum({emp_test1.test_marks},{emp_test2.empno})

Only the last value of the test_marks in the sub report is being passed on. All the previous values are getting overwritten by the last value
Can you please suggest

Thanks

 

[GJParker]

Make suer you have linked the subreports by the empno, this way each time the subreport is run it should only return test2 marks for the indivividual employee.

Edit menu -> subreport

in the 'links from the available fields' box select emp_test1.empno and insert this in the 'fields to link to' box

in the 'subreport parameter field to use ' dropdown select ?Pm-emp_test1.empno

check the 'select data in subreport field' box and select empno from the drop down box.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

I think Gary's right, that you haven't linked the subreport on the emp_no yet. Also, you can suppress all sections within the subreport if you don't need to display the test2 results, but only want to use the results in the calculation in the main report. Or, you can display just the group sum for test2, by suppressing all but the group footer in the subreport.

-LB