Help with Getting median

[geestrong]

Hello,

I am trying to get the Median based on certain criteria. I found these formulas on Business Objects website, and I am trying to modify them work for the report. I am using CR XI. Oracle DB.


Here are the formulas:

Note: the @BackLogResEval is a running total counting the records that fit the criteria. I know that I have 16 records that fit this criteria. Also I have a display formula names @BackLogResDisplay that display the number.

//Evaluate formula - placed in the detail section
WhilePrintingRecords;
EvaluateAfter ({@BackLogResEval});
NumberVar MedianRecNo;
NumberVar OddNoOfRecFlag;
NumberVar Median;

If (OnFirstRecord or {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER} <> Previous({RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}))
and {RPVW_PERMIT_SUMMARY.PERMIT_TYPE} in ['BLR1', 'BLR2' , 'BLR3' ,'BLRS' ,'BLR4', 'B-D1']
and {@CompletedPlans} = 'NO'
then

if {@BackLogResEval} = MedianRecNo then
(
if OddNoOfRecFlag = 0 then

Median := {@BackLogResEval}
else
Median := ({@BackLogResEval} +{@BackLogResEval}) / 2
)

Note: I tried to use a Next or Previous function but I get an error saying "NO Previous Or Next value.' So how can I step though each record?


This is in my initializing formula placed in the Report footer
//Initializing formula
WhilePrintingRecords;
EvaluateAfter ({@BackLogResDisplay});
NumberVar MedianRecNo;
NumberVar OddNoOfRecFlag := 1;
MedianRecNo := Round (({@BackLogResDisplay}) / 2);
if Remainder(({@BackLogResDisplay}), 2) > 0 then
OddNoOfRecFlag := 0



Thanks,
Greg

 

[lbass]

You have to either be able to sort by this formula and do a manual median OR you need to be able to use a running total on this formula using the running total expert.

Let's try the second solution first. In your subreport,use a record selection formula of:

{RPVW_PERMIT_SUMMARY.PERMIT_TYPE} in ['BLR1', 'BLR2', 'BLR3' ,'BLRS' ,'BLR4', 'B-D1']
and {@CompletedPlans}='NO'

Then go to selection formulas->GROUP and enter:
{PER_ACTIVITY.PER_ACT_COMPL_DATE} = Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER})

Then change {@DaysforResEval} to:

if isnull({PER_ACTIVITY.PER_ACT_DECISION}) then
{?EndDate}-{PER_ACTION.PER_ACN_COMPL_DATE} else
if {PER_ACTIVITY.PER_ACT_COMPL_DATE} <= {?EndDate} then
{?EndDate} - {PER_ACTIVITY.PER_ACT_COMPL_DATE}else
if {PER_ACTIVITY.PER_ACT_COMPL_DATE}>={?EndDate} then
{PER_ACTIVITY.PER_ACT_COMPL_DATE} - {?EndDate}

Then insert a running total and choose {@DaysforResEval}, median, evaluate for each record, reset never. Place the running total in the subreport footer.

-LB

 

[geestrong]

LB-

The second solution is not working. The formula @DaysForResEVal will not allow me to use the running total expert. The only option available is Field Heading.


Greg

 

[lbass]

I think this will work if you add "whilereadingrecords" at the beginning of {@DaysforResEval}. I tested this out, and the median seems to be correct using this method.

-LB

 

[geestrong]

Hello LB

Thanks, this method works! Thanks for all the help. Can you suggest any good Crystal Report Books?

Thanks again

 

[lbass]

The George Peck books are good (The Complete Reference: Crystal Reports (Version#). You could also check out Ken Hamady's site. Finally, I think there is a lot to be learned from: a) this site; b) the help files that come with CR; c) Business Objects knowledge base; d) exploring the possibilities using the Xtreme database that comes with CR.

Glad it finally worked, as I took you on a twisty ride!

-LB