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

Meta Queries Workshop

Scott Joyce

2

Advanced Meta Queries

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

3

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.

4

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).

5

KEMS Standard Meta Tables

• Employee Data – comprehensive list
• f all employee-related data fields

(658 fields and counting!)

• Other standard tables include:
• Applicants
• Benefits Enrollment
• Employee Leave
• KeyTime
• Leave Transactions
• Missouri CoreData
• Position Control

Roster

• Salary Guides

6

FIS Standard Meta Tables

• General Ledger Detail
• Invoice History
• Purchase Orders
• Vendor

7

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

8

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)

9

Locating Data Fields

• Place your cursor in the field you would like to locate:

10

Locating Data Fields (cont’d)

• Click the help icon ( ) or hit the F1 key:

• 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

Meta Queries Toolbar / Icons

11

12

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

13

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

14

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

• r 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

15

Values

16

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

• r 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

17

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

18

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

19

Dates

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

20

Query Options

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

Sharing Queries

21

• Email query to a Keystone user
• utside your organization

Sharing Queries

22

IMPORT EXPORT

23

Email / Schedule Queries

24

Scheduler

Date Field Hierarchy 1) Date – a specific date, like “1/1/12.” 2) Day of Month – a number between 1 and

• 31. (You must leave

“Date” blank for this to be enabled.) 3) Day of Week – select as many as you like. (You must leave Date & Day of Month blank for this to be enabled.)

**NOTE: In order for the scheduler to run, you must enter one “Date” entry: (Date, Day of Month, or Day

• f Week), and one “Time”

entry (Time or Interval).**

Time Field Hierarchy: 1) Time – Enter a time, either in military time (16:00), or regular 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.

**ADDITIONAL NOTE: The ability to schedule queries can be disabled for specific users via Keystone

• security. Please contact

Keystone to learn more**

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 system variables, such as: @RETURN (no entry), @DATE (current date), @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.”) Clear Schedule – Ability to clear the scheduled job (removes all selections on this screen).

26

Advanced

File (for local exports): When the Meta Query is run manually and exported, you can direct the results to a specific file location. Enter a valid path and filename, without the extension (for example, \\server\directory\file). The filename can include “@DATE” or “@TIME”. Type (for scheduled jobs): Designates the file format used when the Meta Query is run from the scheduler (CSV, TAB, PIPE or XML). File (for scheduled jobs): When the Meta Query is run from the scheduler, you can direct the results to a specific file location. 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.

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

Meta Table/Query Cleanup utility

• New utility added - "Meta Table/Query Cleanup“ (KZ0330)
• 1. Delete all customer meta tables that are not associated with any queries,
• 2. Delete all queries that have not been accessed or run since XX/XX/XX date,
• 3. "Refresh" existing queries so they match the field names in the current
• tables. (Example, if you have updated the field name in a Meta Table).
• Also, added created / modified dates to Meta Tables; Modified Meta Table entry not to

allow the user to delete a table that is being used by a query, not allow user to delete a field being used on a query, report to the user if the table is being changed and it identifies a query that has fields that are not on the table, report and change the Display Name on the query if it does not match what is being saved on the Table.

35

Miscellaneous Enhancements

• Added the ability to create task notifications (send e-mails) when a

new Meta Query is copied, shared and unshared. This is defined in KZ0050

• Added ability to “double-click” Criteria in Meta Queries to modify

them (right-click allows you to add/delete)

• Added Meta Query Property to skip sending emails if no query results

are found.

• Added a warning in Meta Table Maintenance (KZ0133) if the Meta

Display is a duplicate on the screen. Both options are only currently applicable when entering the data elements, not when loading from a dictionary.

36

Miscellaneous Fixes

• In Meta Table Maintenance, added a check for a comma

in the Meta Display field on the META and APPMETA screens.

• Removed empty attachment files for emailed queries, and

added “No results matching selection criteria found.”

• Data with the format of nnnn-nn-nnn will no longer be

reformatted when exporting to excel.

• Corrected all known problems with Meta Query dates

(created, accessed, modified).

37

How to use Excel

Everyone uses Excel a little differently – there is no “right” way! With that in mind, here are some suggestions for analyzing your Meta Query results in Excel …

38

General Navigation Tips

• Keyboard shortcuts:
• Moving around:
• CTRL+arrows – move to the edge of your data set
• CTRL+HOME / CTRL+END – move to the top-left or the

bottom-right of the data

• Select a row: Shift+SPACEBAR
• Select your data set: CTRL+A
• Delete a row: ALT, E, D (think: “edit  delete”)
• Insert a column (or row): ALT, I, C (or R) (think: “insert

column: or “insert row”)

• Undo: CTRL+Z
• Redo: CTRL+Y (or F4)

39

Remove Duplicates

• Data from Meta Queries, by default, is normalized or “exploded.” In
• ther words, if your data columns include multi-valued fields, then

your single-valued fields will repeat on multiple rows.**

• To eliminate duplicates, follow these steps (keeping in mind that you

may be losing multi-valued data).

• Under the “DATA” tab, click “Remove Duplicates.“
• Then, select only the columns where your data is duplicated.
• Click “OK” – and Excel will tell you the results:

** In version 5.8 of Keystone Client, you are able to select different format options for your data. 40

Formulas

• There are hundreds of formulas included in Excel. It can be daunting

to figure out where to start.

• The “Formulas” tab can be useful in sorting through and using any

given function:

• Useful formulas:
• CONCATENATE (under the Text group) – Joins several text strings into one string
• IF (under the Logical group) – Checks a logical test, and returns one value if the

condition evaluates to TRUE, and another value if that condition evaluates to FALSE:

• COUNT/COUNTA (under the More->Statistical group) – COUNT counts the number
• f cells in a range, while COUNTA counts the number of cells in a range that are

not empty.

41

Formulas (examples)

• CONCATENATE – Joins several text strings into one string:
• Note the use of a space above (“ “) as one of the text strings.
• IF – Checks a logical test, and returns one value if the condition

evaluates to TRUE, and another value if that condition evaluates to FALSE:

42

Other Useful Tips

• Remove formulas from your data, keeping the

results/values only:

• Select and copy the fields
• Click “Copy”
• Click “Paste  Paste Values” (while keeping the data selected)
• The values will “overwrite” the existing formulas
• Use the “$” when referencing a cell in a formula

to always reference a specific column ($A1), row (A$1), or cell ($A$1).

• Play around with the “Filter” feature – this

provides a number of ways to analyze your data

• n the fly

43

PivotTables

• Subtotals are a useful feature, but PivotTables are

even better:

• Try “Recommended

PivotTables” –

• Excel will provide you with a

few suggestions … it might know what you are looking for before you do!

44