Dynamic Table LINK in Crystal 2008...?

[MJRBIM]

Crystal 2008 running on top of ORACLE 10g.

I have a report made up of three (3) tables -

EVALUATION_CASE
BOARD_MEETING
COMM_MEETING

There can be MEETING details for a single CASE_ID in both MEETING tables - but I only want to show the details of BOARD_MEETING or COMM_MEETING based on the CASE_CLASS value in EVALUATION_CASE.

CASE_CLASS = 'B' then only show details from BOARD_MEETING table.

CASE_CLASS = 'C' then only show details from COMM_MEETING table.

I will post the table examples below - but what I am trying to accomplish is to DYNAMIC LINK the tables based on the CASE_CLASS value in EVALUATION_CASE. I assume this would require some kind of a COMMAND...but I can't figure it out.

Thanks in advance for any help or advice!

Table = EVALUATION_CASE

CASE_ID EVALUATION_ID CASE_CLASS
----------------------------------------------------------
1012 5001 B
1012 5002 B
1012 5003 B
1013 5004 C
1014 5005 B
1015 5006 B
1016 5007 C
1016 5008 C
1017 5009 B
----------------------------------------------------------


Table = BOARD_MEETING

EVALUATION_ID BOARD_MEETING_ID BOARD_MEETING_DATE
----------------------------------------------------------
5002 6013 07/06/2011
5003 6014 20/06/2011
5004 6015 06/06/2011
5005 6016 07/06/2011
5007 6017 06/06/2011
5007 6018 07/06/2011
5009 6019 20/06/2011
----------------------------------------------------------


Table = COMM_MEETING

EVALUATION_ID COMM_MEETING_ID COMM_MEETING_DATE
----------------------------------------------------------
5002 6013 28/05/2011
5004 6014 29/05/2011
5005 6015 28/05/2011
5006 6016 29/05/2011
5007 6017 28/05/2011
5008 6018 28/05/2011
5008 6019 29/05/2011
----------------------------------------------------------


RESULTS

CASE_ID EVALUATION_ID MEETING_ID MEETING_DATE
----------------------------------------------------------
1012 5001 <NULL> <NULL>
1012 5002 6013 07/06/2011
1012 5003 6014 20/06/2011
1013 5004 6014 29/05/2011
1014 5005 6016 07/06/2011
1015 5006 <NULL> <NULL>
1016 5007 6017 28/05/2011
1016 5008 6018 28/05/2011
1016 5008 6019 29/05/2011
1017 5009 6019 20/06/2011
----------------------------------------------------------




Senior Business Intelligence Consultant
Toronto, Canada

SeagateInfo 7 -> Crystal Enterprise -> BOE-XI (R2) / (R3.1) | Crystal ( 7 -> 2008) design | WebI design | Best practices for Java / .Net / Web Services

 

[kristal9]

Hi,

Why wouldn't you just write a formula for meeting id and meeting date?

For example
@meeting_id
If [EVALUATION_CASE].[CASE_CLAS] = "B" then [BOARD_MEETING].[MEETING_ID]
ELSE
If [EVALUATION_CASE].[CASE_CLAS] = "C" then [COMMITTEE_MEETING].[MEETING_ID]


@meeting_date
If [EVALUATION_CASE].[CASE_CLAS] = "B" then [BOARD_MEETING].[MEETING_DATE]
ELSE
If [EVALUATION_CASE].[CASE_CLAS] = "C" then [COMMITTEE_MEETING].[MEETING_DATE]



To obtain the null values where applicable, be sure both tables are left outer joins.

 

[-cush-]

If you have more than one "one to many" relationship, you can expect your records to multiply. You may need to just have the evaluation case in your primary report and have to subreports each in their own subsection. You can supress the appropriate subsection based off of the case class.

 

[lbass]

I think you could do this with a union all where you use the case table with one of the meeting tables on each side of the union all, like this:

select eval.caseID, bd.evaluation_ID, bd.board_meeting_ID, bd.board_meeting_date
from evaluation_case eval
left outer join board_meeting bd on
eval.evaluation_ID = bd.evaluation_id
where eval.case_class = 'B'
union all
select eval.caseID, com.evaluation_ID, com.board_meeting_ID, com.board_meeting_date
from evaluation_case eval
left outer join comm_meeting com on
eval.evaluation_ID = com.evaluation_id
where eval.case_class = 'C'

-LB