Trygve Thayer
asked on
Crystal Reports. Need to find the earliest activity record by timestamp for each ticket.
Generating a report to see initial response time for a chart. I have a dataset that pulls in the Incident_List table information and then is linked to an activity table with multiple records for each ticket. What I am trying to do is show first record where Activity.WF field is equal to 254 or 257 and then based on the records listed find the earliest record based on the Activity.Create_Date This field is a Date/Time field
What I have been able to do so far to limit the records is to use the Expert record to limit but that does not narrow it down to one record with the earliest date. Here is the code on the Expert record. Attached is a wordpad file showing a screenprint of a single ticket where I limited in the expert recordCrystal_Data.rtf
{Activity.WF_Item_Num} in ["254", "257"] and
{Incident_List.Ticket} = "T055164"
What I have been able to do so far to limit the records is to use the Expert record to limit but that does not narrow it down to one record with the earliest date. Here is the code on the Expert record. Attached is a wordpad file showing a screenprint of a single ticket where I limited in the expert recordCrystal_Data.rtf
{Activity.WF_Item_Num} in ["254", "257"] and
{Incident_List.Ticket} = "T055164"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You'd suppress the detail section, so you'd only see the values from the first record in the group (in the group header). If you sort the records by the date, that will be the earliest record.
Of course if you have a lot of data, then the report may end up reading (and then suppressing) a lot of data that you don't actually need. Also, any CR summaries will include all of the records, so any totals, etc. would have to be calculated using formulas and variables, or possibly using running totals.
James
Of course if you have a lot of data, then the report may end up reading (and then suppressing) a lot of data that you don't actually need. Also, any CR summaries will include all of the records, so any totals, etc. would have to be calculated using formulas and variables, or possibly using running totals.
James
ASKER
The reason I am trying to filter the data is so I can add a chart at the bottom. I will play around with it but my experience on inserting a chart is minimal and have found how to set it up if the data in the details is what I am looking for.
If you need a chart then you have to filter to get one record otherwise all the detail records will be included in the chart.
mlmcc
mlmcc
ASKER
yes that is what I am trying to do but unsure on the code to do filter to one record
ASKER
I have noticed in my dataset I have filtered the remaining records have a count. Is there a way to add to the filter to get the lowest number. Attached is a screenprint of the records
dataset.rtf
dataset.rtf
I don't think you can do this in the record selection.
Are you just looking for the earliest date itself, or some other data from the record that has the earliest date?
If you only need the earliest date, then a SQL Expression might be an option (if CR supports them for your datasource). If you need more than that, I'm not sure if a SQL Expression will do. The other option would be to create a query that will gather the required data (eg. a view or stored procedure in the db, or a CR Command (a query that you create in CR)).
Edit:
I hadn't noticed your latest post when I posted this. Unless the lowest number is always the same (eg. 1), I think you have the same problem. Trying to filter the records based on the lowest number is the same as trying to filter them based on the earliest date.
James
Are you just looking for the earliest date itself, or some other data from the record that has the earliest date?
If you only need the earliest date, then a SQL Expression might be an option (if CR supports them for your datasource). If you need more than that, I'm not sure if a SQL Expression will do. The other option would be to create a query that will gather the required data (eg. a view or stored procedure in the db, or a CR Command (a query that you create in CR)).
Edit:
I hadn't noticed your latest post when I posted this. Unless the lowest number is always the same (eg. 1), I think you have the same problem. Trying to filter the records based on the lowest number is the same as trying to filter them based on the earliest date.
James
ASKER
What I am needing to do is end up with the earliest record for each ticket number. There are a few thousand tickets and each ticket has 4 or more record that match the {Activity.WF_Item_Num} in ["254", "257"] is how I am filtering to get the response records. And now am trying to figure out how to narrow it down to one record. Maybe {Activity.Num = Minimum in {Incident_List.ticket} or something like that.
Once I have one record per ticket then I can create the chart. Many thank in advance.
Once I have one record per ticket then I can create the chart. Many thank in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That makes a lot of sense. I wonder if I can group the report and put the firs record in the group and then export to an excel. Sounding like what I am wanting to do is not possible in cr
The export could work. I don't know what you planned to do with the Excel file, but it could be used as the datasource for another report.
James
James
What you want can be done in the report it just requires a subquery.
mlmcc
mlmcc
ASKER
I have ended up creating a Crystal report and sorted by decending to get the most recent response be adding a group and putting data there. I then exported this data to excel and then created a second Crystal Report to generate the chart. I will be giving split points as both participants helped to get me and end result.
ASKER
Not how I planned but got an end result. Thanks to all.
You're welcome. Kind of a roundabout way to get there, but if it works for you. :-)
James
James
ASKER