Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Assistance with multiple records in report Post Reply Post New Topic
Author Message
jennifer_falcon
Newbie
Newbie
Avatar

Joined: 15 Jan 2009
Online Status: Offline
Posts: 35
Quote jennifer_falcon Replybullet Topic: Assistance with multiple records in report
    Posted: 14 Sep 2011 at 9:23am
Good Day,
I have three tables. Probsummary, screlate, CM3R

Probsummary contains records called Incidents
CM3R contains records called Changes
screlate contains the relationships between the Incidents and Changes.

I have been asked to create a report based on the number of incidents opened for a particular group.

An incident has the unique identifier of probsummary.number
A change has the unique identifier of cm3r.number

An incident can be an incident without a change.
An incident can be an incident related to another incident.
An incident can be an incident related to a change
An incident can be related to more than one change.

Some examples:

Incident 1 has no changes related to it. Therefore, there is no relationship in the screlate table.
Incident 2 has an incident related to it. Therefore, there is a relationship in the screlate table.
Incident 3 has one change related to it. Therefore, there is a relationship in the screlate table.
Incident 4 has 2 changes related to it. Therefore, there are more than 1 relationship in the screlate table.

I want to create a report that will query the probsummary table for all Incidents created in the past month. In this report, I want to be able to list the changes related to the incident. I was hoping to do this without replicating the incident twice.

Example:

Incident 4 is related to Change 4 and Change 5.

The way my report looks now is

Number             OpenDate            CloseDate               Change #

Incident 4         01/01/11              02/01/11                 Change4
Incident 4         01/01/11              02/01/11                 Change5
Incident1          03/05/11              04/05/11                
Incident2          03/05/10              06/11/10                 

My intention is to print out the report so it can look more like this:

Number           OpenDate            CloseDate    Change #  Additional Change

Incident 4       01/01/11              02/01/11      Change4   Change 5
Incident1        03/05/11              04/05/11                
Incident2        03/05/10              06/11/10   

There may also be some cases where an Incident can be related to more than 2 changes. I don't know how to accomplish this or if it can be done. Am I better off making a bunch of subreports?

Any help is appreciated!

Thanks!             


IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 20 Sep 2011 at 3:42am
use an outer join from the incident table to the other tables.  The outer join will allow all the incidents that exist in the date range to print, and if there are other details that exist they will print, as opposed to the inner join where only the incidents with other information will print.
 
to change the join, right click on the link on the link tab, and 'select link options'. If drawn from teh incident table to the relatioin table to the other table, you would be using Left Outer Joins.
 
I'm not sure about the Enforce Join...I do all my selecting in SQL stored procs so I am not sure how exactly this works... you might want to search for some of DBlank's post as they explain this option (sometimes).
 
HTH
 
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.031 seconds.