Solved

Crystal Report...

Posted on 2000-04-09
16
322 Views
Last Modified: 2012-08-14
I want to retrieve data from more than one table in crystal report.How can i do that ?Both table related to each other through "Id_no".

I am eagerly waiting for your reply.Please soon reply me soon.

Thanks,
bhavesh
0
Comment
Question by:bhavesh55
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 1

Expert Comment

by:ndb
Comment Utility
Via the Visual Linking expert.
Just drag the Id_no field from the first table to the other.
0
 

Author Comment

by:bhavesh55
Comment Utility
ndb,
Both of this tables are already linked through "Id_no" field.I mean it already shows linking between them when i try to look from the visual linking expert.

But i have a problem.Like i have 2 tables:
1) Subcase
2) Contact

I put some of the fields from only "subcase" table than it looks ok.I mean it retrieves appropriate values.

But than after i try to put "contact" table's field in design mode and try to take a look for "preview" of that report than it doesn't show me anything.

So please let me know if you have any idea.

I am eagerly waiting for your reply.
Thanks,
bhavesh
0
 
LVL 4

Expert Comment

by:wileecoy
Comment Utility
bhavesh55,

Are you using the Select Expert? Or does your report have to meet certain criteria?

If so, make sure that the criteria can be met.  It is easy to set up the criteria so that no records match - and thus, your report is blank.

If you do have criteria, try leaving fields from both tables and then removing the criteria to see if you get all the records.  Then, as they work, add back the criteria one-by-one.
0
 
LVL 1

Expert Comment

by:ndb
Comment Utility
Might sound stupid but.
You should change the direction of the arrow in CR linking expert.
0
 

Expert Comment

by:AshokKumar
Comment Utility
Go to the visual linking expert. U'll get the view of tow tables. Just Click or the Id_No field of Subcase table and drag and drop at Id_No field of Contact table. A link will be created and then, right click on the link. U'll get some options like left outer join, right outer join, equal join etc. Based upon your requirement choose the option. In your case i think u have to click on the Equal Join option and then clikc OK.

Now REFRESH the report. It should do the trick.

Get back to me
0
 

Accepted Solution

by:
scrumper64 earned 100 total points
Comment Utility
I've had trouble with Crystal Reports where if I create a report with many tables and one of those tables is not on the report, the table is lost from the SQL.
The problem can be fixed by editing the SQL directly in Crystal (i.e. not using the Visual Linker.
To avoid this: My advice is to make sure a field from each table is put on the report when you create it.  Any fields you don't want can be made invisible.
When I write Crystal Reports I generally use Stored Procedures, there are no problems them.

** BEER, SEX AND BRISTOL CITY **
0
 

Author Comment

by:bhavesh55
Comment Utility
Ashokkumar and other Experts,
I still have the same problem.

In Visual linking expert,it shows me link between 2 tables "subcase" and "contact" through field "objid".

When i insert "Database Fields" from "subcase" table than it shows me all records in the preview of that report perfectly.

But if i insert any field of "contact" table than after i try to show preview of that report and it doesn't show me anything.

Let me know how do i solve this ?

One more thing is that how do i use relationship in crystal.Like there is "x" relationship name between "subcase and case" table and there is "y" relationship name between "case and contact" table.so how do i reach from "subcase to contact" through that relationship name in crystal.

In short, let me know how do i retrieve records into crystal report from 2 different table using "objid"(one common field in both the tables through which they relate to each other).

I am eagerly waiting for your reply.

Thanks for your time in advance.
Thanks,
bhavesh

0
 

Author Comment

by:bhavesh55
Comment Utility
scrumper64 ,

You told me to write sequel statement in crystal.How do i wrote below statement in crystal and where.Please let me know.

SQL Statement:

Select first_name
from contact
Where subcase.objid = contact.objid

Thanks for your time in advance.

Thanks,
bhavesh55
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Expert Comment

by:scrumper64
Comment Utility
SQL should be:

Select first_name, *field*
from contact, subcase
Where subcase.objid = contact.objid

make sure you have fields from both tables after the select, i.e. if first_name is from subcase then *field* should be from contract.

If the link between subcase and contract is optional then try:

Select first_name, *field*
from contact
left outer join subcase on subcase.objid = contact.objid


0
 

Author Comment

by:bhavesh55
Comment Utility
scrumper64 ,

You told me to write sequel statement in crystal.How do i wrote below statement in crystal and where.Please let me know.

SQL Statement:

Select first_name
from contact
Where subcase.objid = contact.objid

Thanks for your time in advance.

Thanks,
bhavesh55
0
 

Author Comment

by:bhavesh55
Comment Utility
scrumper64 ,
When i wrote your 2nd sequel statement
in "sql expression Editor" than it gives me

"General Sql Server error:....."

Please Let me know how do i handle it step by step ?

Thanks,
bhavesh55
0
 

Expert Comment

by:scrumper64
Comment Utility
Maybe you're using a crappy database like Access, I don't think it likes outer joins.
It's probably best to start with a new report and make sure you display fields from both tables as I said in the Proposed Answer.
0
 

Author Comment

by:bhavesh55
Comment Utility
scrumper64
It already displays me value when i right click(In right click menu, i choose Browse field Data) on it after puting it into the report's detail section.But it didn't show me at the time of previewing the report.


So let me know if you have any idea.

Thanks,
bhavesh55

0
 

Expert Comment

by:AshokKumar
Comment Utility
bhavesh55:

If you r using Sql Server then do this:
Create a View

Create View <ViewName> As

Select <Select Field Lists>

From SubCase A, Contacts B Where
A.ObjId = B.ObjId

Now, instead of calling two tables call this view alone. Your problem will be over. If using Access create the same above SQL statement as .Sql File and call it in the report. It should solve your problem. Even if it doesn't solve your problem, then send me your email id.
0
 

Expert Comment

by:AshokKumar
Comment Utility
bhavesh55:

If you r using Sql Server then do this:
Create a View

Create View <ViewName> As

Select <Select Field Lists>

From SubCase A, Contacts B Where
A.ObjId = B.ObjId

Now, instead of calling two tables call this view alone. Your problem will be over. If using Access create the same above SQL statement as .Sql File and call it in the report. It should solve your problem. Even if it doesn't solve your problem, then send me your email id.
0
 

Expert Comment

by:AshokKumar
Comment Utility
bhavesh55:

If you r using Sql Server then do this:
Create a View

Create View <ViewName> As

Select <Select Field Lists>

From SubCase A, Contacts B Where
A.ObjId = B.ObjId

Now, instead of calling two tables call this view alone. Your problem will be over. If using Access create the same above SQL statement as .Sql File and call it in the report. It should solve your problem. Even if it doesn't solve your problem, then send me your email id.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now