Alternate Select for rare conditions

[BiJae]

I have a situation that I wanted to run by y'all to see if my logic is correct. First let me describe the business setting. We have examiners that file reports on stores annually. A report is filed and it becomes the "Active Report." This report remains active until the next report is filed on that store.

I built a report using Crystal XI to show our "problem stores" those with a COMPOSITE rating of 3 or more using the following formula:

{build.ACTIVE} = "Yes" and
{build.REPORT} = "RE" and
{build.COMPOSITE} >= 3 and
{build.RECDATE} < {?Begin}

ACTIVE is a yes no field
REPORT is a report type criteria
COMPOSITE is a numeric value to rate the store
RECDATE is the date the report was received.

This report works well for capturing everything that was received before the parameter date {?Begin} with a Active=Yes. I first ran into a problem when a new report came in for a store after the {?Begin} date criteria. When this new report was received the Active report became Inactive and the flag was set to "NO".

In order to capture that report that "fell off" I was thinking about modifying the database schema to include a "DateSetInactive" field to be automatically dated for the date the new report was received. I was thinking that this would allow me to modify the current report to select as described above, OR select where ACTIVE = "NO" AND SetInactive < 30 days in addition to the other criteria.

Would this be possible? If so any suggestions on how to creat the select statement to properly perform the OR condition?

Thank you for your help. Please let me know if further expliation is required.

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[RobbieB]

Is {?begin} a parameter entered by the user? Where does that date get set? Do you want to include the reports submitted on the [?begin} date?

 

[lbass]

If what you want is always the most recent report less than {?begin} (active or not), then instead of using the active criterion, you could use a record selection formula of:

{build.REPORT} = "RE" and
{build.COMPOSITE} >= 3 and
{build.RECDATE} < {?Begin}

...and then go to report->selection formula->GROUP and enter:

{build.RECDATE} = maximum({build.RECDATE},{table.store})

...assuming that you have a group on a store field.

-LB

 

[BiJae]

Thank you for your replies.

First, yes the {?begin} date is the parameter entered by the user when the report loads.

I solved this problem another slightly varied way. I used an order of operations to deliver the information that I wanted. I've copied the code below hoping that it may help some one else in the future.

{build.REPORT} = "RE" and
(
   (
    {build.ACTIVE} = "YES" and
    {build.RECDATE} < {?SelectDate}
   ) 
  or
   (
   {build.ACTIVE} = "NO" and
   {build.InactiveDate} in {?SelectDate} to currentdate
   )
)

Thank you again for your assistance!

CHEERS

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart