ethority training guide - university of hawaii · in our example the dean has asked you to provide...

eThority

Training Guide

University of Hawaii

eThority - Creating User DataBooks From Templates

Version 1.0.0

eThority

Training Guide

eThority

Training Guide

Last Updated: 01/29/2016 eThority - Creating User DataBooks From Templates Page 2

Table of Contents

Objectives .................................................................................................................... 3

Resources ................................................................................................................... 4

eThority Glossary ......................................................................................................... 5

Steps to Creating a Report: .......................................................................................... 7

Learning Through an Example – Creating an Account Status Report 0F0F0F ....................... 8

Step 1: Choose the Data - Select the Template ........................................................... 9

Step 2: Select the Fields For a Report: ...................................................................... 11

Step 3: Select Certain Records by Adding Filter Criteria to a DataBook ...................... 16

Step 4: Add Groups and Subgroups to a DataBook ................................................... 22

Step 5: Determine if the Query will be Run on a Periodic Basis and/or for Different

Selection Criteria ........................................................................................................ 25

Step 6: Using PowerFields to Create a Calculated Field ............................................ 32

Step 7: Refine the Report Output by Moving / Hiding Columns & Displaying Totals .... 42

Step 8: Confirming the Report Contains the Correct Results ....................................... 46

Appendix A: Quick Reference Chart: ......................................................................... 47

Appendix B: Miscellaneous Tips for DataBook Creation: ............................................ 48

Attachment 1 ............................................................................................................. 50

Other Enhancements to the Account Status DataBook .......................................................................... 50

Selecting Additional Fields and Restructuring the DataBook ................................................................. 51

Notes: ........................................................................................................................ 54

eThority

Training Guide


Objectives

Determine which template to use for your DataBook

Add fields to a DataBook

Select certain records

Add Groups or Subgroups

Enter runtime criteria or Control Values

Create calculated fields using PowerFields

Add calculated fields and display totals to your report output

Confirm your output contains the correct results

eThority

Training Guide


Resources

Website: The FMO website has a Financial Systems page at http://www.fmo.hawaii.edu/financial_systems/index.html that focuses on specific material related to the systems with the following dedicated tabs Overview, FAQs, Contact Us, Policies and Guidelines, Forms and Training.

Questions: For questions about eThority contact John Burke at: [email protected].

Technical Issues: For technical issues submit a trouble ticket: Submit a Trouble Ticket.

Listserv: If you are a member of the eThority listserv, you will receive information related to eThority such as announcements, system availability and training classes. To subscribe to the ETHORITY-USERS listserv, send an email to: [email protected], no subject, and in the body of the message “subscribe ETHORITY-USERS your full name” without the quotation marks.

eThority Reference Guides:

For a general overview on how to use eThority and the basic functions the tool offers refer to the Introduction to eThority and Report Viewers Training - Manual. The document can be located at: http://www.fmo.hawaii.edu/financial_systems/docs/Introduction_to_eThority_and_Report_Viewers.pdf

For additional information into meaningful structures for reporting, graphing and distribution refer to the guide: eThority Enterprise Orientation Guide located at: http://www.fmo.hawaii.edu/financial_systems/docs/eThority_Enterprise_End_User_Orientation_

Guide_version_5.0.pdf

For a detailed list of all Global DataBooks and Templates in eThority refer to the guide: eThority DataBook Descriptions located at: http://www.fmo.hawaii.edu/financial_systems/docs/eThority_DataBooks_Descriptions.pdf

This guide also provides potential uses for most of the DataBooks, possible Control Values needed, the information contained in the output and how the data is grouped. This reference guide is a “living” reference document with updates as new DataBooks are created in eThority.

For a detailed list of all fieldnames and descriptions in the GL Financial Transactions-Template refer to the guide: eThority Fieldnames and Descriptions for the GL Financial Transactions Template document located at: http://www.fmo.hawaii.edu/financial_systems/docs/GL_Fin_Tran_Temp_Field_Defs.pdf

http://www.fmo.hawaii.edu/financial_systems/index.html

mailto:[email protected]

https://authn.hawaii.edu/cas/login?service=https://www.hawaii.edu/kualifinancial/feedbackForm.php

http://www.fmo.hawaii.edu/financial_systems/docs/Introduction_to_eThority_and_Report_Viewers.pdf



http://www.fmo.hawaii.edu/financial_systems/docs/eThority_Enterprise_End_User_Orientation_Guide_version_5.0.pdf



http://www.fmo.hawaii.edu/financial_systems/docs/eThority_DataBooks_Descriptions.pdf


http://www.fmo.hawaii.edu/financial_systems/docs/GL_Fin_Tran_Temp_Field_Defs.pdf

eThority

Training Guide


eThority Glossary

DataBook: a query of data that represents a report. A DataBook may contain a

combination of sorting, grouping or calculated totals. DataBooks fall into three (3)

categories or subsets: Templates, Global DataBooks and User DataBooks.

1. eThority Templates

A Template is a core dataset that includes all of the available fields and all of the

records. The data is not grouped, sorted or filtered in any way. Each Template

in eThority is based on commonly referenced functional needs such as GL

Financial Transactions, AR, Capital Assets, and Contracts & Grants to name a

few. These Templates have the suffix “-Template” and can be used to create a

specialized “report” by an eThority user. Templates cannot be merged or joined

which means that fields from one Template cannot be used in conjunction with

fields from another Template. Therefore, it is important to select the correct

Template at the outset when creating a customized or specialized DataBook. All

Templates are DataBooks but all DataBooks are not Templates.

2. Global DataBooks

A Global DataBook is created by an eThority data architect to assist users in

obtaining information about their fiscal activities. All users can run Global

DataBooks, but they cannot be overwritten. Global Databooks have been

developed for queries that are commonly used by campus users. A user can run

a Global DataBook, save the DataBook as a User DataBook and alter the User

DataBook to meet the user’s specific reporting needs.

3. User DataBooks

A User DataBook is created by an eThority user. User DataBooks are accessible

only to the user who created the DataBook and those with whom it was explicitly

shared.

eThority

Training Guide


Data Architect – Also referred to as the reporting team. Data architects create the

Global DataBooks and are responsible for the back end data structures that drive

the eThority DataBooks.

PowerField – A user-created custom expression/calculation (i.e., custom field) that is

used in a DataBook.

Control Value – A parameter specified in a DataBook that prompts a user for values

for use in the DataBook.

eThority

Training Guide


Steps to Creating a Report:

The list below is an outline or suggested steps to follow in order to successfully create a

report, or a DataBook in eThority.

1. Choose the Data

2. From the source data, select the fields for report

3. Select which records should be returned or filter the data

4. Add any sorting or grouping (subtotals)

5. Determine if the query will be run on a periodic basis and/or for different

selection criteria

6. Create calculated fields for your report

7. Refine your report output by moving / hiding fields and display totals

8. After you run your query, see if it contains the correct results

eThority

Training Guide


Learning Through an Example – Creating an Account Status Report0F0F0F

1

In our example the Dean has asked you to provide a status report of your accounts on a

monthly basis. This would include the total expenditures and what has already been

encumbered for all of your accounts by object code. The screenshot below shows the

“report” that will be created. This guide will walk through the steps used to create this

report by following the suggested outline.

1 Although the term “report” is in this training manual, eThority is not a reporting tool, per se. It is a data analytics

tool that queries data and provides the DataBook query output in a form that represents a report.

eThority

Training Guide


Step 1: Choose the Data - Select the Template

The first step in creating a DataBook in eThority is choosing the data. Templates

contain the data we use to build DataBooks in eThority. In our example you will need to

provide a status of your accounts on a monthly basis. This would include the total

expenditures and encumbrances for all of your accounts by object code. Account and

object code transaction data is found in the General Ledger. For data related to all (CG

and Non-CG) Accounts, the eThority DataBook Description reference document

indicates that the GL Financial Transactions – Template is useful for creating

DataBooks that lists all transactions, transaction amounts and balances. This Template

is in the KFS GL DataBooks section of eThority.

Step # Procedure

1. Click on the plus sign next to the “KFS GL DataBooks” folder to expand that

section.

eThority

Training Guide


Step # Procedure

2. To use one of the Templates, double click on the name which opens the Report

Builder in a new browser window.

For this exercise we will be using the GL Financial Transactions – Template.

Double click on “GL Financial Transactions – Template”.

Note: Some templates may take more time than others to open due to the amount

of data or field attributes they contain.

Note: Refer to the eThority DataBook Descriptions document for additional details

on the data contained in a template to use to build your “report”.


eThority

Training Guide


Step 2: Select the Fields For a Report:

Step # Procedure

3. After running the template, all of the fields contained in the Template will display in

the new browser window. The second step in report creation is selecting the fields

for your report. This is more easily accomplished in eThority by first removing all of

the fields from the Template and then selecting only those fields to include in the

DataBook.

All fields or columns that may be used in a Template (or in a DataBook) are listed

in the Field Selector. Navigation: VIEW > Field Selector

Click on “VIEW” then click on “Field Selector” to access the list of columns in the

Template.

For more information on the Field Selector features, refer to the eThority Enterprise

Orientation Guide at :

http://www.fmo.hawaii.edu/financial_systems/docs/eThority_Enterprise_End_User_Orientati

on_Guide_version_5.0.pdf



eThority

Training Guide


Step # Procedure

4. The Select Fields for DataBook window opens that displays the Available Fields on

the left and the Current DataBook Fields on the right. By default, the Available

Fields are in the order in which the Template was created. Fieldnames in bold

green font indicate the fields were selected in the current DataBook and those

highlighted yellow indicate the fields are calculated fields.

To remove all fields from the Template click the “Remove All” button at the bottom

of the Current DataBook Fields section.

In the next few steps we will select only those fields to include in the DataBook.

eThority

Training Guide


Step # Procedure

5. The Available Fields is a listing of all the fields in the Template that are available

for use in creating your DataBook. To make finding the desired fields easier, sort

the list of Available Fields alphabetically by clicking on the Column Name header in

the Available Fields section.

Click on the “Column Name” to sort the list of available fields.

Note: Because the fields were removed, after sorting the Available Fields, the field

names are no longer in bold green font. Calculated fields remain highlighted in

yellow.

6. To select the fields in the customized DataBook, either double click on the desired

field, or select the field and click on the “Add Field” button.

Note: Fields will appear on the reporting area in the order they appear in the

Current DataBook Fields Section.

eThority

Training Guide


7. In this example, select the fields that will provide the Dean with a monthly account

status. Take a moment to consider the possible data that would need to be

included in a monthly status report. Scrolling through the list of “Available Fields”

the following fields in Template appear to be a reasonable selection:

“Account Number“

“Budget Summary 1 Code-Name“

“Encumbrances FYTD”

“Expenditure FYTD”

“Fiscal Period Code”

“FO Code”

“Object Code-Name”

“University Fiscal Year”

Note: Refer to the: Field Definitions GL Financial Transactions Template

reference document for field names and descriptions.

8. Note: Fields that contain “-Name” are concatenated fields or a combination of two

fields together. For instance, “Object Code-Name” is a combination of the two

fields: “Object Code“ and “Object Code Description”. “Object Code-Name”

displays both the “Object Code” and the “Object Code Description”. (e.g., 3200-

SUPPLIES, OFFICE)

eThority

Training Guide


Step # Procedure

9. After clicking “OK” eThority displays the “REFRESH NEEDED” watermark across

the reporting area to indicate that you have made changes that affect the results.

Do not click on the “Refresh” button at this time as refreshing the DataBook

increases processing time.

eThority

Training Guide


Step 3: Select Certain Records by Adding Filter Criteria to a DataBook

Step # Procedure

10. The third step in report creation is selecting certain records or adding filter criteria.

There are two ways to add filter criteria to the data that appears in the DataBook.

1) Drag and drop any column header to the Filter section of the Data Shaping

Panel to open the Filter Builder Dialog box to define the criteria ( e.g., FO

Code = 030) or

2) Click the “Filter” button in the Data Shaping Panel to select from a field

listing in the Filter Records window. Once a fieldname is selected the Filter

Builder Dialog Box opens.

Note: In eThority any field in the Template can be used as a filter criterion, even if

it was not selected to be included in the DataBook.

Step # Procedure

11. In this scenario add filter criteria to include data in University Fiscal Year 2015 and

FO Code 030. Click on “Filter” in the Filter panel to open the Filter Records

window.

eThority

Training Guide


Step # Procedure

12. There are two ways to select a column or field name in the Filter Records window:

1) Double click on the name or

2) Click on the name and drag the column name into the section on the right

of the Filter Records window.

For this example, select “FO Code” and then specify a 3-digit FO Code to add as a

filter to the DataBook.

Scroll down the Column Names; double click on “FO Code”.

Note: Sort the Column Names first to easily find the column name.

eThority

Training Guide


Step # Procedure

13. After selecting the filter criteria from the Column Names, the Filter Builder Dialog

Box opens in order to specify or define the filter criterion.

For this exercise, select the radio button beside “User Value”.

As a system default, the radio button beside the “Pick List” is pre-selected and a

list of values appears in the section below. While the values are loading a “please

wait…” message appears under the Select Multiple Items heading.

Note: In order to create the pick list, eThority will go through all records to

determine which values have been previously used in KFS which is time-

consuming. Also, a value may be valid, but if the value has not previously been

used in KFS, it will not display in the pick list. Therefore it is a good practice to

select the radio button beside “User Value” when defining a Filter.

Note: When the pick list radio button is selected the only comparison allowed is

“Equals”.

eThority

Training Guide


Step # Procedure

14. When the User Value radio button is selected:

1) More Comparisons are allowed: Equals, Less Than, Less Than or Equal

To, Greater Than, Greater than or Equal To and Contains

2) The Values field is an open field to enter filter criteria

3) The Filter Description field contains a partial formula that the system

completes based on the comparison selected and values entered.

15. The User Value can be a letter, a digit or a special character. Additionally, eThority

allows the following:

Begins with - the value of the field starts with the value entered

Ends with - the value of the field ends with the value entered

Contains - the value entered appears somewhere in the field value

In range - the value is between the beginning and ending value, inclusive

Is empty – there is no value in the field

16. For this example enter the 3-digit FO Code “030” in the “Values” field and click the

“OK” button in the Filter Builder window.

17. Note: The “Exclude These From Results Set” checkbox can be used to exclude

certain data or to return only rows of data where all fields are populated.

As an example, to exclude Non Imposed Fringe (Chart Code NI), the filter would be

Data Field: Chart Code

Comparison: Equals

Values: NI

Click the checkbox beside “Exclude These From Results Set”. This will return all

other Chart Codes except NI.

eThority

Training Guide


Step # Procedure

18. After defining your filter, the Filter Records window displays the filter criteria.

Click the “Close” button at the top right of the Filter Records window.

Note: To edit, delete or lock a filter, right click on the field name used in the filter

and select from the options listed. Locking a filter prohibits users from removing

the filter.

Step # Procedure

19. To include only University Fiscal Year 2015 data in the DataBook, add a second

Filter. Repeat Steps 12 – Step 18 to add additional filter criteria.

Note: When the Filter Builder window opens, enter “2015” into the Values field

and click the “OK” button.

20. Click the “Refresh” button at this time.

Note: There is a checkbox beside “Auto-Refresh Grid” when saving a DataBook.

This option not recommended during DataBook creation.

eThority

Training Guide


Step # Procedure

21. The refreshed DataBook will only include data that was filtered and in this example

the DataBook was filtered by FO Code 030 and University Fiscal Year 2015.

Note: Displaying the “FO Code” and the “University Fiscal Year” columns in the

reporting area is unnecessary information that can be added to the title when

exporting or printing the DataBook. In later steps these columns will be hidden so

they do not display in the final “report”.

eThority

Training Guide


Step 4: Add Groups and Subgroups to a DataBook

Step # Procedure

22. After the data has been filtered to include only certain records, the next step in

eThority DataBook creation is adding any sorting or grouping to the data. You can

order the data using either the Group or Sort feature but fields can only be included

in one or the other. The main difference is Group allows you to calculate subtotals

at the different levels. Multiple field(s) may be selected for either a Group or Sort.

If more than one field is specified, the Group or Sort is nested.

Note: In eThority any field in the Template can be used as a Group or in the Sort,

even if it was not selected to be included in the DataBook.

Data can be grouped using one of the following methods:

1) Drag and drop the column header into the Group section of the Data

Shaping Panel at the top of the tab.

2) Click on “Group” in the Data Shaping Panel at the top of the tab

a. click and drag the desired field from the listing on the left, or

b. double click on the fieldname

Note: To un-group data, drag the desired column header from the Group section in

the Data Shaping Panel and drop it over the rows of data in the reporting area.

Note: For any field used in Group, eThority will not display the details below the

column header. A unique value will display at the far left of the report. Multiples of

the same value will not display beneath the column header within the body of the

report. To display details below a column header, the field must be added a

eThority

Training Guide


second time. Typically, data architects move fields used in Group to the end of the

reporting area so that they do not clutter the report columns. (VIEW > Field

Selector)

Note: The same steps above are used to add fields to the Sort section.

Step # Procedure

23. In this example the DataBook will be grouped by “Account Number”, “Budget

Summary 1 Code-Name” and then by “Object Code-Name”.

Drag and drop the “Account Number” and “Budget Summary 1 Code-Name”

column headers into the Group section of the Data Shaping Panel at the top of the

tab.

Note: The default sort order is ascending represented by the up arrow.

Note: Left click on the green arrow to change the direction of the arrow from up

or down, to sort groups in ascending or descending order.

eThority

Training Guide


Step # Procedure

24. An alternative to dragging the column name up from the DataBook is to click the

“Group” button in the Group section of the Data Shaping Panel.

The Group Records window opens where you can either drag and drop or double

click from the list of all column names.

Note: You can Group on any field that in the Template even though you did not

select the field to be in your DataBook.

Add “Object Code-Name” to the DataBook grouping by double clicking on the

Object Code-Name field and then click the “Close” button on the Group Records

window.

Note: To reorder the Groups, left click on and hold the Group to move, drag it to

the desired location and unclick.

eThority

Training Guide


Step 5: Determine if the Query will be Run on a Periodic Basis and/or for

Different Selection Criteria

Step # Procedure

25. The fifth step in report creation is determining if the query will be run on a periodic

basis and/or for different selection criteria. This step is accomplished in eThority

using Control Values.

Control Values give the creator of the DataBook the ability to build complex filters

or expressions that may be changed easily each time the DataBook is run.

Note: The user may be prompted for Control Values upon opening the DataBook if

the ”Ask for Control Values” checkbox is checked when the DataBook is saved.

Step # Procedure

26. In this example, since the Dean would like a monthly account status report, it

makes sense to create a Control Value for “Fiscal Period” that can be changed at

runtime each month the DataBook is run.

Click “POWERFIELDS” then click on “Configure Control Values” to open the

Configure Control Values Window.

Note: Creating a Control Value filter for use in your DataBook is a two-step

process 1) create the Control Value and 2) add the new Control Value to the filter

section of the Data Shaping Panel.

eThority

Training Guide


Step # Procedure

27. The Configure Control Values Window displays four fields that will be completed by

the DataBook creator:

1) Control Value - The prompt presented to the user on running the DataBook.

2) Data Type - A dropdown selection of the data type appropriate to the user

response (i.e., Date, Currency, Year, DataBook Column).

3) Column - If Data Type is DataBook Column, select the appropriate

DataBook Column. The values from the column selected here will be

presented to the user for selection. If any other Data Type is selected this

field will remain blank.

4) Value - Enter a default Control Value. The user may edit this value on entry

to the DataBook. If this value is a DataBook Column Data Type, the field

will contain an ellipsis button. Click the button to select a value from a

list of the column values. Otherwise enter a value directly into the field.

Click the “Add” button to add a parameter to begin creating a Control Value.

28. The Control Value field populates with “Item 1” after clicking the “Add” button.

eThority

Training Guide


Step # Procedure

29. In this example we will create a Control Value for Fiscal Period, and we will set the

Value to “01” or July.

Click in the Control Value field and enter “Fiscal Period Filter”. This is the text that

will be displayed to the user.

Note: When creating Control Values it is helpful to include ”Filter” in the Control

Value field to distinguish it from a column name in your DataBook.

eThority

Training Guide


Step # Procedure

30. To select the Data Type, click in the blank field to display the drop down selections

for the Data Type. eThority allows the following Data Types:

1. Databook Column – Do not use – this takes a very long time to populate

due to having to read the all entries in the table

2. Global Data Value – is not currently being used

3. Currency – used for fields containing monetary values

4. Date – used for fields containing dates or requiring date function i.e. month

name

5. Decimal – used for numbers that have more than 2 decimal places, not

generally used

6. Integer – used for fields containing non-monetary values for use in

mathematical calculations

7. String – can either be numeric or non-numeric data but it cannot be used in

mathematical calculations

8. Year - used to represent a year

Click in the Data Type field and select the appropriate type. For this example, the

Fiscal Period field is a string (character) so select “String” from the drop down list.

31. For the Column field, the Data Type is not DataBook Column so this field will

remain blank.

eThority

Training Guide


Step # Procedure

32. In the Value field enter a default Control Value. Users may edit this value on entry

to the DataBook. Enter a valid Fiscal Period used in your DataBook.

For this example enter “01” in the Value field.

Note: The Value field contains an ellipsis button if the Data Type is DataBook

Column. To make selections, click the ellipsis button to select a value from a list of

the column values. eThority needs to determine all the values for the DataBook

Column which is a time consuming process.

33. To add another Control Value click the “Add” button and enter the respective

parameters for that Control Value.

For this example we will not add another Control Value.

eThority

Training Guide


Step # Procedure

34. The second step in creating a Control Value filter for use in your DataBook is

adding the new Control Value to the Filter section of the Data Shaping Panel. To

do so, you need to add a Filter with the DataBook Column (e.g., Fiscal Period

Code) associated with the Control Value you created equaling your new Control

Value field (e.g., Fiscal Period Filter)

For this example we created a Fiscal Period Control Value so drag the “Fiscal

Period Code” column heading into the Filter Panel.

35. In the Filter builder, select the radio button beside “Data Field” and select the

appropriate operator for the filter, which in this example is “Equals”. The Data Field

you select will be the Control Value filter you created.

Note: All Control Values are listed in red text at the bottom of the Field dropdown.

For this example, click the dropdown beside “Field:” scroll down the list and select

“Fiscal Period Filter”; then click the “OK” button on the Filter Builder window.

36. Click the “Refresh” Button to view the Groups and additional Filter applied to the

DataBook.

eThority

Training Guide


Step # Procedure

37. The output now displays data for FO Code 030, University Fiscal Year 2015 for

Fiscal Period Code 01 and the data is grouped by Account Number, Budget

Summary 1 Code-Name, and Object Code-Name.

By setting up the Fiscal Period Filter as a Control Value Filter, a specific Fiscal

Period can be selected when the DataBook is opened.

Note: To display the Control Values prompt each time the DataBook is opened

select “Ask for Control Values” when saving the DataBook

Please refer to Appendix B or the Introduction to eThority and Report Viewers

Training Manual for additional information on Control Values prompts.

eThority

Training Guide


Step 6: Using PowerFields to Create a Calculated Field

Step # Procedure

38. Referring back to the Dean’s request, we also need to provide a total of the

expenditures and what has already been encumbered. Since this was not in the

list of Available Fields we need to create a calculated field using PowerFields.

Click “POWERFIELDS” then click on “Edit PowerFields” to open the Edit

PowerFields Window.

Note: Depending on how a PowerField is created, it may require excessive

resources to calculate it. Simple calculations in PowerFields do not use a lot of

system resources but Substitution PowerFields or PowerFields that calculate fiscal

year or fiscal period are resource intensive and will greatly increase the DataBook

runtime. Use of Control Values where feasible, is better for performance.

Note: ITS will cancel DataBooks that utilize excessive resources.

Click the “Add” button to display the list of PowerField types from which to choose.

eThority

Training Guide


Step # Procedure

39. From the PowerField type prompt, select the radio button beside the type of

PowerField you would like to create. The options are:

1. eXpression – Build formulas similar to a spreadsheet and displays in a new

column

2. Simple Substitution – Translate existing data values (e.g., MA = Manoa)

3. +/- Margin - Automatically calculates Margins, Markups, and difference

between columns

4. Tabulator – Generate a running total or row-by–row change for a column

5. Ranking – Rank the values for a column

6. % of Total – Compare an individual value to a Group and Grand Totals.

For this example click the radio button beside “eXpression” since we are building a

formula for our PowerField.

Click the “OK” button to open the Edit PowerFields window to begin creating your

PowerField.

eThority

Training Guide


Step # Procedure

40. Field Name will be the header name for the new column that will contain the results

of the expression. As mentioned earlier we will create a PowerField named “FYTD

Enc + Exp”.

Click in the Field Name field and enter “FYTD Enc+Exp”.

41. Data Type determines how the data in the expression will be displayed

1) Currency: Displays numerical data with $ and two digits after the decimal,

and will round to the nearest two digits if more exist. (e.g., $1.73)

2) Date: Displays data in a date format. (i.e., mm/dd/yyyy)

3) Decimal: Displays numerical data with all available digits displayed after

the decimal point. (e.g., 1.734628937)

4) Integer: Displays numerical data as a whole number, rounded up (e.g.,

1.743 becomes 2)

5) List: Data will be available in a pick list format when filtered.

6) Percent: Displays numerical data in a percentage format.

7) String: May be non-numerical data or a number with no numerical

significance

8) Year: Displays four digit numerical data as a year.

9) Yes/No: Displays data in Yes/No format (i.e., 0 becomes a No and a 1

becomes a Yes.)

Click in the Data Type field and select “Currency” from the drop down options since

the total amount should be represented as dollars and cents.

eThority

Training Guide


Step # Procedure

42. In the next steps we will create the expression by selecting the fields used in the

calculation. In this example we will calculate the expression “FYTD Enc+Exp” as

the sum of the fields “Encumbrances FYTD” plus “Expenditure FYTD”.

Click the “Edit” button under Use this Expression to open the eXpression Builder.

43. The grid on the left contains all the columns of the DataBook. You can double click

or drag and drop or single click and then click the “+ADD” button to place

them in the panel to the right.

Constants may be added by entering a Custom Value or by selecting a pre-defined

value from Math Value or Date Value in the User Values box on the bottom left.

When selecting multiple fields, an operator dropdown displays. (The four

possible operators are + (addition), - (subtraction), * (multiplication), or / (division) ).

eThority

Training Guide


Step # Procedure

44. In this example, drag and drop or double click the “Encumbrances FYTD” field from

the Column Names section, then drag and drop or double click the “Expenditure

FYTD” field into the panel on the right. When you select the second field, an

operator dropdown displays. In this example “FYTD Enc+Exp” is the sum

of the two fields selected so we do not have to change the operator.

45. Click the “OK” button on the eXpression Builder window.

46. In the next steps, we will determine whether to apply the eXpression just created to

all records or to a select group of records.

eThority

Training Guide


Step # Procedure

47. To specify whether the expression should be applied to each row or certain rows,

click the “Edit” button under For these Records. The Condition Builder will display.

48. From the Condition Builder, drag and drop or double click fields to create a

conditional filter. The Filter Builder Window opens where you can specify the

values for the conditional filter. After specifying your filter conditions in the Filter

Builder, click the “OK” button on the Filter Builder Window, then click the “OK”

button on the Condition Builder Window.

“Beginning Balance” is an example of a conditional filter such that if the “University

Fiscal Period Code” = “BB” then the “Actual Amount” is included in the “Beginning

Balance”, otherwise the “Actual Amount” is not included in the “Beginning

Balance”.

For this example, we will apply the FYTD Enc+Exp expression to ALL Records so

there is no need to perform an Edit.

eThority

Training Guide


Step # Procedure

49. In the Edit PowerFields window you can click on any of the buttons at the top of the

window to “Add”, “Duplicate”, “Edit” or “Delete” a PowerField. After each

PowerField has been created, eThority requires that you test the syntax by clicking

the “Test” button

For this example, we are only creating one PowerField so click the “Test” button.

You should receive a message “The expression syntax is valid.” If you do not

receive the valid message, your expression will need to be edited.

Note: A common syntax error is failing to select a Data Type.

50. Click the “OK” button then click “Save” at the top of the Edit PowerFields Window.

51. Click the “OK” button at the bottom of the Edit PowerFields Window.

Do not click the “Refresh” button at this time.

eThority

Training Guide


Step # Procedure

52. Newly created PowerFields will appear at the end of the reporting area after

refreshing the DataBook.

53. To view the results of your changes, click the “Refresh” button.

Step # Procedure

54. The sample report we have created has details at the transaction level (Object

Code totals may be expanded). Another change to our “report” for the Dean is

to provide a summary level view at the Object Code-Name level since the Dean

does not need to view the transaction detail. The summary level view will suppress

the details and remove the ability to drill down to the transaction level.

eThority

Training Guide


Step # Procedure

55. To summarize data used in Group, right click on the field in the Group section of

the Data Shaping Panel and select “Summary Only”. After selecting “Summary

Only”, the word “Summary” will display to the right of the field name in the Group

section of the Data Shaping Panel.

Right click on the field “Object Code-Name” in the Group section and select

“Summary Only” to summarize the data at the Object Code Name level.

56. Click the “Refresh” button.

eThority

Training Guide


Step # Procedure

57. The reporting area of the DataBook created is now summarized at the object code

level but no details display in the reporting area. Additionally, we have unneeded

fields in the report, such as those used as filter criteria.

eThority

Training Guide


Step 7: Refine the Report Output by Moving / Hiding Columns &

Displaying Totals

Step # Procedure

58. The next step in creating a report is to further refine the report. Using the Field

Selector you can add or remove fields, rename, rearrange or hide columns in your

final DataBook.

To add a field, select from the list of Available Fields.

To rename a field so the column header displays differently from the

original column name, click in the “Display Text” field and enter the desired

text you choose to display in your “report”.

To change the order of the fields displayed in the reporting area when

reading left to right, highlight the desired field and click on the “Move Up”

button or the “Move Down” button.

To hide a column from displaying in the reporting area, click on the

checkbox beneath the column heading “Hide” in the row beside the

respective column you choose to hide from displaying in the reporting area.

Hidden fields remain available for use in Filter, Group and Sort.

eThority

Training Guide


Step # Procedure

59. In this example, it makes sense to hide the columns that were used as Filters

(Fiscal Period Code, FO Code, and University Fiscal Year) and to move the fields

used in Groups (Budget Summary 1 Code-Name and Object Code-Name) to the

end of the reporting area. Also, it makes sense to display fields in the following

order on a report when reading from left to right: Expenditures, Encumbrances

followed by the sum of Expenditures + Encumbrances.

Navigate to the Field Selector and hide the columns “Fiscal Period Code”, “FO

Code”, and “University Fiscal Year” by clicking in the box beneath the column

heading “Hide”.

Using the “Move Up” and “Move Down” buttons move the “Budget Summary 1

Code-Name” and “Object Code-Name” fields to the bottom of the list of fields.

Move the “Expenditure FYTD”, “Encumbrance FYTD” and “FYTD Enc+Exp” fields

so they appear in the field list in this sequence.

60. For this example, we have no column header names to change in the “Display

Text” field so click the “OK” button at the bottom of the Select Fields for DataBook

window.

eThority

Training Guide


Step # Procedure

61. Another refinement to make to the “report” is displaying totals for “Expenditure

FYTD”, “Encumbrances FYTD” and “FYTD Enc+Exp”.

Step # Procedure

62. In eThority, displaying totals for a column is a column option. To select the column

options, right click on the header and choose from the options provided. To display

totals, select the “Sum” option. A small icon displays in the column header

indicating the column measure is Sum.

Note: A Grand Total for all rows will only display if the User Preference checkbox

beside “Show Grand Total” has been checked.

In this example, right click on the “Expenditure FYTD“ column header and select

the ”Sum” option to display totals at all group levels for that fieldname. Repeat this

step to display totals for the “Encumbrances FYTD” and “FYTD Enc+Exp” columns.

63. Click the “Refresh” button to view the structural changes to the DataBook.

eThority

Training Guide


Step # Procedure

64. The reporting area of the DataBook created now displays July 2014 Total

Expenditures, Encumbrances and the sum of Encumbrances and Expenditures for

all accounts in FO Code 030 summarized at the object code level. When opened,

the DataBook will prompt the user for the Fiscal Period so the DataBook can easily

be run on a monthly basis.

65. For additional DataBook enhancements please refer to Attachments 1 and 2.

eThority

Training Guide


Step 8: Confirming the Report Contains the Correct Results

Step # Procedure

66. The final step in report creation is determining whether the report contains the

correct results. Data is fed from KFS on a nightly basis into eThority so the data in

eThority reflects all posted transactions “as of yesterday”.

In this example, since we used the eThority General Ledger Financial Transactions

Template we can spot check the “report” output by using the KFS General Ledger

Entry Lookup and entering the FY, Chart, Account Number and Fiscal Period.

67. The screenshots above show the eThority DataBook “report” matches the data in

KFS for the Object Codes in one Account Number. Other spot checks to consider

are summations and any calculated fields (PowerFields) you have created.

68. Click on FILE > Save As to save your new User DataBook.

eThority

Training Guide


Appendix A: Quick Reference Chart:

Action eThority Feature Training Guide Reference

Choosing Data Select Template Pg. 9-10

Selecting fields for report Field Selector Pg. 11-15

Selecting certain records Filters Pg. 16-21

Exclude certain records Filters Pg. 19

Sorting data Groups or Sorts Pg. 22-24, 51

Add Subgroups/Reorder Groups Groups Pg. 22-24

Entering values at runtime Control Values Pg. 25-31

Resorting columns Field Selector Pg. 42-43, 50

Hiding columns Field Selector Pg. 42-43, 50

Display Totals Sum Pg. 44-45

Create a filter Control Value Pg. 16-20, 25-31

Create New Field PowerField Pg. 32-38

Summarize Data in a Group *Summary Only Pg. 40

Rename Columns Display Text Pg. 42, 51

eThority

Training Guide


Appendix B: Miscellaneous Tips for DataBook Creation:

User Preferences Settings:

Navigation: CONFIGURE>User Settings>My Preferences or VIEW>User Preferences

A Grand Total for all rows will only display if the User Preference checkbox beside “Show

Grand Total” has been checked.

Page Size: Defines the number of rows displayed in a single DataBook from 100 to 20,000.

By displaying more rows per page there are fewer pages on the screen that you have to

change. When paging through the results eThority requires the DataBook to recalculate. This

may improve performance if you have a very large “report”.

Before creating a Control Value Filter Default the User Preference checkbox beside “Allow

Filters to be Subgrouped” must be checked.

Saving:

Navigation: FILE > Save As or FILE > Save

There is a checkbox beside “Auto-Refresh Grid” that displays when saving a DataBook. This

option will refresh your DataBook every time the DataBook is changed (including a simple

resort of your data) and is not recommended during DataBook creation.

To display the Control Values prompt each time the DataBook is opened select “Ask for

Control Values” when saving the DataBook.

Printing:

Navigation: FILE > Print

eThority will attempt to fit the entire report onto an 8 ½” x 11” page by shrinking the font size

until the contents fit the page width, which may render the report unreadable depending on the

number of columns in the report. If there are too many columns in the report t print, the

resulting print output will be a blank page. You may need to export the results to print your

report on legal size paper.

When printing a DataBook, the columns will be the width of the column header. If the data

width exceeds the column header width the printed data may overlap into the next column. A

work around is to add spaces to the column header to make the column header wider in VIEW

> Field Selector >Display Text. (e.g., “Exp” may be changed to “________Exp______” where

the underline _ represents spaces.

eThority

Training Guide


In order to use the Print function in eThority, be sure to have your popup blocker disabled so it

to allows popups from the eThority website.

Reset Options:

Navigation: VIEW > Reset Column Widths - This option will reset the column widths to

eThority defaults. The default is the longest value within the data or the header.

Navigation: VIEW > Reset All – This option removes all Groups, Sorts, Totals and Filters from

your DataBook. Control Values and PowerFields remain intact.

eThority

Training Guide


Attachment 1

Other Enhancements to the Account Status DataBook

Suppose for your own purposes, you need transaction level detail for the information provided in

the DataBook you just created for the Dean. You can simply add a few additional fields

(transaction description and the date the transaction posted) to the same DataBook, remove the

Summary mode at the Object Code level, rerun the DataBook and save it with a different name.

Suppose you would like your reporting area to look something like this:

eThority

Training Guide


Selecting Additional Fields and Restructuring the DataBook

Step # Procedure

69. In this example we will select additional fields, move them into the desired location

to appear in the reporting area, and rename some column headings.

To select transaction level detail, navigate to the Field Selector, scroll through the

list of “Available Fields” and select the following fields:

“Transaction Ledger Entry Description”

“Transaction Post Date”

70. Use the “Move Up” “Move Down” buttons to move the newly added fields up the list

of column names to display immediately before the “Expenditure FYTD” column.

eThority

Training Guide


Step # Procedure

71. Rename the text that displays as the column header by typing over the name in the

Display Text field.

In this example rename ”Transaction Post Date” as “Trans Post Dt”.

72. For transaction level detail to display in the reporting area, we no longer need the

“Object Code-Name” in Summary mode.

Right click on the field “Object Code-Name” in the Group section and click on

“* Summary Only” to remove the summarization of the data at the Object Code-

Name level.

73. To Sort the records in the DataBook by Transaction Post Date, click on “Sort” in

the Data Shaping Panel to choose “Transaction Post Date” from the list of Column

Names, then close the Sort Records window.

Note: Alternatively, you can drag the column header fields into the Sort section of

the Data Shaping Panel if the column is visible in your reporting area. Refer to

Step #22 for additional details on Sorting.

74. Click the “Refresh” button.

eThority

Training Guide


Step # Procedure

75. The new “report” now shows the same information in the Dean’s “report” with

additional transaction level detail that includes the Transaction Ledger Entry

Description and is sorted by the Transaction Post Date.

76. Use the Save As feature to save your DataBook with a name different from the

DataBook you created for the Dean.

eThority

Training Guide


Notes:

ethority training guide - university of hawaii · in our example the dean has asked you to provide...

Documents