Thank you very much for the reply, and I apologize for not including more detail. I'll try to describe the problem more clearly.
My users are entering 3 lab values into a form along with the date that the labs were recorded. Those values are then recorded into our database when the user submits the form. The values are FEV1, FVC & FEF.
These values are then displayed in my crystal report in the details section, sorted by lab date. So the user will see the date of the lab, FEV1, FVC & FEF. The purpose of this report is to spot trends in the patients pulmonary function, so the nurses need to view the lab values sorted by the lab date.
For each of these columns of displayed values, I need to then run the calculations that are described below starting with the FEV1 column:
1) Review all of the values that are entered for FEV1 and select the Maximum Value.
2) Review the remaining values and select the 2nd highest FEV1 value. Before setting this value as the 2nd highest value, it has to meet the following two criteria:
a) the difference between the date of the Maximum FEV1 & the 2nd Highest FEV1 value has to be >= 21 days
AND
b) the difference in the actual value between the Maximum FEV1 and the 2nd Highest FEV1 value has to be <= 10%.
If either of these criteria are not met, we would want to locate the next (3rd) highest FEV1 value entered by my users and evaluate it as we've done above in steps 2a & 2b.
3) Once we have the Max & 2nd Max values set, we need to get an average of the two.
4) This average will then be used in a simple division formula whose result will be displayed in the column adjacent to the FEV1 column. The formula will divide the actual FEV1 value (entered by the user) by the average that we've just calculated. This should be displayed in the column adjacent to each FEV1 value that is entered. So for each row, we should see the FEV1 value and the calculated value FEV1 % Predicted.
This same process will need to be followed for the other two columns mentioned above (FVC & FEF).
So the fields that are displayed in the details section are: Test Date, FEV1, FEV1 % Predicted, FVC, FVC % Predicted, FEF, FEF % Predicted.
The FEV1, FVC and FEF fields are provided by the users. The FEV1% Predicted, FVC % Predicted and FEF % Predicted fields are then calculated and presented for each of the rows in the details section.
I hope this is helpful. If there's a way for me to post a pdf of the report as it now sits... it might be helpful to actually see the layout.
Again... thank you very much for any help that you can provide. It's very much appreciated.
IP Logged
