multiple datasource in crystal reports

[Former Member]

Hi There,

I dont have much experience with crystal reports so please bear with me until I get this issue resolved.

I have been given a task to create a report with following table from different datasource (ODBC). It is a pervasive database.

Datasource1

Tables are:

PODetail - JobNo, PONo, Desc, Client

POHeader - PONo, City, Address

Datasource2

Tables are:

Job-Master - JobNo, PM

I have added the tables in crystal report and created the links between tables. The problem is that JobNo in tables Job-Master and PODetail is in different format. For example:

PODetail.JobNo is 1-100, 1-101, 2-102

Job-Master.JobNo is 100, 101, 102

Now, the good thing is what really matter is the number after "-" (dash) and thats the job number. Anything before dash is for state. Every job number is unique and that is what I have been told.

Anyway, I can use mid function to sort the jobno issue. So I tried that by adding following fields but no records and in fact whole report just hangs.

I chose fields PODetail.PONo, Job-Master.PM and new formula field created by using Mid (Job-Master.JobNo,3)

Can someone please suggest me what I am doing wrong here?

Thanks

Angad

[Former Member · Accepted solution]

I must change the title of my question to following:

Creating join between two tables and each table from different datasource. and

Datasource1

Tables are:

PODetail - JobNo, PONo, Desc, Client

POHeader - PONo, City, Address

Datasource2

Tables are:

Job-Master - JobNo, PM

I have added the tables in crystal report and created the links between tables. The problem is that JobNo in tables Job-Master and PODetail is in different format. For example:

PODetail.JobNo is 1-100, 1-101, 2-102

Job-Master.JobNo is 100, 101, 102

[abhilash_kumar · Accepted solution]

Hi Angad,

1) Add the PODetail table as the datasource for the Main Report.

2) Create a formula with this code:

     tonumber(mid({PODetail.JobNo},instr({PODetail.JobNo},"-")+1,len({PODetail.JobNo})));

3) Insert a Subreport with Job-Master table as its datasource. Place it on the Details Section.

4) Right-click the Subreport > Change Subreport Links > Move the formula we create above to the "Fields to Link to" area

5) At the bottom where it says "Subreport Fields to use", choose the JobNo field.

This essentially links both the JobNos. together.

Let me know how this goes!

-Abhilash

[Former Member · Accepted solution]

That really went well.

That is what I was looking for. Though, I still don't have data from corresponding table but at least I am getting there. Now, I know I am doing something wrong so I will try to work it out.

[abhilash_kumar]

Hi Angad,

If you've linked on the JobNo field then it won't work. Although their datatypes might match, however their contents do not.

Either create a command object that strips out everything before the dash and then join on the JoBNo or use a Subreport. The Subreport will connect to one of the datasources and you need to create a report level formula that will strip out the JobNo for you which can then be linked to the other datasource's JobNo.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Hi Abhilash,

You have valid reponse. Even, I also found similar answers when I searched for it. The problem is that I am not developer so I don't think command object is option for me. Now, with sub reports there is no particular example that can guide me through this report.

I have found a PDF version of book on crystal reports XI so I will look into it. If you have any example available online then please share it with me.

Cheers

Angad

[Former Member]

One more thing, I want to do following

Show PODetail.JobNo

Show PODetail.PONumber

Show Job-Master.PM (this must be corresponding to PODetail.JobNo)

[Rafaee_Mustafa]

Hi Angad,

u can try right (Job-Master.JobNo,3) in your query

[Former Member]

This function may only help once I figure out extracting records.

Thanks for your help.