These tools will take practice and usage.
Purpose
HRMS Query is a web-based query builder which is capable of translating an LEA’s business needs into a technical SQL query. Currently, query builder is intended to be used as a supplement to the HRMS Reporting suite.
Technical Architecture of Query Builder
Query Builder is a Domino-based SQL generator.
How to Use Query Builder Security
Query Builder uses the same built-in security as HRMS. In order to view the Query Builder, users must have the appropriate access rights. Once a user is logged into the HRMS system, access to the individual Query Builder is granted according to the user's group membership.
Query Builder Main Page Basic Usage
Step 1 (choose a report group)
Report Groups: are data views consisting of tables which already have all the necessary joins. Once you select a data group you can choose multiple tables then fields within the data group.
Step 2 (choose a table)
Select a Table: Select any of the available tables within a report group. These tables have all the necessary predefined joins. Once you have made a selection, individual fields will display.
Step3(Current Action)
Current Action: Default selection is “Select Fields to Include”. - This allows users to select all the fields that need to be displayed.
“Filter On” – Is a predefined list of filter conditions which let you narrow down your
results set. An example is the filter “Equal”. Once you select equal as the filter, select a field. A prompt then appears asking for the value you wish to test for. Anything can be entered. Notice that a Where Clause is being generated to the right of the filter
conditions.
“Order By” – lets you order based on any field that is selected.
Steps 2 and 3 can be repeated as many times as necessary to build your query.
Run Your Query
Notice that a SQL query has been generated at the bottom of the page.
Max Rows changes the amount of records that are returned. Default selection is 10.
To return all records enter 0
Submit Button – automatically submits the query and returns a result set
New Local Report – automatically submits the query to a new local web report form
Build Query – this is used when the “Continuously rebuild SQL query” box is unchecked.
Advanced Features
Continuously rebuild SQL query – if checked (recommended) this continuously rebuilds your SQL statement based on your selections.
Show All Tables This option displays all available tables. However, these tables are not joined and it is the responsibility of the user to manually join the tables in their SQL statement.
Where Clause Users can manually enter in where clauses.
Output Options
Content Type: This selection allows the user to choose between “XML”, “Plain Text XML”, and “Excel”.
d) XML: This is the default choice and should generally be used to view all queries. Using this option, the user may view the report data in the browser in XML, or if a stylesheet is used, see the report formatted according to the selected stylesheet.
e) Plain Text XML: This choice displays XML data, but the output type to the browser is plain text. This option does not result in formatted data. This output provides a way for a user to save XML report data to a text file. If a stylesheet is selected, the XML command that includes the stylesheet is included in the plain text, but the stylesheet is not applied in the browser. The user may then change the stylesheet specified, if desired, to use another
"home-grown" stylesheet. Users are encouraged to contribute such stylesheets to the HRMS project.
f) Excel: This option allows a user to export a query’s XML data to an Excel file.
Exportable Stylesheets page. This is accomplished by running the report with “Excel” selected as an option. When prompted select save the file to any desired location. Only the stylesheets have to be in the
C:\HRMS_Stylesheets directory. The XML data can be saved anywhere you like, including a network drive.
Introduction to
Structured Query Language (SQL) in the HRMS system To understand how to use SQL you must first understand a database.
Database - A collection of information organized in such a way that a user/software program can quickly select desired pieces of data. You can think of a database as an electronic filing system
The HRMS database is made up of three main libraries (HRMSAPP, HRMSLEA, HRMSSHR)
Table - basic component of a database (DB). It has a number of rows and columns, (similar to a spreadsheet)
HRMSLEA tables:
Table Indentified by HRMSLEA.Employee (table)
View - a view is a virtual or logical table composed of the result set of a pre-compiled query. Unlike ordinary tables in a relational database, a view is not part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database Create View – Like a table but not a real table. These are used in the Query Builder appear to be real tables but they are views to help with joins also used in Adhoc Reports from the reporting tool
Sample Data from HRMSLEA.EMPLOYEE each row is called a column in this example we have three columns listed
Basic Queries SFW Statement
Structured Query Language (SQL) is a data sublanguage that has constructs for defining and processing a database
Pronounced S-Q-L or Sequel
Used to query standard DBMS: Database Management Systems (HRMS database) Tables consisting of Columns and Rows
(think: Excel)
Without the where statement you will choose all columns Lets do this in query tool
As you see the result is not much, but we will expand on this later Take Note:
When searching for characters in a SQL where statement, enclose the datatype in single quotation marks
Where emp_last_nm = ‘JOHNSON’;
In the select statement, multiple selections are separated by a comma Select Emp_Last_Nm, Emp_First_Nm, Emp_SSN_TXT
When specifying table names in the libraries use a period between the library name and table name
From HRMSLEA.Employee
Practice:
• Show the first, middle, and last names of all employees with the first name of John. order by clause to the end of the SQL statement.
Asc Ascending order (default) Desc Descending order
Select emp_last_nm, emp_ssn_txt From hrmslea.employee
Order by emp_ssn_txt desc;
SPECIAL SELECT OPERATORS
Asterisk () Selects all values from the specified table
Select * from hrmslea.Employee – this will take a long time to run Will pull everything from the hrmslea.employee table
Distinct eliminates duplicates from the records returned
Will pick unique records only will not return duplicate of the column noted after the distinct
Select Distinct emp_last_nm from hrmslea.employee
Special Where Operators
Where emp_ssn_txt Is Not null;
Or
Select emp_last_nm, emp_ssn_txt From hrmslea.employee
Where emp_last_nm Like ‘Adam’;
Compound Conditions in the Where Clause And
Or Not
“And” and “Or” combine two conditions to form a single compound condition. They can be applied repeatedly. Not is applied to single condition.
Select emp_last_nm, emp_ssn_txt From hrmslea.employee
Where emp_last_nm like ‘ADAM’ and emp_ssn_txt is Not null;
Conditions for And
Both statements have to be true One of the statements have to be true For the statement to execute
Alias
An alias is used to rename a column or table in result set.
It doesn’t actually rename the table or column
Select emp_last_nm as LastName, emp_middle_nm as MiddleName, emp_last_name as LastName
From hrmslea.employee as EmployeeTable;
Where emp_last_nm like ‘ADAM’ and emp_ssn_txt is Not null;
Column name at the top will display Alias Unions and Joins
A Union combines the rows of the selected tables together. For this to work the rows of tables have to be the same
A join combines the data from two or more tables to make a single result set.
Will talk more about in Advance portion:
Expanding on the Basics
SELECT * FROM LIBRARY.TABLE_NAME …
What upper does
Converts a string to upper case.
And the where clause
Different options
Complete SQL Statement:
SELECT emp_ssn_txt,
upper(emp_last_nm) as emp_last_nm, upper(emp_first_nm) as emp_first_nm, upper(emp_middle_nm) as emp_middle_nm FROM hrmslea.employee