Report Design: Help with Linking and Grouping

Print Page | Close Window

Help with Linking and Grouping

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5681
Printed Date: 26 Apr 2024 at 9:50pm

Topic: Help with Linking and Grouping

Posted By: skydiver
Subject: Help with Linking and Grouping
Date 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

Replies:

Posted By: DBlank
Date 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.

Posted By: skydiver
Date 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.

Posted By: DBlank
Date 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?

Posted By: skydiver
Date 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.

Posted By: DBlank
Date 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

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...

Posted By: skydiver
Date 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.

Posted By: DBlank
Date 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

Posted By: skydiver
Date 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.

Posted By: rahulwalawalkar
Date Posted: 05 Mar 2009 at 3:36am

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

Posted By: skydiver
Date Posted: 05 Mar 2009 at 3:05pm

I will try to implement this with standard dbf files. I figured I would need to look at the join types but wasn't sure how to go about making the correct join types.

Posted By: skydiver
Date Posted: 09 Mar 2009 at 12:05pm

I am stuck trying to implement the SQL commands in my situation where I have flat DBF files. Any suggestions on how to link the databases & aliases to replicate the SQL commands?

Posted By: skydiver
Date Posted: 11 Mar 2009 at 2:07pm

bump...

Print Page | Close Window