History Tracking dilemna

[NGonz]

Hi all,

I have a report that tracks the 'headcount' of people in different departments in different regions. Based on that different calculations can then be made. To find out this number I have two formulas (for every month of the year) that will theoretically determine what is the Active headcount and what is the InActive headcount. I then simply subtract the Inactive from the Active to get my final value.

To keep it simple, we have a Users database file that stores the current status of employees and a Transfers table that tracks when employees have changed location/assigned to, process/deptarment, or status (terminated, long-term disability, active, etc.).

The problem lies in the fact that employees tend to move around some and catching these is turning out to be harder than I thought (isn't that always the case). I'm using Crystal Decisions 8.5 with SQL 7.0. The reports is grouped by 'assigned_to' and then by 'processname'.

I have the Inactive Headcount formula for January (below) which seems to work fine. What I'm concentrating on currently is the 'Active' formula. I'm having a hard time (and I don't know if it's possible) getting the report to find past employees that used to belong in a certian process and then add that number to the affected month's value since the report is grouped by processname. Restated, I have no problem finding the prior status if a person has stayed in the same process group but trying to find out who USED TO belong in the processgroup is quite a different story.

Within the Users table the current status is maintained. Within the Transfers table we have a copy of all the employees with the current status as well as a 'base'. When changes are made, changes are made to both tables but the Transfers table lists the trans_date which is the date of the transaction and an edate field that will have a value IF the last status has changed. In the case that this record is the current info the edate field will have a null value.

==============================================
Inactive 'Salary' Headcount formula:
Local Stringvar endofmonth := "1/31/" + {@Current_YEAR};
if CurrentDate > CDate(endofmonth)
then
//looking for a closed status (edate)
((
{cccusers.userID} = {ccctransfers.frn_userID} AND {ccctransfers.trans_date} < CDateTime(endofmonth) AND
{ccctransfers.edate} >= CDateTime(endofmonth) AND {cccusers.paytype} = 'S')
AND
//that has one of these status changes
(
{ccctransfers.new_status} <> 'A' OR {cccusers.frn_locationID} <> {ccctransfers.new_frn_locID} OR
{cccusers.processname} <> {ccctransfers.new_processname} OR {cccusers.assigned_to} <> {ccctransfers.new_frn_locID}
))

OR

//looking for an open status (isNull (edate))
((
{cccusers.userID} = {ccctransfers.frn_userID} AND
CDateTime(endofmonth) >= {ccctransfers.trans_date} AND
{cccusers.STATUS} = 'S' AND
isNull ({ccctransfers.edate}
)
AND
//that has one of these status changes
(
{ccctransfers.new_status} <> 'A' OR {cccusers.frn_locationID} <> {ccctransfers.new_frn_locID} OR
{cccusers.processname} <> {ccctransfers.new_processname} OR {cccusers.assigned_to} <> {ccctransfers.new_frn_locID}
))
=====================================================

Any suggestions/comments are welcomed,
Thanks in advance,
NGonz

 

[kenhamady]

Can you give an example of the person you are having trouble finding, showing what his records would look like in both tables? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[idle]

Also note that If Then Else statements are in the form of
IF logical test THEN
some formula result
ELSE
some other formula result
I'm pretty sure that your formula, while syntactically correct, is not in this format.

 

[NGonz]

Thanks for the responses.

Ken here is a sample of the records I'm dealing with. I've only listed the pertinent fields:
======================================================
~~ CCCUSERS ~~
FIELD VALUE
lname Gonz
fname N
frn_locationID 2 //locations table- 2=Atlanta
processname 5 //process table- 5=Information Systems
paytype S //'Salary'
status A //'Active'
userID 3816 //'Autonumber' field
hire_date 1/25/99
term_date 2/15/01 //gets updated when CCCTransfers
//is updated - denotes a change
assigned_to 2 //similiar to frn_locationID

~~ CCCTRANSFERS ~~
FIELD VALUE
frn_userID 3816
trans_date 1/25/99 //The transaction date
edate //This field will be blank if the
//transaction is open/current. If
//there is a value here then the
//status is closed or is 'history'
trans_type 4 //denotes type of transfer - status,
//processname, location or assigned_to
old_frn_locID 2
new_frn_locID 2
old_processname 2
new_processname 5 //Currenlty looking for this change
old_status A
new_status A
=======================================================

Please note that the CCCTransfers table has a current 'status' record for every employee in the CCCUsers table. When an status change occurs the edate field is updated (now a closed record) and another record is created with an Null value denoting the new current status. In the example above another prior record would've had an open edate with a old_processname (and new_processname since this was a 'base' record) of 2. Once this 'processname' status change occured the edate field will have a date value and a new record will be created showing the new_processname to equal 5. The current formula I'm working on is dealing with 'processname' but will eventually need to find any historical status change.

Idle, thanks for the comment which reminds me to say this: My 'Inactive' and 'Active' formulas are not really formulas but actually Running Totals that I calculate in the Group Footer #2b section that is suppressed. They summary field being used is a distinct count on @Full_Name which is fname+lname. The resets occur on change of the Processname group. I may be wrong but I think you can have an IF-THEN without an ELSE.

My current 'Active' Running total isn't even worth showing here since we've had to make database changes and such for this problem which seems to grow the more I look at it. Thanks again for the response/help.

Sincerely,
NGonz

 

[kenhamady]

And how do you know, by looking at just these fields and values, that this is the record you want? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[NGonz]

Ken,

In the 'Inactive' running total this was easily defined as {cccusers.userID} = {ccctransfers.frn_userID} and I believe this would work since it would only calculate for people in the particular processname. All CCCUsers.UserID records have a duplicate ID in the CCCTransfers.frn_userID field.

As far as the 'Active' running total that is part of the problem. In the case of looking for an employee that used to belong to a certian process, I wish there was a way to do this: GroupName(proccessname) = old_processname but as you know you can't do this in a running total with a printtime formula. And processname = old_processname doesn't seem to work either. In either case I would be looking outside of the Group(processname) into other groups and taking from their totals and that's the part I'm not sure I can do.

Does that answer your question?

NGonz

 

[kenhamady]

I am afraid that I am going to have to bail out here. This is beyond the level of support that I can provide here in TT.

You could hire someone to take a more indepth look at it with you, or you can simplify the question and repost it.

Good Luck. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[NGonz]

Let me try this another way - As an example here's a visual:

GH1 - assigned to
GH2 - proccessname
====================
GF2a
Jan Feb Mar Apr
Atlanta
HR 5 4 4 (@Active-Inactive)
MI 10 11 11
========================
GF2b (suppressed)
Jan Active etc.
5
Jan Inactive etc.
0
=======================

Basically I want to run the report in i.e. April and catch past status changes (change in status, location, assigned to, or processname).
Does that help simplify? If not I understand this has been driving me nuts.

Sincerely,
NGonz

 

[kenhamady]

Sorry, it is the time involved in trying to understand your data structure that is making this into a consulting project as opposed to a technical question. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/