Formula for List of Active Employees in a Date Range 1

[VickieT]

Hello

I am using Crystal 9 and a SQL DB.

I have to create a report to show all active employees during the period August 13, 2004 and August 20, 2004. I have a table and field for the active status but I'm not reporting on a date field from the database just a time frame that I need to report on.

I only have {table.status} along with employees names and titles. Any suggestions on how to do this? If I haven't given enough info. please let me know. Thanks.

Vickie

 

[synapsevampire]

Obviously it's impossible to select a period when you don't have a date field, so you need to use a date field.

You'll get better results by posting:

Example Data (show the fields)
Expected output (using the example data)

In general you would create a date range parameter for the range of interest, then use Report->Edit Selection Formula->Record and place something like:

(
isnull({table.termdate})
or
{table.termdate} > maximum({?MyDateRange})
)
and
(
{table.hiredate} <= maximum({?MyDateRang})
)

Hard to guess when you don't post specifics. The status is almost meaningless as you need to know dates, not what their current status is. Perhaps if you have historical rows it will prove useful.

Note that you don't really need a range here, the upper date is the only important one, but you may need it if your data is stored differently.

-k

 

[VickieT]

Synapsevampire you are so AWESOME! You always come through in the pinch.

Example Data

{ESYAPPTR.status} = "A" (this will report on the employees who are active. The problem is that I am not reporting on any date field just a date range which doesn't correspond to any field.

My expected output would be:

Employee Name Employee Title

This would be the employees who were on active status during that time period.

I will try what you suggested though and see if that works. Thanks for your thoughts.

Vickie

 

[Turkbear]

Hi,
I'm confused..You indicate that
not reporting on any date field just a date range which doesn't correspond to any field.

If there is no date associated with the employee record, how, as Synapse stated, can you expect to use a date range to select records?

 

[VickieT]

Synapsevampire

I tried the formula you gave and it worked beautifully! Thanks so much for once again saving me on this one.

Vickie