Slide 1

Introduction to the Query System (APEX) Presented by: Lori Johnson, MIS Administrator Central Area Michigan Works! Consortium Bill Hellar, CIO SEMCA Nancy Gibson, Information Systems Specialist W.E. Upjohn Institute, Michigan Works! Kalamazoo-St. Joseph

Slide 2

Objectives Review tools available under the APEX system Navigate through the APEX system Find OSMIS screen documentation Run Canned Queries Show how to build a simple query using the APEX Query Builder Time for questions and answers

Slide 3

What is the Query System (APEX) https://query.michworks.org https://query.michworks.org Available only to those who have requested access from their MWA OSMIS Administrator. All tools on the query system are accessible with the same username and password. Set of tools made available by the State to query data stored in the OSMIS. APEX Document Libraryall OSMIS screens have been documented to show what table and field would be needed for each piece of data in OSMIS to aid in creating your own queries. APEX Canned Query a number of canned queries ready to run without having to write a query. Application Express (APEX)develop and write SQL queries to pull information from OSMIS.

Slide 4

Query System Navigation:

Slide 5

APEX Document Library

Slide 6

Training documents and training videos for the APEX system available here Every OSMIS screen is documented with the APEX tables and fields you would need to develop your own queries Screen documentation details the table name and the field name for each field you enter data for in the OSMIS OMSIS screens are labeled first by program then by OSMIS menu item ADULT- Registration WR Support Service OY Funding Source

Slide 7

Document Library Navigation

Slide 8

APEX Canned Query

Slide 9

Canned query site features a number of ready made queries for each program Easy as click Go Performance Reports: Details MWA created reports for TAA, Wagner-Peyser, PATH, and WIA programs Canned queries for each WIA Performance measure have been developed by the APEX Workgroup Reference Tables: Quick query to find values found in fields that end in _CD, Program Codes, Service Codes, and Zip Codes My MWA: Section for each individual MWA to have their own canned queries added for all users within that MWA OSMIS Administrator must ask OSMIShelp@michigan.gov for the inclusion to this menu itemOSMIShelp@michigan.gov Queries to be added to the MY MWA menu will need to be requested by your OSMIS Administrator

Slide 10

APEX Canned Queries Home

Slide 11

Canned Query Functionality

Slide 12

Canned Query Functionality--Actions The Actions button allows you to customize any canned query to your preferences Select ColumnsRemove columns from the output FilterAdd filter criteria (or conditions) to the canned query Rows Per PageCanned Query defaults to show only the first 15 results. Format Sort, Control Break, Highlight, Compute, Aggregate, Chart, Group By Save ReportSave any customizations you have done to the canned report Downloaddownload report results into excel spreadsheet

Slide 13

Canned Query Functionality-Select Columns

Slide 14

Canned Query Functionality--Filter

Slide 15

Canned Query Functionality--Download

Slide 16

Canned Query Functionality--Rows Per Page The canned query defaults to only show the first 15 rows for each query Get in the habit of choosing the Rows Per Page function from the drop down to retrieve all your results.

Slide 17

Canned Query--My MWA Menu Access for only your MWA Helpful to save queries that your agency use frequently Queries are built by you and then the OSMIS help desk assists by uploading the query to the canned query site

Slide 18

Application Express (APEX)

Slide 19

Tool to build and write your own queries Two different tools available: Query Builder: simple point and click tool to build simple queries. Can join tables, do some aggregate functions, sort your query. Most queries can be built within Query Builder SQL Commands: area where you can write your own SQL (structured query language) query. Can process much more complex queries. Can save queries built in both Query Builder and in SQL Commands But can only access that saved query in the same tool in which it was saved

Slide 20

Query Builder Click on SQL Workshop drop down menu Navigate to Query Builder

Slide 21

Query Builder Navigation

Slide 22

Query Builder Choose the table(s) you want from the Object plane-it displays in the Design Pane Select the columns (OSMIS fields) you want to display in your results by clicking the box located to the left of the field. Now you should see your column selection in the Results Pane below Once you have selected all the columns from your corresponding tables, you will set up conditions and run your query Tip: Use the condition document found in the library document as a help aid Select RUN Query results will show in the result pane where you set your conditions Save the query for future use.

Slide 23

Query Builder-More than 500 rows By default, Query builder will only return the first 500 rows To see all results, must copy and past the SQL from Query Builder to the SQL Commands tool. Choose SQL in the Results Pane Copy the query Use the shortcut in the menu bar to get to SQL Commands Paste the query in the top box Choose ALL from the drop down row box Click Run

Slide 24

Questions?