Custom Reporting -- Creating an Establishment Report - 4.0.9

This tutorial applies to version 4.0.9 and later of iHRIS Manage. To see this tutorial for different versions of the software see the following:

For other versions, see Creating an Establishment Report (versions) .

. We will also be adding some aggregating functions to the relationship which are a new topic introduced here.

In this tutorial we discuss how to make an establishment report. This report will show the total number of current staff by cadre and facility against a set establishment.

You may wish to read the instructions on setting an establishment before proceeding.

For the purposes of this tutorial, we will assume that we are interested in the "Staffing Norm" establishments for the year 2010.

Note: In this relationship, we are assuming that you have set the establishment/staffing norms consistently on (job,facility).

Overview

We first start by creating a variance relationship. We will assume that you are already familiar with creating custom reports, for example as in Creating A Staff List.

Next we will need to select the fields we wish to include in the report.

Finally we will create a report view to display the total positions against the establishment.

In creating this relationship, it would be helpful to know the differences between JOIN, LEFT JOIN and RIGHT JOINs in SQL. Here are a few places you can read:

• join explanation
• left vs right join

Creating the Base Relationship

We are only interested in the current positions in the system. We create a new form relationship as follows:

• Create a new form relationship called "staff_norms_2010" with the primary form "establishment"
  • Now limit the "establishment" form, so that the the "establishemnt_period" field equals "Staffing Norm - 2010" or whichever establishment you wish to work with.
  • Under the "establishment" form we will join in the "facility" form linked by the "establishment+location" field.
    Note: It is important that this form is joined as a Left Join and to allow multiple joins. In this way we get a copy of the appropriate establishment form for each "facility" in the system.
  • Under the "establishment" form we will join in the "job" form linked by the "establishment+job_cadre" field.
    Note: It is important that this form is joined as a Left Join and to allow multiple joins. This way we get a copy of the establishment form for each job in the system. Combining with the join for the facility, we will now get a copy of the appropriate establishment form for each unique pair of (facility,job) in the system.
    • Under the "job" form, we will join in the "position" form via the "position+job" field.
      Note: It is important that this form is joined as a Left Join and to allow multiple joins. This way we get a copy of the job form for each position in the system that links to that job. Combining with the joins for the facility and job to the establishment form, and once we have done the linking in the next step, we will now get a copy of the appropriate establishment form for each unique triple of (facility,job,position) in the system.
      • We will link the "position" form so that the "position+facility" field is the same as the "facility+id" field. This is to ensure that the position matches both the facility and job that the establishment form is referencing.
      • Next we need to join the "person_position" to the "position" form so that "person_position+position" = "position+id."
        Note: It is important that this is a JOIN with a limit of one. (if it is left join, you will be picking up all positions even if they are not filled)
        • As many people may have held this position over the years, we will want to choose the most recent position. We will also want to make sure that they are currently employed. So we need to add two limit the "person_position" form so that:
          • start_date is notnull (to indicate that some held this position)
          • end_date is null (to indicate that this position is still being held by someone)

The resulting relationship should look like the upper half of this

Linking a Form

To explain a bit about the linking, the "establishment" module has two fields:

1. The "location" field which is a mapped field that can either be a "facility" or a "facility_type"
2. The "job_cadre" field which is a mapped field that can be either a "job" or a "cadre"

Linking the position form to the ancestral "facility" form will result that you only populate the report where the "position" form has its "facility" mapping to the facility form in the relationship

Adding the Relationship Functions

Now that we have created our base relationship, we need to create two relationship functions.

Aggregate Total Positions

Recall, that when we joined in the base relationship has a copy of the establishment form for each triple of (facility,job,position). We really only want to have a copy of the establishment form once for each pair (facility,job) and we only want the total number of positions for each pair (facility,job), not the positions themselves. Thus our first step is to create an "aggregating" function:

• Under "Functions" create a new function called "filled_positions" that has:
  • SQL Query: count(`position+id`)
  • Description: Filled Positions
  • Form Field: I2CE_FormField_INT
  • Aggregate: position,person_position
    Note: you can only set the aggregate once you have created "filled_positions." Edit it and set it so that it aggregates on the position form as indicated.

Variance

Now that we have a function to get the total number of positions for each pair of (facility,job) we want to display the variance -- the difference between the establishment and the filled positions. Since we will have needed to do the count(`position+id`) first, we will need to make the 'variance' a dependent function of the 'filled_positions'. The actual establishment number is stored in the "amount" field of the "establishment" form which is the primary form in the relationship. So to create the function we do:

• Go under 'filled_positions'
• Click 'Dependent Functions'
• Click 'Add a new function' and add a function with these properties:
  • SQL Query: `primary_form+amount` - `+filled_positions`
  • Description: Variance Between Establishment and Filled Positions
  • Form Field: I2CE_FormField_INT

Note here that all functions in the SQL query have a + automatically added to the beginning of their name

The resulting functions should look like the lower half of this

Creating The Report

We create a report view based on the "staff_norm_2010" relationship with the following forms and field settings:

• The primary form (establishment)'s amount field is enabled with header "Establishment"
• The job form has the field title enabled with header "Job"
• The facility form has the field title enabled with header "Facility"
• The function 'filled_position' is enabled with header "Filled Positions"
• The function 'variance' is enabled with header "Variance"

Creating The Report View

You may now create a report view based on the staff_norm_2010 report

Variations of the Relationship

In the above we assume that you have set the establishment on the pairs (job,facility). However, you may have set the establishment differently.

• If you set it on the pairs (cadre,facility) then in the relationship, instead of joining job to the establishment form, you would join cadre (right join with multiples). Then under the cadre form you would join job (right join with multiples). The rest would proceed as before.
• If you set in on the pairs (job,facility_type) then in the relationship, instead of joining facility to the establishment form, you would join facility_type (right join with multiples). The under the facility_type form you would join facility (right join with multiples). The rest would proceed as before.
• If you set it on the pairs (cadre,facility_type) then in the relationship, instead of joining job to the establishment form, you would join cadre (right join with multiples). Then under the cadre form you would join job (right join with multiples). Also, instead of joining facility to the establishment form, you would join facility_type (right join with multiples). The under the facility_type form you would join facility (right join with multiples). The rest would proceed as before.

Ancestor Forms

An "ancestor" form in a relationship is any form at a higher level in the current form in the relationship hierarchy. In the above example we have the hierarchy:

• person_position
  • position
    • facility
      • facility_type
        • establishment
    • job
      • cadre

In this hierarchy, we have the following ancestors:

• person_position: has no ancestors
• position: has person_position as an ancestor
• facility:has person_position and position as ancestors
• job:has person_position and position as ancestors
• facility_type: has person_position, position, facility, and job as ancestors
• cadre: has person_position, position, job, and facility as ancestors
• establishment: has person_position, position, job, facility, cadre and facility_type as ancestors