Table link via formula 2

[southbean]

Hello All,

I need to create a Cross-Tab report using two tables (SQL Server 2K) where – for whatever reason – the PK field in tbl1 is an integer and the FK in tbl2 is a varchar. Therefore, I can not make a physical link in Crystal.

The only way I thought I could get around this problem was to use a formula to simulate the link.

 {tbl1.ID} = ToNumber({tbl2.ID})

I have no other way to verify the data so my question is this: Does this work? (it seems to) and is there a better way to accomplish this? Would a SQL expression be better?

I‘m using CR 10.

Thanks for any/all advice!

- Tom

 

[Turkbear]

Hi,
Even better would be to create a view in your database that does the join and use that for your report.

Your method, while it will probably work, will likely return ALL the records from both tables and then check the criteria..With big tables this could be very slow.

Using a command object that is built from the query you want is another option.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

It should work.

More efficient would be to create an Add Command and paste in the SQL wherein you use a CAST on the varchar to an integer.

After summararily disposing of your dba's body in the neighbor comnpanies dumpster, create a View on the database to be used in lieu of the table with the varchar, whereing the view is a

select *, cast(table.field as integer) newfieldalias from table

Now you'll have the original field available for instances where you need it, and better yet a correctly typed converted field for linking, etc.

-k

 

[southbean]

Excellent! Thanks you. Both good suggestions. I will see if I can create a View on the database using the CAST option.

Thanks again,

- Tom