CREATING YOUR OWN REPORTS USING QUERY BUILDER IN EPICOR HCM

Epicor HCM Query Builder is a reporting tool that allows you to create custom database queries to analyze and evaluate your HR data. You can use this tool to pull raw data or as the source for presentation-style reports to use in Report Builder. The Query Builder tool is simple to use and anyone with the right permissions can create custom queries in Epicor HCM.

Following are the benefits of using the Query builder:

1. You can easily create custom queries that do not require intimate knowledge of your database.
2. You can create data sources that cannot be created against the Reporting Services model delivered with Epicor HCM.
3. It gives you an interface that simplifies the query building process, which means no technical expertise is required to query your data in meaningful, truly customized ways.
4. Query Builder provides access to the Epicor HCM database from the user interface.
5. No technical expertise is necessary to create powerful queries that give you deep insight to your HR data

Out-of-the-box, the Query Builder is available to users who have permission for the Admin role. You can open this availability to other roles as you see fit. Following steps will help you in creating a Custom report using the query builder.

Step 1: Open the Query Builder

Find the Query Builder on the Reports tab, and then navigate to Ad Hoc  Query Builder.

Query Builder opens in a new browser window. To start a new report, from the File menu, select New, and then select one of the following query types.

Step 2: Select the type of Query

Once you select the type of query to create, you are prompted to enter additional information about the query, including the name of the query and a description. You are also prompted to select query options, such as filtering to current history records, filtering to active employees only, and sharing the query.  Upon entering query information, the Query Builder opens the Designer tab.

Step 3: Designing the Query

The Designer tab is a graphical user interface, allowing you to design queries. Use the Designer tab to add fields to a query, include tables in the query, manage table joins, and add criteria to a query

1. Adding a Field

To add a field from a table to your query, select the check box (1) next to the field you want to add. The fields that you add to the query display on the Fields tab(2) at the bottom of the form. Use the Fields tab to change field information:

Use the Alias field to change the display name for the field. The alias you enter displays as the column header on the Results tab.
For example, you might want to change the display name of Full Name to Employee Name to describe the data that is returned.

Use the Sort Order field to change how the query sorts the results: Ascending, Descending, or None.

Use the Filtered field to apply on-the-fly criteria on the Results tab. If you select true in the filtering for a field, when you open the Results tab, a Filters selection displays at the top of the results, allowing you to define that data that is returned.
Using filtering means that you do not need to create and change criteria each time you return the results of a query.

Use the Visible field to display or hide a column (or columns) on the Results tab. Select True to display the column on the Results tab; select False to hide it.

Click ‘Add field’ to insert a new, blank line on the bottom of the Fields grid. Use this open line to enter a custom formula to create non-standard displays of data. The syntax of your formula must match a built-in SQL function.
For example, you may want to display first initials and last names. The Epicor HCM standard is first name and last name. Add a field as a formula that concatenates the first letter of the value in the FirstName column with the value in the LastName column.

The formula you enter must also include the name of the table from where the data is pulled. Using the example above, the formula to return the first letter of the first name is
SUBSTRING(tPerson.FirstName, 1, 1), where ‘SUBSTRING” is the SQL function, ‘tPerson.FirstName’ is the table and column in the database, and ‘1, 1’ tells the formula to start with the first letter and display one character (similarly, ‘1, 3’ would start with the first letter and display three characters).

1. Adding and Remove Table

To add a new table to the query, click the Plus Sign (+) located in the upper-right corner of the table. The Related Tables form opens in a new window and lists all tables associated with your root table. Use the Search field to quickly find a table to add to your query.

Once you have located the table to add, click the ‘back next’ to the table name and linking field to add the table to your query.

When adding tables to a query using the Related Tables form, remember:

The Related Tables for only lists tables and views related to the parent.

Tables may appear more than once, as some tables are joined to the parent table on multiple fields. For example, the Person Location History table is joined to the Person table twice: once on the PersonGUID field for the employee and on the PersonGUID field for the employee’s supervisor.

When searching for a table to join, the results display any table where there’s a match, including table names and column names.

You can also add tables to your query by locating the linking field in the base table and clicking ‘forward next’. This adds the table directly related to the linking field.

After building your query on the Designer tab, Query Builder administrators can use the Query tab to work directly with the SQL on the Query tab. All users can view the output on the Results tab.

Step 4: Verify the Query

The Query tab provides direct access to the SQL code, and because of that, you should have a strong understanding of the SQL programming language before using this tool. There are two main uses for the Query tab: creating new SQL queries and updating the SQL code for a query designed on the Designer tab.

Caution 

Limitations are in place to provide a safer tool that cannot be used to change the data within the database. Be aware of the following security related items when using the Query tab in Query Builder:

Only Query Builder Administrator users can modify a query using the SQL tab.

Only Select statements can be processed.

Any SQL statement that updates or removes data will not be processed (such as Insert, Update, or Delete statements).

Removing the PersonGUID field from the SQL statement will remove the row security from the query.

Using the Query Builder Auto Format SQL feature enables the application to attempt to format the SQL code. When writing SQL code with numerous filters in the WHERE clause, you should turn off this feature.

If any of the SQL code is modified on this tab, Query Builder will attempt to interpret it and display it on the Designer tab. If the SQL is too complex, Epicor HCM may not be able to interpret and display the code. In that case, you can still view the output on the Results tab.

Step 5: Check the Result

The Results tab displays the results from a query. From here, you can page through the results, re-size columns, export the results to a Microsoft Excel spreadsheet (click the MS Excel icon in the upper-right corner), and print the query (click the printer icon).

The My Query component is a home page component in Epicor HCM. Users can display the My Query component on their My Home tab.