Record Selection possible?

[69AceCool]

CRXI
ODBC Connection to Remedy ARS 5.1

Hi All,

I would like to ditch a subreport that I have within a main report. The current way the report works is like this. Show me each 'Parent' Incident where a 'Team' fails a resolution SLA time within a certain period. The subreport is linked by an INC# which retrieves each 'Child' record which shows the timing detail of the incident along with a number of other useful fields.

What I would like to do if possible is create a SQL expression that will retrieve all of these records in the main report without the need for the subreport:

Show me each 'Parent'Incident where a 'Team' fails within a certain period, then show me all 'Child' records relating to the Parent Incident. (The Incident numbers are the same in both the Parent and Child records)

As always any help is much appreciated.

 

[dgillz]

I am not sure you need a sql expression to do this. Have you tries linking thetables and if so what problems are you having retrieving the desired data?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[69AceCool]

Hi dgillz,

The current report works fine, however I would like to make it more efficient by not using subreports. There is only one table that holds this information.

Cheers.

 

[bmarks]

Not sure if I follow you completely, but are you saying that the parent and child records are in the same table?

If so, you will then have to link the table to itself. You can do this in the Database Expert by choosing the table a second time and then giving the second instance of it an alias and then link on the fields that link a Parent record to the Child record (i.e. incident number).

 

[69AceCool]

Parent and Child are on the same table. I know how to link tables within Crystal, however performance drops when you link Remedy tables using Crystal.

I'm sure there must be a way of running a SQL query on 1 table....?

 

[bmarks]

I'm not familiar with Remedy, however the SQL query would be linking the table to itself, so not sure how it would be much different than linking the tables with the expert. It would be something like:

select *
from tablename alias1
inner join tablename alias2 on alias1.incidentnumber = alias2.incidentnumber and alias1.??? = 'Parent' and alias2.??? = 'Child'
where alias2.timing --whaterver you need here to determine the timing ----

This will link the table to itself. I'm assuming that the incident numbers are in the same field for both parent and child and that there is someother field(s) that indicate whether it is a child or parent record.