| Author |
Message |
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
   Topic: Adding command bloats report
Posted: 23 Feb 2015 at 11:43pm |
|
Hi,
I have a 6 tables all linked and the report works fine, TableA contains a Jobs1 field. When I add an additional table TableB which also has a Jobs1 field it bloats the report from 387 records up into the thousands.
How do I link the two tables so that I only get the Jobs results from TableB which are in TableA?
Thanks :)
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Feb 2015 at 3:28am |
|
is it a one to one relationship between A and B?
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 24 Feb 2015 at 3:30am |
|
Thanks for the reply - I think its one to many. For a bit of a newbie how do I confirm?
Looking at the tables TableA contains 1 job record per job where as TableB contains many job records per job.
Edited by shabbaranks - 24 Feb 2015 at 3:39am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Feb 2015 at 6:09am |
it is a 1 to many then.
you might be able to deal with it by
a sub query in place of table B
a join condition on table B
updating the WHERE on the command to limit the data
It really depends on why you are joining in table B, what the content in table B is and id that content can be limited in scope before it is joined.
|
|
IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 25 Feb 2015 at 5:43am |
|
Like DBlank said, the reason you go from 387 to thousands is because you are returning a record for every instance where the jobs1 field is returned from table A and every instance where the jobs1 field is returned from table B.
You can confirm this by looking at the data. When you drop the fields you want to see from both table A and table B in the report, do you see multiple records where the same jobs1 field is listed?
If you simply want to reduce the number of records you are viewing, group on the jobs1 field (or whatever the unique ID field is). If you won't be missing data that is important, you can then place your desired information in the group header and suppress the details section.
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 25 Feb 2015 at 10:46pm |
|
Thanks guys Im almost there with this one. Ive got my results down from 3000 to 1410 but this is using a select expert.
Im now trying to extract a field called notes which relates to a job. Not all jobs have notes so in order to filter out the ones which don't have notes I have created a formula:
if isnull({Transaction_Detail.Notes}) then true
ELSE IF ({Transaction_Detail.Notes}) = " " then True
Else False
I then create a filter on this formula to only show false items. What Im trying to do now is link these two reports (not sure what the best way to go about it is - command or other?) and then on my original report show which notes correspond to the relevant jobs.
Apologies if Im not being very clear Im new to crystal reporting :)
|
|
IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 26 Feb 2015 at 2:39am |
|
What is the objective of separating the jobs that do have notes from the jobs that do not have notes?
If you simply want the jobs with notes grouped together and the jobs without notes grouped together, you can add a group to the report based on your formula field.
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 26 Feb 2015 at 2:45am |
|
Thanks for the reply, the reason for separating the jobs with notes against the ones with out the notes is because I am trying to limit the amount of duplicates (which kind of links to my original question).
My report as it stands shows the expected number of results. If I try and incorporate the notes from a separate table which links to the job I get thousands of results back (duplicates) which is what I am trying to avoid.
Thanks
|
|
IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 26 Feb 2015 at 3:46am |
|
If you group your data based on job#, it should help remove duplicate records. You may want to play with your join type to see if that helps. It could be you need a left or right outer join instead of an inner join.
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 26 Feb 2015 at 3:51am |
|
Unfortunately it doesn't seem to help, I go from 386 records to 11000 and this is after Ive had a play with the joins to see if it resolves the issue :(
|
|
IP Logged |
|
|
|
