JUNE 2008
AGENCY USER GUIDE
WORKFORCE PROFILE COLLECTION
- WACA AND THE TEMPLATE
NEW EDITION
AGENCY USER GUIDE – W ACA AND THE TEMPLATE
SECTION I. WORKFORCE PROFILE TEMPLATE ............................................... 4
Introduction........................................................................................................ 4 7 Steps to Using the Template and Sample Spreadsheet ................................................. 5 Step 1: Downloading and Saving the WFP TEMPLATE ................................................... 5 Step 2: Workforce Profile Template ........................................................................... 5 Step 3: The Sample Spreadsheet ............................................................................. 6 Step 4: Load an Existing Spreadsheet........................................................................ 6 Step 5: FTE Summary........................................................................................... 7 Step 6: Check Data for Warnings.............................................................................. 8 Step 7: Exporting to ASCII .................................................................................... 10 Resetting the Workbook....................................................................................... 11
SECTION II
WACA—WORKFORCE ANALYSIS COMPARISON APPLICATION ........ 12
Introduction...................................................................................................... 12 System Requirements ......................................................................................... 12 7 Steps to Submit Your Agency’s Workforce Profile Data ............................................... 13 Step 1: Accessing the WACA ................................................................................ 13 Step 2: Import Process ........................................................................................ 16 Step 3: Switching between Quarterly & Annual Mode.................................................... 18 Step 4: Viewing your Data .................................................................................... 19 Step 5: Data Validation Process ............................................................................. 21 Step 6: Exporting Data ........................................................................................ 24 Step 7: Send a message to the State Administrator...................................................... 24 Reporting in the WACA........................................................................................ 26
NSW Public Sector Workforce Profile Collection
Page 3
SECTION I. WORKFORCE PROFILE TEMPLATE
INTRODUCTION
From June 2005 all workforce profile collections will be collected by the Workforce Analysis Comparison Application (WACA). WACA is a web-based human resource information collection, validation and reporting system developed by the Queensland and Victoria Governments. It is a joint project managed by the WACA Management Committee that comprises the Queensland, Victoria, New South Wales, South Australian and Tasmanian State Governments. WACA provides an on-line data collection tool that allows Public Sector Agencies to: • • • validate their workforce data; analyse their workforce characteristics at a point in time and over time; and compare their workforce characteristics with whole of Government.
Before data can be uploaded into the WACA it must be standardised and consolidated in the Workforce Profile Template and converted to an ASCII text file. During a financial year there will be 2 types of template and sample sheets provided for agencies: 1. 2. Annual Template – to be used for the Annual Collection. The Annual template will incorporate the June Quarter; and Quarterly Template – to be used for September, December and March Quarters.
Note: For agencies too large to use the Microsoft Excel Template, please refer to ASCII File Definitions – Appendix C and Appendix D of the Data Specifications manual.
Page 4
NSW Public Sector Workforce Profile Collection
7 STEPS TO USING THE TEMPLATE AND SAMPLE SPREADSHEET
STEP 1: DOWNLOADING AND SAVING THE WFP TEMPLATE
Workforce Profile template is available for download from the Workforce Profile website. It is important to use a new version of the template for each collection as there may be slight changes to the operation of the template. A new version of the template will be released before the end of each quarter. Follow this link to the Workforce Profile website, or type it into the address line of your browser: http://www.dpc.nsw.gov.au/public_employment/workforce_profile/waca. Follow the links to WACA page to find the template. Right click on each link and select Save Target As to download the file when prompted. Save the file to an appropriate location on your computer or network.
STEP 2: WORKFORCE PROFILE TEMPLATE
Enabling Macros The Workforce Profile Template contains macros to create the sample workbook, assist with calculating FTE, identifying warnings and exporting text files. Macros need to be enabled on your computer for these functions to work correctly. Note: The Workforce Profile macros have no unpredictable instructions and do not change files on your hard-drive. To enable macros you will need to have your macro security set at medium. To change macro security, go to Tools|Options|Security|Macro Security and choose the Medium option. Excel will prompt with the dialogue box shown at right each time an Excel workbook containing macros is opened.
9
Click on the Enable Macros button.
When the Template workbook is opened the splash screen shown adjacent will be displayed.
9
Click on the OK button.
Upon opening the Template you will notice a new menu item “WFP Tools” has been inserted before “Help”. This menu will appear while the template is active. These tools are used to prepare the data for upload into the WACA. Each of the functions under the tools menu will be described later in this document. You should now be ready to upload data from your workforce profile workbook to the template.
NSW Public Sector Workforce Profile Collection
Page 5
Note: If you have a question relating to your agency’s IT configuration and the enabling of macros please contact your IT Helpdesk. For questions about the Workforce Profile Template setup or operation please contact the Workforce Profile Team on (02) 9228 5465 or (02) 9228 3265. Alternatively, email: workforceprofile@dpc.nsw.gov.au
STEP 3: THE SAMPLE SPREADSHEET
The Sample workbook can be created using the function in the WFP Tools menu. The layout of the Sample workbook is the same as the Workforce Profile Templates. It can be identified by the green cell colouring in the header. Data extracted from the HR Information System should be transported to the Sample workbook. Using the sample workbook helps ensure the data has been extracted to the correct worksheet columns before loading into the template. The sample workbook contains no Macros or formulas allowing you to cut, paste and modify data without affecting the data-quality checking features. The sample spreadsheet should be used to prepare all the information relating to the collection period, before using the macros to load data into the Template. The data specifications manual will assist you in preparing your data. When the data in the sample workbook has been prepared and saved the WFP Template can be opened.
STEP 4: LOAD AN EXISTING SPREADSHEET
This process:
• • • • • • •
At the discretion of the user, deletes data in the open Template and initialises the workbook. Opens the File Open dialog box and prompts for the selection of the file to be imported. Imports data from the selected file into the Workforce Template. Closes the import file. Formats columns, headers, etc as appropriate in the Workforce Profile spreadsheet. Runs the FTE Summary. Checks cells for out-of-range warnings, and applies colours to these and blank cells.
The tools menu “WFP_Tools” for the Workforce Profile is inserted before Help on the Excel menubar. Menu items are enabled according to the status of the workbook. On first opening the workbook there are only 2 menu items enabled, Import Data to Template and under Utilities, Create the sample Workbook.
To import the data, click on the WFP Tools menu in the Template. Note: Do not paste data directly into the template Navigate the menu to: Load an Excel Worksheet.
9
Click on Load an Excel Worksheet.... to begin the importing process.
Page 6
NSW Public Sector Workforce Profile Collection
A Windows file locater dialogue box will appear requesting the name and location of the file to be loaded into the Template. Using this window, find and select the workbook you want to import to the template.
9 9
Browse to the location and highlight the file to import into the template. Click on the Open button.
The template will load the data from the sample workbook, delete any blank rows and format the data. A dialogue box will display the number of records loaded into the Template.
9 9
Click on the OK button. FTE Summary calculations will commence.
STEP 5: FTE SUMMARY
This menu item is located on the WFP_Tools Menu.
9
To initiate manually, FTE Summary.
Click
on
After calculation of the FTE’s, they will be displayed in a dialogue box with a summary of the valid records for each of the periods collected will be displayed.
• • •
the CENSUS Period FTE the QUARTERLY Reference Period FTE the ANNUAL Reference (Annual Template Only) Period FTE
9 9
Click on the OK button. Check data for Warnings will commence.
This summary also indicates the number of valid records used to produce the FTE, the number of overrides and the total number of records for the relevant period. If the number of valid records plus overrides is less than the total number of records is indicative that records exist where at least one of the required fields for FTE calculation is missing or invalid. Any invalid records will calculate to zero for the respective period. Employees who have separated from the agency prior to the period being
NSW Public Sector Workforce Profile Collection
Page 7
calculated are excluded from this count. Efforts should be made to identify invalid entries and amend the missing data or provide an FTE override where applicable. The Census, Quarterly and Annual FTE’s are calculated for each employee using information provided within the Template. The calculation methodology is detailed in the Workforce Profile Data Specifications Manual. It is possible that an atypical FTE may be calculated due to normal “hours paid” reconciliation practices conducted within Agency HR systems. This may occur when an employee is paid in advance or has been overpaid. Agencies should adjust the invalid item used to calculate the FTE, or provide an override FTE, to reflect actual worked FTE of the employee. Note: Changes can not be made to the calculated FTE columns of the excel template. Summary totals and new FTE calculations will only be achieved by re-running the FTE summary from the WFP_tools menu. An override feature has been built into the template. Each calculated FTE has a corresponding override column within the template. Data entered into the override column is entered manually and will take precedence over calculated values. Agencies should ensure all relevant data is entered for all employees employed during the reference period, to enable the calculation of the Census, Quarterly and when appropriate the Annual FTE figures. Data used to calculate FTE is mandatory.
STEP 6: CHECK DAT A FOR WARNINGS
The template undertakes checking of the data to ensure it conforms to the data specifications. When data is imported into the template checking will commence automatically, but if required subsequently, can be initiated by selecting the Check Data for Warnings on the WFP_Tools menu. Warning checks should be manually run after any changes have been made to the data in the template. Check for Warnings The Check data for Warnings menu item tests each cell in the worksheet for a “Null”, or when specified, a value that is outside the allowable range. Consult the Data Specifications Manual (pages 11 – 13) or Appendices D and F for valid ranges. Checking for errors may take some time for larger agencies and a progress bar will indicate the progress in percent of records checked. Check data for warnings can be initiated from the WFP_Tools menu.
9
Click on Check data for Warnings.
Cells that are identified in the checking process will be highlighted either red or amber. Blank Cells
Blanks
Check Cell Value
Any Blank cells, other than FTE override columns will be highlighted in RED. Data cannot be exported to ASCII until all blank cells have been populated. Note: One of the primary aims of the Template is to ensure that NO Blank cells are contained in the data before it is uploaded into the WACA database. Blank cells are considered FATAL errors, and each of these errors must be rectified before the data can be written to a text file and uploaded into the WACA web-based collection facility. Page 8 NSW Public Sector Workforce Profile Collection
Check Cell Value Any cells that are not consistent with the range or value list outlined in the data specifications will be highlighted AMBER. Reasons may include: • • • • An incorrect value for a categorical variable (eg Employment Category). A value less than min or greater than max, for a scale variable (eg Total Hours Worked). Items that should not be coded as missing, not applicable or withdrawn (i.e. -1, -2 or -3) Where a date is not provided in the yyyymmdd format.
Warnings are based on the most extreme values that are likely to occur in the NSW Public Sector. There may be occasions where there is a valid reason for an employee to have a value which exceeds the min or max test values. A cluster of amber cells in a worksheet may highlight a problem with the data layout or extraction process. Warnings also apply to calculated FTE’s. Ordinarily, the FTE in any period will be between 0 and 1.2. Calculated FTE’s which fall outside this range as a result of the normal reconciliation process of an agency’s HR system can be manually adjusted using the corresponding override column. “Warnings” and “Warning Summary” worksheets Where a warning is identified as a result of selecting the Check data for Warnings a “Warnings” worksheet and a “Warning Summary” worksheet will be added to the workbook. Refer to Appendix F of the Data Specifications Manual for a full list of warnings.
Figure 1: An example of warnings identified and written to the “Warnings” worksheet. This will write a table to a new worksheet in the template workbook. This is especially useful for large data sets, where the coloured cells are not readily visible outside the viewing area. The table identifies: • • • • • Worksheet row number Warning number, consistent with Appendix F and WACA Item Name Hyperlinked Cell Reference Warning Description
Fixing Errors It is important to save your data regularly after each editing session. The Template should be used to check data quality and for small-scale changes. If after importing your data into the Template you find that large-scale changes are required, these changes should be made in the Sample Excel workbook and then reloaded into the Template. You can re-load new data into the Template as many times as is necessary. Reloading or resetting the workbook will destroy all data existing in the Template workbook. NSW Public Sector Workforce Profile Collection Page 9
Note: Do not cut and paste data into the template. Formulae may be unintentionally imported into the Template which will cause the Template to function unpredictably. Data may be corrected in the Template by simply typing the correct information into a cell. This process is acceptable for small changes providing the new data entered meets any warning condition outlined in the specifications and the cell colour remains clear when the Check data for Warnings is run. The aim of the template is to have NO red or amber coloured cells. Note: Blank cells are considered FATAL errors and each of these errors must be rectified before the data can be written to a text file.
STEP 7: EXPORTING TO ASCII
An important feature of the template is the ability to create files compatible with the import process in the WACA. The WACA has been designed specifically to accept what may be described as, fixedformat Text, Flat or ASCII files. Each variable in the template is assigned a specific number of spaces in the exported file. As each variable is assigned a fixed number of spaces it is critical that each variable conform to the data specifications. Export as ASCII can be used when:
• • •
All data has been checked in the template. All Blank cells have been populated in the template. The Agency has checked and resolved their warnings. 9 Click on Export as ASCII
If there are blank cells in the data area of the Template worksheet a dialogue box will be displayed. Note: It will be necessary to populate all blanks before writing to ASCII will be allowed. Before the file is written to ASCII, you will be prompted to run the FTE Summary.
9 9
If you have made changes since the summary was last run, click on the Yes button. Once you have made changes there is no need to run the summary again.
Use the Window’s Save As dialogue to select a location and edit the file name.
9
Click on the Save button.
Note: Agencies should keep a record of all text files submitted to the WACA. This can be done by editing the file name within the quotes to avoid overwriting previous files.
Page 10
NSW Public Sector Workforce Profile Collection
If there are No Blank cells, the Template will write an ASCII file in the location selected by the user. A dialogue will display the number of rows written to the ASCII file and the Path and File Name. Once a note of these details has been made:
9
Click on the OK button.
Note: These details will be required when uploading to the WACA.
RESETTING THE WORKBOOK
Occasionally, the Template may cease to function correctly or you may want to reset it to its initial state. This could occur when your computer system has a problem or when a process initiated by the Template software was unexpectedly terminated, for instance, if the user of the Template shuts down windows unexpectedly or if hardware or software conflicts occur. If this happens, an option is available on the WFP_Tools menu to Reset the workbook.
9
Click on Reset this Workbook.
Note: The sheet may be used immediately.
NSW Public Sector Workforce Profile Collection
Page 11
SECTION II WACA—WORKFORCE ANALYSIS COMPARISON APPLICATION INTRODUCTION
These guidelines are to be used when completing the Workforce Profile Collection with the use of the Workforce Analysis Comparative Application. This section provides step by step explanatory notes to submit your return: System requirements Step 1: Accessing the WACA Step 2: Importing data into the WACA Step 3: Switching between Annual and Quarterly Collection Mode Step 4: Viewing your data in the WACA Step 5: Validation in the WACA Step 6: Exporting Data Step 7: Sending a Message to the State Administrator Reporting in the WACA
SYSTEM REQUIREMENTS
To access the WACA you need a PC, Web Browser and a connection to the Internet. The WACA requires the minimum hardware and software described below.
Component
Microprocessor
Description/Comments
PC Pentium II 200 MHz or higher.
RAM
Windows 95/98/ME/XP: 32MB RAM. Windows NT: 64 MB RAM. Windows 2000: 64 MB RAM.
Hard Disk
Only required for Web Browser installation.
Video Monitor
VGA or higher-resolution monitor; Super VGA recommended.
Mouse
A mouse is required.
Operating System
Independent.
Software
Web Browser. Recommended Web browsers are: Internet Explorer version 5.0 or higher, Netscape version 4.7 or higher.
Internet
Fast Internet connection such as LAN connection, ADSL or Cable is recommended. For Dial-up connections the recommended speed is 56K. Other than Web Brower installation, no further installation is required.
Installation
Page 12
NSW Public Sector Workforce Profile Collection
7 STEPS TO SUBMIT YOUR AGENCY’S WORKFORCE PROFILE DATA
STEP 1: ACCESSING THE WACA
OPEN WACA BROWSER
The WACA is located on a server maintained by a web hosting service in Queensland. Opening the WACA browser page can be achieved by;
1. Typing the Uniform Resource Identifier (URI) in your internet explorer:
address line. Note: https designates a secure site and the “s” is a necessary part of the address.
https://www.waca.org.au/
2. A Link through NSW Department of Premier and Cabinet: Workforce Profile and Planning Web
Site. Although Agencies can use the link shown above, Agencies are encouraged to use the link established through the Workforce Profile and Planning Web site. An interface page has been established between Workforce Profile and WACA and this page will contain regular updates, downloads and help information. To access this link;
9
Select the NSW DPC Home Web page: www.dpc.nsw.gov.au
9
From the DPC website click on the link Public Sector Employment.
9
A new drop down menu will appear. Click on the Workforce Profile and Planning link then click on Workforce Analysis and Comparison Application link on the left hand side to enter the WACA interface page.
9
Once the WACA interface page has been loaded save this page to your internet Bookmarks or Favourites Menu http://www.dpc.nsw.gov.au/public_employment/workforce_profile/waca
NSW Public Sector Workforce Profile Collection
Page 13
9
From this page select the URL: https://www.waca.org.au/ Once you have selected the WACA URL, a popup window may appear advising of a secure connection. Click on the OK button.
WACA LOGIN
The WACA Login screen should look like the screen capture. To log into WACA you need a Username and Password.
9 9 9
Type your Username. Type your Password. Click on the Logon button.
There are two ways to gain access to a username and password;
3. The Workforce Profile WACA State Administrator assigns a single agency username and password
with administrator rights. Administration of these accounts is maintained by contact between PSWO and your agency. If you do not have a username and password for your agency you will need to contact the Workforce Profile Team. may then add users within your agency by creating sub-agency accounts. When adding new users, use the convention [agency code–n] where n is an integer, and assign a password. On first login the user can update their details and change their password. User access to the WACA can be set to base, advanced or administrator.
4. Once the State Administrator has assigned you with your agency username and password, you
State Administrator
Agency Login
Agency defined SubUser -
Agency defined Sub -User
Agency defined SubUser -
EXIT WACA
When using WACA your web browser initiates a connection with the WACA server. Closing the browser on your PC does not send a response to the server that you are disconnecting. To exit the WACA, you must ensure that you click on the Menu Item: Log Off from your WACA Menu and then close the Web Browser. This will prevent unauthorised access by other users.
Page 14
NSW Public Sector Workforce Profile Collection
Note: If you are accessing the site during the day and you do not log off, you will remain logged on for an 8 hour period and access can be established by opening and closing your Web Browser. After 8 hours of inactivity, the user will automatically be logged off from the WACA server.
CREATING A SUB-AGENCY USERNAME & PASSWORD
From the WACA menu by selecting Admin | User Administration you can create or modify the user details.
91
To add a new user, click on the Add button. The first insert on your right will be presented.
1
9
Items marked with an asterisk 2 are compulsory fields. All users have access to modify these values for their own details. However, only the State Administrator and the Agency Administrator has the access to modify other User’s Miscellaneous Details.
2
Note: You will need to type in the new user’s password twice, consistent with. secret password confirmation protocols.
9
Once you are satisfied with the information you have entered, scroll down to the bottom and click on the Add New User button.
93
To modify a username, click on any established user account detail and the User Details Screen will be displayed.
3
NSW Public Sector Workforce Profile Collection
Page 15
4After making the necessary changes click on the Update User Details button.
4
STEP 2: IMPORT PROCESS
The next step after logging onto the WACA is to import your data. You are only allowed to import your data if you have an Agency Administrator; or Advanced User access levels. To successfully complete your data import process you will need to complete the following processes; • • • Initiate File Import Selecting Upload File Importing Data
INITIATE FILE IMPORT
To initiate the file import process; From the WACA menu, select File | Import. Please note when you Import data, it is stored in a temporary file location within your user account. This file will be replaced with a new file each time data is imported into your account.
91
Annual or Quarterly: The next screen requires you to indicate if you are importing a Quarterly or an Annual collection. The import process requires information about what template is being uploaded to the WACA. This will determine how the file is managed once in WACA. Select either Quarterly OR Annual. Privacy Protocol: The next required field is the Privacy Protocol. The import process requests the user to acknowledge that they have advised all staff of the Privacy Guidelines. The import process will not proceed unless you have acknowledged that you have NSW Public Sector Workforce Profile Collection
92
Page 16
informed staff of their rights. Select Yes to proceed or select No and the process will be stopped and you will be required to contact the WACA State Administrator.
93
Establish Start & End Dates: Input the start and end dates for your Reference Period. If you are uploading annual data please enter the annual start and end dates. Otherwise use quarterly reference period dates. Once you have entered the relevant date ranges. Click on the Next button.
9
Note: These dates will form the upper and lower limits checks for Item 3k_q, Item 3k_a (the number of days in the reference period), Item 6b (date commenced with Agency). If more than one pay period exists within an Agency, the earliest start date and latest end date should be used.
SELECTING THE UPLOAD FILE
The next step in the importing process is to upload your data file. You are required to select the file to be uploaded to the WACA server. The file must have a file extension “TXT or PRN”. The file can be zipped using a zip program complying with the ZIP Archive file format. To begin the upload process;
91 9
To choose the file to be imported; click on the Browse button. Select the file to upload. The file MUST be in ASCII plain text (*.TXT or *.PRN) format. These files can be zipped using the standard windows zip programs and uploaded. This option will be important for agencies with large data sets. Once you have highlighted the file that you wish to upload in the Windows browse dialogue, click on the Open button.
1
9
92
The filename textbox on the WACA Upload File screen should now be populated with the path to the file as illustrated in the screen capture. For this example we have chosen a file from G:\ drive – and the file to upload “999 WFPTextFile.prn”. Click on the Upload button.
2
9 93
The time to upload or transfer the file will depend on its size and the bandwidth of your Internet connection. The progress of the file transfer is indicated by a blue progress bar on the upload screen. See screen capture. When the File Upload is complete you will be informed in a dialogue. Click on the Next button.
3
9
Note: DO NOT interrupt the Upload File and File Import process by closing your browser, logging out of the WACA or attempting to perform other WACA functions as this will resulted in the file Import process being aborted. NSW Public Sector Workforce Profile Collection Page 17
IMPORTING DATA 91 Once the file upload is completed the
next step is to Import your data into the WACA database. The time taken to import will depend on the size of your file. There is no progress indication for importing your data. Processing of the file is indicated by the spinning gears. WACA will inform you with a dialogue when the File Import is complete.
1
Note: Benchmark tests indicate an average import rate of 3.5 seconds per 1000 records.
92
The File Import process is completed when WACA displays the Import Completed dialogue.
2
STEP 3: SWITCHING BETWEEN QUARTERLY & ANNUAL MODE
Once you have imported the data and selected the Collection Type during the import process this selection will remain active for the rest of your session.
9
In order to manually switch between the Quarterly and Annual collection you can from the WACA Menu select File | Switch to Quarterly/Annual Collection Mode.
9 9
Select the option button for collection you are working on. Click on the Switch button.
the
Once you make a selection it will take effect for the rest of the session. This means that all of your validation, reports etc will be applied to the collection type you have selected only. To view data from another collection you must use the Switch to Quarterly/Annual Collection Mode function again.
Page 18
NSW Public Sector Workforce Profile Collection
STEP 4: VIEWING YOUR DAT A
The next step in the file import process is viewing your imported data. It is important to view your data to ensure that the data has been imported correctly and contained in the correct columns.
VIEWING DATA
There are two ways you can view your imported data.
91 9 92
Go to your WACA Menu, click on File | View. or Immediately after you have completed the File Import process, Please click here to view the imported data.
The imported data screen should look similar to the screen capture below.
NSW Public Sector Workforce Profile Collection
Page 19
NAVIGATING THROUGH RECORDS
The record navigation tool can be used to further view the imported data. Along the top left hand side of the imported screen is the Record Navigator. The Record Navigator can be used to move through records.
1
2
3
4 10
5
6
7
8
9
11
Note: Any search in the WACA that displays records will automatically divide the number of records displayed on each page into smaller number, in this case 10. For example, if there are 100 records in total, then there will be 10 pages each containing 10 records. This window is therefore not a good tool to check the data quality of large data sets, which should be conducted using Validation. The order of the bullet points below are corresponding to the numbers on the “red discs”.
1Will 2Will 3Will 4Will 5 6 7 8 9
take you to the beginning of the record set page i.e. the 1st page; move you back one page; advance you one page;
take you to the end of the record set page. For example if there are 10 record set pages it will take you to the page 10; Search Box - will allow you to enter text that you wish to search for; List of searchable fields. The items in this List are the searchable fields; Click this button to begin the search; Click this button to refine your search; This button will reset the search values i.e. will list all records without any search criteria; Page indicator - indicates the current record set page that you are at;
10
set page list. This set of numbers is the number of record set pages available to you. This allows you to randomly move to the specific record set numbers. The difference between this method and the one in point 1 is that in point 2 & 3 is that you are moving sequentially through the pages i.e. from 1, 2, 3… and so on. However, with this method you can jump directly from page 1 to page 5.
11 Record
Page 20
NSW Public Sector Workforce Profile Collection
SORTING RECORDS
You are also able to sort the records in an order. Simply, click on the icon in the column that you wish to sort by, as highlighted in the figure below. The default sort order is ascending.
STEP 5: DAT A VALIDAT ION PROCESS
Although you can perform data quality checks within your WFP FTE Template, an important feature of WACA is to further carry out a set of tools that allow you to validate your data based on a number of predefined lookup tables, date validation, and cross checks defined by the PSWO. See WACA Warnings Table on page Error! Bookmark not defined.. This section describes the essential steps required to validate your data in the WACA.
COMMENCING VALIDATION 9 To commence your data validation process
click on the Menu Item Validation | Record Validation from the WACA menu. The Record Validation method processes the imported data, checking every single field of every record and then allows you to view and filter the warnings. Because the process is extensive it may take some time depending on the number of records.
9
A warning window will appear regarding the length of time that the Validation process will take, as well as the danger of process interruption. Click on the Validate button.
9
NSW Public Sector Workforce Profile Collection
Page 21
9
Progress of the Validation will be indicated on your screen. The time taken for the validation process will depend on the size of your file and the number of errors in it. When the Record Validation is complete a new screen will appear to inform you that the process is completed.
9
VALIDATION RESULTS
Once the validation is complete you will see the Validation Result – Summary Screen. The validation result will display the total number of errors and warnings found by the Validation process. You will be able to view a summary of your agency name; Number of Records validated; Records with at least one Fatal Error; and Records with at least one Warning Error. Note: The results of the Validation will be retained even if you have logged off and re-logged on providing that you have not: (a) Re-Imported the data or (b) Re-run the Validation process. To view your Last Validation select Validation | Last Validation from your WACA Menu.
9 9
Click on the Summary TextBox to view the list of options. Click on one of the options. A description of the options is below.
Summary: the summary window is the default screen displayed after running the validation. Validation Result errors by Record ID: to view the detailed validation reports. Summary by field: allows you to view the validation errors grouped by field. This effectively allows you to see how many errors of each type were produced by the database fields. Summary by field displayed in one page: displays all the errors in the one screen. If you have large amount of errors, this screen may take a little longer to load and is only recommended for users with very fast Internet connection. Summary by Record ID: The Summary by Record ID option from the dropdown menu allows you to view the validation errors grouped by the unique Record ID. This effectively allows you to see how many errors of each type were produced by each Record. Export all Validation results: This option allows you to export your uploaded data in an Excel file.
Page 22
NSW Public Sector Workforce Profile Collection
The Validation Result List includes;
Validation Result Field
Row No. The row that caused the warning.
Description
Type
Warning Number i.e. “Warning nn”. See WACA Warnings Table in Appendix F.
Field
The name of the database field that caused the error.
Record ID
Unique Record Identifier.
Entry
Description of the error.
WACA WARNING LIST
For a full list of warnings please refer to Appendix F of the Data Specifications Manual. Unresolved warnings will not stop the Agency from submitting their return for final acceptance; however, all Agencies should endeavour to reduce the number of warnings within their data set.
FIXING ERRORS
The majority of error checking should be completed in the Workforce Profile FTE Template however; further data problems may only be discovered after validating the data in the WACA. Note: Any changes required to your data cannot be made in the WACA. You will have to go back to your Workforce Profile Sample Spreadsheet and your Workforce Profile FTE Template. The process for amending your data errors is: • • • • • • Record the employee ID and warning produced in the WACA Open your FTE Template and amend the relevant data Run the error checking and FTE Summary in your Template again Export to ASCII Logon to WACA and Import Text file Run validation.
NSW Public Sector Workforce Profile Collection
Page 23
STEP 6: EXPORTING DAT A
Once imported, data can be exported to an Excel file. There are 2 ways to export data.
91
Data can be exported from the WACA by selecting Menu Item File | Export.
1
92
Data can be exported from the View screen by clicking on the Export button.
2
Note: Data can be exported with a range of calculated fields used in the WACA. To include these calculated fields select the Include Calculated Fields option in the export process.
STEP 7: SEND A MESSAGE TO THE STATE ADMINISTRATOR
Messages in the WACA refers to the process which a User has performed in order to alert the State Administrator that they have completed the data Validation process and data is ready to be viewed. This function is only available for Agency Administrator Users and Advanced Users. Either the Agency Administrator or Advanced User of each agency submits the final version of the collection data. Once you are satisfied that your data is ready, all you have to do is to send an alert message. Note: Before sending a Message to the State Administrator you should complete the data Import and data Validation process. There are 3 ways to send a Message to the State Administrator:
1. From the WACA menu a message can be sent by
clicking on the selection Validation | Send Message to State Administrator
1
1
Page 24
NSW Public Sector Workforce Profile Collection
2. From the View data screen
click on the Send Message to State Administrator button.
2
2
3. Alternatively from the Last Validation screen
click on Send Message to State Administrator button. the
Once you have clicked on the Send message to state administrator, you will be presented with a confirmation screen. Please read the statement carefully.
9
If you understand click on the Yes button.
NSW Public Sector Workforce Profile Collection
Page 25
REPORTING IN THE W ACA
Once you are satisfied that your data is clean you can use the Report generating feature of the WACA. Here you can define your own Reports and specify the Parameters that each Report can use. Note: The Public Sector Workforce Office undertakes extensive data quality checks of data provided by agencies, but cannot accept responsibility for inaccuracies of supplied data. Users with Agency Administrator and Advanced Users Access levels are allowed to Define Reports.
DEFINING REPORTS
The first step in the reporting process is to define your Report and Report Fields.
9
Select WACA Menu Item: Reports | Ad Hoc Reports | Define. This will invoke the Adhoc Report – Select Parameters screen shown below.
Adhoc Report – Select Parameters Screen
1 2 3
9
To define a new report: Type a description for the report in the Report Name TextBox 1 When . created the report will remain as a defined report until deleted from the system using the delete button in the edit report window. Select a Primary Field 2 from the drop down list, as shown above. Any field can be selected from the imported data as well as any of the calculated fields, eg age, which is calculated by WACA. Only one selection can be made. This is referred to as the dependent field. Add Other Fields 3 - select as many fields that you may choose when the report is run to crosstabulate with. This selection is also referred to as the independent field. To select the Other Fields, highlight the name of the field in the left option pane then click on the Add button. To remove the
9
9
Page 26
NSW Public Sector Workforce Profile Collection
field from the list, highlight the field to be removed in the right option pane then click on the Remove button.
9
Click on the Create Adhoc Report button.
Note: The ability to further refine the report, i.e. selecting the measure and the independent field for cross tabulations, is offered when the report is run.
EDITING REPORTS 9 Select WACA Menu Item: Reports | Ad Hoc Reports | Edit Reports. 9
You will be presented with the Select adhoc report t o edit window populated with a list of available reports. To edit a report click on the Report Name, eg if you previously defined a report called Gender by Age, as defined in the list above, you click on this name.
9
9
Change the details of the report as required using the Add or Remove buttons. The use of these buttons is the same as in “defining reports”. When changes have been finalised Click on the Update Report button, highlighted in the screen capture, to save the changes to the report. To delete previously defined reports, Click on the Delete button.
9
9
The WACA reporting module has no facility for copying a report definition.
RUNNING REPORTS 9 To generate a Report click on
Menu Item: Reports | Ad Hoc Reports | Run Reports. The following screen will be shown. To run/generate a report click on the Report Name, eg if you previously defined a report called Gender by Age, as defined in the list above, you click on this name. Page 27
9
NSW Public Sector Workforce Profile Collection
Running a report requires the selection of several Report Parameters shown in the screen capture.
Select the report parameters according to the following definitions.
9
Select the Population Filter.
This filter gives you the following options: All – Includes all employees reported for the Reference Period, including those where Item 3g is missing. No further filters are made. All Exclude Missing - Includes all employees reported for Reference Period, excluding where, Item 3g is missing or withdrawn. Permanent – Includes all permanent employees, both Senior Officers employed under the Crown Employees Senior Officer Award and Other Permanent Employees. (Item 3g equals 1 or Item 3g equals 2). Temporary – Includes all employees employed under Section 38 of the PSM Act, employees paid a rate and entitled to leave and other benefits of employment. This group also includes temporary teachers (Item 3g equals 3 or Item 3g equals 14). Contract Executive – Includes all Senior Executive Service Employees employed under Part 2a of the Public Sector Management Act, as well as SES equivalent positions, not employed under the Act. (Item 3g equals 4 or Item 3g equals 12). Contract Non Executive – Includes all non-executive employees employed on fixed term individual contracts. (Item 3g equals 5). Casual – Includes employees engaged to perform duties on an “as-required” basis, which are usually paid at an hourly rate. (Item 3g equals 6). Other - Includes employees engaged to work for sessional periods or at a particular time of the year, Cadets, Trainees, Apprentices, Statutory Appointees and Tipstaves. Missing – Only selects employees where the employment category is reported as missing, withdrawn, or not a valid code. (Item 3g is less than zero or Item 3g is greater than 15). Active/Current at Census Date – Includes employees considered to be an employee of the agency on the Census Date. (Item 3h equals 1).
Page 28
NSW Public Sector Workforce Profile Collection
Not Employed at Census Date - Includes employees not considered an employee of the agency on the Census Date. (Item 3h equals 2). Did Not Work Census Period – Includes casual employees who did not work during the last pay period of the reference period, but is considered to be an employee of the agency at this date. i.e. “on the books”. (Item 3h equals 3).
9
Select the FT or PT filter.
This filter gives you the following options: All - includes All Employees, both full-time and part-time. No employees are excluded. Full Time – Employees are considered to be full-time if they work 35 hours a week or more. Part Time - Employees are considered to be part-time if they work less than 35 hours a week.
9
Select the Measure.
This selection gives you the following options:
Head Count – This measure counts all employees who have worked during the period, even if they only work for one day. Quarterly Period FTE – This measure translates the hours each employee works into a proportion of the hours worked by an equivalent full time staff member. For example, a person working one day per week would be calculated at 0.2 FTE. This measure is an average FTE during the Quarterly reference period. Census Period FTE – This measure translates the hours each employee works into a proportion of the hours worked by an equivalent full time staff member. For example, a person working one day per week would be calculated at 0.2 FTE. This measure is an FTE taken at a point of time, i.e. the last pay fortnight of the Quarterly FTE collection. Primary Service Function FTE - This measure sums the proportion of “Census Period FTE” attributed to the primary service function of the established position. Note: The Secondary Service Function FTE measure should be run as a separate report and aggregated with this report when reporting on the FTE contribution to “Service Functions” within an agency. Secondary Service Function FTE - This measure sums the proportion of “Census Period FTE” attributed to the secondary service function of the established position. Note: The Primary Service Function FTE measure should be run as a separate report and aggregated with this report when reporting on the FTE contributed to “Service Functions” within an agency. Movement Rates - This measure calculates the Separation and Commencement Rates for the agency. Separation Rate – The number of employees from the agency as a proportion of the average number of people employed in the agency over a given time period. The separation rate is calculated for noncasual employees for the quarterly reference period. The formula used for the 1st Quarterly FTE collection was: Commencement Rate – The number of people starting work with an agency as a proportion of the average number of people in the agency over a given time period. The commencement rate is calculated for non-casual employees for the Quarterly Reference Period. The formula used for the 1st Quarterly FTE collection was: Error! Objects cannot be created from editing field codes.
NSW Public Sector Workforce Profile Collection
Page 29
Sick Leave - This measure sums Item 5d_q “Paid Sick Leave Taken during the Quarterly Reference Period”.
9
Select the Primary Field.
Select a Primary Field from the drop down list. Only one selection can be made. Any field specified in the data specification for the collection being reported can be selected. In addition, WACA generates some calculated items, eg age. A comprehensive list of these calculated items is shown in the table on the next page. This field is also referred to as the dependent field.
Item Name
Age Age Bracket 16–99 < 25 25–34 35–44 45–54 55–64 65 + Length of Service Service Bracket 0- < 3 3< 5 5<9 10 <15 15 < 20 20 + Remuneration Band
Item Range
Comment
Calculated age of employee, (in whole years) Calculated age of employee, (in whole years) grouped into age groups.
Length of Service in an Agency. Calculated using Item 6b. Length of Service in an Agency. Calculated using Item 6b, grouped into brackets.
< $30,146 $30,146–$44,264 $44,265–$56,012 $56,013–$72,434 $72,435–$90,542 $90,543–$122,499 $122,500 +
Calculated Remuneration Band of the employee using Item 4b.
ASCO 1 Lvl ASCO 2 Lvl ASCO 3 Lvl Type of Duties Team Customer Position Customer
rd nd
st
1–9 11–99 111–999 1–5 1–5 1–5
ASCO Level One, using the 1 digit of the position code, Item 3l. ASCO Level Two, using the 1 two digits of the position code, Item 3l. ASCO Level Three, using the 1 three digits of the position code, Item 3l. Type of Duties, using 4 digit of the position code, Item 3l Team Customer, using 5 digit of the position code, Item 3l Position Customer, using 6 digit of the position code, Item 3l. Primary Service Function, using the 7 and 8 digits of the position code, Item 3l. Secondary Service Function, using the 9 and 10 digits of the position code, Item 3l.
th th th th th th th st st
st
Primary Service Function Secondary Service Function
11–99 11–99
Page 30
NSW Public Sector Workforce Profile Collection
9
Select the Primary Field Location.
This selection determines the location of Primary Field in the report. The options are: Down the side. (Row – Default). Across the top. (Column).
9
Select the Other Field
Select a field that you would like to cross-tabulate with. Like the primary field selected when defining the report you can select any imported item, or items generated by WACA and listed in the table above. This field can be referred to as the independent field.
9
Select the Run Report On field.
This allows you to either run the report on the “Imported data” or the “Historical data”. The imported data is data imported by the agency and not yet verified and saved by the NSW WACA administrator. Historical data is data which has been submitted by the Agency and saved by the NSW WACA administrator into a permanent table for each agency.
9
Click on the Run Report button.
This selection will actually run the report using all the selections outlined above. The output is presented in an excel table, as well as an excel chart. Note: You can only report on items selected when the report was defined. To add or remove items use the edit report function to modify the report. If you are a Base user then you can only run a Report on Historical Data.
DOWNLOADING & SAVING REPORTS
Each download report can either be saved to your computer or immediately opened for viewing in the browser.
9
After running the report generator, you will see the Reports – Generation completed dialogue. The report can be downloaded to your PC by clicking on the link Click here to download. Or follow the directions on the dialogue. Click on the Save File Download dialogue. button in the
9 9
In the Windows™ Save As dialogue, change the file name and browse to the location where you will save the file. Ensure that the “Save As Types” is set to “Microsoft Excel (.xls)”. Click on the Save button. Dependent on your Windows™ settings when the file has downloaded the Download Complete dialogue may be shown. Click on the Open button to view the file in Microsoft™Excel, where installed.
9 9
9
NSW Public Sector Workforce Profile Collection
Page 31
9 9
To immediately open the report for viewing in Excel, within the browser window, click on the link Click here to download. Click on the Open button.
Note: Microsoft™Excel must be installed on your PC in order to view the Excel Spreadsheet. Sample Report
Sample Chart
Page 32
NSW Public Sector Workforce Profile Collection
REPORTING SCENARIO SAMPLES
The reporting features of WACA are interactive, which allows users to select the items for reporting. Care should be taken in selecting items as not all items will produce useful data. Some typical reporting requirements are listed below to assist agencies in selecting items to report on. Sample Reporting Scenarios
Report Requirements
The number & gender of employees in Agency. The FTE as at the Quarterly Census Date. FTE Attributed to Internal Customers (need to run measure 1, then measure 2 and sum the two reports). All
Population Filter
FT or PT
All
Measure
Headcount
Primary Field
Agency Code
Other Field
Gender
All
All
Census Period FTE 1. Primary Service Function 2. Secondary Service Function
Agency Code
Agency Code
All
All
Agency Code
Service Function
Number of Employees by Employment Category. The Full time equivalent rate of employees at the Census date by Gender. The Full time equivalent rate of employees for the Quarterly Reference period by Employment Category and Gender. Employee Movement of agency employees by gender for the Quarterly Reference Period. The remuneration band of full time permanent employees by Gender. Age Profile of Agency Employees. Agency Separation, Commencement and Stability Rates.
All
All
Headcount
Agency Code
Employment Category Gender
Active/Current at Census Date All
All
Census Period FTE
Agency Code
All
Quarterly Period FTE
Employment Category
Gender
All
All
Quarterly Period FTE
Employment Movement
Gender
Permanent
Full Time All
Headcount
Rem Band
Gender
All
Headcount
Age bracket
Gender
All
All
Movement Rates
Agency Code
Agency Code
NSW Public Sector Workforce Profile Collection
Page 33