Looping Backwards Through Records

[carla]

Hello,

I have a vertical list of numbers in my details section. Each number is a result of a formula that calculates the number of days it took to resolve a support case. I need to check if the last number in the list is "0". If it is I have to check the second to last number to see if it is a "0", and so on until I find the first non-zero number from the bottom of the list.

Any ideas on how I can go about this? Should I use a loop? If so, how do I loop backwards? I am unsure of the best approach.

I am using CR 8.5.

Thanks for any help!
Carla

 

[IdoMillet]

If you explain what you need to do with the resulting number, it may be easier to suggest the best approach.

Cheers,
- Ido ixm7@psu.edu

 

[carla]

Well, it's kind of long winded, but here's the gist.....

I am reporting off a Technical Support case management database. Each Support case is a group. Each case has an audit trail associated with it that tracks each time the status of the case is changed. I have placed the line items in the audit trail in the Details sections. Some cases may have been opened and closed several times meaning that there may be several line items. I have a formula that looks at the each line item in the audit trail and based on the value, calculates the number of days between the date the case was opened and the date the case status was changed.

Without going into too much more detail, I have to find a way to start at the bottom of the line items for each case (group) and move up until I find a line item with a non-zero value. Once I have that number for each case, I will average them together for each month to find the Average Time to Close for the month.

Hope that clarifies things.
Thanks for any help,
Carla

 

[dgillz]

Do you not want to simply exclude the zeros from the report? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[carla]

Right, I don't want to exclude zeros from the report because if there is no non-zero value then I will need to take zero as my value in order to include the case into my average for the month.

 

[IdoMillet]

Sounds like you need for each case the maximum date from all line items belonging to that case. Is taht true?

If this is the situation, you can use a subreport for each group and pass back the value using a shared variable or, if you are comfortable with SQL, use a correlated subquery (or a view) that returns that info directly:

The easiest approach would be to use a view (query in MS Access) of :
----------------------------
Select Case_ID, Max(my_date)
FROM CASE_Detail_Table
Group By Case_Id
----------------------------

You can then join that View as if it was
a table to the case table.

Cheers,
- Ido ixm7@psu.edu

 

[IdoMillet]

Another approach would be to reset a variable in the group header, reset its value to any non-zero value in the record band, and use it in a formula in the group footer.

Cheers,
- Ido ixm7@psu.edu

 

[kenhamady]

To answer your original questions:
1) You can't use a Loop formula in CR to go through records, only to go through values in a single record.
2) You can process records singly using a variable, but only in the order that they appear, not Backwards. However, if you could sort these records in descending order, that would allow you to work forward from the FIRST record, and use the value you find in the first non-zero record in subsequent records.

Another approach is to write a formula that says,
if value = 0
then Date
else Date(1,1,1)

Then calculate the Maximum of this formula for each group. This maximum can be used in running total formulas (as long as they are the kind that use variables). Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com