Gap Analysis Excel Report Reference Guide

CAT Enterprise 1.6

NOTICE AND DISCLAIMER

The information contained herein (WORK) was prepared by or contributed to by various parties in support of professional continuing education.

For purposes of this Disclaimer, “Company Group” is defined as PetroSkills, LLC., OGCI Training, Inc., John M. Campbell and Company, its and their parent, subsidiaries and affiliated companies, and, its and their co-lessees, partners, joint ventures, co-owners, shareholders, agents, officers, directors, employees, representatives, instructors, and contractors.

Company Group takes no Position as to whether any method, apparatus or product mentioned herein is or will be covered by a patent or other intellectual property; furthermore, the information contained herein does not grant the right, by implication or otherwise, to manufacture, sell, offer for sale or use any method, apparatus or product covered by a patent or other intellectual property right, nor does it insure anyone against liability for infringement of same.

Except as stated herein, Company Group makes no warranties, express, implied, or statutory, with respect to the WORK, including, without limitation, any warranties of merchantability or fitness for a particular purpose.

Company Group does not guarantee results. All interpretations using the WORK, and all recommendations based upon such interpretations, are opinion based on inferences from measurements and empirical relationships and on assumptions, which inferences and assumptions are not infallible, and with respect to which competent specialists may differ. In addition, such interpretations, recommendations and descriptions may involve the opinion and judgment of the USER. The USER has full responsibility for all interpretations, recommendations and descriptions utilizing the WORK. Company Group cannot and does not warrant the accuracy, correctness or completeness of any interpretation, recommendation or description. Under no circumstances should any interpretation, recommendation or description be relied upon as the basis for any drilling, completion, well treatment, production, financial decision, or any procedure involving any risk to the safety of any drilling venture, drilling rig or its crew or any other individual. The USER has full responsibility for all such decisions concerning other procedures relating to the drilling or production operations. Except as expressly otherwise stated herein, the USER agrees that Company Group shall have no liability to the USER or to any third party for any ordinary, special, or consequential damages or losses which might arise directly or indirectly by reason of the USER’s use of the WORK. The USER shall protect, indemnify, hold harmless and defend Company Group of and from any loss, cost, damage, or expense, including attorneys’ fees, arising from any claim asserted against Company Group that is in any way associated with the matters set forth in this Disclaimer.

No part of the text or original figures of the WORK may be reproduced in any form without the express written consent of PetroSkills, LLC. Use of the WORK as a reference or manual for adult training programs is specifically reserved for PetroSkills, LLC. All rights to the WORK, including translation rights, are reserved.

© Copyright PetroSkills, LLC., 2012

This WORK is copyrighted by PetroSkills, LLC. and printed under exclusive license by PetroSkills, LLC.

TABLE OF CONTENTS

Overview

About the Gap Analysis Excel Report

Features and Benefits

Generated Report

Report Template

Template Criteria

Properties Selection

Name

Creator

Owner

Scheduling

Notification

People Selection

Include Self

Select All

Include the Following

Exclude

Add Filter

Position Selection

Report on Assigned Position

Report on People with the Following Positions

Assigned Position

Growth Position

Assigned or Growth Position

Add Filter

Options Selection

Report Output

About the Generated Report

Downloading and Saving a Generated Report

Opening a Report for the First Time

Structure

Gap Analysis

Gap Analysis Percent

Assessment Percent

ReportSettings

ReportData

Report Calculations

Percent Developed

Detailed Example

Appendix

Working with CAT reports and Excel PivotTables

What is a PivotTable?

How is the PivotTable used?

What is a PivotChart?

PivotTable Field List

Field List

Report Filter

Column Labels

Row Labels

Values

Defer Layout Update

Tips and Troubleshooting

Expanding/Collapsing PivotTable Rows

How do I hide (blank) in the PivotTables/Chart?

What are the white areas?

Why do I see 0%?

The columns I want are not in the PivotTable Field List

Saving Your Generated Reports

Gap Analysis Excel Report Reference Guide

Overview

The Gap Analysis Excel Report Reference Guide will help intermediate and advanced users to understand the Gap Analysis Excel report’s template criteria and how it affects the report output. This reference guide also covers the report output structure and the calculations used in generating the report. A detailed example is included at the end of this guide to provide a clear, in-depth understanding of the Gap Analysis Excel report.

About the Gap Analysis Excel Report

The Gap Analysis Excel report is an extension of CAT’s Gap Analysis and Group Gap Analysis pages. The display and functionality are similar and include development percentages; however, the report output provides summarized, customizable data.

Displaying its information in several Excel worksheets, the Gap Analysis Excel report provides a wide range of data analysis. As in the Gap Analysis, Position assessments are similarly displayed using colored bars representing Participant or Supervisor assessments, making Skills and Skill Groups with “Few” and/or “Some” assessments clearly visible, and Skill gaps and strengths easily identifiable.

The Excel report displays this information in PivotTables and a PivotChart, allowing you to filter and manipulate data according to your preferences. Report data can be selected or deselected to be shown in the report, and the look and feel of the PivotTables and PivotChart can be easily customized.

Although the report is similar to the Gap Analysis in CAT, the Excel report provides you with more flexibility, features, and options.

Features and Benefits

The Gap Analysis Excel report benefits people who are familiar with Microsoft Excel Pivot functionality. Tables and charts are automatically updated every time data is manipulated, providing the flexibility to customize the report to your specific needs.

By specifying exactly what you need out of the report, it works as a powerful tool that helps you in several ways:

• Identify Skill gaps and areas of strength for development planning
• Form regional analytics on Skill gaps and areas of strength
• Filter or manipulate Skill information to analyze competency by Person Profile information such as functional discipline, years of service, assets, and more

With an understanding of the criteria you select when creating the report template, you can best achieve the goals to suit your organization’s needs.

The following are just a few of the template options:

• Customize the people you report on by specifically selecting who to include or exclude, either individually, by Group, or by Position
• Schedule automatic generation of reports at specific intervals
• Include Person Profile information

Generated Report

The generated Gap Analysis Excel report opens directly in Excel and is best viewed in Microsoft 2007 or later. The Excel workbook includes five worksheets or tabs: Gap Analysis, Gap Analysis Percent, Assessment Percent, Report Settings, and Report Data.

The first three worksheets (Figure 1) show Skill gaps and strengths as well as assessment and development percentages. They summarize and display color-coded information in Excel PivotTables and a PivotChart. When used in the worksheets, Pivot functionality provides a wide range of flexibility and customization.

• The Gap Analysis worksheet is similar to CAT’s Gap Analysis, but its information is displayed in a numeric bar chart summarized in a PivotChart and a PivotTable. The assessment counts are shown in a column by assessment levels of All, Some, Few, and Not Assessed.
• The Gap Analysis Percent worksheet is similar to CAT’s Gap Analysis. It provides a quick way to identify gaps in Skill areas. It displays assessment level columns along with Position, Competency Map, Skill Group, and Skill level rows.
• The Assessment Percent worksheet displays the assessment status information in a PivotTable format with row value levels that can be expanded starting at the Position level, then Competency Map, Skill Group, Skill, and user ID.

Figure 1— Gap Analysis, Gap Analysis Percent, and Assessment Percent Worksheets

The last two worksheets (Figure 2) refer to the report structure and the data used to generate the Gap Analysis Excel report.

• The ReportSettings worksheet shows the criteria selections for the report that were chosen in the report Wizard.
• The ReportData worksheet consists of the data set pulled from CAT. The PivotTables and PivotChart in the first three worksheets use this data set.

Figure 2—ReportSettings and ReportData Worksheets

Report Template

The report template identifies what data and how data will appear in your generated report. Creating a report template involves selecting criteria from several pages in the template Wizard. The Wizard is accessed by selecting Reports from the CAT’s main menu, and then right-clicking on Gap Analysis Excel in the Reports tree and choosing New Report Template. Once a report template is saved, the report can then be generated.

Note: If the report is not listed in the Reports tree, you do not have permissions to use this report. Contact your CAT Administrator or System Owner for access.

Template Criteria

The report template Wizard is made up of several pages of criteria that allow you to create a template according to your organization’s needs. This template will determine the details of your generated report’s output.

Creating a report template begins with the Properties Selection page and ends with the Options Selection page. The Next and Back buttons on each page can be used for navigation through the setup Wizard. Once the report template has been saved, you can navigate between the report Wizard pages by clicking on any of the icons and changing your selections on each page.

The template Wizard consists of four pages of report criteria as shown in Figure 3:

Figure 3—Wizard Toolbar

Properties Selection

The first page that appears in the report template Wizard consists of basic information about the report template, such as the name of the template, who created it, and who owns it. This page also includes optional selections for scheduling report generation and setting up email notifications, which will help you manage the generation and distribution of the report.

The Properties Selection page, as shown in Figure 4, includes Name, Creator, Owner, Scheduling options for report generation, and Notification options for email distribution.

Figure 4—Properties Selection Page

Name

The default template name is “New Report Template [#],” with [#] being the next higher number available within all the template names in the system. The “80,” as shown in the template name field in Figure 4, is system generated to make the template name unique. If a template with a lower number has been deleted or the name has been changed, a newly created report template name will default to the lowest number available.

Report template names are listed in the Reports tree in the left panel in alphabetical order. Reports generated by a particular template will be displayed as a list under the template name, from most recent to oldest (Figure 5).

Figure 5—Template and Generated Report Tree

You can change the template name in the Name field or from the Reports tree by using the Rename function.

Template naming considerations:

• Up to 50 alpha-numeric characters.
• Must be a unique name within the system; otherwise, a warning message is displayed, and you will not be able to continue until you change it.
• Select a name that is clear for everyone using the report. Consider including the department, group, position, or other relevant traits that will make it easier to identify this report.
• Avoid long names. It is easier to scan through a large number of reports if they have shorter names.

Note: The names of previously generated reports are not affected by template name changes.

Creator

The name of the person who created the report template is automatically displayed as Last Name, First Name, (User ID). It is grayed-out and cannot be changed.

Owner

By default, the Creator is also the Owner. A template can have only one Owner; however, both the owner and creator will have access to it. This feature is useful when the report template has been created by an Administrator but will be used by someone else such as a Supervisor.

To transfer ownership of the template, click on the blue hyperlinked Owner name (Figure 6).

Figure 6—Owner Field

The Ownership option is displayed. Type the last name, first name, employee number, or user ID in the Search field, and then click on the Search icon. Your results are displayed in the Search Results box. Click on the name of the person you want to transfer ownership to, and click the Add > button. The new owner name is displayed in the Owner box (Figure 7).

Figure 7— Template Ownership Transfer

The results displayed in the Search Results field will depend on the current Owner’s permissions. For example, an Administrator must have permission to “Can Supervise & Report on Subordinate Supervisors” for any subordinate Supervisor to appear in the search results.

You can change the ownership again by repeating these steps. Click on the hyperlinked Owner name to hide this option.

Scheduling

This feature allows you to automatically generate a report on a predefined timeframe. By default, reports will only be generated when requested. Once enabled and configured, the Scheduling option allows you to generate a report in the future on a defined timeline. You define the start and end date (or you can have it generated indefinitely), the frequency, and the time the report will be generated (Figure 8).

The Scheduling feature is convenient for monthly review meetings as well as quarterly and yearly evaluations.

Figure 8—Scheduling Options

Follow these steps after clicking on the Schedule Report checkbox:

1. Click the Calendar icon next to the Start field.
2. Select December 1, 2012 on the calendar.
3. Click End by, then the Calendar icon.
4. Select December 31, 2012 on the calendar.
5. Click Daily, and enter “1” in the Every__ day(s) starting on box.
6. Select a time from the Run at: dropdown menu.

1. Click the Calendar icon next to the Start field.
2. Select the start date on the calendar.
3. Click End by, then the Calendar icon.
4. Find the date January 2 2015 on the calendar and click on it.
5. Click Monthly, and enter “3” in the Every__ month(s) starting on box.
6. Select a time from the Run at: dropdown menu.

1. Click the Calendar icon next to the Start field.
2. Find December 31 2012 on the calendar and click on it.
3. Click No end date.
4. Click Monthly, and enter “12” in the Every__ month(s) starting on box.
5. Select a time from the Run at: dropdown menu.

Notification

This feature is enabled with Scheduling. It sends an automatic email every time the report is generated according to the recipient list you create. The email is generated by the system and notifies the recipient(s) that the report is ready.

You can choose to include a link to the report in the email. The checkbox at the bottom of the Notification area enables this option. The owner’s name will appear at the bottom of the Notification area with a checkbox. It is grayed-out if you do not have an email address in the system (Figure 9).

Note: When making your list of recipients, keep in mind that the report generated will be specific to your level of authority. It is the same as generating a report and emailing an electronic copy. The report output is not modified in any way by the notification recipients.

Figure 9—Notification Page

To add people to the Recipient(s) list, use the Search field. It will search by Last Name, First Name, Employee Number, and User ID. Select the desired person from the Search Results box and click the Add> button.

Note: Search results will only return people you have access to and who have an email address within CAT. Contact the CAT administrator if the person you want to add does not show up in the Search results.

To remove someone from the Recipient(s) list, click the recipient’s name and click the Remove < button.

People Selection

This page allows you to identify the people to report on. You can select People, single Groups, and/or multiple Groups. Your selection choices are based on your role within the system.