Is subreporting the only option?

[nikhil27]

Hi,

I have two tables containg the data like this:

Table1:

EMP_ID MONTH BONUS
1 July 200
1 July 100
1 Aug 300
2 May 300
3 July 400
3 Dec 900

Table2:

EMP_ID Month Comments
1 July Happy
1 July OK
1 Aug Who Cares
2 May Satisfied

Now I have to display the data like this

EMP_ID MONTH BONUS COMMENTS
1 July 300
Happy
OK
1 Aug 300
Who Cares
2 May 300
Satisfied
3 July 400
3 Dec 900

A join on these two tables wouldn't produce the proper results beacuse I have to do a sum on 'BONUS' field. Is subreport the only option to achieve this?

Any pointers regarding this will be of great help.

Thanks,
Nikhil

 

[lbass]

A subreport makes sense to avoid duplicate records. Insert a group on employee, a group on month, insert a summary on the bonus field and drag it to the month group header, and then insert the subreport in the group footer for month, linking the subreport to the main report on the month field.

-LB

 

[nikhil27]

Thanks lbass!!

But isn't subreports a bit slow..performace wise.

Can't I write some formula to suppress the duplicate records.

Can we do this using some formula? You are so good at writing the formulas

Thnaks,
Nikhil

 

[nikhil27]

Thanks lbass!!

But isn't subreports a bit slow..performace wise.

Can't I write some formula to suppress the duplicate records.

Can we do this using some formula? You are so good at writing the formulas

Thanks,
Nikhil

 

[Madawc]

If I've understood you correctly, you can't sum Bonus because it would sum it four times; each instance of Table 1 linked to each of Table 2.

A running total might do the job. One option is to evaluate for each change of field, asuming Table 1 has a unique field you can use.

Alternatively, if Table 2 BONUS COMMENTS are unique, you could count them for each group and then divide the sum by the count. Or sort and not add to the running total if BONUS COMMENTS = Previous(BONUS COMMENTS)

Incidentally, it doesn't seem very good database design. I suppose you have no control over that.

Madawc Williams (East Anglia, UK)

 

[fredp1]

Another approach is to have a SQL Expression for Table 1.

All you'll need is table2 in the report and create a SQL Expression like
(
Select Sum(Bonus)
from Table1 T1
where table2alias.Emp_Id=t1.emp_id
and table2alias.Month=T1.Month
)

Have your EMpId and Months as groups, then just add the sql expression and Comments to the details.

I agree with Madac about the db design... the month column should be a date field otherwise you will run into problems when you roll into another year.

Cheers
Fred