Download - SentricWorkforce Query Builder
![Page 1: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/1.jpg)
SentricWorkforce Query Builder
Getting the Data out of SentricWorkforce
Sentric, Inc.
![Page 2: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/2.jpg)
Josh Davis Sentric Product Support Specialist
My Credentials: Over 16 years of industry experience in HRIS in support
Questions? Hang on until the end, we’ll have 15 minutes of
Q&A Or email me at [email protected]
![Page 3: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/3.jpg)
Webinar How To I will not be able to hear you during the webinar until we get to the question and answer portion, but you have some tools to tell me if you need some help.
By your attendee status are some status icons to help me know if you need me to speak up or speak more softly. You can also put out the life saver to tell me if you need some assistance.
![Page 4: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/4.jpg)
Agenda
What is Query Builder?
Building a Query
Database overview
Superviews
![Page 5: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/5.jpg)
What is Query Builder?
Report Tools Poll
![Page 6: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/6.jpg)
What is Query Builder?
![Page 7: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/7.jpg)
What is Query Builder?
1. An Ad-hoc (for this) reporting tool2. Best at…
1. Getting the raw data2. Getting data from multiple tables3. Finding missing data4. Data verification5. Create simple calculations or
expressions
![Page 8: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/8.jpg)
What is Query Builder?
1. Some real world queries you might create1. Active employees on Jan. 1, 20122. Employees enrolled in a medical plan with the
option and employee cost for the plan3. Employees and their dependents with the age of
the dependent4. List of all new hires since the beginning of the
year
![Page 9: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/9.jpg)
What is Query Builder?
1. You may want a custom report or to export yourdata to MS Excel if you want to…1. Create complex calculations or expressions2. Generate a chart or graph3. Add complex parameters4. Create a pivot table
![Page 10: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/10.jpg)
What is Query Builder?
Keep this in mind… 1. Security applied to queries by default2. Each user’s queries have their user name
prefixed3. The more you work with Query Builder the
easier it gets and the more confident you willbe in the results
![Page 11: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/11.jpg)
What is Query Builder?
Query Builder Poll
![Page 12: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/12.jpg)
Building a Query
![Page 13: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/13.jpg)
Building a Query
![Page 14: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/14.jpg)
Before you start 1. Why are you building the query?2. What fields do you want to return?3. The more you know about the finished product
you want, the easier it will be to build thequery.
Building a Query
![Page 15: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/15.jpg)
Creating a Person Query 1. Go to Reports > Analytics / Ad Hoc > Query
Builder2. Select File > New > Person Query…
Building a Query
![Page 16: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/16.jpg)
Creating a Person Query 1. New Person Query pop-up form will come up2. Enter a Name and Description3. Check boxes if you want…
1. Current data2. Active employees3. To share this
query
Building a Query
![Page 17: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/17.jpg)
Creating a Person Query 1. Add fields by
clicking thecheckbox
2. Selected fields willshow on Fields tab
3. Uncheck or pressthe “X” button toremove
Building a Query
![Page 18: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/18.jpg)
Creating a Person Query 1. Add tables related
to tPerson usingthe “+” button atthe top of the table
2. Opens the RelatedTables Form
Building a Query
![Page 19: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/19.jpg)
Creating a Person Query 1. Filter the Related Tables window using the Search
feature2. Search for table or field names3. Add a table using the double chevron button4. You may need to scroll down in order to see the
table you added
Building a Query
![Page 20: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/20.jpg)
Creating a Person query 1. To add a code table press the double chevron
button next to a code field2. From the code tables you can add the
descriptions for the codes in the system
Building a Query
![Page 21: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/21.jpg)
Creating a Person query 1. Two other buttons on the top of every table
1. Expand / Collapse2. Remove table
2. Move table by clicking a dragging by the top
Building a Query
![Page 22: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/22.jpg)
Creating a Person query 1. To view the data the query returns click on the
Results tab2. Results will often show in multiple pages3. Can be printed or exported to a CSV (excel) file
Building a Query
![Page 23: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/23.jpg)
Creating a Person query 1. On the Fields Tab you can…
1. Add new fields2. Group your data for totals3. Change the Alias (column heading)4. Sort the data5. Add a run-time filter6. Determine which fields to show or hide7. Remove fields
Building a Query
![Page 24: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/24.jpg)
Creating a Person query 1. Use the joins tab to see which tables are joined2. Select the type of join you want between tables
1. Inner2. Left Outer3. Right Outer
Building a Query
![Page 25: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/25.jpg)
Creating a Person query 1. Filter the data using the
Filters panel2. Click the “ALL OF” to turn
on the add a filter box3. Most filters are Column-
Value4. Select the field and set the
value you want returned
Building a Query
![Page 26: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/26.jpg)
Hopefully you solved the puzzle and made the query, but if not we here at Sentric can give you a hand!
Building a Query
![Page 27: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/27.jpg)
Demonstration
![Page 28: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/28.jpg)
Database Overview
![Page 29: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/29.jpg)
Database Overview
![Page 30: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/30.jpg)
1. Knowing the database makes you a better reportwriter!
2. The SentricWorkforce database was built with a set of conventions.
3. Knowing how the system was developed will help youlocate the data faster and build your queries better.
Database Overview
![Page 31: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/31.jpg)
1. Databases reflect reality2. It all starts with tPerson
1. Applicants2. Employees3. Dependents
3. Every Person in the databasehas a record in the tPersontable
Database Overview
![Page 32: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/32.jpg)
1. PersonGUID1. Global Unique Identifier2. 36 character string of letters and numbers3. In most tables that start with “tPerson…” in the
database4. Tells the system who the data relates back to in
tPerson
Mike Meadows =
0952050B-2BFB-11D3-A3E0-006008AADDF9
Database Overview
![Page 33: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/33.jpg)
1. Connection to tPersonal1. Unique relationship – only get one record in each
table2. Data in tPersonal includes fields like Birth Date,
Gender, and Ethnic Group
Database Overview
![Page 34: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/34.jpg)
1. Connection to Person tables that allow multiplerecords1. Limited or no time (date) period2. Tables like tPerson…Award, Address, Language,
and Certification
Database Overview
![Page 35: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/35.jpg)
1. Connection to Person tables that allow multiple records andhave effective date ranges1. All of these tables start with tPerson…defining value…Hist
for examples tPersonJobHist or tPersonBasePayHist2. These tables have a Start Date, End Date and Current flag
Database Overview
![Page 36: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/36.jpg)
1. Code tables are used to define values that are used over andover again in a system to enforce consistency
2. If you see a drop-down field it is usually associated to a codetable
Database Overview
![Page 37: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/37.jpg)
1. Code fields will have the suffix ‘Code’2. Use the double chevron button next to a code field to add the
related code table3. Code tables will include the description values for codes. For
example, the Department Code value “IT” may have thedescription “Information Technology”
Database Overview
![Page 38: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/38.jpg)
Superviews
![Page 39: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/39.jpg)
Superviews to the rescue!
![Page 40: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/40.jpg)
Superviews
1. Superviews1. Created to make reporting easier2. Fields from many tables in one location3. Include codes and descriptions4. Commonly used calculations
1. Annual Salary2. Service Years3. Age4. Hourly Salary
5. Prefix “VRPT”
![Page 41: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/41.jpg)
Superviews
1. Superviews1. Active – only current data
for active employees2. AllApplicants – data for
everyone with a currentapplication
3. AllPersons– data for everyperson in the database
4. Base – data for employees5. gcPIT – Point-In-Time –
data at different set date6. History – Data from main
history tables – current andhistoric
![Page 42: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/42.jpg)
Superviews
1. Superviews1. Can save time if you need straight forward data2. Allows you to get complex data very quickly
![Page 43: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/43.jpg)
Demonstration
![Page 44: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/44.jpg)
Test Your Knowledge!
![Page 45: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/45.jpg)
Questions
If you called in on the phone please enter *5 on your phone to ask a question.
(=*5
If you are listening on your computer please press the File question request button on your screen. Please note that you must have a microphone.
I will un-mute your line one at a time, so once you press *5 or press the button you may need to wait just a bit for your turn.
![Page 46: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/46.jpg)
What is Query Builder?
Future Webinars Poll
![Page 47: SentricWorkforce Query Builder](https://reader031.vdocuments.us/reader031/viewer/2022021502/58a35e5a1a28ab14598b6b8f/html5/thumbnails/47.jpg)
Thank You for attending!
Please take the survey following the Webinar