Skip to Content
author's profile photo Former Member
Former Member

HR Report in BI for Headcount Turnover & Retention Report

Hi Gurus,

I need to create HR Report in Bex Analyser. The report is "Headcount Turnover & retention Report".

Report columns are:

Cost Center name || Headcount as of 1/1/09 || Num of New Hires (YTD) || Number of Terms YTD || Num of terms that were active on 1/1/09 (YTD) || Turnover % || Retention %

Where Turnover % is calculated bydividing total terms over total current headcount.

Where Retention % is dividing the number of those termed YTD who were active on 1/1/09 those were active on 1/1/09.

Headcound = Active employees(1/1/1900 to 1/1/2009).

New Hires = Newly hired after 1/1/09

Terms = who leaves company after 1/1/09.

I Created four restricted keyfigures

1).Headcount as of 1/1/09 with ( Number of employess, Calander day (1/1/1900 - 1/1/1009), Employee status [ Active]).

2).Num of New Hires with (Number of Actions, Calander day (1/1/2009 - Current Calander day variable),

Action type [New Hire]).

3).Number of Terms with ( Number of Actions, Calander day (1/1/2009 - Current Calander day variable),

Action Reason [ Leaving]).

4).No.of terms that were active on 1/1/09 with (number of Actions, Calander day (1/1/09), Action Reason (Leaving), Action Type (Active)).

In my result it is displaying all as active employess and i didn't see any terms. For headcount it is displaying multiple times for one employee. One employee should have only 1 in every field. But each employee having like 9, 17, 25.....etc. I checked in BI side there in cube content each employee has '1' in all fields. I don't know why it is taking multiple times.

Please can any one help me on this. Sorry for such a very long mail.

Thanks,

Aparna.

Edited by: Aparna Pyeddu on Feb 10, 2009 2:57 PM

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Feb 10, 2009 at 06:31 PM

    Aparna,

    Good to see a HR question after a long time. There could be several things that could go wrong in this report.

    First we will start with you did not see any terms:

    Reason terms are based on Number of actions and Actions are generally on period end dates. If you 01/01/2009 is not a period end date you wont have any action on it and it will not display any terms. I would suggest to research in Master Data for 0Employee with any person you know for sure is terminated.

    Second thing go back to business and ask if their retirement is to see terms on a particular date or Fiscal period end. (Terms are generally seen as in a Period range. eg from Period 01/2007 to 13/2007)

    Based on your requirement it seems that you need YTD calculations. YTDs are not that straight forward with a single restricted key figure.YTD for Hires term and Actives are different animals. It would take a long answer to solve your problems.

    Try these solutions even if yo do not get them. USE POSTING PERIOD and Fiscal Year instead of dates.

    Headcount avg actives YTD: you want to get actives from 01/01/2009 to the period entered by the user. Say 05/2009

    So what you want to is get actives as of 05/2009, 04/2009,03/2009, 02,2009 and 01/2009

    and add them up and divide them by 5.

    Terms YTD: Give a range: Period 1 to Period entered by user.

    Hires YTD: Give a range: Period 1 to Period entered by user.

    I know this not a straight forward and easy solution. but this is the way HR is. Let me know if you need further help.

    ~abhijit

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Abhijit,

      Thank you very much for fast reply.

      I don't have posting period and fiscal year in this cube. I have only

      - calender year/ month

      - calender year/ quar

      - calender day

      - calender month

      - calender year

      - quarter

      I need terms from 1/1/09 to current date. So I selected value range 1/1/09 to current day (variable). Same thing with New hires. I didn't get anything with this selection.

      For Headcount I need "1/1/1900 - 1/1/2009". The employess are displaying correctly with texts, But the number of employees are displaying wrong.

      Displaying result as I need result as

      "John" 17 "John" 1

      "Don" 10 "Don" 1

      Can you please help on this.

      Thanks,

      Aparna

  • author's profile photo Former Member
    Former Member
    Posted on Feb 10, 2009 at 08:44 PM

    Terms:

    01/01/09 to current should work. Please try to create a new query which has no filters in it and just put This one Key Figure with this rage and try to see what results you get for different dates after 01/01/2009

    Use dates at least after one period.

    There is a possibility that there are no terms during this time. Use the transaction LISTCUBE and try to find out if there is any data in the cube..

    If yo still do not get the data Hardcode it for 2008 and see if you get any data. Try the same for hires

    For headcount only use one date: That is current date entered via variable.

    Restrict to only variable. Do not give it a range. See if that works.I did not understand what was your problem with Headcount/

    Pelase check the Master Data for 0Employee. RSA1-> Ctr F find 0Employee and Maintain Master data for a personnel Number you know for sure is terminated/ hired within that date range and check the date of ACction. (Valid from and Valid to.)

    Note: Make sure exactly what they mean by Headcount as of 1/1/09. They probably want YTD and not as you are getting it from 1/1/1900 to 1/1/2009.

    Let me know if this doesn't help.

    Edited by: Abhijit N on Feb 10, 2009 9:45 PM

    Edited by: Abhijit N on Feb 10, 2009 9:45 PM

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi abhijit,

      Thanks for you reply on this.

      I checked in BI master data, everything is good. I created new query with out any filters for terms with date range Feb 2008 to Feb 2009. Nothing was happened. getting as "No applicable Data".

      For headcount I kept same selection. Two employees were selected. So I need to get Total no.of employees as "2". But in the reports it is displaying as "4" as Total and first employee as "9" and second one as "4" total "4", Where as first one passed away. But Passed employee displying as active in Employement status.

      You can find it below more clearly....

      Employee : Richard , Christopher

      Employement status : Active , Active

      Number of Employees : 4 , 9

      Number of Actions :

      Overall result : 4

      Where as Richard is now Inactive. Still showing Active. But in master data it is Inactive which is correct.

      I don't know why only bex is giving wrong data. Could you please help me on this.

      Thanks,

      Aparna.

      Edited by: Aparna Pyeddu on Feb 12, 2009 5:51 PM

  • author's profile photo Former Member
    Former Member
    Posted on Feb 24, 2009 at 08:18 PM

    In you query in the Characteristic properties you make it to go against only maser data. Try that out.

    Click on Characteristic, Proerties-> Filter Value Selection During Query Executio-> select the radio button "Values in Master data Table".

    Hope that works out.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Abhijith,

      Thank you very much for reply.

      You mean in Bex Query designer. I didn't understand where to do. Could you please explain me bit more clear.

      You said characterstic properties, May I know where they are exactly. I mean in Bex analyser/ query designer/ BI workbench.

      Thanks for your help.

      Thanks,

      Aparna.

  • author's profile photo Former Member
    Former Member
    Posted on Feb 26, 2009 at 07:32 PM

    Aparna,

    This option is present on the Query designer. Per my knowledge this option is only available in 7.0

    When you open the query. Select the percticular characteristic. To the right hand side there would be a properties window. In that wondow the advanced tab will have that option.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.