Formula Help 1

[TEM3]

Crystal Reports 8.5 and Oracle tables.

Pending assignments are any assignment not completed. Backlogged assignments are assignments pending more than 30 business days.

The following formula will give me pending assignments for any given date (DDATE). Now I would like to test if the assignment was pending more than 30 days at DDATE. If this statement is true (PendCnt is incremented), what is the syntax to check if the assignment is backlogged?


Shared NumberVar PendCnt;

if ( (IsNull({ALL_ASSIGNMENTS.Date Completed})) or ({ALL_ASSIGNMENTS.Date Completed} > {@DDATE}) ) and
(Date({ALL_ASSIGNMENTS.Date Assigned}) <= {@DDATE}) then PendCnt := PendCnt + 1

 

[rosemaryl]

Dear Tem3 ....

What are the contents of @DDATE

What do you consider 30 days? Including weekends or only business days?

regards, ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[TEM3]

@DDATE is just a date. Right now the report gives a historical picture for 12 months (well, actually dates in 30 day increments), so @DATE starts as currentdate and is reduced by 30 days to give the previous month, etc. for 360 days.

But, as I said, just replace @DDATE with any date......

The logic I hope for is:

If the assignment in the record was pending on @DDATE, was it pending more than 30 calendar days (forgive me if I said "business" days before)? If it was pending more than 30 days, then a "BACKLOG" counter needs to be incremented. I just need the syntax for doing both in this one formula......

 

[rosemaryl]

Dear Tem3,

Okay let's try this (I used Crystal's Exteme db for testing so the fieldnames have changed - just change the date fields back to yours):

//place where you want to evaluate like in the details.
WhilePrintingRecords;
Shared NumberVar PendCnt;
Shared Numbervar BackLog;

Pendcnt := if (IsNull({Orders.Ship Date}) or {Orders.Ship Date} > {@DDATE} ) 
           and ({Orders.Order Date} <= {@DDATE}) then PendCnt + 1 else pendcnt;

Backlog := if datediff('d',{Orders.Ship Date},{@DDATE}) > 30 then Backlog + 1 else Backlog;

//can display the value of both in details for a reality check.
pendcnt & ' - ' & backlog

Now, wherever you want to display the value of pendcnt or backlog you would create a separate formula for each one and place where you want it to display.

//display pendcnt value
//place group footer where you want to display
WhilePrintingRecords;
Shared NumberVar PendCnt;
Pendcnt


//display backlog value
//display in group footer where you want to display
Whileprintingrecords;
Shared NumberVar Backlog;
Backlog

If you need to reset the variables at a group level then create this formula:

//@reset vars
//place in header of group where you want to reset.
WhilePrintingRecords;
Shared NumberVar PendCnt := 0;
Shared Numbervar BackLog := 0;

I think the above will do it for you ... if not, please provide more information.

regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[kskid]

Based on what you have posted, then

if isnull({ALL_ASSIGNMENTS.Date Completed}) then
   pendcount := pendcount + 1;

IF isnull({ALL_ASSIGNMENTS.Date Completed}) and
datediff("d",{ALL_ASSIGNMENTS.Date Assigned},currentdate) => 30 then
  backlog := backlog + 1;

-LW