Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Separate groups Post Reply Post New Topic
Author Message
clutch69rs
Newbie
Newbie


Joined: 21 Jul 2017
Online Status: Offline
Posts: 3
Quote clutch69rs Replybullet Topic: Separate groups
    Posted: 24 Jul 2017 at 5:14am
Hi,
I have 2 sets of data to pull in, from 2 separate tables. The data in set 1 and set 2 is not related in any way.

Set 1: apples, oranges, bananas, grapes

Set 2: red, blue, yellow, green, purple

I have the above sets grouped separately in the group footer on fruit.table and color.table.

Is it possible to pull in the results for set 1 without affecting the results for set 2? That is, I'm seeing set 1 repeat when set 2 loads a result. Set 1 will repeat x number of times, with x being the number of results from set 2. I understand that I could do what I need from a subreport, but this particular report is already a subreport of a much larger report. Thanks in advance for any help or advice for this novice user :)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Jul 2017 at 5:41am
If possible consider using a union statement in a stored proc or crystal command to create one result set to use
IP IP Logged
clutch69rs
Newbie
Newbie


Joined: 21 Jul 2017
Online Status: Offline
Posts: 3
Quote clutch69rs Replybullet Posted: 24 Jul 2017 at 7:46am
Thanks, Dblank. I've tried a union statement and a crystal command; the results still repeat.
apple
red
apple
blue
apple
yellow
oranges
red
oranges
blue
oranges
yellow
...etc
..etc.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 24 Jul 2017 at 8:46am
since that data is unrelated, it sounds like you will get a cartesian product every time if you join them.

in a stored proc you could select all of one table into a temp table and then select the other table into the temp, that would eliminate the duplication.

I would think that 2 select statements unioned together like DBlank suggest should work in a similar fashion.

my two cents
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Jul 2017 at 9:12am
agreed with lockwelle
the two tables with cross join if you just pull them in with no join (this happens when you use one field from each table. The union command needs to replace the use of each separate table.
The union would be
select fruitname as ObjectName, 'Fruit' as ObjectType from fruittable
UNION
select colorname as ObjectName, 'Color' as ObjectType from colortable

that should give you one unified result that becomes your source

Edited by DBlank - 24 Jul 2017 at 9:13am
IP IP Logged
clutch69rs
Newbie
Newbie


Joined: 21 Jul 2017
Online Status: Offline
Posts: 3
Quote clutch69rs Replybullet Posted: 24 Jul 2017 at 9:19am
Thanks so much, lockwelle and DBlank. That did the trick.
IP IP Logged
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.016 seconds.