Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Adding command bloats report Post Reply Post New Topic
<< Prev Page  of 2
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Feb 2015 at 4:33am
what data exactly do you need from the 'notes' table?
IP IP Logged
shabbaranks
Groupie
Groupie


Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
Quote shabbaranks Replybullet Posted: 27 Feb 2015 at 12:47am
Its not the notes table, there is a notes field within a table which also has a jobs field - some jobs have notes. This table links to another table (1 to many) and I am trying to pull all the jobs from TableA and any notes from TableB which relate to the jobs on TableA.

When I link the two Im getting thousands of results because of the one to many link and Im trying to reduce the output to only show results with notes.

Thanks
IP IP Logged
adavis
Senior Member
Senior Member


Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
Quote adavis Replybullet Posted: 27 Feb 2015 at 2:15am
Try changing your join from inner join to left outer join. Be sure you place the notes field in the report.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2015 at 4:31am
So I need clarity- you keep indicating you are getting thousands of records that you don't want but you also seem to want all of fthe notes which seem to be exactly those thousands of records. I was assuming that you wanted to limit the scope to somehting like the most recetn not only which would keep your record set to the original number form table A.
However I am now wondering if you really do wnat all of theose extra thousands of records so you can see all of the notes but you only want the data from table A to disaply once and not keep repeating making it look like more than one record.
Which is the correct direction? Dispalying all of the notes or not?
IP IP Logged
shabbaranks
Groupie
Groupie


Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
Quote shabbaranks Replybullet Posted: 02 Mar 2015 at 12:29am
Hi, just to clarify - that's correct Im trying to get all jobs some of which contain notes and some don't. The duplicated results are coming up as soon as I link an additional table job-job. And yes this is exactly what Im trying to get

"you can see all of the notes but you only want the data from table A to display once and not keep repeating making it look like more than one record"




Edited by shabbaranks - 02 Mar 2015 at 12:32am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Mar 2015 at 3:59am
first if you only want records that match between the two tables make it an inner join, if you need records gfrom table A with no match to table B make it an outer join between the two tables.
 
as for 'removing the duplicates' you won't actually remocve them, you just hide the duplicate content. Removing them removes all the 'notes' too.
Option 1:
group on the primary key from table A (likely the TransactionData field)
place the records from Table A in the group header (dispalys once) and place the Notes from table B in the details (displays once per row of data)
or
place all fields onto deatail section and under the field properties select 'suppress if duplicated' -this will reset on the change of the group
Option 2:
Order by the primary key in table A
place all fields on the detail section
conditionally suppress all the data you want to by using a suppression formula
example:
next(tableA.primaryKeyfield)=tableA.primarykeyfield)
 
IP IP Logged
<< Prev Page  of 2
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.