CR ver9; Unable to expand info req'd on service call

[JoyCR9]

I am running CR version 9 on SQL (not sure of the version) and on Oracle.

My main table in linking is: SERVICE_CALL

Linked off the SERVICE_CALL table is the SERVICE_CALL_XREF table. These two tables are an equal inner join using the SERVICE_CALL_ID field found in both tables.

Within my SERVICE_CALL_XREF table are items:
SERVICE_CALL_XREF_LABEL
SERVICE_CALL_XREF_ID

For each SERVICE_CALL_XREF_LABEL there is a corresponding SERVICE_CALL_XREF_ID. The SERVICE_CALL_XREF table has system generated SERVICE_CALL_XREF_IDs to keep this all together.

My problem is that I want to pinpoint those service calls where the XREF_LABEL = Metrics. I don't care about any of the other calls. I can do this easy enough through record selection criteria.

The problem comes in when I want to report other SERVICE_CALL_XREF_LABELs and the corresponding SERVICE_CALL_XREF_IDs. For example, once I've pinpointed my calls in question, I then want to report on select SERVICE_CALL_XREF_LABELs within those particular calls.

Call 7032 has a SERVICE_CALL_XREF_LABEL of Metric and shows on my report. Within call 7032 I now want to report the SERVICE_CALL_XREF_LABEL of Vendor and it's corresponding SERVICE_CALL_XREF_ID; as well as my SERVICE_CALL_XREF_LABEL of Reason and it's corresponding SERVICE_CALL_XREF_ID. I can only get the Metric SERVICE_CALL_XREF_LABEL.

My only group is SERVICE_CALL_ID. I've tried various if statement formulas to get this info with no success. I don't want subreports.

Column Headers:
Call ID Metric Vendor Reason

Thanks for any help.

 

[JoyCR9]

Report should look like:

Call # Metric Vendor Reason
7032 Scrub Smith Defective Part
Smith1
7055 Scrub Smith Warranty

 

[lbass]

You could add the SERVICE_CALL_XREF table a second time using a left join from {SERVICE_CALL_XREF.ID} to {SERVICE_CALL_XREF_1.ID}. Your initial record selection statement can be:

{SERVICE_CALL_XREF.LABEL} = "Metric"

Don't put any selection criteria on the alias table. Instead, create formulas like:

//{@Vendor}:
if {SERVICE_CALL_XREF_1.LABEL} = "Vendor" then {SERVICE_CALL_XREF_1.Description}

//{@Reason}:
if {SERVICE_CALL_XREF_1.LABEL} = "Reason" then {SERVICE_CALL_XREF_1.Description}

-LB