| Author |
Message |
fuzzy ninja
Newbie
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 9
|
   Topic: Help with mulit table report
Posted: 05 Sep 2012 at 12:04pm |
Good day,
Please forgive me as I am pretty new to Crystal.
I am trying to build a report that has two parameters and searches two different tables and returns all data that matches the parameters .
May tables are:
Table1.Name - Contains Client names
Table1.Date - Date record created
Table2.Name - Contains supervisor names
My Parameters are:
Name
Date Start
Date End
My goal is to have to report prompt for date and name. I would like it to search the Table1.Name and then Table2.Name with the same parameter and return all records. Currently we have two separate reports one to search the Table1.name and one to search the table2.name. I am trying to combine it all into one report.
I have tried to build it with:
{ TABLE1.DATE} in {?DATE START} to {?DATE END} and { TABLE1.NAME} startswith {?NAME} or { TABLE2.NAME} startswith {?NAME}
This returns all the records in table1 and then stops. It dosnt check table2.
Is what I am wanting even possible?
Andrew
|
 IP Logged |
|
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 05 Sep 2012 at 12:24pm |
|
I dont see the relation between the two tables, if there isnt one, use a sql command and union all.
select T1.Name T1.Date from Table1 T1 where
{T1.date}between CDate(?from) and CDate(?to) and
T1.Name like {?Name*}
union all
select T2.Name T2.Date from Table2 T2 where
{T2date} between CDate(?From) and CDate(?To) and
T2.Name like {?Name*}
|
|
IP Logged |
|
fuzzy ninja
Newbie
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 05 Sep 2012 at 1:53pm |
|
I am not sure if this is what you meant by relation:
From the database expert I have my tables listed and then from the link tab I did a Left outer join not enforced from Table1.Order# to Table2.Order#
Table 1 is the 1 and the Table2 is the many. Table 1 contains one order (date, time, cost..) per order number and table two contains multiple names of the employees associated with each order number.
|
|
IP Logged |
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 06 Sep 2012 at 9:34pm |
Hi To check this we need to verify the SQL which was written by Crystal. Refresh your report and go in DAtabase Menu--Show SQL Query .. Now copy this query and execute at your database end and see how many records you are getting.
|
|
Thanks,
Sastry
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 07 Sep 2012 at 4:16am |
When using "OR" you need to group the conditions appropriately in order for things to work correctly. So, I would change the selection criteria to this: { TABLE1.DATE} in {?DATE START} to {?DATE END} and ({ TABLE1.NAME} startswith {?NAME} or { TABLE2.NAME} startswith {?NAME} ) -Dell
|
|
|
|
IP Logged |
|
fuzzy ninja
Newbie
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 07 Sep 2012 at 4:51am |
Dell,
I was thinking that originally and just tried it again. It still dosnt work. Crystal returns the records contained in the first table but dosnt return anything from the second table. I know of at least one record in the second table that should be returned as well.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 07 Sep 2012 at 4:56am |
What are your joins from Table 1 to Table 2? -Dell
|
|
|
|
IP Logged |
|
fuzzy ninja
Newbie
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 07 Sep 2012 at 5:01am |
Table1.Order --> Table2.Ordernum
Join type - Left outer join, Enforce join - not enforeced, link type - =
Table 1 is the one and table 2 is the many. Only 1 name is listed for each order in table 1 but several names are listed for each order in table 2
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 07 Sep 2012 at 5:51am |
I would delete the join, save your report, then add the join back - make it left outer and do NOT change any of the "enforce" options (in @17 years of working with Crystal, I've NEVER had to make changes the default join enforcement to get things to work!) What happens when you put just the date filter in your report? Do you get all of the data you expect? -Dell
|
|
|
|
IP Logged |
|
fuzzy ninja
Newbie
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 07 Sep 2012 at 5:56am |
Still no go. :-(
What do you mean by your last comment?
"What happens when you put just the date filter in your report? Do you get all of the data you expect?"
I have a feeling I am over my head with this one HAHA
|
|
IP Logged |
|
|
|
