Meta Queries Workshop Scott Joyce Advanced Meta Queries Which - PowerPoint PPT Presentation

Meta Queries Workshop Scott Joyce

Advanced Meta Queries • Which table do I use? • How do I link two or more tables? • Tips and Tricks • New Features 2

Reference Guides & Standard Tables • Keystone has delivered several standard Keystone Meta Tables with the last few software updates. • These tables will have a number of links built in, which customers can reference and reuse to build their own tables. 3

Reference Guides & Standard Tables • Using Standard Meta Tables is ideal since it: • allows you to share queries easier • Easier to support • Enable you to take advantage of new features and integration • An easy way to reuse these tables for your own purposes – to minimize the data that is accessible - is to copy the standard table to a customer table using the “View/Copy Keystone Meta Tables” utility (KZ0333). 4

KEMS Standard Meta Tables • Employee Data – comprehensive list of all employee-related data fields (658 fields and counting!) • Other standard tables include: • Missouri CoreData • Applicants • Position Control • Benefits Enrollment Roster • Employee Leave • Salary Guides • KeyTime • Leave Transactions 5

FIS Standard Meta Tables • General Ledger Detail • Invoice History • Purchase Orders • Vendor 6

Reference Guides • We have put together reference guides / complete lists of data files and fields in Keystone’s FIS/KEMS applications. These are available in our Customer Resources / Web SCARs portal: • KEMS: Go to User Manuals  KEMS (HR, Payroll)  Meta Queries • Financials: Go to User Manuals  Financials  Meta Queries 7

Meta Table • Create the Meta Table (KZ0133) • Manual entry or “Load” option • Link Files • Primary File • Secondary File • Sort Options • Renaming Meta Display (Log Out) 8

Locating Data Fields • Place your cursor in the field you would like to locate: 9

Locating Data Fields (cont’d) • Click the help icon ( ) or hit the F1 key: 10

Meta Queries Toolbar / Icons • New / Save / Save As / Delete • Copy / Share / Unshare / Add to Shortcuts • Show/Hide Criteria • Add Criteria / Properties / Sort / Multi-Level Sort • Run Query / Run Query Sample • Export Options (Excel, Text, XML) • Exit 11

Meta Query • Create the Query • View fields (select) • Load All Option • Test Sample Data • Organize the Report • Insert, Delete Columns • Add Criteria • Test Criteria • Nest Criteria • Use the ? Option • Multiple Sort Option • Operators 12

Operators • # not equal to Example; BENEFIT.GROUP # ADMIN Results; All benefit groups except ADMIN • < less than Example; HIRE.DATE < 01.01.XX Results; All employees hired before 01.01.XX • = equal Example; ZIP = 08002 Results; All employees with a zip code equal to 08002 • > greater than Example; HIRE.DATE > 01.01.XX Results; All employees hired after 01.01.XX 13

Operators • begins with begins with Example; DED.CODE begins with 1 Results; Deduction codes that begin with 1 (100, 101, 102) • between between Example; ANNUAL.SALARY between 10000, 20000 or 10000;20000 *Note: Do not use commas or select from table Results; Employees with an annual salary between $10,000 and $20,000 • contains contains Example; DED.CODE.DESC contains HEALTH Results; Deduction codes with the word HEALTH in it Use ‘;’ to select multiple codes 14

Values 15

Operators • begins with begins with Example; DED.CODE begins with 1 Results; Deduction codes that begin with 1 (100, 101, 102) • between between Example; ANNUAL.SALARY between 10000, 20000 or 10000;20000 *Note: Do not use commas or select from table Results; Employees with an annual salary between $10,000 and $20,000 • contains contains Example; DED.CODE.DESC contains HEALTH Results; Deduction codes with the word HEALTH in it 16

Operators • does not contain does not contain Example; BLDG.DESC does not contain HIGH Results; All buildings that do not have High School in the description • ends with ends with Example; DED.CODE.DESC ends with TSA Results; Deduction codes with TSA as the last word in the description • in in Example; DED.CODE in 997;998;999 Results; Returns employees with deduction codes 997 OR 998 OR 999 • is not null is not null Example; DED.MAX.BALANCE is not null Results; All deductions that have balances • is null is null Example; RET.TYPE is null Results; All employees with no retirement type info 17

Operators • sounds like sounds like Example; LNAME sounds like SMITH Results; Employees with the last name of Smith, Smyth, Smithe • unlike unlike Example; DED.CODE unlike 997;998;999 Results; Returns employees that do not have deduction codes 997 OR 998 OR 999 18

Dates TIP: These are “relative dates” – relative to the date when the query is executed. Useful for scheduled/recurring queries. 19

Query Options • Save • Save As (My Queries) • Rename • Share, Copy • Add a Short Cut • Scheduler Option • Import/Export 20

Sharing Queries • Email query to a Keystone user outside your organization 21

Sharing Queries IMPORT EXPORT 22

Email / Schedule Queries 23

Scheduler Date Field Hierarchy 1) Date – a specific date, like “1/1/12.” 2) Day of Month – a number between 1 and **NOTE: In order for the 31. (You must leave “Date” blank for this to scheduler to run, you must be enabled.) enter one “Date” entry: 3) Day of Week – select as (Date, Day of Month, or Day many as you like. (You of Week), and one “Time” must leave Date & Day of entry (Time or Interval).** Month blank for this to be enabled.) **ADDITIONAL NOTE: The ability to schedule queries can be disabled for specific users via Keystone Time Field Hierarchy: security. Please contact 1) Time – Enter a time, either in military time (16:00), or regular Keystone to learn more** format (4:00pm – no spaces or periods). 2) Interval – Enter a time in minutes. E.g., entering “60” will make the query run every hour. 24

Scheduler (cont’d) Args – if your query has run-time prompts, you may enter a list of arguments, separated by semi-colons. You can use Clear Schedule – Ability to system variables, such as: clear the scheduled job @RETURN (no entry), (removes all selections on @DATE (current date), this screen). @TIME (current time), @BOM (beginning of month, @EOM (end of month, @PERIOD (current month / year). You can also add/subtract days (e.g., “@DATE - 1” means “yesterday.”)

Advanced File (for local exports): When the Meta Query is run Type (for scheduled jobs): manually and exported, you Designates the file format used when can direct the results to a the Meta Query is run from the specific file location. scheduler (CSV, TAB, PIPE or XML). Enter a valid path and filename, without the File (for scheduled jobs): When extension (for example, the Meta Query is run from the \\server\directory\file). The filename can include scheduler, you can direct the results to a specific file location. “@DATE” or “@TIME”. Enter a valid path and filename, without the extension (for example, \\server\directory\file). The filename can include “@DATE” or “@TIME”. Format – See following slides. 26

Meta Query results – new format options • Added Meta Query format options – Meta Query properties icon – • Select the “Advanced” tab • The options are: • Standard (Normalized), • Combine Multivalues, and • Standard (Remove Duplicates). • Only one option can be selected at a time • The second and third options are only available when using a single multivalued association in your columns 27

Advanced Format Options: “Standard (Normalized)” Example • Default format – same as previous versions: 28

Advanced Format Options: “ Combine Multivalues ” Example • The multivalues are combined into one cell, with line breaks between each value: • Displays cleanly with the line breaks in Meta Queries and in Excel. Sorting is still available. • CON: Can’t do math on the multivalued data 29

Advanced Format Options: “Standard (Remove Duplicates )” Example • Similar to “standard” format, but single valued fields do not repeat/duplicate: • The multivalued data displays on separate rows • Displays cleanly, and you can do math on all data • CON: Sorting is not available 30

Search Meta Tables • Added the ability to search for fields in Meta Tables • Can search by the actual database field name as well • Can click on the fields to add them to as a query column 31

Keystone Meta Tables • Changed table heading from Keystone Meta Tables to *Keystone Meta Tables. • These “standard tables” will now display at the top of the screen, above the Customer Meta Tables 32

Meta Query Maintenance – improved searches • On the Meta Query Maintenance Screen, modified the lookup to allow partial searches as well as searches on upper and lower case in User and Name fields. 33

Meta Query Maintenance – improved error checking 34