The best why to process with this report one date two criteria

[geestrong]

Hello,

CR XI and Oracle DB

I have a report that will be using two selection criteria and one date range(Delivery Date). The report is supposed look at plans that are ASSISNED and COMPLETED plans within a particular range , usually a month or two, and plans that are PENDING. Pending means that it could have been assigned several months ago and not yet completed.

I am bringing in data from 11/2004 to the present. My issue is that is it possible to bring in all the data within 11/2004 to present in the background... then the user will only see the data within the StartDate and EndDate parameters(i.e. 6/1/2005 to 7/31/2005). I need to check which plans have been COMPLETED within the user defined criteria. The problem is to pending which needs look at all the PENDING plans that have been assigned to the the worker.

The report is not getting the accumulated PENDING plans. Can this be done on one report which the users wants or will I need to create a subreport.


Gregory Sanders

 

[lbass]

You should be able to write a record selection formula like:

(
{table.planstatus} in ["Assigned","Completed"] and
{table.date} = {?daterange}
) or
{table.planstatus} = "Pending"

I'm not sure how the date field relates to the Pending status. If you want to limit those in pending status to those plans with dates on or after 11/04, then change the formula to something like:

(
{table.planstatus} in ["Assigned","Completed"] and
{table.date} = {?daterange}
) or
(
{table.planstatus} = "Pending" and
{table.date} >= date(2004,11,01)
)

-LB

 

[geestrong]

Hello,

Here is some more clarification.
The assigned value is the name of the individual field name is PER_ACTIVITY.ASSIGNED_TO. Next the "Completed" is the value "APPR" that is in the PER_ACTIVITY.ACT_DECISION.

The delivery date is the delivery date PER_ACTION.PER_ACN_COMPL_DATE.

Pending is either "HOLD" OR NULL in the PER_ACTIIVITY.ACT_DECISION.

Greg

 

[lbass]

I don't understand how the assigned value then fits in. Are you trying to distinguish between Assigned/Completed and Unassigned/Completed? And does it relate at all to the pending status? Please explain.

For the pending part, you would need to change the formula to:

isnull({PER_ACTIIVITY.ACT_DECISION}) or
{PER_ACTIIVITY.ACT_DECISION} = "HOLD"

-LB

 

[lbass]

Also, since you are checking the field for nulls, this clause should be first in the record selection formula.

-LB

 

[geestrong]

The report is tracking how many plans were assigned to an individual during the time period chosen, how many plans that he or she approved within the period, how many plan since 11/2004 have been assigned to an individual that are still PENDING "not complete"

Here is the selection criteria that you suggested

...
and
(
{PER_ACTIVITY.PER_ACT_DECISION} in ["APPR"]
and not isnull({PER_ACTIVITY.PER_ACT_ASGN_TO})
and {PER_ACTION.PER_ACN_COMPL_DATE} in [{?StartDate} to {?EndDate}]
)
or
(isnull({PER_ACTIVITY.PER_ACT_DECISION} or
({PER_ACTIVITY.PER_ACT_DECISION}='HOLD' ))

 

[lbass]

If you want to distinguish the number of plans assigned versus the number approved, you cannot use criteria for completion in the record selection formula. Also, what date field is used for assignment? It sounds like you want that date field within the parameter range. So your formula would then look like:

(
isnull({PER_ACTIVITY.PER_ACT_DECISION}) or
{PER_ACTIVITY.PER_ACT_DECISION}='HOLD'
) or
(
not isnull({PER_ACTIVITY.PER_ACT_ASGN_TO}) and {table.assignmentdate} in {?StartDate} to {?EndDate}
)

This would return plans that were on hold or with no decision from any period, in addition to those which were assigned during the parameter period.

Then create a formula in the formula expert:

//{@approvedinperiod}:

if {PER_ACTIVITY.PER_ACT_DECISION} in ["APPR"] and {PER_ACTION.PER_ACN_COMPL_DATE} in {?StartDate} to {?EndDate} then 1 else 0

You can then place this in the detail section, right click on it, and insert a summary (sum) to get the number of approved plans during the period. You could create similar conditional formulas to get the number of assigned plans during the period or the number on hold. If you have row inflation, you would need to use running totals instead.

-LB

 

[geestrong]

This solution works but I kicks out alot of the data. I just met with the end user and we decide to create a subreport.


Thanks