University of California, Riverside

Human Resources Data Warehouse

Using the Query Builder

The Query Tool allow users to create ad hoc queries of information from the hundreds of different data element stored within HRDW. Below is a brief description of the elements of the Query Builder, as well as instructions on using it to create queries.

The Criteria tab allows the user to select specific criteria needed to create a query. The criteria are organized into logical sections based on the nature of the information. On the Criteria tab certain default selections are already made when the user logs in. The current Fiscal Year and Accounting Period are selected by default and the Employee Status Code is set to Active. The user may change any of these default selections in order to create a query.

To modify any criterion, the user clicks on the hourglass next to that criterion, and makes the appropriate selection(s) in the search window that displays.

PLEASE NOTE: While the data displayed in the search screens is not limited by HRDW, the results availble to each user will depend on the accountability structures to which he/she has been given access within EACS.

The Columns Tab is used to select specific data elements within HRDW to be displayed as columns in the results set when the query is run. On the columns tab a number of heading or sub-columns are presented (ex. Employee, Appointment, Distribution, etc.). Selecting any of these headings presents the user with a number of data elements for selection. The user selects from each subcategory to create the number of columns that he/she need to reproduce the report in the query.

*The Employee ID, First Name, Last Name are selected by default.
After choosing the appropriate criteria and columns, the user selects the Search button to generate the the Query (this may take a few seconds/minutes, depending on the size of the result set and the number of columns selected).

Once the Query is generated the results will show up on the web page below. The user can re-sort the results of the search by any of column headings presented. Additionally, the user may choose to export the results to Excel by selecting the Excel icon next to the Search button.

*The Excel icon is only seen once the Query is generated.
Search and Excel Button
The Settings tab allows the user to view everything that has been selected from the Criteria and Columns Tabs as well as the Structure Query Language (SQL) of the query that is generated by these selections. This can be assist the user in verifying what has been selected. The Settings tab also contains information about the most recent load of data into HRDW from PPS
Other buttons that can be utilized in the Query Builder:
The Sort button allows the user to perform more complex sorting on the columns that have been chosen. A total  three drop down boxes are given in which the user can indicate the preferred data elements for sorting. The user is also given a choice of how to sort, either ascending or descending.
Sort Button
By default, HRDW is set to Details mode, which gives the user rows of detailed information which include Employee ID, First Name and Last Name.

Details Button

The Summary button allows the the user to summarize their search. Only certain columns are allowed to be selected in this mode to give just a count of the sections. The Summary button also give a Count column in the results that give a total count of the criteria in that column.

Summary Button

The Sum and Average (Avg) buttons only work in Summary mode.

To get the Sum or Avg using these buttons the user must go to the Columns Tab and choose the columns with the Sum symbol "Σ" to give the Sum of those columns.

Sum Button

Once all the columns are chosen the user selects the Search button. The results will have an extra column called Count that gives a total count of the criteria in that column.

Sum Button 2

The Average (Avg) button gives the user the average of that Sum column selected. To check the average the user may divide the Sum column (the column selected with the Sum symbol) by the Count column. Then select the Avg button to get the Average.

Average Button


More Information 

General Campus Information

University of California, Riverside
900 University Ave.
Riverside, CA 92521
Tel: (951) 827-1012

Department Information

Computing & Communications
Computing & Communications Bldg.

Tel: (951) 827-3555
Fax: (951) 827-4541