Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

Crystal Report...

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
bhavesh55
Asked:
bhavesh55
  • 6
  • 4
  • 3
  • +2
1 Solution
 
ndbCommented:
Via the Visual Linking expert.
Just drag the Id_no field from the first table to the other.
0
 
bhavesh55Author Commented:
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
 
wileecoyCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ndbCommented:
Might sound stupid but.
You should change the direction of the arrow in CR linking expert.
0
 
AshokKumarCommented:
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
 
scrumper64Commented:
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
 
bhavesh55Author Commented:
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
 
bhavesh55Author Commented:
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
 
scrumper64Commented:
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
 
bhavesh55Author Commented:
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
 
bhavesh55Author Commented:
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
 
scrumper64Commented:
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
 
bhavesh55Author Commented:
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
 
AshokKumarCommented:
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
 
AshokKumarCommented:
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
 
AshokKumarCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now