1 / 64

State of Connecticut Core-CT Project Query

State of Connecticut Core-CT Project Query. Updated 4/14/2003. Welcome. Getting Started. Welcome to the Query course! Instructor introduction Participant introduction Training facility orientation Ground rules Parking lot

brendan-powers
Download Presentation

State of Connecticut Core-CT Project Query

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. State of ConnecticutCore-CT Project Query Updated 4/14/2003

  2. Welcome Getting Started • Welcome to the Query course! • Instructor introduction • Participant introduction • Training facility orientation • Ground rules • Parking lot • To participate in this course, you should have already completed the following training: • PeopleSoft Navigation Tutorial • HOV 100 - HRMS Business Process Overview

  3. Welcome Objectives • In this training, you will learn to: • Use the basic concept of Query in Core-CT. We will specifically focus on the following topics: • Data Dictionary • Core-CT Query Standards • Running an Existing Query and Viewing Results • Creating a New Query • Maintaining a Query • Defining Query Criteria • Advanced Query Options • Use Core-CT as a tool in supporting these topics • Utilize Core-CT functionality to maximize your efficiency

  4. Agenda

  5. Agenda

  6. Welcome Training Tools • In this training session, we use tools created specifically for this course • Presentation • We use a PowerPoint presentation to guide us through our discussion of processes and key points related to using Core-CT • Exercises • We developed exercises that enable you to practice the skills you learn in each module • Training Database • The functionality in our database mirrors the Core-CT production environment closely, though changes may be made prior to go-live • Significant changes made after training will be communicated to you • Data is loaded into the Core-CT training database to help you learn how to use Core-CT, not to reflect a specific agency’s transactions • You can make mistakes and experiment without consequence

  7. Welcome Training Tools • We also use a web-based help system during the class. This tool is available to you after go-live. • Web Learning Assistant • Web-based tool that provides help and detailed information for specific Core-CT job functions • Contains: concepts, procedures, flows, and a glossary • The WLA is your primary reference tool (now and once Core-CT goes live) • The WLA will evolve with Core-CT. As functionality changes, so will the content in the WLA Let’s walk-through the tool together now WLA Browse Path: Core-CT Information Access > Core-CT Query > Query Basics > Creating Simple Queries

  8. Welcome Training Tools • Job Aids are available to reinforce the information introduced in this course. Job Aids summarize key information and outline critical processes in Core-CT. • General Job Aids • Module Specific Job Aids • Reporting Job Aids

  9. Welcome Core-CT Navigation • Together we will practice the skills you learned in the Core-CT Navigation Tutorial • Universal Navigation Header • Activity: Log in and use the Universal Navigation Header to sign off Core-CT • Left-Hand Navigation Menu • Activity: Use the Left-Hand Navigation Menu to step through the various levels of the directory structure

  10. Agenda

  11. Data Dictionary Overview •    The Data Dictionary: • Provides you with information about the fields that make up the Enterprise Performance Management (EPM) Reporting Tables • Enables you to look up and review descriptions of fields that you may be unfamiliar with in Core-CT

  12. Data Dictionary Overview • Separated for HRMS and Financials, the Data Dictionary contains the following information for each reporting table: • Field – the data field name (e.g. DEPTID or VENDOR_ID) • Label – the name of the field as it appears on the page (Department or Vendor ID) • Type – distinguishes whether the field is made up of characters (char), numbers (nbr), or dates • Length – the length of the field • Description – a brief description of the field

  13. Data Dictionary Walk-through and Exercise • Let’s review the Data Dictionary: • First, we will access the Web Learning Assistant • WLA Path: Core-CT Information Access > Core-CT Query > Query Basics > Using the HRMS Data Dictionary • Core-CT Path: EPM Reporting Tools > EPM Data Dictionary • On your own, review some of the HRMS Reporting Tables • Click the specific Reporting Table link to access the table • Raise your hand if you need any assistance

  14. Agenda

  15. Core-CT Query Standards Overview • Private vs. Public Queries: • Private Queries – only the User ID that created the query can open, run, modify, or delete the query • Public Queries – any user with access to the records used by the query can open, run, modify, or delete the query

  16. Core-CT Query Standards Overview • Naming Standards: • All query names must begin with the first 3 letters of your Department ID • Use letters and numbers (no symbols) • Use underscores (_) instead of spaces

  17. Agenda

  18. Using an Existing Query Overview • Query is an end user reporting tool which allows you to specify and extract the precise information that you want to retrieve from Core-CT and use it for many purposes • You can use queries in the following ways: • To run queries as a separate process • To display data • To download query results to an Excel spreadsheet to further manipulate the data

  19. Using an Existing Query Process Flow • Where does the Core-CT system fit into the process? Access Query Manager Define Selection Criteria? No No Output to Excel or Page? Excel Download Query to Excel Create Joins? Run Query Create Query Yes Yes Page Add Record Join Define Selection Criteria View Query on Page

  20. Using an Existing Query Key Points • When using an existing query, please note the following: • The information you extract from Core-CT is data refreshed from the HRMS or Financials system as of the previous day. As a result, all of the information is only as current as the previous day. • The Run option is useful if you want to run multiple queries or run the same query multiple times with different run time prompt values and compare the results of the queries • You can configure your environment to open the Excel file in a separate window or save it as a file on your local hard drive by modifying the File Type Option settings in Excel • When running a report to Excel, the data is “static” and will not automatically update when the query is run again. You will have to download the query to Excel again to see the updated version.

  21. Using an Existing Query Walk-through and Exercise • Let’s log-in to Core-CT • First, we will walk through the process together (Exercise #1) • Scenario: Running a Query and Downloading Results to Excel • WLA Path: • Core-CT Information Access > Core-CT Query > Query Basics > Running Queries • Core-CT Information Access > Core-CT Query > Query Basics > Downloading a Query to Excel • Core-CT Path: Reporting Tools > Query > Query Manager • On your own, complete Exercise #2 in your Exercise packet • Remember to utilize the Web Learning Assistant • Raise your hand if you need any assistance

  22. Agenda

  23. Agenda

  24. Creating a Query Overview • Creating your own queries enables you to select the tables that you want to execute the query from and tailor the fields so that only the data you want displays • The steps involved in creating a simple query include: • Selecting records • Adding fields • Editing field properties • Editing query properties • A more complex query may also include: • Defining selection criteria • Creating joins

  25. Creating a Query Process Flow • Where does the Core-CT system fit into the process? Access Query Manager Define Selection Criteria? No No Output to Excel or Page? Excel Download Query to Excel Create Joins? Run Query Create Query Yes Yes Page Add Record Join Define Selection Criteria View Query on Page

  26. Creating a Query Key Points • When creating a query, please note the following: • You can extract precise information using visual representations of your Core-CT database, without writing Structured Query Language (SQL) statements • If you click the Save button without first completing the Properties page, a dialog box displays prompting you to type the Query Name, Description, and Owner fields

  27. Creating a Query Walk-through and Exercise • Let’s log-in to Core-CT • First, we will walk through the process together (Exercise #3) • Scenario: Creating a New Query • WLA Path: Core-CT Information Access > Core-CT Query > Query Basics > Creating Simple Queries • Core-CT Path: Reporting Tools > Query > Query Manager • Remember to utilize the Web Learning Assistant • Raise your hand if you need any assistance

  28. Creating a Query Troubleshooting • If the query does not produce the desired results, you can: • Access the Edit Field Ordering page and re-number the columns in the New Column group box • Access the Edit Field Properties page and replace the Column number with next text

  29. Creating a Query Walk-through and Exercise • Let’s log-in to Core-CT • First, we will walk through the process together (Exercise #4) • Scenario: Creating and Modifying a Query • WLA Path: Core-CT Information Access > Core-CT Query > Query Basics > Creating Simple Queries • Core-CT Path: Reporting Tools > Query > Query Manager • On your own, complete Exercise #5 in your Exercise packet • Remember to utilize the Web Learning Assistant • Raise your hand if you need any assistance

  30. Agenda

  31. Agenda

  32. Maintaining a Query Overview • Core-CT enables you to maintain and update existing queries by: • Updating existing query information to fit your query needs • Renaming queries • Deleting queries that are no longer needed

  33. Maintaining a Query Process Flow • Where does the Core-CT system fit into the process? Access Query Manager Define Selection Criteria? No No Output to Excel or Page? Excel Download Query to Excel Create Joins? Run Query Create Query Yes Yes Page Add Record Join Define Selection Criteria View Query on Page

  34. Maintaining a Query Walk-through and Exercise • Let’s log-in to Core-CT • First, we will walk through the process together (Exercise #6) • Scenario: Managing a Query • WLA Path: Core-CT Information Access > Core-CT Query > Query Basics > Deleting Queries • Core-CT Path: Reporting Tools > Query > Query Manager • On your own, complete Exercise #7 in your Exercise packet • Remember to utilize the Web Learning Assistant • Raise your hand if you need any assistance

  35. Agenda

  36. Defining Query Criteria Overview • Core-CT enables you to add the following criteria to your query: • Selection Criteria • Expressions • Effective Date Criteria

  37. Defining Query Criteria Overview – Selection Criteria • Selection criteria enables you to selectively retrieve only the data you want • Refines your query by specifying conditions that the retrieved data must meet • Serves as a test that Core-CT applies to each row of data in the table that you are querying • If a row passes, Core-CT retrieves it • If a row does not pass, Core-CT does not retrieve it

  38. Defining Query Criteria Overview - Expressions • Expressions are calculations that Core-CT performs as part of a query. • You can use expressions: • To calculate a value that Core-CT does not provide by default • As comparison values in selection criteria • As columns in the query output

  39. Defining Query Criteria Overview – Effective Date Criteria • The Effective Date (EFFDT) field provides a historical perspective, allowing you to see how the data has changed over time • When you add a row of data to an effective dated table, you specify the date on which the data becomes effective • When you add a row of data, you specify a new effective date and Core-CT retains the previous version of the row as history • Please note that you can only specify effective dated criteria for tables that contain the EFFDT field

  40. Defining Query Criteria Process Flow • Where does the Core-CT system fit into the process? Access Query Manager Define Selection Criteria? No No Output to Excel or Page? Excel Download Query to Excel Create Joins? Run Query Create Query Yes Yes Page Add Record Join Define Selection Criteria View Query on Page

  41. Defining Query Criteria Process Flow • Where does the Core-CT system fit into the process? Define Selection Criteria? Yes Define Selection Criteria Add Expressions Add Prompts Add Criteria Add Having Criteria

  42. Defining Query Criteria Walk-through and Exercise • Let’s log-in to Core-CT • First, we will walk through the process together (Exercise #8) • Scenario: Adding Criteria to a Query • WLA Path: Core-CT Information Access > Core-CT Query > Defining Selection Criteria > Entering Selection Criteria • Core-CT Path: Reporting Tools > Query > Query Manager • On your own, complete Exercise #9 in your Exercise packet • Remember to utilize the Web Learning Assistant • Raise your hand if you need any assistance

  43. Agenda

  44. Agenda

  45. Defining Prompts Overview • When running a query, you can add a prompt to further refine a query • A dialog box displays for you to specify the values • The query uses the value as the comparison value • Query results are returned based on the prompt value

  46. Defining Prompts Process Flow • Where does the Core-CT system fit into the process? Access Query Manager Define Selection Criteria? No No Output to Excel or Page? Excel Download Query to Excel Create Joins? Run Query Create Query Yes Yes Page Add Record Join Define Selection Criteria View Query on Page

  47. Defining Prompts Process Flow • Where does the Core-CT system fit into the process? Define Selection Criteria? Yes Define Selection Criteria Add Expressions Add Prompts Add Criteria Add Having Criteria

  48. Defining Prompts Key Points • When defining prompts, please note the following: • To ensure that the user selects only valid values for the field • Select an associated prompt table (if applicable) from the Edit Prompt Properties page • To allow the use of a wildcard (%) in order to return all values • Select “No Table Edit” from the Edit Type drop down list on the Edit Prompt Properties page • Select “Like” from the Condition Type drop down list on the Edit Criteria Properties page

  49. Defining Prompts Walk-through and Exercise • Let’s log-in to Core-CT • First, we will walk through the process together (Exercise #10) • Scenario: Adding Prompts to a Query • WLA Path: Core-CT Information Access > Core-CT Query > Defining Selection Criteria > Defining Prompts • Core-CT Path: Reporting Tools > Query > Query Manager • Remember to utilize the Web Learning Assistant • Raise your hand if you need any assistance

  50. Agenda

More Related