Job Role Competency Status Microsoft Power BI report user guide : Rail Industry Worker Knowledge Centre

Job Role Competency Status Microsoft Power BI report user guide

TABLE OF CONTENTS

OVERVIEW
USING THE REPORT
- Basic training on Power BI
- Opening the report
REPORT PAGES
ADDITIONAL FEATURES
FAQS

OVERVIEW

This document provides an overview of the Microsoft Power BI Report focused on job role competency validity for employed, associated or swiped RIW cardholders. This report is available to approved Employer Admin users. For more information, please refer to Connecting a new user to Microsoft Power BI reporting

The report data is refreshed from the RIW System periodically during the day, and includes the following key features:

Grid-like matrices that display all worker competencies required by job roles held by workers that the user has access to. The matrices use coloured icons and text to show competency validity and competency expiry for each job role. Colour coding highlights workers with active blocks, active suspensions or to indicate that the swipe relationship is the only one that is present.
A clickable link is shown against each worker, which takes the user into the RIW User Interface (UI) to view the worker record and perform actions such as awarding a competency.
Metrics show crews and projects with the most invalid job roles.
Extensive filtering to quickly zoom in on the required information. Available filters include job role, crew name, project name, competency filters, worker filters and more.
Access to worker records is secured to ensure only primary, secondary, associated and swiped workers that an Employer Admin user can see also through the RIW UI are displayed in the report.
The date and time of the last overnight refresh is shown at the top of each report page.

Screenshots shown in this guide are from the RIW demo environment. Whilst the worker first name and surname have been scrambled for confidentiality reasons, the functionality is the same as the RIW production version.

VISIBILITY - SWIPED WORKERS

Swiped workers are those RIW cardholders who have no relationship with your company apart from being actively swiped into one of your Projects, Sites or Zones. This relationship only exists while the swipe is active.

As there is no formal agreement between your company and the RIW Cardholder regarding access and ongoing visibility to the profile, the view is limited. When viewing a swiped cardholder in the RIW System you will not be able to access any information regarding the RIW Cardholder's medical or subscription. This view caries over into Power BI reporting.

For swiped RIW Cardholders you will see obfuscation in areas that you do not have permission to see. ( i.e. dates will be displayed as 09/09/9999 and non date related data will be displayed as a #). This ensures that you are still able to place filters and export the reports to Excel for further manipulation.

USING THE REPORT

Basic training on Power BI

This guide is focused on a single report consisting of a number of report pages. Although certain Power BI features are described, new users to Power BI should also consult Microsoft’s online training and documentation available from https://docs.microsoft.com/en-us/power-bi/consumer/ and other sources.

Opening the report

Once a user has completed the E-Learning module, they will be provided with a link to open the report. This link opens the report in a web browser:

The report page “Grouped Validity” is shown when the report is first opened. Additional report pages are also available (shown as tabs on the left-hand side of the report).

REPORT PAGES

The following report pages are available:

Grouped Validity
Detailed Validity
Required Competencies
Metrics
Worker Details
Job Role Requirements
Export: Note that this report page does not appear in all screenshots as it was added after the bulk of this Guide was created.
Help

A description of each report is provided below.

Grouped Validity

This report page is based around the Job Role - Competency Validity Matrix. The information shown on the page is intended to be filtered to drill down to the information of interest, using one of the methods described in this Guide.

Feature Overview

A summary of the key features on this report page is shown in the diagram below.

Job Role Filter

The required job role can be search for and filtered using the Job Roles filter in the top-left of the page. Multiple items can be selected using the CTRL-key when selecting a check box against a job role. A specific job role can be located by entering a value into the search box:

TIP: 
When you drill down to the information of interest and expand a level in the table hierarchy:
"Eligible to work" represents the worker has a valid subscription and a primary employer.
"Job Role Valid" represents the worker has a valid job role(s) on their profile.
Both of these statuses should be "true" to be permitted to work.

Competencies Table

This table shows the competencies that related to other filtered items on the page (for example if a job role is selected, only competencies required for that job role are shown). The competency groups (“Comp. Group”) are displayed with a numbered identifier to save on screen space.

The table can be filtered by selecting a specific item from another visual on the page, such as a job role or competency group from the matrix shown beneath the table. The “Comp. Group” is an abbreviated identifier that is described below.

The screen shot below shows the Competencies table filtered for the “Dogman - National” job role:

TIP:
More information can be displayed by making the table go into a full screen "focus mode". Hover over the table and select the focus mode icon in the top-right corner.

Graphs

Two graphs are show on the page. As with other visuals, the count displayed is affected by any other filters that have been applied:

Job Role Validity: Shows a count of the number of workers that have valid and invalid job roles.
Comp Group Validity: A count of the number of workers that hold one or more valid or invalid competency groups.

The results shown on the rest of the page can be filtered by clicking on a graph column. For example, to only show workers who hold Invalid job roles, click on the red bar titled “Invalid” in the “Job Role Validity” graph. Use CTRL-click to apply this filter in conjunction with the other graph, for example.

TIP:
Click on one of the above bars on the column graph to cross-filter the other items on the page. For example, if "valid" int he above "Job Role Validity" graph is selected, only valid job roles appear on the page. The "Comp Group Validity" graph number count may also reduce, because it previously included invalid job roles.

Grouped Validity Matrix

The matrix is made up of columns and rows, as well as coloured icons where the columns and rows intersect. This resembles a pivot table in Microsoft Excel.

A row in the matrix is shown for each worker. By default, the worker’s Surname, First Name, RIW Number and a link to open their record in the RIW UI are shown, along with a flag showing whether the job role is valid or invalid (shown as either “True” or “False”).

TIP: 
Quickly open the worker record in the RIW UI by clicking on the hyperlink icon under the “RIW” column to review the worker record, award competencies etc. The user must be logged into RIW before this link will open successfully.

As the data is only updated once a day there may be a discrepancy in the displayed data - a worker may show in the report as missing a competence, but when you click the RIW UI to access the worker’s profile the competence may have since been loaded and verified and displaying as valid. The data refresh which is used by the report occurs at around 02.00am. If the competence was verified at 08.00am on the same day, this means that the verified competence will not be displayed on the worker’s record until the next data refresh, which that occurs the next day.

TIP:
Show additional worker columns by hovering over the matrix and selecting the double arrows to “Expand all down one level in the hierarchy”. Hide the columns again by clicking the up arrow.

The columns on the matrix list each job role, with the competency groups that this job role requires shown underneath. Competency groups are displayed with a numbered identifier to maximise the number of items that can be seen on the screen at one time. The actual competency name can be seen in the “Competencies” table above the matrix. In the example below, “All#131” and “One#130” are identifiers for competency groups in the “Electrician - National” job role:

Competency group identifiers are prefixed by “One” for “One of the following” competencies, and “All” for “All of the following” competencies:

Where “One of the following” competencies are required for a job role, these are grouped together into a single column on this report page (they are broken out on the “Detailed Validity” and “Required Competencies” pages). The competency status in the matrix shows as valid if the worker holds a valid award for at least one competency in the group, meaning the overall competency group is valid and this part of the job role requirement is met.
For “All of the following” competencies, every competency is displayed in the matrix along with its status for the worker (Valid/Invalid).

TIP:
Click on a job role or competency ID to filter the Competency table above to show competencies in the group. Click again to remove the filter.

Worker Details Drill-down

Although the Competency Validity Matrix displays the most relevant worker information the user might need to refer to, it is also possible drill into the separate “Worker” page to view additional detail, and to compare workers.

To drill down in this way:

Select the “RIW number” for a worker in the matrix. The “Worker Details” button will show in dark grey, signifying that it can be clicked.
Click the “Worker Details” button.
The “Workers” report page opens. Refer to the specific section of this Guide for more information on this report page.
Review the information and click the “Back” button on the top-left of the page to return to the “Grouped Validity” report page.

Job Role Requirements Drill-down

A job role and it’s required competencies can be easily viewed through this drill-down as follows:

Select a job role column heading in the matrix. The “Job Role Requirements” button will show in dark grey, signifying that it can be clicked.
Click the “Job Role Requirements” button.
The “Job Role Requirements” report page will open. The job role definition (“Job Roles”) table is shown at the top and the “Required Competencies” table at the bottom. Refer to the specific section of this Guide for more information on this report page.

Filters

As with any report page, the “Filters” panel on the right-hand side of the screen can be expanded and collapsed to report filters. Refer to the Filters section later in this guide for more information.

Detailed Validity

This report has a similar layout to the “Grouped Validity” page described above. It is useful as a second step in assessing competencies held for specific job roles.

The key differences between this report page and “Grouped Validity” are as follows:

The “Job Role” filter on the top-left is “synchronised” with the “Grouped Validity” and “Required Competencies” report pages - any job role filter values selected on those pages will be reflected here, and vice-versa.
A single graph shows the Competency Status. This can be either “Needed” (i.e. missing), “Invalid” or “Valid”.
On-screen drop-down filters can be used to filter on:
1. “Competency Status” (one or more of “Needed” (i.e. missing), “Invalid” or “Valid” can be selected).
2. “Competency State/Expiry” (one or more from “Expires in 30 days”, “Expires in 60 days”, “Expires in 90 days” and “Pending Verification”).
Competencies in a “One of the following” competency group are shown separately in the matrix, rather than being summarised into a single column.
The matrix values show additional information for each worker, job role and competency, as follows:
1. An additional column level shows the competency identifier (as defined in RIW) within each “Competency Group”.
2. The “Competency Status” will show an icon representing “Needed” (i.e. missing) (a red cross), “Invalid” (a yellow exclamation mark) or “Valid” (a green tick).
3. If the competency has an expiry date, it is displayed.

TIP:
Competency names can be shown on the matrix under their identifier, although this will make column headings wider. To do this, hover the mouse cursor over the matrix, select “Columns”, from the “Drill on” drop-down then click the double down arrows (“Expand all one level in the hierarchy”):
 
The columns can be manually resized to make them smaller - the column name will word-wrap:
 To hide the full competency description again, select the up arrow (“Drill up”):

Required Competencies

This report page is very similar to the Detailed Validity report page. The only difference is that the matrix shows job roles as row headings against each worker, rather than as columns headings. This reduces the number of columns (more rows are shown as a result). This makes it easier to view the required job roles for each worker and see where a single competency is required by multiple job roles.

Worker Details

As described previously, the user can drill from a specific worker into the “Worker Details” report page. This page can also be used to view details on multiple workers that are not shown on the matrices. It contains on-screen filters on “RIW Number” and “Surname” and graphs for “Subscription Expiry” dates and “Highest Fit Medical”.

TIP:
If the report page is only showing a single worker, display all workers by selecting “Clear Filters” at the top of the screen.

The on-screen filters on “RIW Number” and “Surname” can be used to filter to the required worker or workers.
A value in the graph can also be used as a filter (e.g. to only show workers with a “Highest Fit Medical” of value of “1”.
The Filters on the right-hand pane of the screen can also be used to only show primary employed workers, for example. Refer to the separate section on Filters for additional information.

Job Role Requirements

This report page shows awardable job roles that have defined competency requirements.

The “Job Roles” table shows job roles that the user has access to.
The “Required Competencies” table lists competencies that are required for the job role to be valid.
- By default, only awardable competencies are shown - this can be changed using the “Awardable” Page Filter from the Filter panel:
- Other page filters can be used to only show employer competencies, for example (the “Comp. Level” filter), or network competencies for a specific network (“Comp. Level” in conjunction with “Comp. Level Detail”):

To filter “Required Competencies” to only show those for a single job role, either filter the report on a “Job Role Name” using the Filter panel, or select a single job role from the “Job Roles” table at the top of the screen:

TIP:
If the report page is only showing a single job role (this can happen after a drill-down has been performed from a matrix, for example), display all job roles by selecting “Clear Filters” at the top of the screen .

Metrics

This report page shows the top 10 Crews and Projects that have the most workers with invalid job roles.

To drill-down into additional detail, select one of the bars then click on “View Details”:
A new report page is shown.

This lists out the related job roles, crews, current projects and worker details. As with other pages, click on a graph or table row to filter the other items on the screen.

Export

The export tab allows the user to export the job role competency requirements information shown in the job roles competency status matrices in a form that is more readable for distribution via Microsoft Excel, for example. The table must be scrolled right and left to view all available columns that will appear when it is exported.

Before exporting data from the “Competency Status Table”, the on-screen and Filter panel filters should be used to drill-down to the required data set. This might be a specific crew, job role or competencies due to expire in the next 30 days, for example.

To export the information shown in the Competency Status Table to Excel:

Hover the mouse cursor over the Competency Status Table and select “Export data”.
Select the xlsx format as this supports the most rows and has better formatting that a straight CSV file.
The results are then shown in Excel. The spreadsheet also states what filtering was applied when the spreadsheet was exported.

TIP:
The Export to Excel option has a 150,000 row limit when using the Excel (xlsx) format. Results should be filtered in the UI to ensure this limit is not hit – if it is hit Power BI will display a warning when attempting the export.

TIP:
The Excel file will contain a column called “Comp. Group ID” which are the competency group identifiers and prefixed by “One#XXX”  for “One of the following” competencies, and “All#XXX” for “All of the following” competencies represented by a unique number for all of the “One” groupings and individual numbers for the “All” Groups:

Where “One of the following” competencies are required for a job role, these are grouped together into a single column on this report. The competency status in the report under “Competency Status Desc” shows as valid if the worker holds a valid award for at least one competency in the group, meaning the overall competency group is valid and this part of the job role requirement is met. Other status indicators include missing or invalid.  

For “All of the following” competencies, every competency is displayed in the report along with its status for the worker (Valid/Invalid/Missing).

Help

This page provides a brief overview of the report and report functionality.

In addition, the current user details are shown at the bottom of the screen.

ADDITIONAL FEATURES

Filters

Filter Types

Power BI report filtering is very flexible and powerful. The following options are available to filter reports:

On-screen filters: These work by selecting a filter, graph, table or matrix value on the page to filter the other visuals on the page. When filtering in this way, only the current page is filtered. Multiple values can be selected with the CTRL-key. The remaining filter types are shown in the Filter page on the right-hand side of the page:
Filter Search: As quite a few filters may exist, the above “Search” box can be used to search for filters by typing the name.
Visual Filters (“Filters on this visual”): These appear when a specific visual is selected on a page (e.g. a matrix, graph or table).
- These can be useful to filter on a column shown in a table, for example, that is not shown as a global filter. For example, on the “Job Role Requirements” page, select the “Job Roles” table to reveal filters for each column displayed in the table:
- Filtering in this way will affect the other visuals on the report page.
- Be cautious with this type of filter, as it disappears from the filter page if the visual is not selected, so it is not obvious that it has been applied.
- Use the “Clear Filters” button to clear any filters on the page.
Page Filters (“Filters on this page”): These filters appear on some pages. When selecting a value from the filter, only the current report page is affected. These exist on the Job Role Requirements page, for example:
Global Filters (“Filters on all pages”): Several filters have been pre-defined that allow the entire report to be filtered, to provide as much flexibility as possible. These can be seen in the Filter pane when viewing any report page. The first few are shown below:

Identify which Filters are affecting a Visual

It can be useful to understand which filters are affecting a particular visual. This can be seen by hovering over a visual and clicking the Funnel icon in the top-right hand corner. For example, on the “Detailed Validity” report page, the following report has been filtered on the Job Role Name “Forklift Operator - National” and to only show valid workers with valid job roles that also hold a valid card (using the Filter panel):

Clearing Filters

Select “Clear Filters” at the top of the screen to clear the filters on this report page and reset the visuals to their defaults.

The “Reset to default” option at the top of the screen can be used to reset the entire report back to its original state.

Filtering to a single Employer

Some users may have an email address in RIW that is associated with multiple Employer Admin accounts, as they administer records for more than one employer. In this scenario, the user can select their desired employer from the “User Employer Name” global filter shown in the Filter panel.

Filtering to a single Contractor in Charge (CIC) for Crews

For users that have an email address in RIW that is associated with multiple Employer Admin accounts, the user can select the “Crew CIC” filter from the Filter panel, to only show crews for that CIC.

Tooltips

Tooltips appear when hovering over a value in a matrix, for example. This can be useful to understand the rows and columns that relate to the value.

Using “Bookmarks” to Save and Retrieve Filters

It is easy to save a commonly used filter or set of filters for future use.

Doing this also saves the screen layout in place at the time the bookmark was taken. In this way, it is possible to expand the number of visible rows headings in a matrix, for example, and capture this as a bookmark. The rows can then be quickly hidden and shown by selecting the bookmarks.

To capture a bookmark:

Select “Add a personal bookmark” from the menu at the top of the screen.
Enter a name for the bookmark, select the checkbox to make it the default view (if desired) then click Save.

To use a bookmark:

Select the bookmark from the Bookmarks drop-down menu at the top of the screen:

Exporting Results

Power BI provides several methods for exporting data.

Export Menu

This allows the entire report to be exported as a PowerPoint Presentation or a PDF. It can also be printed.

The export can take a while to process, depending on the number of records.

Once completed, the report is downloaded as a file in the selected format or is available for printing.

When using these menu options, only the visible results in each report page are included. If there were 10 pages of matrix results for example, only the visible report page is exported.

A hyperlink is included on the first page of an exported Microsoft PowerPoint report that allows the user to navigate to the report to perform further analysis. This is not currently included in the PDF export.

Export Data

Data from a single visual can be exported to a CSV file, for analysis in Microsoft Excel or another application. For example, a list of the job roles from the Job Role Requirements page can be exported by selecting the “Export data” option from the top-right corner:

The following dialogue appears from which the relevant File format can be selected:

To export worker job role competency status data, use the “Export” tab as described in the Report Pages section above.

FAQS

Which job roles are displayed?

Only job roles that have been defined with competency requirements are included in the report.

Which competencies are displayed?

Competencies are only shown where they are required by one or more job roles the user has access to. Standalone competencies that are not required by job roles are not included in the report.

How are “Always Award New” competencies displayed?

For competencies defined as “Always Award New”, a new competency is recorded and displayed in RIW each time a competency is awarded to a worker. In this report, the current award is displayed in the report.

Is the data in Microsoft Power BI real time?

Information in the report is refreshed as per What are the report refresh times for Power BI?

Do I need a licence to access Microsoft Power BI?

Yes, you will need an individual Microsoft user licence or company licence to access power BI.

RIW Knowledge Centre Article Link:

https://support.riw.net.au/support/solutions/articles/51000108256

Did you find it helpful? Yes No

Knowledge Centre Search