Help with mulit table report
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17502
Printed Date: 03 May 2024 at 2:01pm
Topic: Help with mulit table report
Posted By: fuzzy ninja
Subject: Help with mulit table report
Date 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:
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} Is what I am wanting even possible?
I appreciate any help
Andrew |
Replies:
Posted By: comatt1
Date 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*} |
Posted By: fuzzy ninja
Date 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. |
Posted By: Sastry
Date 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 |
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: fuzzy ninja
Date 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.
|
Posted By: hilfy
Date Posted: 07 Sep 2012 at 4:56am
|
What are your joins from Table 1 to Table 2? -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: fuzzy ninja
Date 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
|
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: fuzzy ninja
Date 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
|
Posted By: hilfy
Date Posted: 07 Sep 2012 at 6:18am
|
Set the selection criteria to just this: {TABLE1.DATE} in {?DATE START} to {?DATE END} Don't worry about the name part of the filter yet. -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: fuzzy ninja
Date Posted: 07 Sep 2012 at 6:23am
| when I do that I get every record within that time frame from table1 only. |
Posted By: hilfy
Date Posted: 07 Sep 2012 at 6:39am
|
Are you displaying any Table2 data on the report? If not, put a field or two on there for testing purposes. After that, if you're just seeing the data from Table1 then there is something wrong with your join from Table1 to Table2. If you can read SQL, I would then go to the Database menu and select "Show Query" to see the SQL that Crystal generates and try to find where the issue is. -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: fuzzy ninja
Date Posted: 07 Sep 2012 at 6:49am
| I added some table 2 fields as well. I get data from both. |
Posted By: fuzzy ninja
Date Posted: 07 Sep 2012 at 7:13am
|
I am having a smiler problem with a different report. The second report only uses table1 which has two phone number fields. Table1.Phone1 and Table1.Phone2 I am trying to do an or search so it returns all records regardless what phone field the phone number was entered into. ({TABLE1.PHONE1} = {?Phone Number} OR {TABLE1.PHONE2}= {?Phone Number}) The only records I get are ones that have the phone number in the phone1 field. It appears that it searches and finds the records that are in the phone1 field but dosnt search phone2. If I comment out the first line I get the records in phone2. If I comment out the second line I get the records in Phone1. |
Posted By: hilfy
Date Posted: 07 Sep 2012 at 7:51am
|
I'm beginning to suspect that the problems with both reports are due to null values in the fields.For the phone issue, try this: ( (not IsNull({TABLE1.PHONE1}) and {TABLE1.PHONE1} = {?Phone Number}) or (not IsNull({TABLE1.PHONE2}) and {TABLE1.PHONE2} = {?Phone Number}) ) For your other report, try this filter: {TABLE1.DATE} in {?DATE START} to {?DATE END} and ( ) -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: fuzzy ninja
Date Posted: 07 Sep 2012 at 8:07am
 YOU ARE A GENIUS! That worked perfectly! THANK YOU! THANK YOU! THANK YOU! |