Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Help with Linking and Grouping Post Reply Post New Topic
Page  of 2 Next >>
Author Message
skydiver
Newbie
Newbie


Joined: 04 Mar 2009
Online Status: Offline
Posts: 8
Quote skydiver Replybullet Topic: Help with Linking and Grouping
    Posted: 04 Mar 2009 at 10:35am
I have two DBASE files that I am trying to create a report out of.  Here are my two files and the links:
 JOBCODE.DBF
     ID - Indexed key field
     NAME - Name of the JOBCODE

EMPLOYEE.DBF
     ID - Unique
     NAME - Name of Employee
     JOBCODE1 - Value will be either 0 or the JOBCODE.ID
     JOBCODE2 - Value will be either 0 or the JOBCODE.ID
     JOBCODE3 - Value will be either 0 or the JOBCODE.ID

Each employee can be assigned up to 3 jobcodes.  I would like a report that lists every jobcode and under that every employee that has that jobcode in any of the jobcode1-3 fields.


Sample Data:
JOB.DBF
ID NAME
1   Manager
2   Cook
3   Server
4   Dishwasher

Sample EMP.DBF
  ID NAME    JOBCODE1 JOBCODE2 JOBCODE3
1000 Bob     1        2        0
1001 Jim     3        0        0
1002 Chris   2        3        0

Desired Report based on sample data:

(Group)Section
   ID Jobcode Name
    1 Manager
      (Details Section with blank suppress)
      1000 Bob
    2 Cook
      (Details Section with blank suppress)
      1000 Bob
      1002 Chris
    3 Manager
      (Details Section with blank suppress)
      1001 Jim
      1002 Chris
    4 Dishwasher
      (Details Section with blank suppress)


I have all 3 of the EMP.JOBCODE1-2 fields linked to the JOB.ID field and have attempted multiple formulas to test the values of all the EMP.JOBCODE fields at run time to no avail.

Any suggestions would be helpful

Thanks
Skydiver


Edited by skydiver - 04 Mar 2009 at 10:37am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Mar 2009 at 11:05am
You will need to use the EMP.DBF 3 times and link each one to the JOB.ID seperately.
First table EMP.JOBCODE1 linked to JOB.ID
Second table EMP.JOBCODE2 linked to JOB.ID
third table EMP.JOBCODE3 linked to JOB.ID.
 
IP IP Logged
skydiver
Newbie
Newbie


Joined: 04 Mar 2009
Online Status: Offline
Posts: 8
Quote skydiver Replybullet Posted: 04 Mar 2009 at 11:43am
I tried this before and got the error when linking that I had multiple paths to the same table and I get an error when I try to run the report.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Mar 2009 at 11:52am
When you add then they should use an alias name to avoid this so you should have 4 tables:
Job
EMP
EMP_1
EMP_2
Did you get these or did the EMP table not alias?
IP IP Logged
skydiver
Newbie
Newbie


Joined: 04 Mar 2009
Online Status: Offline
Posts: 8
Quote skydiver Replybullet Posted: 04 Mar 2009 at 1:50pm
JOB.ID <---EMP_01.JOBCODE1
         |_<--EMP_02.JOBCODE2
         |_<--EMP_02.JOBCODE3

I am grouping on JOB.ID

When I create multiple Alias' of the EMP.DBF file, I am ostensibly linking multiple files to the JOB.ID field  Remember that I am not linking like this:
JOB---> emp_01
      ---> emp_02
etc.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Mar 2009 at 2:22pm

Perhaps my logic is flawed here. I was thinking in terms of a SQL view or stored procedure where you can recreate the data into a single table doing this.

I still think you can do this and create 3 detail sections under the JOB ID group, 1 detail per table and then suppress blank fields. It is late in the day and the coffee is wearing off so I could be wrong Thumbs%20Down
Does this work?
Group1: Job.ID and add job.jobname
Detail1: EMP_01.ID and EMP_01.Name
Details2: EMP_02.ID and EMP_02.Name
Details3: EMP_03.ID and EMP_03.Name
 
It may make your sorting a little weird...
IP IP Logged
skydiver
Newbie
Newbie


Joined: 04 Mar 2009
Online Status: Offline
Posts: 8
Quote skydiver Replybullet Posted: 04 Mar 2009 at 2:55pm
I am getting a little closer.  I tried a nested if formula but it is not catching all the EMP records with the jobcode.  here is a sample of the formula I used (I linked every EMP.JOBCODE# field to the JOB.ID field)


if {EMP.JOBCODE1}={JOB.ID} then
   ToText ({EMP.ID},"####") + "  " + {EMP.NAME}
else if {EMP.JOBCODE2}={JOB.ID} then
   ToText ({EMP.ID},"####") + "  " + {EMP.NAME}
else if {EMP.JOBCODE3}={JOB.ID} then
   ToText ({EMP.ID},"####") + "  " + {EMP.NAME}
else ""

I believe that the problem I am having with this method is that it is evaluating every EMP.DBF under the JOB.ID group record and when it finds a match on the formula it returns the result then moves on the the next EMP record.  I am missing when the EMP record has two JOBCODES assigned.

I almost need to add an additional group to process each record in EMP.DBF and test for every instance of JOBCODE# to catch every employee multiple times when they have more then one JOBCODE assigned.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Mar 2009 at 3:15pm
Yep, this nested process will only catch the first instance it meets and ignore the rest and you need it to link each time, that was why I broke it out into the 3 tables with 3 seperate links.
 
If anyone else has suggestions feel free to chime in here please Clap.
IP IP Logged
skydiver
Newbie
Newbie


Joined: 04 Mar 2009
Online Status: Offline
Posts: 8
Quote skydiver Replybullet Posted: 04 Mar 2009 at 3:46pm
Your idea was my first inclination too until I got the multiple link errors.

I am open to any suggestions.
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 05 Mar 2009 at 3:36am
Hi
 
I tried in sql server using command object here is the query
 
Select
Jb.ID             as 'Job Id',
JB.Name        as 'Job Name',
emp1.ID        as 'JobCode1',
emp1.NAME   as 'JobName1',
emp2.ID        as 'JobCode2',
emp2.NAME   as 'JobName2',
EMP3.ID         as 'JobCode3',
emp3.NAME    as 'JobName3'
from JOBCODE jb
left  join EMPLOYEE emp1 on jb.ID= emp1.JOBCODE1
left  join EMPLOYEE emp2 on jb.ID=emp2.JOBCODE2
left  join EMPLOYEE emp3 on jb.ID=emp3.JOBCODE3
 
Then group by Job Id
 
Then place Job Name field in Group Header
 
then create 3 details section for Jobcode 1, Jobcode2,Jobcode 3 Place the fields accordingly in them and suppress blank sections.
 
 
 
 
Output
 
1 Manager
  1000 Bob
2 Cook
  1002 Chris
  1000 Bob
3 Server
  1001 Jim
  1002 Chris
4 Diswasher
 
The only issue i have is not able to sort details for ID 2 COOK
 
Cheers
Rahul
IP IP Logged
Page  of 2 Next >>
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.015 seconds.