Crystal Report Link tables

Hello,

I am creating a crystal report and am running into challenges in that my unique ID for both tables is not exactly the same. Unfortunately,  the customer number is preceded by 00 in the other table. The ID's are the same outside of that and there are always 2 zeros.

Table1
ABCDEF

Table2
00ABCDEF

Is there any way to strip the two zeros prior to Crystal making the link?
tkwadminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You can use the SQL Query to modify the linking. I recommend you follow these steps:
1. Create the links using the "Visual Linking Expert" using the IDs from both tables (do not worry that they do not join correctly at this stage)
2. Add fields to the report from both tables
3. Open the "Show SQL Query" dialog. In the dialog you would see a query similar to one below:
SELECT
Table1.ID, Table1.Name, Table2.Desc
FROM
{ oj Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID}

Open in new window

Now, you can modify the joining condition to use an expression for the joins.
The easiest solution will be to append '00' in front of the Table1.ID, like this:
SELECT
Table1.ID, Table1.Name, Table2.Desc
FROM
{ oj Table1 INNER JOIN Table2 ON {fn CONCAT('00', Table1.ID)} = Table2.ID}

Open in new window

In the query above I have used the ODBC CONCAT function. If you use a different driver you can use the native syntax, like this one for Oracle:
SELECT
Table1.ID, Table1.Name, Table2.Desc
FROM
 Table1 INNER JOIN Table2 ON '00' || Table1.ID = Table2.ID

Open in new window

Or this one for SQL Server:
SELECT
Table1.ID, Table1.Name, Table2.Desc
FROM
 Table1 INNER JOIN Table2 ON '00' + Table1.ID = Table2.ID

Open in new window

James0628Commented:
In my experience, you can't modify what you see in "Show SQL Query", but I guess some versions of CR might allow that, so it might depend on which version you have.

 One other option would be to simply include the tables, with no link (assuming that the field you mentioned is the only linking field), and then use something like this in the record selection formula:

"00" + {Table1.ID field} = {Table2.ID field}

 You could remove the "00" from the Table2 field, but adding "00" to the Table1 field, as chaau suggested, does seem a bit easier.

 I think using the record selection formula like that to create the link will work, but it may be very inefficient.  CR may end up pulling all of the records from both tables, and then trying to match them.  I'm really not sure.  If you don't have too many records in those tables, that may not be a problem.  Otherwise, you would be much better off creating a query that would run on the server, as chaau suggested.

 If you can't modify what you see in "Show SQL Query", you can open the connection to the db in the Database Expert and see if there is an "Add Command" option.  If so, you can use that to enter the query in CR.  You could add the tables to a report and link them, as chaau suggested, then copy what you see in "Show SQL Query" and paste that into the Command window, and modify it.  If you need any parameters, create those in the Command editor too (CR doesn't seem to like using parameters that were created outside of the Command editor in a Command).

 Another option would be to just create the query in the db (eg. a view or stored procedure), and use that as the datasource for the report.

 James
mlmccCommented:
There were versions where you could change the query.  I believe CR8 was the last one that allowed it.

You can use a COMMAND as James suggests however it is difficult to convert a report to a command so you probably need to build it yourself.

If you want Crystal to build it just drop all the fields the report needs onto the report somewhere.  Don't try to do any fancy formatting.

Run the query and copy as suggested.
Create a new report and select COMMAND as the source and paste the SQL into the COMMAND screen.


mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tkwadminAuthor Commented:
Thanks everyone for the suggestions. I will be trying this out today and will update.
habibbCommented:
Of course you have 2 places to change/convert data:

1- At database end
2- At Reporting end

You have to see possibilities where you can change easily. In my opinion its easy to change at reporting end using formula but this way normally use to display or do some calculation. But if you can not change at crystal report end then second option is at database end. At database end you can either change your SQL query which is making/preparing the table or if its not possible then you can create a view on that table (By removing leading 00 in SQL query for view) and then design/fetch data at crystal report end using this view.
I hope you understand the story.

Thanks
Habibb
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.