HCM Procedures Manual - TN

STATE OF TENNESSEE

HCM Procedures Manual

Running Queries and Reports

11/5/12

State of Tennessee HCM Procedures Manual

HCM Procedures Manual – Running Queries and Reports

Information concerning how to run existing HCM and ELM queries in the Edison system is provided in this manual. Information on where module specific HCM and ELM queries are listed is also provided.

Table of Contents A. Introduction …………………………………………………………………….…………………………….….... A.1. Queries and Reports Defined…………………………….…….................................... A.2 . Differences between Queries and Reports …............................................... A.3. Security Access for Queries and Reports………………………………….….....….……..

3 3 3 3

B. Running a Query………………………………………………….………….……….……………………..….…. B.1. Things to Know about Run Control IDs……………….……................................... B.2. Steps to Create a Run Control ID …………………………………………………............... B.3. Searching for a Query……………………………………………………………….…....…………. B.4. Running the Query - Process Scheduler Request…….…….............................. B.5. Running the Query - Process Monitor and Results………………………..……………

3 3 4 6 8 14

C. Re-running a Query Using an Existing Run Control ID………………………….……....…………. 19 D. Updating Parameters …………………………………….….………………………….………..………………… 22 E. Lists of HCM and ELM Queries ………………………………………………………….…..……….………… 25

November 5, 2012

HCM Procedures Manual – Running Queries (return to top)

Page 2


RUNNING QUERIES AND REPORTS A. INTRODUCTION A.1 Queries and Reports Defined In Edison, queries and reports are requests for specific, existing information to be retrieved from the Edison database. Because the Edison system has multitudes of HCM, ELM, and FSCM information and transaction history, a query or report pinpoints the information being requested. A.2 Differences between Queries and Reports HCM queries are run in the HCM Query Scheduler section of HCM Reporting Tools. ELM queries are run in the ELM Query Scheduler section of ELM Reporting Tools. The user may select the format to receive the data including HTML or Excel. Once the information is returned, the user has the ability in some of these formats to manipulate the data by sorting, filtering, or editing to meet their need. Reports are accessed through each specific HCM functional module such as Payroll, Time and Labor, HR, or Benefits. Like queries, reports allow users to retrieve specific data. Reports differ from queries in that the data is retrieved and returned in a predetermined format. Some reports have the option for the user to manipulate the data that is received. A.3 Security Access for Queries and Reports Access and ability to run queries and reports is determined by the employee’s security roles and data access. B. RUNNING A QUERY There are multiple steps involved in running a query. First, a Run Control ID must be established or identified. Then the query can be scheduled and run. These processes and steps are detailed in the following pages. B.1

November 5, 2012

Things to Know about Run CONTROL IDS: • Run Control IDs are required when running a query or a report. • It is a unique name created for a specific query by a user. Users should only have access to their own Run Control IDs. • The same Run Control ID will be used each time the user wants to run a specific query or report. • Run Control IDs cannot be deleted, so carefully check the name entered before clicking the Add button. The more Run Control IDs created, the longer the list will be when searching for an existing Run Control ID. • The user should select a meaningful naming scheme that allows them to easily identify their Run Control ID for a corresponding query or report. • It must be entered as one word, no spacing. HCM Procedures Manual – Running Queries (return to top)

Page 3


B.2

Steps to Create a Run Control ID

1. Navigate to HCM Reporting Tools> HCM Schedule Query. All HCM queries should be run through the HCM Schedule Query function.

2. When running a query for the

first time, click the Add a New Value tab to create a Run Control ID.

November 5, 2012


Page 4


3. For this example, the user wants to run query TN_HR89_INVALID_NAMES_TN. Create the Run Control ID “InvalidName” for the query by entering it in the Run Control ID box and then clicking the Add button.

4. The system then advances to the Schedule Query page. The newly created Run Control ID is at the top of the page. Next, identify the query associated with this Run Control ID.

November 5, 2012


Page 5


B.3 Searching for a Query The query to be associated with the new Run Control ID must be identified. If the user knows the query name, it may be entered in the Query Name box on the Schedule Query page. If the user is uncertain of the complete query name, a search may be conducted.

1. Identify the query associated with the Run Control ID,

Things to know about Queries: • HCM and ELM Queries were developed using a naming convention specific to each HCM module and ELM. If uncertain of an exact query name, the user may search by entering the appropriate module listed below. By using the module naming conventions listed below, the search will return all queries for that module that begin with the same naming convention (TN_ two letter.) TN_AM (Absence Management) TN_BA (Benefits Administration) TN_HR (Human Resources) TN_TL (Time and Labor) TN_PY (Payroll) TN_EL (ELM) • When searching for queries, search results are limited to 300 queries. If a search is conducted only on the module name (TN_HR, TN_TL, TN_PY, etc.), the query results will only display the first 300 queries for each module, which in some cases, will not be a complete list of all queries from that module.

November 5, 2012


Page 6


2. In this example, the user is uncertain of the query name, but remembers the first part containing HR and the number 89. Enter TN_HR89 in Query Name and click on the Search button.

3. The Schedule Query Search Page displays and returns one result, “TN_HR89_INVALID_NAMES_TN.” If just TN_HR had been entered in the search, multiple query results would have been returned. Click on the TN_HR89_INVALID_NAMES_TN link.

November 5, 2012


Page 7


4. The Query Name and Description fields populate. Select Save for query TN_HR89_INVALID_NAMES_TN to be associated with Run Control ID, InvalidName. Use this Run Control ID to run this query in the future.

B.4 Running the Query – Process Scheduler Request Now that the Run Control ID has been established and the query associated with the Run Control ID has been identified, it is time to run the query. First, information will need to be entered in the Process Scheduler Request.

1. Click the Run button.

November 5, 2012


Page 8


2. The user is taken to the Process Scheduler Request page.

3. The query has not been run at this point. The user must determine the Format for the query results to be delivered. Options including TXT(text), PDF, HTML, and XLS(Excel) are available.

November 5, 2012


Page 9


4. In this example, click the drop down box under Format and select XLS from the list. The query will now be delivered in an Excel spreadsheet format when it is completed.

November 5, 2012


Page 10


5. If wanting to distribute the results of this query to other users, click on the Distribution link.

6. The Distribution Detail page displays. From here the user enters information for the accompanying e-mail as well as the list of users to whom the email and the query results will be distributed. Click OK when finished entering information and return to the Process Scheduler Request page. November 5, 2012


Page 11


7. When scheduling, DO NOT enter any values in the Server Name or Time Zone fields.

8. There is no need to change the information in the Run Date or Run Time fields. The values in these fields reflect current date and current time information.

9. Do not alter the Type field value (in this example, the value in the field is “Web”.) November 5, 2012


Page 12


10. Click the OK button.

11. The Schedule Query page displays. Process Instance number 1701718 has been assigned to this query process which is now scheduled to be run.

November 5, 2012


Page 13


B.5 Running the Query – Process Monitor and Results On the Process Monitor page, information about the query being processed can be monitored.

1. From the Schedule Query page, click the Process Monitor link.

2. Select the Process List tab. The Process List tab is divided into 2 parts: - View Process Request For - Process List

November 5, 2012


Page 14


3. The View Process Request For section contains the User ID of the person running the query. Note: Verify the User ID displayed in the User ID box is yours. If the User ID is incorrect, information results from the query may not be displayed. Correct the User ID if necessary and click the Refresh button.

4. The criteria specified in the View Process Request For section determine the number of queries that will appear in the Process List section. In this example, the Last block indicates results will be returned for queries run over the past “1” day. If, for example, the user would like to view queries run over the past 10 days, the number “10” can be entered in the Last block, and the Refresh button clicked to display the results. It is recommended to keep the Server, Type, and Name boxes blank.

November 5, 2012


Page 15


5.

6.

The Process List section displays the Process Instance number, the User ID of the individual running the query, and the date and time the query was run (Run Date/Time.)

Two important items contained in the Process List are the Run Status and Distribution Status. The query finishes running successfully when the Run Status is Success and the Distribution Status is Posted.

Note: Queries may not return data instantly. Some queries may take several minutes or longer to begin or complete running. Click the Refresh button multiple times until the Run Status changes to Success and the Posted Status changes to Posted. Other common statuses that may occur include: Processing - no results available, query still processing Queued - request is waiting to begin processing in the system Cancelled - query cancelled and will not run November 5, 2012


Page 16


7.

Click the Details link when the Run Status is Success and the Distribution Status is Posted.

8. The Process Detail page displays. From here, click the View Log/ Trace link.

November 5, 2012


Page 17


9.

The View Log/Trace page displays. The name of the query appears, with the designated format type xls, in the File List section. Click the TN_HR89_INVALID_NAMES_TN-1798214.xls .

10. Query TN_HR89_INVALID_NAMES_TN-1798214 displays in the requested Excel spreadsheet format. The query results may be saved by the user in this format.

November 5, 2012


Page 18


Note: For this particular HR query, only one result was returned, as there was only one name found to be invalid.

C. Re-Running a Query Using an Existing Run Control ID The same Run Control ID should be used whenever a user runs the same query. For example, if a user has already set up a Run Control ID with the name “Staffing Pattern” for running the query TN_HR44_STAFFING_PATTERN_RPT, this same Run Control ID should be used each time the TN_HR44_STAFFING_PATTERN_RPT query is run.

1. Navigate from the HCM Reporting Tools page and click on the HCM Schedule Query link.

November 5, 2012


Page 19


2. The Schedule Query page displays. Since query TN_HR44_STAFFING_PATTERN_RPT has previously been run by this user, click on the Find an Existing Value tab.

3. Search for the previously used Run Control ID. If the name or a portion of the name of the Run Control ID is available, enter the known portion in the begins with field. To return all previously created Run Control IDs, leave the begins with field blank. Click Search.

November 5, 2012


Page 20


4. The search results are returned. Because the begins with field was left blank, all Run Control IDs previously created by the user are returned and listed in alphabetical order.

Note: If the user has previously created only one Run Control ID, the user will not see a list of Run Control IDs displayed. Instead, the user will be taken directly to the Schedule Query page. If the user can’t find the Run Control ID, he should carefully search both the Run Control ID field and the Query Name field found in the search results. Run Control IDs can’t be deleted once they are created.

November 5, 2012


Page 21


5. Listed to the right of each Run Control ID is the Description and Query Name of the query associated with that Run Control ID. Click on Run Control ID “StaffingPattern”.

6. The Schedule Query page displays with the Run Control ID, the full Query Name, and the Description. The user may continue the process of scheduling and running the selected query. (See Section B4 to review Steps of Running a Query.)

D. Updating Parameters When an existing Run Control ID is selected to run a query, the previous parameters and prompt values are displayed on the Schedule Query page.

November 5, 2012


Page 22


1. In the parameters, this query contains a Prompt Name, Business Unit, with a value of 31701 displayed. It also displays a date value of 2010-04-01. These were the values last used to run the query. As a reminder, the values entered in the Prompt fields determine the query results returned.

2. To re-run the query with a different value for Business Unit or date, select the Update Parameters link.

November 5, 2012


Page 23


3. The Update Parameters page displays. In this example, there are two prompts. Select the values for each parameter on the page. Use the look-up or calendar buttons for help in choosing an appropriate value. The type and number of prompts will vary by query.

4. Once the parameters for the query have been entered, click the OK button to return to the Schedule Query page. Continue scheduling and running the query. (See Section B4 to review Steps of Running a Query.)

November 5, 2012


Page 24


E. Lists of HCM and ELM Queries In Edison, queries and reports are requests for specific, existing information to be retrieved from the Edison database. Because the Edison system has multitudes of HCM, ELM, and FSCM information and transaction history, many queries pinpointing or requesting specific information have been created. Lists of available HCM and ELM queries may be located by navigating to the following in Edison: Edison Support Info HCM > HCM Query and Report Info. Here the HCM modules of Time and Labor, Payroll, HR, and Benefits, as well as ELM have lists of available queries. Query names are accompanied by descriptions and applicable support information for each query. These listings are of public or shared queries available to agency users having the correct security access to run them.

November 5, 2012


Page 25

HCM Procedures Manual - TN

Recommend Documents