Grouping 1

[piconel]

I have created a report using 2 tables. Within these tables there are 2 field names that have the same TYPE of value, (names) but different Table names {PROBLEMS.GROUP_ID} AND {TRANSFER.GROUP_ID}. I would like to get these two groups to sort together, under 1 grouping.

Is there anyway that I can make a Formula to accomplish this??

Right now, I can only only sort on one group group at a time, so I have Group A, then Group B. Where Group B could also appear later in the report...

Any assistance is appreciated.
Leah Picone

 

[kenhamady]

When you link the tables together do you get two different names for each record? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[FoxG]

Hi Leah,

Can you link the two tables on group_id with an outer join?

Geoff

 

[piconel]

Hi Guys. Thanks for responding.

When I link the 2 tables together, I actually lose information.

First of all, not all information is represented in both tables. Most of the information is in the Problems table. The Transfer table has 'supporting' information.

I need to sort the information based on the Problems table, and then depending if there is information in the Transfer table, based on the Group_Id, I need that information to sort under the proper {Problems.Group_id} heading.

When I link directly on {Transfer.Group_id} I am only returned the details of the information that is in Transfer (which is not all of the records), and when I link directly on {Problems.Group_id} it ignores the supporting information that I am trying to access from {Transfer.Group_id}.

I have not tried to create any formulas: After I linked these 2 would I need to create a formula to access the information?

I have tried linking the tables every which way, using all types of link options, and I end up losing more information....

Any other ideas? Or a formula that I could use, once I have them linked?

Thanks,
Leah Picone

 

[IdoMillet]

Leah,

If I understand you correctly, you want all Problems and all Transfers that have the same Group_ID to behave as if they are records belonging to a single group.

The simplest way to acheive this would be to use a UNION query. Here is a basic outline:

----------------------------------------------------------
SELECT PROBLEMS.Group_ID, PROBLEMS.field1, PROBLEMS.field2
FROM PROBLEMS
UNION
SELECT TRANSFER.Group_ID, TRANSFER.field1, TRANSFER.field2
FROM TRANSFER
ORDER BY 1
----------------------------------------------------------

Cheers,
- Ido

 

[piconel]

Sorry Ido,

your select statement seems easy enough, but let me show you what I have and then maybe you can give me a hint as to how I can convert it to what you suggested...

SELECT
SESION."SESSION_ID", SESION."DESCRIPTION", SESION."MODIFY_DATETIME",
PROBLEMS."PROBLEM_ID", PROBLEMS."SEVERITY", PROBLEMS."USER_ID", PROBLEMS."PROBLEM_CODE", PROBLEMS."OPEN_DATE",
PROB_TRANSFER."GROUP_ID", PROB_TRANSFER."TRANSFER_DATE",
CALL."CALLER_ID"
FROM
"EXAV"."SESION" SESION,
"EXAV"."PROBLEMS" PROBLEMS,
"EXAV"."PROB_TRANSFER" PROB_TRANSFER,
"EXAV"."CALL" CALL
WHERE
SESION."PROBLEM_ID" = PROBLEMS."PROBLEM_ID" (+) AND
SESION."PROBLEM_ID" = PROB_TRANSFER."PROBLEM_ID" (+) AND
SESION."CALL_ID" = CALL."CALL_ID" (+)
ORDER BY
PROBLEMS."PROBLEM_ID" ASC

Thanks,
Leah

 

[IdoMillet]

Leah,

Each select in the UNION statement must return the same
column structure (number of columns and their data types.

So after you remove the TRANSFER stuff from the first select, you can match (use same column location) PROBLEMS."OPEN_DATE" in the 1st select with PROB_TRANSFER."TRANSFER_DATE" in the 2nd select.
You can then add to the 2nd SELECT a bunch of nulls to match the columns from the first SELECT.

hth,
- Ido
ixm7@psu.edu