Gap Analysis Excel Report Reference Guide



CAT Enterprise 1.6Gap Analysis Excel Report Reference GuideFebruary 2013NOTICE 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 pany 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 pany 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., 2012This WORK is copyrighted by PetroSkills, LLC. and printed under exclusive license by PetroSkills, LLC.TABLE OF CONTENTS TOC \o "1-4" \u Overview PAGEREF _Toc342520469 \h 3About the Gap Analysis Excel Report PAGEREF _Toc342520470 \h 3Features and Benefits PAGEREF _Toc342520471 \h 3Generated Report PAGEREF _Toc342520472 \h 4Report Template PAGEREF _Toc342520473 \h 7Template Criteria PAGEREF _Toc342520474 \h 7Properties Selection PAGEREF _Toc342520475 \h 8Name PAGEREF _Toc342520476 \h 9Creator PAGEREF _Toc342520477 \h 9Owner PAGEREF _Toc342520478 \h 10Scheduling PAGEREF _Toc342520479 \h 11Notification PAGEREF _Toc342520480 \h 12People Selection PAGEREF _Toc342520481 \h 13Include Self PAGEREF _Toc342520482 \h 13Select All PAGEREF _Toc342520483 \h 13Include the Following PAGEREF _Toc342520484 \h 13Exclude PAGEREF _Toc342520485 \h 14Add Filter PAGEREF _Toc342520486 \h 14Position Selection PAGEREF _Toc342520487 \h 16Report on Assigned Position PAGEREF _Toc342520488 \h 16Report on People with the Following Positions PAGEREF _Toc342520489 \h 16Assigned Position PAGEREF _Toc342520490 \h 16Growth Position PAGEREF _Toc342520491 \h 17Assigned or Growth Position PAGEREF _Toc342520492 \h 17Add Filter PAGEREF _Toc342520493 \h 17Options Selection PAGEREF _Toc342520494 \h 18Report Output PAGEREF _Toc342520495 \h 19About the Generated Report PAGEREF _Toc342520496 \h 19Downloading and Saving a Generated Report PAGEREF _Toc342520497 \h 19Opening a Report for the First Time PAGEREF _Toc342520498 \h 19Structure PAGEREF _Toc342520499 \h 20Gap Analysis PAGEREF _Toc342520500 \h 21Gap Analysis Percent PAGEREF _Toc342520501 \h 24Assessment Percent PAGEREF _Toc342520502 \h 25ReportSettings PAGEREF _Toc342520503 \h 27ReportData PAGEREF _Toc342520504 \h 27Report Calculations PAGEREF _Toc342520505 \h 28Percent Developed PAGEREF _Toc342520506 \h 28Detailed Example PAGEREF _Toc342520507 \h 29Appendix PAGEREF _Toc342520508 \h 32Working with CAT reports and Excel PivotTables PAGEREF _Toc342520509 \h 32What is a PivotTable? PAGEREF _Toc342520510 \h 32How is the PivotTable used? PAGEREF _Toc342520511 \h 32What is a PivotChart? PAGEREF _Toc342520512 \h 33PivotTable Field List PAGEREF _Toc342520513 \h 34Field List PAGEREF _Toc342520514 \h 35Report Filter PAGEREF _Toc342520515 \h 35Column Labels PAGEREF _Toc342520516 \h 35Row Labels PAGEREF _Toc342520517 \h 35Values PAGEREF _Toc342520518 \h 36Defer Layout Update PAGEREF _Toc342520519 \h 36Tips and Troubleshooting PAGEREF _Toc342520520 \h 37Expanding/Collapsing PivotTable Rows PAGEREF _Toc342520521 \h 37How do I hide (blank) in the PivotTables/Chart? PAGEREF _Toc342520522 \h 37What are the white areas? PAGEREF _Toc342520523 \h 38Why do I see 0%? PAGEREF _Toc342520524 \h 39The columns I want are not in the PivotTable Field List PAGEREF _Toc342520525 \h 39Saving Your Generated Reports PAGEREF _Toc342520526 \h 40OverviewThe 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 ReportThe 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 BenefitsThe 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 planningForm regional analytics on Skill gaps and areas of strengthFilter 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 PositionSchedule automatic generation of reports at specific intervals Include Person Profile informationGenerated ReportThe 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 ( REF _Ref342332677 \h 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. -27940592416483351075011274445110041Figure SEQ Figure \* ARABIC 1— Gap Analysis, Gap Analysis Percent, and Assessment Percent WorksheetsThe last two worksheets ( REF _Ref342512784 \h 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. -279401741761211580158936Figure SEQ Figure \* ARABIC 2—ReportSettings and ReportData WorksheetsReport TemplateThe 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 CriteriaThe 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 REF _Ref341969794 \h Figure 3: Properties SelectionPeople SelectionPosition SelectionOptions SelectionSave Figure SEQ Figure \* ARABIC 3—Wizard Toolbar2028190-25095Properties 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 REF _Ref339524744 \h Figure 4, includes Name, Creator, Owner, Scheduling options for report generation, and Notification options for email distribution. Figure SEQ Figure \* ARABIC 4—Properties Selection PageName 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 REF _Ref339524744 \h 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 ( REF _Ref341969871 \h Figure 5). Figure SEQ Figure \* ARABIC 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 ( REF _Ref339365327 \h Figure 6).Figure SEQ Figure \* ARABIC 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 ( REF _Ref341969905 \h Figure 7). Figure SEQ Figure \* ARABIC 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 ( REF _Ref341969937 \h Figure 8). The Scheduling feature is convenient for monthly review meetings as well as quarterly and yearly evaluations.Figure SEQ Figure \* ARABIC 8—Scheduling Options Follow these steps after clicking on the Schedule Report checkbox: ExampleProcedureGenerate a report on a daily basis for the month of December of this year.Click the Calendar icon next to the Start field.Select December 1, 2012 on the calendar.Click End by, then the Calendar icon.Select December 31, 2012 on the calendar.Click Daily, and enter “1” in the Every__ day(s) starting on box.Select a time from the Run at: dropdown menu.Generate a quarterly report the first day of each quarter starting today and for the next 3 years.Click the Calendar icon next to the Start field.Select the start date on the calendar.Click End by, then the Calendar icon.Find the date January 2 2015 on the calendar and click on it.Click Monthly, and enter “3” in the Every__ month(s) starting on box.Select a time from the Run at: dropdown menu.Generate a yearly report every December 31st at 12 pm indefinitely.Click the Calendar icon next to the Start field.Find December 31 2012 on the calendar and click on it.Click No end date.Click Monthly, and enter “12” in the Every__ month(s) starting on box.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 ( REF _Ref341971298 \h 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 SEQ Figure \* ARABIC 9—Notification PageTo 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.17005304750People 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.The People Selection page, as shown in REF _Ref341970041 \h Figure 10, includes several options: Include Self, Select All, Include the following, Exclude, and Add Filter.Figure SEQ Figure \* ARABIC 10—People Selection Page Include Self Check this box if you would like to report only on yourself, or you want to include yourself in the report. This is useful, for example, when a Supervisor shares the same Position as the people they supervise and he/she wants to be included in the report for comparison purposes.Select All Check this box to select or deselect all the people at once.Include the Following This section displays the groups and people you can report on.Choosing by Group or Subgroup – This method is useful when you want to report on everyone in specific groups. When people are added or removed from those groups the report template will automatically apply these changes every time you generate a report. (Clicking on the top-level group will automatically select everyone below that group in the tree including all subgroups.)Choosing by Individual – This method will report only on the individual people selected and will not change if more people are added or removed to any of the groups to which they belong. ExcludeSelections can be limited further by excluding people based on role (Supervisors, Mentors, or Administrators). The level of permission that your organization has given you will determine which roles you can exclude.Add Filter Selections can be limited further using person filters. This can be more efficient when reporting on large datasets of people who share common characteristics. The three components of the Add Filter option are Property, Condition, and Variable ( REF _Ref337711645 \h Figure 11).Figure SEQ Figure \* ARABIC 11—Filter OptionPropertyThe first component is the standard account properties and any Person Profile categories that have been set up within your system appear in the dropdown list. What you see will be determined by your security settings. By default, the properties displayed are: User ID Last NameMiddle NameEmployee NumberEmail AddressTelephone NumberPositionDisplay PreferenceAccount StatusInactive StatusCannot Change PasswordConditionThe dropdown list for the middle component depends on the type of data selected for Property:Property TypeConditions AvailableTextis, is not, contains, does not contain, exists, does not existDateis before, is after, is on , is not on , exists , does not existNumberis less than, is greater than, is equal to, is not equal, exists, does not existVariableThe last component depends on the Property type selected. The variable will be either a dropdown list or free-form entry. Examples:To report on only people who can login to the system, the filter would match REF _Ref337711645 \h \* MERGEFORMAT Figure 11 where “Account Status is Active.”To report on everyone except people who are based in North America, Person Profile data needs to exist, and then a filter can be applied such as “Work Location is not North America.”Note that when multiple filters are used, all conditions must be met. If you used these two examples together, your report would only include active people who are not in North America. Position Selection This page allows you to specify which Positions to report on based on the people you have selected on the People Selection page. The default is “Report on Assigned Position” which will return a report on all Positions assigned to the people being reported on. To report on specific Position(s) or to include Growth Position(s), the default can be changed. By selecting specific Positions, the generated report will include only the people with those Position(s).The Position Selection page, as shown in REF _Ref337102790 \h Figure 12, includes options to report on people with an Assigned Position, a Growth Position, or a combination of both.Figure SEQ Figure \* ARABIC 12—Position Selection PageReport on Assigned PositionThe report will be generated by default on the Position(s) currently assigned to everybody you have selected on the People Selection page. Report on People with the Following PositionsChoose one of these options to report only on people with specific Positions—people who have other Positions will be filtered out of the report. You can choose from Assigned Position, Growth Position, or Assigned or Growth Position. By choosing any of these options, you will be able to select specific Positions from the Position Classifier tree. Assigned Position In a similar way as the default setting Report on Assigned Position, the Assigned Position allows you to choose specific Positions that are assigned to your selected people. This option is useful when you are interested in a specific Position across different Groups.Growth Position Select this option if you are interested only in data regarding planned or desired future Positions to be held by your set of people.Assigned or Growth PositionSelect this option to report on Assigned Position(s) and Growth Position(s). For example, selecting this option with the Position “Senior Geologist” will return information on everyone who has this Position as either an Assigned or Growth Position.Once you have selected the appropriate radio button, the Positions you select will be displayed within the tree with a corresponding checkmark. The number, displayed next to the classifiers, in the tree indicates how many Positions have been selected.Figure SEQ Figure \* ARABIC 13—Position Selection Page with Assigned Position and Position Classifier TreeAdd FilterFilter choices vary according to the configuration of your system. Example:To report on only the Positions that are part of the “Exploration Geologist” set of Positions, a filter could be set for “Sub Classifier is Exploration Geologist.”Refer to the previous “People Selection Page” section in this guide for more details on the filter options. 181069018415Options Selection This page allows you to choose up to 10 Person Profile Categories. By default this page is blank, and it will only include Person Profile information if your organization has decided to include this data in your system. Generally, Person Profile categories refer to work location, business unit, and position. REF _Ref339440057 \h \* MERGEFORMAT Figure 14 shows a typical Options Selection page. The options you choose on the page will affect your report output.Figure SEQ Figure \* ARABIC 14—Options Selection Page Once you have completed the Options Selection page, clicked the Save icon to save the template. The report template can be edited using the Gap Analysis Excel report Wizard.Report OutputOnce a template has been saved, you can generate a report by right-clicking on the template name in the Reports tree and selecting Generate Report. Your generated report’s output is based on the selections saved in the report template. With this report’s worksheets and Pivot functionality, you can customize and interpret this information to determine Skill gaps or strengths and assess their extent for the selected people. With this information at hand, you can then prioritize development needs and assign resources optimally.About the Generated ReportThe Gap Analysis Excel report is best viewed in Excel 2007 or later. The report initially opens ready for you to interpret the information on the worksheets. To analyze, investigate, or manipulate this information further, all of Excel’s functionalities are available. Those proficient in Microsoft Excel PivotTables/Charts will benefit the most.Downloading and Saving a Generated ReportThe generated Gap Analysis Excel report is saved in CAT. To download the report, click on its name in the Reports tree, and the generated report will be saved to your computer. Although you can access a new copy of the report from CAT at any time, take note that it is only saved in the system for a specified number of days. The default is 60 days, but it may be different depending on your organization’s settings.Opening a Report for the First TimeWhen opening a report for the first time, it may appear blank, and an Excel message bar or a dialog box will display a security alert indicating that the file may contain macros. Click on Enable Editing or Enable Content to unblock the report.StructureThe Gap Analysis Excel report opens in Excel with five tabs at the bottom representing the report’s worksheets: Gap Analysis, Gap Analysis Percent, Assessment Percent, ReportSettings, and ReportData. The main view displays the information of the currently selected tab, as shown in REF _Ref341936085 \h Figure 15.7715253048635Figure SEQ Figure \* ARABIC 15—Gap Analysis Excel Report Opening Screen and TabsSimilar to CAT’s Gap Analysis, the three main worksheets (Gap Analysis, Gap Analysis Percent, and Assessment Percent) will allow you to identify gaps and strengths in a customizable format.Gap AnalysisThe Gap Analysis worksheet, as shown in REF _Ref341970194 \h Figure 16, includes development percentages summarized in a Pivot view. This worksheet is composed of a PivotTable and a PivotChart. The PivotTable Field List, hidden by default, is used to filter and customize the data shown in the table and chart. Figure SEQ Figure \* ARABIC 16—Gap Analysis WorksheetThe PivotChart displays the same information in the PivotTable as a bar graph. It initially displays Positions and Competency levels vertically, showing the assessment counts across each bar. The Gap Analysis PivotChart can be filtered along with the PivotTable by using the Position Classifier and Position Sub Classifier dropdown menus in the B column at the top of the worksheet. More advanced filtering can be done by using the PivotTable Fields List or by using the gray PivotChart Field buttons located in different parts of the chart; both options must be enabled as they are hidden by default. To learn how to enable them, please refer to the Pivot functionality sections of the Appendix.The numbers inside each colored bar represent the Sum of Part of All (green), Some (yellow), Few (red), and Not Assessed (gray) Count. High Importance is denoted by backslashes (\\\) in the corresponding bar. Figure SEQ Figure \* ARABIC 17—Gap Analysis Worksheet, PivotChart Tip: Is your chart’s data unreadable? All Positions are displayed by default, and Excel auto-sizes them to fit in the PivotChart. Thus, the more Positions that are displayed, the less legible the PivotChart may be. To reduce the number of Positions displayed in the PivotChart, filters can be used by selecting from the dropdown options for Position Classifier or Position Sub Classifier. The data in the PivotTable ( REF _Ref341796985 \h \* MERGEFORMAT Figure 18) drives the PivotChart, which shows a numeric count summary of assessments by assessment levels. It also includes High Importance counts. 213Figure SEQ Figure \* ARABIC 18—Gap Analysis Worksheet, PivotTableThe PivotTable columns (1) display counts by assessment levels of All, Some, Few, and Not Assessed and by High Importance:Sum of Part ALL HI Count – number of assessments assessed at All with a High Importance settingSum of Part ALL Count – number of assessments assessed at AllSum of Part Some HI Count – number of assessments assessed at Some with a High Importance settingSum of Part Some Count – number of assessments assessed at SomeSum of Part Few HI Count – number of assessments assessed at Few with a High Importance settingSum of Part Few Count – number of assessments assessed at FewSum of Part Not Assessed HI Count – number of assessments that have not been assessed with a High Importance settingSum of Part Not Assessed – number of assessments that have not been assessedBy default, the rows (2) are pre-set by Position, based on the selections made, and they allow viewing the data under each Position by the four Competency levels: AwarenessFundamental Application Skilled ApplicationMasteryRow labels can be filtered to isolate specific information for further analysis by selecting the row label filter button and choosing one or more criteria from the dropdown list. The entire PivotTable can also be filtered by using the Position Classifier or Position Sub Classifier (3) as well. The information displayed can be modified as desired using the filtering and PivotTable field features. The table will be automatically updated, and row and column values will be changed according to the selections made.If advanced filtering or structural changes are required, please refer to the Pivot functionality sections of the Appendix.Gap Analysis PercentThe Gap Analysis Percent worksheet, as shown in REF _Ref342520443 \h Figure 19, displays the Gap Analysis percentages in a color-coded format that mimics the Gap Analysis user interface in CAT. The report provides a quick way to identify Skill gaps and help evaluate development needs. 123Figure SEQ Figure \* ARABIC 19—Gap Analysis Percent WorksheetThe Gap Analysis Percent PivotTable shows percent developed averages. Cells include the actual average and are also color coded to reflect the developed percentage ranges to quickly identify gaps.The PivotTable columns (1) display the four Competency levels (Awareness, Fundamental Application, Skilled Application, and Mastery).By default, the rows (2) are pre-set by Position, Competency Map, Skill Group, and Skills. These levels can be collapsed as needed to give you a more top-level view. Use the expand + and collapse - icons to hide or show additional levels of a Position. You can also isolate specific information by selecting the row label filter button and choosing one or more items from the dropdown list. The entire PivotTable can be filtered by using the Position Classifier or Position Sub Classifier as well.Right-clicking on a level will allow you to expand/collapse everything at that level using the Expand/Collapse option and then selecting Collapse Entire Field as shown in REF _Ref341853938 \h \* MERGEFORMAT Figure 20.Figure SEQ Figure \* ARABIC 20—Collapsing Below Position Level If advanced filtering or structural changes are required, refer to the Pivot functionality sections of the Appendix.Assessment PercentThe Assessment Percent worksheet, as shown in Figure SEQ Figure \* ARABIC 21, includes percent developed and percent assessed for both the individual and for any cross assessments that have been completed. For any areas that you are investigating further, this particular worksheet will provide additional information on the assessment status in terms of completion of both assessments and cross assessments.The PivotTable will display information in the following order: Position, Competency Map, Skill Group, Skill, and User ID.Figure SEQ Figure \* ARABIC 22—Assessment Percent WorksheetTip: To remove the blank rows at the top of the table, click on the dropdown arrow beside Row Labels at the top and unchecked blank.The Assessment Percent PivotTable summarizes assessment status information using averages for participants and supervisors. The averages are indicated in four columns:Average Participant’s % DevelopedAverage Participant’s % AssessedAverage Supervisor’s % DevelopedAverage Supervisor’s % AssessedThe table will allow data to be expanded, starting at a Position level and continuing with Competency Map, Skill Group, Skill, and User ID. These levels can be collapsed or expanded as needed. Cells are color-coded for to quickly identify gaps.The layout can be modified as desired using the PivotTable field features. The table will be automatically updated and the table row and column values and layout will be changed according to your selections.If advanced filtering or structural changes are required, refer to the Pivot functionality sections of the Appendix.ReportSettingsThe ReportSettings worksheet shows the selections you made to create the template using the People, Position, and Options Selection pages. When working with several reports, it’s useful to refer back to the ReportSettings worksheet to confirm your selections.Figure SEQ Figure \* ARABIC 23—ReportSettings WorksheetUnder Person Selection, only the highest selected level will be displayed. For example, if you chose a top-level group with its subgroups, the subgroup names will not be shown. If you only chose a Participant node or a subgroup, it will appear with the names of its top-level group. Names of people in nodes or groups will only appear if you have chosen individual people.ReportDataThe ReportData worksheet shows all the data you selected when creating the report template and is used to generate the Gap Analysis, Gap Analysis Percent, and Assessment Percent worksheets. It is highly recommended to use this sheet only as a reference and not to modify the data in this sheet. Figure SEQ Figure \* ARABIC 24—ReportData WorksheetReport CalculationsThe Gap Analysis Excel report calculates a percent developed for each Skill Description and uses these values for all percentages within the report. Percent DevelopedThe percentage calculations use the assessments for each Skill Description, which are then combined into summary percentages for the higher Skill levels up to the Position. The percentage calculations provide a numeric way of viewing relative strengths or weaknesses within the Position. The percentage calculations are based on the assessment values of All, Some, Few, and Not Assessed set by Participants, Supervisors, and Mentors. To calculate the percent developed, the report uses the formula: (1 x ALL) + (0.5 x SOME)Totalwhere:ALL = Total number of required Skill Descriptions assessed as ALLSOME = Total number of required Skill Descriptions assessed as SOME0.5 = Default weighting for SOME; it can be a different number according to the configuration settings of your organization.Total = Total number of required Skill DescriptionsThis formula is used in all calculated fields within the PivotTables. Detailed ExampleI want to report on the all the groups that I supervise based on Assigned Position. Figure SEQ Figure \* ARABIC 25—Template Properties Selection PageFrom the Gap Analysis PivotChart, looking by Position, I can see that:Assessments appear complete or nearly complete for the Process Engineer and Drilling Engineer.Assessments are in progress for Reservoir Engineer, Geologist, and Junior Engineer.Figure SEQ Figure \* ARABIC 26—Gap Analysis Worksheet, PivotChart Based on PositionTo look at Drilling Engineer in more detail, first filter by Sub Classifier to display only Drilling. Figure SEQ Figure \* ARABIC 27— Gap Analysis Worksheet, PivotChart Filtered by Sub Classifier DrillingFor the Drilling Engineer Position, I can see from the Gap Analysis PivotChart that:For the Skilled Application level, three skills have been set to High Importance, and, on average, only one of these three is completely developed.Of all the people assigned this position, on average, only about 40% are developed at this level (10/25).Looking at the Gap Analysis Percent worksheet, the Drilling Engineer position is displayed at the top.Looking at the data for the Skilled Application level, I can see that: Directional Planning is 44% developed.Drilling Fluid System Planning is 67% developed.Bit and Hydraulics Planning is 22% developed.These gaps could be closed by either mentoring or on job assignments that could provide hands-on opportunity for development.Figure SEQ Figure \* ARABIC 28— Gap Analysis Percent Worksheet, PivotTable for Drilling EngineerThe Assessment Percent worksheet shows that the least-developed Skills are Drilling Hydraulics and Drillstring Design. Also Drilling Hydraulics has 0% developed, indicating an area that will require some attention. For Drillstring Hydraulics, all assessments have been completed by andyw, levans, and csmith, but that no cross assessments have been completed. I need to get the cross assessments completed and to schedule some development (training or mentoring) for these three people.Figure SEQ Figure \* ARABIC 29— Assessment Percent Worksheet, PivotTable for Drilling EngineerAppendixWorking with CAT reports and Excel PivotTablesSeveral of CAT’s reports are generated in Excel format; therefore, the data can be viewed simply by selecting the different worksheets and expanding and collapsing rows. The data, however, can be further customized and manipulated using Excel’s PivotTable functionality. Some basic information about PivotTables is provided below, but this functionality is meant to be used by a person who is familiar with Excel PivotTables. Although the report opens in a Pivot view, it is strictly Excel functionality; therefore, for further information on how to use PivotTables, please consult Microsoft’s help. What is a PivotTable?A PivotTable is an Excel feature that allows you to work with large amounts of data in a summarized format. A PivotTable is a very powerful tool, and its functionality will allow you to manipulate your report data tailored to your needs. When you generate one of CAT’s Excel-based reports, the PivotTable(s) automatically extract, organize, sort, count, total, or average the data that results from your selections when creating the report template. How is the PivotTable used?The information displayed in PivotTables can be modified as desired by filtering data from the PivotTable Field List. Tables will be automatically updated, and row and column values will be changed according to your selections.When you first open a generated CAT Excel report, the PivotTable Field List is hidden by default, but it can be accessed by selecting the Options tab under PivotTable Tools and then selecting the Field List button. In REF _Ref341854917 \h Figure 30, you can see an example of a PivotTable (1) with the PivotTable Field List box (2) on the right. The values in this box will vary depending on the type of report and the type of worksheet.12Figure SEQ Figure \* ARABIC 30—Worksheet with PivotTable Field Selection WindowWhat is a PivotChart?A worksheet may also include a PivotChart. It is similar to a PivotTable, except that it displays its data in a chart format (such as a bar graph as shown in REF _Ref341855076 \h Figure 31). The PivotChart is layered on top of the PivotTable which drives the PivotChart’s data. The data in the PivotChart and the PivotTable are linked: when you make changes to one, the other will adjust to match these changes. The PivotChart can be customized in the same way as the PivotTable with the PivotTable Field List.It can also be modified by enabling dropdown values directly onto the chart by selecting the PivotChart and selecting Analyze from the PivotChart Tools tab and then selecting Field Buttons. You can also use many of Excel’s formatting options to change the look and feel of the PivotChart. Figure SEQ Figure \* ARABIC 31—Worksheet with PivotChart Field Selection WindowPivotTable Field ListThere are several options within the PivotTable Field selection window that you can use to modify the default setup and create a report that suits your particular needs. (Remember this functionality is most useful for those who are proficient in using Excel PivotTables.)The options available in the PivotTable Field List will vary for different CAT reports and their individual worksheets. When working with a PivotChart, the boxes that indicate the values regarding its orientation are labeled differently from those for the PivotTable. Those values can be dragged into the other boxes to change the orientation of the PivotTable. Note that these boxes may not have any values in them.123456Below you will find a brief description of each part of the PivotTable Field List.(1)Figure SEQ Figure \* ARABIC 32—PivotTable Field Selection Window Field List The PivotTable Field List (1) is comprised of both data columns from the ReportData worksheet and Calculated Fields derived from that data. The PivotTable and PivotChart can be customized according to your needs by checking or unchecking the desired boxes from the options shown in the lists.(2)Report Filter (3)The values in this box include the values in the rows above the PivotTable, such as Position Classifier and Position Sub Classifier.Column Labels The values that appear in the columns of the PivotTables also appear as Legend Fields in the PivotCharts. (4)Row Labels The values that appear in the rows of the PivotTables also appear as Axis Fields for the Pivot Charts. (5)Values (6)The Values field shows options that can be set for the mathematical operation that will be performed on the data. Depending on the particular worksheet, it may include averages, counts, sums, or no operation. Defer Layout Update The Defer Layout Update allows you to stop the PivotTable or PivotChart from automatically refreshing every time a change is made. It is a useful feature when making multiple changes to large data sets.For more information on using PivotTables, consult Microsoft’s help.Tips and TroubleshootingExpanding/Collapsing PivotTable RowsExpanding and collapsing rows in the PivotTables is a way to view more or less information. This is easily done by clicking on the + icon beside an item to expand it and on the – icon to collapse it. You can also expand/collapse any of the child levels within a parent level.An easy way to expand/collapse first and second levels all at once is by using the Expand/Collapse menus in the Options toolbar under the PivotTable Tools tab. Click on any of the first-level items and select Expand Entire Field to expand all the first-level items in the worksheet. Click on any of the second-level items and select Expand Entire Field to expand all of the second-level items in the worksheet. Repeating these steps and using the Collapse Entire Field option will collapse the items at the selected level. Figure SEQ Figure \* ARABIC 33— Expand & Collapse Entire FieldHow do I hide (blank) in the PivotTables/Chart?When a report is generated, blanks cannot be filtered out automatically, since Excel remembers both what was selected and what wasn’t selected. As a result, you may notice rows with (blank) values displayed in your PivotTables or Chart, such as the ones shown in REF _Ref341869873 \h \* MERGEFORMAT Figure 34: Figure SEQ Figure \* ARABIC 34— PivotTable with (blank)To hide them, click on the down arrow (which will appear with a filter icon if a filter has been applied) and, from the dropdown menu, uncheck (blank). The (blank) values will no longer appear in your PivotTable or Chart. Note that this must be done for each individual worksheet. Figure SEQ Figure \* ARABIC 35— Dialog Box to Filter (blank)What are the white areas?White cells where there should be a value for a certain Skill Description means that there is no Skill Description assigned for this particular Competency level.Figure SEQ Figure \* ARABIC 36— No Skill Description = White CellWhy do I see 0%?The reason you are seeing a value of 0% in a cell of a worksheet depends on the worksheet type. For a worksheet that is displaying assessment percentages, the 0% simply indicates where no assessments have been done for a certain Position, Map, Skill Group, Skill, or person. For a worksheet that is displaying percent developed percentages, the zero percent can either mean that no assessments have been made to calculate a percent developed, or that the assessments have been set to Few. To determine which of the two reasons applies to your particular case, double-click on the 0%:Figure SEQ Figure \* ARABIC 37—PivotTable 0% CellA new worksheet will open with information about this specific cell. Looking at the Few and Not Assessed columns, locate the cell that has a number one (1) in it and then look at the column name to determine if it was assessed as Few or Not Assessed. Figure SEQ Figure \* ARABIC 38—Worksheet Showing Assessment CountsThe columns I want are not in the PivotTable Field ListBy default, Person Profile information is not included in the Excel reports; therefore, that type of data is not included in the PivotTable automatically. To add those extra fields to the PivotTable, go to the PivotTable Tools tab. Under Options, click on Change Data Source. Figure SEQ Figure \* ARABIC 39— Change Data SourceIt is recommended that you add no more than three columns at a time as both Excel 2007 and 2010 will stop responding. If more than three columns are required, repeat the Change Source Data step as many times as needed.Saving Your Generated ReportsIf you plan to modify a PivotTable or PivotChart, first copy the worksheet as a new worksheet within the same file. The original information will then be preserved for comparison or to allow reversion by simply deleting the new worksheet. It is also recommended to save a complete copy of the original generated report to your computer. Keep in mind that generated reports are deleted automatically after a specified period of time (usually 60 days). This time is set within your system settings. This concludes the Gap Analysis Excel Report Reference Guide. For basic instruction on creating templates and generating reports, please refer to the CAT Enterprise Reports Manual.? Copyright PetroSkills, LLC., 2012 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download