Generating custom reports for Human resources

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2

The tables in this topic provide examples of common questions you might ask, and the measures and dimensions in the data cube for Human Resources that you can use to answer those questions.

After the data cube has been deployed and processed at least one time, you can use Microsoft Excel, Microsoft SQL Server Analysis Services, or other tools to create custom reports based on the data in the cube. As you become more familiar with the data cube, you’ll be able to find answers to more and more of your questions.

If you have Power View configured, you can also use the Analyze data button in the Workers list page to access the data cube. Power View provides quick and easy visualization of your data. When you use Power View, you can access the same measures and dimensions you would access by using Excel or SQL Server Analysis Services. After you select the dimensions and measures, you can quickly visualize raw data, adjust it to make multiple charts that correspond to each other, and save the charts to a library. You can access that library in a list format or place any chart from the library into any Role Center.

Tip

The data in the custom reports will be accurate as of the last time the cube was processed. To keep the data current, the cube should be processed frequently, for example, each night.

For more information about how to create custom reports from the data in a cube, see Create a report by using Power View to connect to a cube, Create a report by using SQL Server Report Builder to connect to a cube, or Create a report by using the Excel data connection wizard to connect to a cube.

Note

This topic does not apply to versions of Microsoft Dynamics AX 2012 prior to cumulative update 7 for AX 2012 R2.

Demographic information

Questions to answer	Measures and dimension attributes to use
How much of the workforce is female versus male?	The Number of workers measure in the Workers measure group The Gender dimension attribute under the Worker details dimension
Which ethnicities are represented at our organization?	The Number of workers measure in the Workers measure group The Ethnic origin dimension attribute under the Worker details dimension
What are the ages of our employees?	The Number of workers measure in the Workers measure group The Birth date dimension attribute under the Worker details dimension
Are employees who were hired more than 10 years ago being promoted?	The Number of workers measure in the Workers measure group The Worker.Worker - Name dimension attribute under the Worker dimension The following dimension attributes under the Employment dimension: Employment start date Assignment start date Assignment end date The Position description dimension attribute under the Positions dimension Tip To see all of the positions that a worker has had, see the Worker position assignments table later in this topic.
What are the names of workers in particular job types and departments?	The Number of workers measure in the Workers measure group The Worker.Worker - Name dimension attribute under the Worker dimension The Department - Name attribute under the Departments dimension The Job type attribute under the Jobs dimension

Headcount, tenure, and turnover

Questions to answer	Measures and dimension attributes to use
What is our total headcount?	The Number of active workers measure in the Employment turnover measure group The Company dimension attribute under the Company dimension
How many hires did we have this month, quarter, or year?	The Number of hired workers measure in the Employment turnover measure group The Employment end date.Month, Employment end date.Quarter, or Employment end date.Year dimension attribute (Optional) The Worker.Worker - Name dimension attribute under the Worker dimension (Optional) The Company dimension attribute under the Company dimension (Optional) A filter for the Employment end date.Year to equal a specific calendar year Tip Instead of using the employment start date, if you use the Employment end date dimension attributes with the Number of hired workers measure, the count displayed is the number of workers hired during the specified period.
What are our turnover percentages this month, quarter, or year?	The Employment turnover rate measure in the Employment turnover measure group The Employment end date.Month, Employment end date.Quarter, or Employment end date.Year dimension attribute
How many employees are leaving versus staying at our organization? What are the reasons employees are leaving?	The Number of workers terminated measure in the Workers measure group The Termination reason dimension attribute under the Employment details dimension
How many people are in each department?	The Number of workers measure in the Workers measure group The Department - Name attribute under the Departments dimension
Is turnover higher at different legal entities?	The Employment turnover rate measure in the Employment turnover measure group The Company dimension attribute under the Company dimension
How many employees have worked at the organization under six years, from 6 to 10 years, from 11 to 15 years, and more than 15 years?	The measure with the years of service you want in the Workers measure group, for example, Workers with years of service 0-5 count (Optional) The Worker.Worker - Name dimension attribute under the Worker dimension (Optional) The Company dimension attribute under the Company dimension (Optional) The Department – Name dimension attribute under the Departments dimension
How many months has a particular worker worked here?	The Number of months of service for a worker measure in the Workers measure group The Worker.Worker dimension attribute under the Worker dimension

Skills, competencies, and training

Questions to answer	Measures and dimension attributes to use
What skills does a particular worker have?	The Count of workers measure in the Competencies measure group The Worker.Worker - Name dimension attribute under the Worker dimension The Skill dimension attribute under the Skills dimension
What education does a particular worker have?	The Count of workers measure in the Competencies measure group The Worker.Worker - Name dimension attribute under the Worker dimension The Education disciplines and Description dimension attributes under the Education dimension
What are the names of the courses that we track at our organization?	The Count of workers measure in the Competencies measure group The Course description dimension attribute under the Courses dimension
What are the training courses that we are currently conducting?	The Count of participants with training status in the Course participants measure group The following dimension attributes under the Courses dimension: Courses Course description Status
What are the names of the instructors and students for each course?	The Count of participants with training status in the Course participants measure group The Instructor dimension attribute under the Course instructors dimension The Participants dimension attribute under the Course participants dimension
What is the status of each possible course attendee?	The Count of participants with training status in the Course participants measure group The Status dimension attribute under the Course participants dimension
Which courses have started and ended this year and last year?	The Count of participants with training status in the Course participants measure group The following dimension attributes under the Media dimension: Course start date.Date Course end date.Date

Recruitment projects, applications, and applicants

Questions to answer	Measures and dimension attributes to use
What are the recruitment projects that we are currently recruiting for?	The Count of applications measure in the Applications measure group The following dimension attributes under the Recruitment projects dimension: Recruitment projects Recruitment project description Open date Close date
What are the names of the recruiter and hiring manager for the recruitment projects that we have open or that are completed within the past two years?	The Applicants hired count measure in the Applications measure group The following dimension attributes under the Recruitment projects dimension: Recruitment projects Recruitment project description Open date Close date The Hiring manager.Worker dimension attribute under the Hiring manager dimension The Recruiter.Worker dimension attribute under the Recruiter dimension Tip The hiring manager is often the new hire’s manager. If hiring managers are specified, they appear in the Hiring manager field in the Recruitment project form.
What is the trend over the past 10 years for the average days to recruit?	The Average days to recruit measure in the Recruitment projects measure group The Applicant employment start date.Year dimension attribute under the Applicant employment start date dimension (Optional) The Recruitment projects dimension attribute under the Recruitment projects dimension
Did it take longer to recruit for certain positions or jobs?	The Average days to recruit measure in the Recruitment projects measure group The Position dimension attribute under the Positions dimension or the Job dimension attribute under the Jobs dimension
Did it take longer to recruit if a particular recruiter was assigned?	The Average days to recruit measure in the Recruitment projects measure group The Recruiter.Worker dimension attribute under the Recruiter dimension
Did it take longer to recruit if a particular hiring manager was involved?	The Average days to recruit measure in the Recruitment projects measure group The Hiring manager.Worker dimension attribute under the Hiring manager dimension
Did it take longer to recruit for the same position in one year versus another year?	The Average days to recruit measure in the Recruitment projects measure group The Position dimension attribute under the Positions dimension The Applicant employment start date. Year dimension attribute under the Applicant employment start date dimension
Did it take longer if the project involved recruiting for multiple openings?	The Average days to recruit measure in the Recruitment projects measure group The following dimension attributes under the Recruitment projects dimension: Recruitment projects Number of openings
How many applicants have we hired this year, last year, and in previous years?	The Applicants hired count measure in the Recruitment projects measure group The Recruitment project description under the Recruitment projects dimension The Applicant name dimension attribute under the Applicant dimension The Applicant employment start date.Year dimension attribute under the Applicant employment start date dimension
How many applications did we receive today, this week, or this past month?	The Count of applications measure in the Applications measure group The following dimension attributes under the Applications dimension: Date of receipt Applications (Optional) The Applicant name dimension attribute under the Applicant dimension (Optional) The Recruitment project description under the Recruitment projects dimension
How many recruitment projects are currently interviewing?	The Count of applications measure in the Applications measure group The following dimension attributes under the Applications dimension: Application status Applications The following dimension attributes under the Recruitment projects dimension: Recruitment projects Recruitment project description (Optional) The Applicant name dimension attribute under the Applicant dimension
How many applications do we receive from particular online job sites, newspapers, or trade magazines?	The Count of applications measure in the Applications measure group The following dimension attributes under the Media dimension: Media Description Media type Tip Both None and Unknown mean that there is no media or media type for that application.
How many applicants are subsequently hired from each of those media sources? Have particular media sources had a trend of producing more or fewer hires over the past 10 years?	The Applicants hired count measure in the Recruitment projects measure group The Media dimension attribute under the Media dimension –or– The Applicants hired count measure in the Recruitment projects measure group The Recruitment project description under the Recruitment projects dimension The Applicant name dimension attribute under the Applicant dimension The Applicant employment start date.Date dimension attribute under the Applicant employment start date dimension The Media dimension attribute under the Media dimension

Worker position assignments

Questions to answer

Measures and dimension attributes to use

What are the active and past positions that a particular worker has held at our organization?

The Number of position assignments measure in the Workers measure group The Worker.Worker - Name dimension attribute under the Worker dimension The following dimension attributes under the Positions dimension: Position Position description Is position worker assignment active The value of the Is position worker assignment active dimension for a current position is Yes, and for a past position is No. Tip Under the Positions dimension, there are two dimension attributes for the position ID, Position and Positions. Position shows distinct values while Positions shows duplicates. When you create a report by using measures and dimensions, it doesn’t matter which one you use; both attributes produce the same results.

Tips

• Some dimensions, such as those related to benefit deductions and contributions, do not contain date attributes. For these dimensions, you can’t answer questions about what a measure was during a particular time period, or how it changed over time.

• When you work with the Worker details dimension and the Positions dimension, you see attributes called ValidFrom and ValidTo. Use these dimension attributes when you analyze date-effective information about positions and workers.

  To see which worker details are date effective, click the Maintain versions button in the Worker or Position form to open the date manager form. All these field values have an effective date and an expiration date, which are reflected in the cube as ValidFrom and ValidTo.

• Under the Employment dimension, you can use the Is position detail active and Is position worker assignment active dimension attributes that are set to Yes to help you view date-effective data. These attributes filter out data that is out of date. For example, if you use the Number of employments measure together with the company, department, and worker dimensions, you might see a count of 11 instead of 7 because a worker might have worked in one of the positions in a specific department in the past. The past position adds to the count unless you set Is position worker assignment active to Yes.

• A worker can have more than one position assignment and can work in more than one company at the same time. As a result, the Number of employments measure and the Number of position assignments measure in the Payroll workers measure group give different results when they are used with the Company dimension. The Company dimension attribute represents the legal entity. When you use it with the Number of employments measure, it shows the number of past, present, and future workers in the legal entity. When you use it with the Number of position assignments measure, it shows the total number of past, present, and future position assignments that have been assigned to workers in a company.

• When you work with demographic information such as gender, you might wonder about the difference between Unknown and None. Gender is an optional field in the HcmPersonPrivateDetails table, so if a record exists for that worker in that table and this field is not set, it shows as None. Unknown is displayed if there is no record in that table for that worker. You can use filters to keep these from being displayed.

• Under the Worker dimension, there are two dimension attributes for the names of workers, Worker.Worker and Worker.Worker - Name. The Worker.Worker attribute shows duplicates, and the Worker.Worker - Name attribute shows distinct values. When you create a report by using measures and dimensions, it doesn’t matter which one that you use; both attributes produce the same results.

• As you work with dimensions, sometimes you see an IsNotApplicable dimension attribute. This attribute is added to every dimension because one row always is retained for records that are not applicable. If a measure contains any values that are not in the dimension, the measure is designated as IsNotApplicable.