Sporadic Formula

[jdemmi]

Using Crystal 8.0

I have a formula that I use in two reports. It works fine in one report, and sporadically in the other. Here is the formula:

***Planned_Select****************************

evaluateafter({@Report_Initialize}) <----Where stWO_Plan = &quot;&quot;

global stWO_Plan as string

if ({A_WPOPERATION.ERECTIMESTAMP} in minimum({?Date Parameter}) to maximum({?Date Parameter})) and ({A_WORKORDER.WONUM}<> stWO_Plan) then
formula = 1
stWO_Plan = {A_WORKORDER.WONUM}
else
formula = 0
end if


The report's recordset is ordered by hierarchy, location, worknumber, and timestamp. The logic here is that it returns 1 for each of the FIRST workorder records it encounters that falls within the date range. This formula field is placed in the detail section of the report, and summed for sub-totals and Grand-Total.

The locations are arranged in a hierarchy. If I choose to run the report high in the hierarchy, everything is returned correctly. If I choose lower down in the hierarchy, it may miss some, especially if there is only one workorder (but multiple records).

I have tested it and I know the formula is being called. For some reason it does not recoginze any record as satisfying the criteria, yet clearly there are.

Any ideas?

 

[kenhamady]

I think part of the problem stems from the fact that your formula is being evaluated WhileReadingRecords (else you couldn't subtotal them) and that means you are assuming that the data is in the correct order as CR reads them. You are sure that the recordset is being given to CR in the correct order?

I don't understand what this part means:
&quot;The locations are arranged in a hierarchy. If I choose to run the report high in the hierarchy...&quot; 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

 

[jdemmi]

Ken,

Thanks for your response. Let me see if I can clarify things a bit.

I am fairly sure the recordset is being evaluated in the correct order. I revealed the detail section of the report and the records appeared in the order I expected. I can see the formula field is returning zero where I expect the value to be one. (I am making the assumption that the records are being evaluated in the forumla in the same order as they appear on the report.) The Order By section of the report query seems correct when I select to Show SQL Query...

I have done some experiments. I have put the report in preview mode, then opened the formula window and changed something in it which was purely cosmetic and did not change the evaluation logic. When I closed the formula window, magically the values displayed on the previewed report change to the correct value. When I ran the report again, using the same criteria, the values returned to their original incorrect selves. I can go back and play with the formula again, with the same results as before. I have done this many times. From this I have concluded the issue somehow swings on the process of querying the database. For some reason the formula is incorrectly evaluated when the data is being fetched from the database. But once the data resides on my computer in some temporary storage, Crystal is able to evaluate it correctly. This is strange indeed but I think it is key to the answer.

Concerning the hierarchy, let me see if I can explain clearly. We have a location-ancestor table. One column is the ancestor, the next column is a location that has the first column as its ancestor. In other words, if you select a particular value in the Ancestor column, the query will return all locations that consider that ancestor location value as an ancestor, whether near or far. (With several thousand locations, you can imagine the table gets quite big.)

Our hierarch is as follows: Business Unit - Zone - Site - Plant. So if my user selects a Business Unit location we want the report to group on the next level down, Zone, and sum up everything under them. If the user selects a Zone location, then we want to group on Site, and so on.

To accomplish this, I use the location-ancestor table twice in the query for the report. The first location-ancestor table (let's give it the alias of Top) is used in the selection criteria, setting the Ancestor location equal to the location value passed in by the user. The next instance of the location-ancestor table (let's call it Next) is linked as follows: Top.Location = Next.Ancestor. We also constrain it Next.Type = &quot;Zone&quot;, let's say. (I have code that determines the next level down type, based on the type of the top level location chosen by the user.) This is how we are able to group on the next-level-down.

Now, here is what I meant by running the report high in the hierarchy. If the user chooses a Business Unit location, the report is grouped on Zones, and all the workorder values are evaluated correctly. However, if the user goes lower in the hierarchy and chooses Site, the next level down being Plant, sometimes one workorder is incorrectly evaluated, especaially if there is only one workorder that falls into the selected time period.

At first I thought the problem was mis-classification of the locations in the hierarchy. That is, if you choose something very high in the hierarchy, mis-classification lower down doesn't matter. But if you go lower, then mis-classification might loose a location and therefore workorders will be missed. Unfortunately, that is not the problem. I can see the records are showing up in the detail as they should, and my formula simply isn't evaluating it correctly.

And here is the last bit of evidence. I made a second report that is like the first in every way except that it does not attempt to group on the next-level-down. So the user selects the top location, and the report returns the data grouped on locations, with no regard to how low or high the location is relative to the selected top location. Everything is correctly evaluated no matter how low or high in the hierarchy the initial selected location is. So I conclude from this that the inclusion of second instance of the location-ancestor table in the report query somehow affects the formula evaluation as the data is being fetched from the database.

That is my best guess at what is happening. I can corner the rat, but I can't trap him. Do you have any ideas?

Wayne

 

[kenhamady]

There is a flaw in this logic:
>>I am fairly sure the recordset is being evaluated in the
>>correct order. I revealed the detail section of the report >>and the records appeared in the order I expected.

What you are seeing in preview is after the sort has happened in CR or WhilePrintingRecords. But your recordset (and your formula) are evaluated long before the sort or WhileReadingRecords. That is why you can change the formula after perview and get it correct, and rerun the report and get it wrong again.
You should be fine when the recordset records are in order before CR gets them. You can't test this with CR because you can only see the records AFTER CR groups them. Your problem is too complex for me to sort out in this thread, but I am going to guess that your recordset is in the correct order in the cases that work, but not in the correct order in the cases that fail.
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