Assigning a count to a previous record

I have a formula that counts on each employee and their department grouping, one of the counts (the last in red) I would like to assignment the count to the previous department and not the current department field in the last if, then, else statement.  How would I go about doing this?

WhilePrintingRecords;
NumberVar CountTransfers;
If OnFirstRecord then CountTransfers := 0  else

If {EMSH_SAL_JOB_HISTORY.ACTION_DATE} in {@DateStart} to {@DateEnd}

then

if {EMSH_SAL_JOB_HISTORY.ACTION_TYPE} = "LOA" and
   {EMSH_SAL_JOB_HISTORY.SEQ_NUMBER} = 1 and
   Previous({EMSH_SAL_JOB_HISTORY.DEPT}) = {EMSH_SAL_JOB_HISTORY.DEPT}
   then CountTransfers := -1
else
if {EMSH_SAL_JOB_HISTORY.ACTION_TYPE} = "RFL" and
   {EMSH_SAL_JOB_HISTORY.SEQ_NUMBER} = 1 and
   Previous({EMSH_SAL_JOB_HISTORY.DEPT}) = {EMSH_SAL_JOB_HISTORY.DEPT}
   then CountTransfers := -1
else
if ({EMSH_SAL_JOB_HISTORY.ACTION_TYPE} in "XFR" and
   {EMSH_SAL_JOB_HISTORY.ACTION_REFER} = "JDP") and
   {EMSH_SAL_JOB_HISTORY.SEQ_NUMBER} = 1 and
    {EMSH_SAL_JOB_HISTORY.DEPT} <> Previous({EMSH_SAL_JOB_HISTORY.DEPT})
    then CountTransfers := -1

else 0
GrapeladyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
I don't understand what you are trying to do?

Can you explain it in words?

How are you counting?

How is this formula used?

mlmcc
0
GrapeladyAuthor Commented:
I want to be able to assign the count to a previous department code.  The main formula is Count-Transfers Out (Calc). Currently the count is on the current department and not on the previous department.  The salary history table I am using to pull the information from the action is occurring on current record, but change is on previous record.  So was thinking is there a way to assign the count to look at the previous record.  Currently I have Calc, Display and Reset formulas.  I have attached the report.  If you look at the detail line, Dept 500 and 510, EE# 1435, I want to see the -1 on Dept 500.  The formula is looking at various scenarios, the 1st 2 are working, the last scenario I would like to somehow assign the "then CountTransfers := -1" to Previous(Field Department). Is this possible?
Turnover-Report---Employee-Activ.RPT
0
PortletPaulfreelancerCommented:
What version of MS SQL Server are you accessing?
Are you using a custom sql query in your report? (I can't open the .rpt file)

(if using a query can you paste that here as a code block please)
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

James0628Commented:
The formula in your first post is {@Count-Transfers Out (Calc)} ?

 I could be wrong, but I rather doubt that that formula is doing what you think it's doing.  Previous just looks at the previous record.  If you use that formula in the detail section and the report is grouped by dept and then emp, then Previous({EMSH_SAL_JOB_HISTORY.DEPT}) = {EMSH_SAL_JOB_HISTORY.DEPT} will always be true, except on the first record for the first employee, and _only_ the first employee, in each dept.  That is, assuming that there is only one UNIT_NAME for each DEPT (since you group on a combination of those two fields), so there is only DEPT value in each group 1.

 I suspect that you're thinking of that as "previous DEPT for this emp", but it's not.  It's just DEPT from the previous record, which will, by definition, be the same for every record in the group, so the previous value is only different for the very first record in the group (where Previous gives you the dept from the last record in the previous dept group).

 Maybe I'm wrong and you're doing exactly what you want to do there, but all that you're really doing with that Previous (DEPT) check is identifying the first employee in each group 1.  Unless, like I said, a single group 1 can include more than one DEPT value.

 James
0
GrapeladyAuthor Commented:
So should I delete the statement "If OnFirstRecord then CountTransfers := 0  else" in the formula and then it would reflect previous department per employee?
0
James0628Commented:
That just sets the CountTransfers variable to 0 when it's the very first record in the report, but the variable is going to be 0 at that point anyway, unless you set it to something else before that formula (eg. in the report header).  IOW, "If OnFirstRecord then CountTransfers := 0" doesn't really do anything, except keep the rest of the conditions (everything after the Else) from being evaluated on the very first record in the report.

 Previous is always going to give you the value from the previous record, period.  If the report is grouped by dept and then emp, then Previous won't (can't) give you the previous dept for an employee (if that's what you're after), except when there are no other emp's in between two records (eg. the last emp in one dept is also the first emp in the next dept).

 Say the report included:
 DeptA
   Emp1
   Emp3
   Emp4
 DeptB
   Emp2
   Emp4

 Previous (Dept) on the Emp2 record will give you DeptA, from the last record in the DeptA group (which was for Emp4).
 Previous (Dept) on the Emp4 record in the DeptB group will give you DeptB, from the Emp2 record that's right before it.

 If that still doesn't make sense, I suggest that you put just the Previous function (with DEPT or whatever field you're interested in) in a formula and put that in a detail section and see if it's giving you what you expect for each record.


 Using the example above, if you actually want to know that Emp2 has no previous dept, and that the previous dept for the second Emp4 record is DeptA (because Emp4 is also in DeptA), then you're going to need something else.  One option would be to save the last dept for each emp in a variable.  But there are limits on that.  How many emp's could there be on a report?  Using a subreport might be another option (although it might be quite inefficient).


 And if I've misunderstood what you're trying to do, then please explain.

 James
0
GrapeladyAuthor Commented:
Thank you for the insight on the formula and the way report is searching and reporting the information.  The formula Count-Transfers Out (Calc) is on the detail line and the display and reset are based on the grouping for Last, First names and the employee number, which is a unique value.  So if I am understanding what you are saying since the department is the first grouping and the employee the 2nd grouping then the formula will look at dept first and then the first employee in the 2nd grouping.  So if I introduce something like Emp1 # = Previous(Emp 1 #) will the search be looking per employee per dept?
0
PortletPaulfreelancerCommented:
What version of MS SQL Server are you accessing?
Are you using a custom sql query in your report?

BTW: If you have no interest in using a SQL query for this please let me know and I'll remove the unnecessary topic.
0
James0628Commented:
> So if I am understanding what you are saying since the department is
 > the first grouping and the employee the 2nd grouping then the formula
 > will look at dept first and then the first employee in the 2nd grouping.

 Not exactly (if I follow what you're saying).  The formula isn't really looking at the grouping at all.  What matters is the order of the records.  In this case that's determined by the grouping, but the results from the Previous function would be the same if you were sorting by dept and then by emp, with no groups at all.


 > So if I introduce something like Emp1 # = Previous(Emp 1 #) will the
 > search be looking per employee per dept?

 I'm not sure what you mean by "per employee per dept", but the Previous function doesn't care, or even know, about your groups.

 If the record right above that on the report was for the same EMP (maybe you have multiple detail records for each EMP within the same DEPT), then EMP = Previous (EMP) will be true.

 If you only have one record for any given EMP within each DEPT, then EMP = Previous (EMP) will probably be false most of the time.  It will only be true if the first EMP for a DEPT is the same as the last EMP for the DEPT right before that (IOW, you happen to have two records in a row with the same EMP).

 James
0
GrapeladyAuthor Commented:
This is a Crystal XI only, remove the MS SQL Server from the question. I cannot do it.
0
GrapeladyAuthor Commented:
To James, so basically what I am asking for is not possible?  I will have to report on the department where the employee is currently, and not capture the department they were previously?
0
mlmccCommented:
Is the information in the database on where they came from?

May be able to use a subreport or a command so that information is available as a separate record.

mlmcc
0
GrapeladyAuthor Commented:
Yes the database has sequential records based on action/expiration dates and action types for employee history.  So if the employee had a department change from one record to another, I can capture the previous record?  I have not done something like this with a subreport or a command, familiar with them but have not actively used them in this manner. Please advise.
0
James0628Commented:
As I see it, your basic problem is "connecting" the records for each employee.  With the report grouped, or sorted, by department and then employee, you have a record for employee 123 in department ABC, and then records for other employees in that department and other departments, and then another record for employee 123 in department XYZ.  You can't use Previous (department) on that last record to get department ABC from the first record.  You just get the department from the record right before that one, which would be for some other employee.

 How many different employees could you have on one report?  More than 1000?  One option might be to save the last department for each employee in an array, but CR arrays are limited to 1000 elements, so anything more than that gets more complicated.


 The basic idea behind the subreport would be to have it read the same basic data, and get the previous department, if any, for the current employee.  Which means that the subreport would presumably need to be put in the detail section, so that it's run for every record.  That will be very inefficient, but depending on how much data you have, and any other requirements, you may be OK with it.

 Using the example above, when the main report got to the record for 123 in department XYZ, the subreport would look for any records for 123 in another department before XYZ, and find the record in department ABC.  This means that the subreport needs to be able to tell which department(s) came before the current one in the main report.

 Which brings up another question.

 When you're trying to check to see if an employee was in another department, what are you really looking for?

 Are you looking for people that moved from one department to another?  If so, the order would seem to be important.  Were they in department XYZ before and now they're in department ABC, or was it the other way around?

 Or are you just looking for people that have records in more than one department, and it doesn't really matter whether they went from ABC to XYZ, or vice versa, or are in both at the same time?

 James
0
GrapeladyAuthor Commented:
Going back the original question:
Employee 123 is in department ABC
Then has a new record created and the employee is moved to department XYZ
I wanted to be able to do a -1 count on the previous department to show the employee moved out of that department.

But the explanation is I cannot do it because of the groupings.

if ({EMSH_SAL_JOB_HISTORY.ACTION_TYPE} in "XFR" and
   {EMSH_SAL_JOB_HISTORY.ACTION_REFER} = "JDP") and
   {EMSH_SAL_JOB_HISTORY.SEQ_NUMBER} = 1 and
    {EMSH_SAL_JOB_HISTORY.DEPT} <> Previous({EMSH_SAL_JOB_HISTORY.DEPT})
    then CountTransfers := -1

If I were to do the subreport, how would the incorporation of the count be setup on the main report?

Or how would an array be setup to do this?  Would it be a separate formula and then placed into this Count=Transfer Out formula?
0
James0628Commented:
The array idea that I had in mind would only work to do something like put a +1 in the second department (XYZ).  It would not allow you to put a -1 in department ABC.  The problem is that variables are set as the records are read, so when the report reads the record in department ABC, it doesn't know that the same employee is also in department XYZ.

 If you'll never have more than 1000 employees on a single report, then an array still might work (just not in the way that I was originally thinking).  You could have a subreport in the report header of the main report that would read all of the data and save the department for each employee.  Basically, one array would have a list of employees and then another array would have the department for each of those employees.

 But how many departments do you need to store for each employee?

 In order to show a -1 in department ABC, you would need to store the last department for that employee (XYZ) in the array, so that the report would know that ABC was not their last department.

 But if you also need to produce a specific value in department XYZ because that employee was also in department ABC, then you'd also need to store their first department (ABC), or some kind of flag, so that when the report got to department XYZ, it would know that the same employee was in an earlier department (ABC).

 Could an employee be in more than 2 departments?  Maybe they were in department ABC, then moved to XYZ, and then moved to GHI.  If they could be in more than 2 departments, then you may need more than a "first department" and "last department" for each employee.


 And how do you define "previous department"?  In the example, they were in department ABC and then moved to XYZ, but what if it was the other way around?  That would make ABC their final department and XYZ is the previous department, but if the report is grouped/sorted by department name, then ABC will be seen as the previous department, because it's earlier in the report.

 Maybe you don't really care about which department an employee was in first or last and are just interested in the fact that they were in more than one department.  But if you need to know that XYZ was actually the previous department, not ABC, and the report is sorted by the department name/ID, then you'll need something other than the position on the report to determine which department was "previous".  A date or sequence number or something.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrapeladyAuthor Commented:
James, I think I will just keep with the department the employee is currently in.  Thank you for all your advise and thoughts on this.
0
James0628Commented:
So, do you need any more help with this, or are you just going to stick with what you have?

 James
0
GrapeladyAuthor Commented:
I am going to try another way of tracking the transfers, so I don't think I need further help on this question.  Again thank you for your insight on my options.
0
James0628Commented:
No problem.

 James
0
GrapeladyAuthor Commented:
all good thoughts, but I don't believe I found the solution I was looking for.  Thank you again James.
0
James0628Commented:
You're welcome.  Unfortunately, at least as I understand it, it's a difficult situation.  The simplest thing, from the report's POV, would probably be if you could modify the data that's coming into the report.  For example, use a stored procedure or a CR Command to gather the raw data and manipulate it a bit.  In the simplest case, maybe add a "previous department" field (however you define "previous department").

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.