BO and PeopleSoft

[JBC2002]

Hello,

I am trying to create a report in Business Objects. PeopleSoft is our HR platform. I want to creat a report based on our Job history Universe. Specifically, I have built a report to pull all of the promotions for a given time period. I would like to add a "field" that will show me the previous salary, level, etc. of the employee in the same row of information. Example.

Pro 12/09/2002 Doe, John 3(new level) 2 (old level)

I'd appreciate all the help I could get, this issue has vexed the best people here for years, and frankly I want to find a solution.

 

[blom0344]

Hello JBC,

I think that the solution to your problem will be based entirely on the way you store your data. Does every employee have a unique identifier?. Is there a counter that increments when a person goes to another level (i.e. does a new record come into existence when level or salary changes)
Is there a timestamp or date so we can order the data related to one person and get the most recent entry (and then use a second query to get the record just before the most recent one) If we can do the latter, then using 2 linked dataproviders will get you what you want.
If you have a small testset we can have a look at it (in the forum or directly through the mail) T. Blom
Information analyst
tbl@shimano-eu.com

 

[JBC2002]

T. Bloom:

Thank you for your reply. I have an email in to our team, but here are the answers I know.

Each person does have a unique id. I was thinking about using the actual effective date of the movement transaction (promootion in this case) as the date you mentioned in your last question. I am not sure about your counter second question.

If I follow you and think about your last point. I will start with the universe that has the movement data, pull the movement I need, link that universe back in, create a second query to create a new "field" and for each record identify the HRID and the effective date and then pull in the reulting "field" I choose based off of the last transaction.

I pose another couple of questions, and I hate to use your experience, but thank you.

1) Using your second option, I do not know how to create a formula that will look at the preceeding transaction and pull back in the requested field. Could you help - or direct me to a good source

2) What if the preceeding transaction was a set up transaction for the promotion (transfer first per se) and the level is still the same. I do have to account for the fact that the preceeding transaction was the same level?

3) Thank you again for your help.

JBC

 

[blom0344]

Hello JBC,

I do not think that pulling queries in and out of universes is the way to go with your problem (apart from the fact that basing off queries like you describe is possible at all)
I imagine that you run Peoplesoft on one of the major RDBMS platforms (ORACLE,DB2,SQLserver etc)
The latest types of these have so-called analytical functions available. I work with both ORACLE and DB2, but in my case the ORACLE version is a bit too old, so the example i give is based on DB2.
I imagine a table with persons based on their id. Stored in this table are level and salary. Everytime there is a mutation a new entry in the table is made. The transaction-date is recorded in the table and to keep a unique key, some kind of incremental number is used (unless the date is a timestamp, which makes the record unique)
Suppose you are interested in the last two records per person in a general sense.

Table:
person_id mut_date level salary


SELECT * FROM
(SELECT
PERSON_ID,
LEVEL,
SALARY,
RANK() OVER(PARTITION BY PERSON_ID
ORDER BY MUT_DATE DESC) AS R1
FROM TABLE
WHERE .........) AS XXX
WHERE R1 < 3
ORDER BY PERSON_ID,MUT_DATE

will give you the latest 2 records for a given person. With 2 mutations on the same day, this will obviously not give correct results. So if you have an incremental number to work with, you use that one instead of the date.

If you create a database view (or materialized table with ORACLE) you can add it to your universe and use the R1 field for report purposes. T. Blom
Information analyst
tbl@shimano-eu.com