Crystal Reports, MS SQL && Clear Quest

scojones

Hey guys,
We are using Clear Quest as our Change Management tool. Clear Quest uses MS SQL as it's backend database.

I have the lovely job of creating reports based on the data in the MS SQL database, one of which is Defect Distribution per component.

So, a defect can be in one of several states, from assigned, resolved, to closed. Now Clear Quest has absolutely no problem seeing what current state a defect is in. However, with MS SQL, when a defect changes state, a record of this is stored in the MS SQL database...

As a defect changes state, a record of this state is saved in MS SQL. Thus, Crystal is reading all of these states as individual records, and is duplicating data. This is a lot more complicated than it sounds. For instance…

I am searching for defects which are open, and a defect is open if it is in any state but ‘closed’. Now since each state transition is saved in MS SQL, and if I search for every defect that is not ‘closed’, it gives me back a lot of records. However, if a current defect is closed, the records of it being in other states also exist in the database, and crystal reports is showing these records, even though the defect is closed.

I know this is more of a logic problem than anything else. Does anyone see a way around this? I've been stuck on this for the last 4 - 5 days.

I can be more descriptive if needed

Cheers guys.

scojones

Actually scrap what I just said. Upon further inspection (which actually means not beleiving what I was told by my collegues and checking it out for myself), there is only one record in MS SQL. Crystal seems to be duplicating the data and saying that the record has every state that I search for.

krinpit

Sounds like you've got an SQL INNER JOIN which is causing the duplication on the report

scojones

Yeah, it is something like that I suspect. I'm not using any SQL statements though, Crystal is doing the filtering for me. Time to get Crystal Reports on the phone

Caixa

What's the formula being used in the CR select expert?

scojones

{work_request.submitted_date} > DateTime (2003, 01, 24, 05, 00, 00) and
{statedef.name} in ["Assigned", "Deferred", "Errata_Pending", "Errata_Required", "Impact_analysis", "Implementation", "Submitted", "Verified"] and
{project.name} in ["OMITTED"] and
{work_request.fldcolumn} = "Defect" and
{work_request.project} = {project.dbid}

I've omitted the project names for NDA reasons.

Caixa

Actually re-reading your posts, are your tables linking properly in the Database Expert?

scojones

Yep they are, the relationships are set up.

statedef.id => work_request.state
work_request.project => project.dbid
work_request.owners => users.dbid