CR 8.5 Joining tables with no Join

[yamyam]

Hi,

I am trying to join to tables with no key.

The tables are employees and bonus.

employees

Surname Start Date
smith 01/09/1998
jones 09/11/2010
brown 08/08/2009

bonus
01/01/1970 0.1
01/01/2009 0.075
01/04/2010 0.05

based on the start date I wish to give the employees a bonus based on the percentage. The bonus rate if effective if the startdate falls between the bonus dates.

Any ideas how this can be achieved in CR 8.5.


Thanks

 

[kray4660]

I don't think you would join the tables (CR would complain), but I think in this case you would be okay.

If the values in the bonus table are static (i.e, fixed for all time to come), then I would just use those values in whatever formula you need instead on pulling the values from the table.

I hope this helps.

 

[yamyam]

Hi Kray4660,

I used to have the values in a formula but they now want to tweak the values to get to the amount they want to pay.

This is the reason the table was introduced. Thanks for your ideas, I have just found an old 8.5 manual, I am looking at the options. I know I can do it using a SQL command in later versions but not 8.5.

 

[kray4660]

Well you can sorta modify the SQL in 8.5, but you have to be careful. I still think you can keep the tables unjoined and just ignore the warning message.

 

[lbass]

Are these the ONLY fields in the new table: date, bonus rate? The problem is that you have to compare against sequential records if there is only one date field.

-LB

 

[lbass]

Actually, I think you can use a selection formula like this:

{employee.startdate} > {bonus.date}

Then use a formula like this to determine the correct bonus rate per employee, assuming a group on employee:

if {bonus.date} = maximum({bonus.date},{employee.employee}) then
{bonus.rate}

-LB

 

[kenhamady]

It sounds like you have control of the structure of the bonus table. If that is true then this would be much cleaner if each bonus record had both a start and a stop date. Then your selection formula could identify the exact bonus record for each employee like this:

{employee.startdate} in {bonus.startdate} to {bonus.enddate}

Also note that the visual linking expert allows unequal joins. So you can join the employee date to the bonus date using a >= or <= join.

And in version 8.5 you can also go into the Show SQL window and add join conditions that can't be defined through the visual linking window.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/