Note that this case study applies to the Lawson application but just as easily could be applied to any custom report question.

A client of mine recently gave me an interesting reporting request: Does Lawson have a standard report detailing any changes in employee status and the personnel actions (excluding rehires and new hires) associated with them?

The answer was no.

You know the next question: Can you write me a report detailing any changes in employee status and the personnel actions (excluding rehires and new hires) associated with them? And, while we’re at it, can that report have a date range? And can you exclude the terminated status?

That was my task. My tools: Crystal Reports XI and Lawson Reporting Services (LRS). Now, normally this wouldn’t be so hard. For those of you familiar with the Lawson tables, you know that status (if set to track history on HR10) is located on the HRHISTORY table (FLD_NBR = 20). This report request, however, had a little wrinkle that made it a bit more challenging than normal: some personnel actions may have had status changed on the employee and some may not. In other words, one couldn’t always say that the POSITION personnel action always had status changed.

There was one more little wrinkle: all employees were loaded into Lawson via the HR511, not via PA52.4. In other words, employees’ initial HRHISTORY status value was not tied to a personnel action. In technical terms, you couldn’t just join EMPLOYEE to PERSACTHST to HRHISTORY to get the desired results. Even as outer joins, the report would not work. I would have to get creative.

First Things First: Group by Employee

I began building my report with the EMPLOYEE table. To make this report work, I first need to group this by employee. This step is necessary to enforce the date range (as we will see later), as employees may have had status date changes over the last five years but an end-user may only want to see them from the last pay period.

Using the group expert, we can accomplish this:

Note that this report also has a department group.

Joining Tables

The remaining tables for this report are: PERSACTHST, HRHISTORY, and DEPTCODE.

Note that I did not build this report using the Lawson OLEDB Connector. HRHISTORY is a very, very large transactional table with hundreds of thousands of records (and growing). I used a SQL OLEDB connection which does not enforce Lawson security. This was not an issue for the client.
I then joined the tables as follows:

The order of the joins is imperative here. By going from EMPLOYEE to HRHISTORY (with FLD_NBR = 20) in the Select Expert as an inner join, I am only pulling employees who have had status changes. The left outer join from HRHISTORY to PERSACTHST, in English, means that the report will retrieve status change values whether they were part of a personnel action or not.

Now that I have the tables joined properly, I can continue building my report.

The counterpart to the employee group is a “maximum effective date” calculation:

I then put the maxdate formula in the Employee group header:

Filtering Data with the Select Expert

I restricted the dates by adding a parameter and referencing that parameter in the select expert:

I tied this parameter to my maxdate calculation so this report can be run to only pull employees who have had status changes in the past X weeks.

Since I am only interested in changes in status, I added a count of BEG_DATE in the employee footer.

I then set that count greater than 1 in the select expert. Remember, I don’t want to see employees with only one value in HRHISTORY for status; they couldn’t have had a change! This is the case because history was loaded via HR511 (Lawson’s employee conversion program.)

I was only interested in three cases (LEAVE personnel action, POSITION personnel action, and “no” personnel action for history changes). The client didn’t want terminated statuses include or REHIRE and HIRE personnel actions. To accomplish this, I had to use a slightly involved “OR” statement in the Select Expert:

(EMPLOYEE.EMP_STATUS} in [“01”, “02”, “03”, “04”, “05”,”06″]
AND {HRHISTORY.FLD_NBR} = 20
AND {HRHISTORY.A_VALUE} <> “08”
AND (ISNULL ({PERSACTHST.ACTION_CODE}) OR {PERSACTHST.ACTION_CODE} IN [“POSITION”, “LEAVE”]))

Results

Running the report gives me the following prompt:

and the following results:

Voilà!