How to manage the history?

[lolab07]

Hi all,

I try to get experience on Crystal Report and the first report I have to do seems not so easy...

The report is on a defect tracking system.
The defect life cycle is : New -> Assigned -> Open -> ... (Status field)
The only way to detect a change is by the ChangeDate field (having fields like newdate, assignedate... would have been easier).

My required report is following:
This is easy to get a snapshoot of today (number of new, number of assigned...)
But to get the same kind of report 1 week before, 2 weeks before..., x weeks before (the weeks in X-axis) makes me crazy.

Using the group (group 1: changedate, group 2: status), I can detect the change of status during each week; so the activity: during 1 week there are x new-new defects, y defects from new to assigned...
But I cannot access to the cumulate number of new defects, of assigned defects (I want, for a particular week, the x remaining defects at new...).

Any idea I can investigate?

My environment: Crystal Report 8.5 or XI and SQL server.

Thanks in advanced for your help

 

[synapsevampire]

You may find that posting technical information serves everyone better than descriptions:

Crystal version
Database/connectivity used
Example data
Expected output

The only requirement I could gleen from your post was "(I want, for a particular week, the x remaining defects at new...)"

Which remaining at new is a bit ambiguous.

You can limit rows in the report to whatever you need by using the Report->Edit Selection Formula->Record, such as:

{table.date} = lastfullweek
and
{table.status} = "NEW"

Otherwise I'm not sure what you have nor what you need.

-k

 

[chelseatech]

The problem you are trying to solve requires you to have one defect to appear in multiple places. For example, a record that was new two weeks ago and assigned last week, and is open this week has to be in three places at once.

Do you have a table showing each event for each defect, or just the defect and it's current status. You need an event table (or equivalent fields) to get the report you want.

In the end it comes down to DB design. if you don't have fields to record each event you can't use that in your report. You will need to change the DB and app.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lolab07]

Hi Synapsevampire and Chelseatech,

Thanks for your answers.

Synapsevampire, sorry, you didn't understand my issue. Let me take another example: today, if I access to my defect tracking system, I can have the actual number of defects in New, in Assigned, ... If I wait 1 week, and log me again, the number of New, Assigned... will change, OK? The number of new I get, doesn't only represent the defects which were created last week. Some of them were created a long time ago. I don't want to detect the activity of next week. I want to have the evolution curve of the New, of the Assigned...
If I define Changedate as a group, using "on change of" into the chart expert, I will only detect the activity!

Chelseatech, in my database, I have 2 tables, 1 for current status (defect table), another for the history (defect_history table). Table structure is DefectID, Status, Changedate...

Is there any function which can give me the last record for each defect before a specific week?
Example: we are week 5, the last record of defectID 1244 before week 5 is following record:
Defect_history.defectID= 1244, Defect_history.status= "Resolved", Defect_history.changedate= "18-feb-05" (->week3)
Then, if I select last records of all defects and count the number of New, of Assigned... using @week as group, I will get my evolution curves