Travel Data Sets

Travel Data Sets in Power BI are the basis for the Pending report set. They are made available to analysts to create their own reports to meet on-the-fly and seasonal needs. Information available in the data sets is delineated below. The data sets are refreshed every morning with actions taken the day before.


 Travel - Pending Status Travel Requests Data Set

This data set includes information about Travel Requests, no matter their status in Archivum. Once a Travel Request is submitted, the information for that request is available in this data set the next day. Additional information about the Expense Reports connected to a Travel Request can be obtained from this data set.


Table nameTable typeTable descriptionColumns / Fields

Chartfield College-Global Department


This table contains college and global department information, including code, abbreviation, name, and ID for the chartfield information provided.

College Code
College Code Abbreviation
College Name
Department Name HSC Directory
Global Department Abbreviated Name
Global Department ID
Global Department Name
Global Department Type Indicator

Data Refresh


This table contains the details regarding the date and time that data was last refreshed from the source system. Last Refresh Date
Last Run Timestamp
Expense Report Created Date
This table contains date and time information captured for the expense when the expense report was initiated.

Calendar Month
Calendar Quarter
Calendar Year
Day of the Week
ER Created Day
Fiscal Month
Fiscal Quarter
Fiscal Year
FYYY-Quarter
Month Abbreviation
Month Name
YYYYMM

GEMS Department Profile View

View

This table contains a list of GEMS departments and its associated global department (links via Global Department ID).

This can be used to determine which GEMS departments are grouped under a given Global Department.

Abbreviated Name - Travel
College Code
Creation Date
Department Code
Department Desc
Department Name
Department Profile ID
GEMS Department
Begin Date

Global Department ID
Short Name
Type Indicator

MDM Current Employee View

View

This table contains appointment data for employees.

Department ID
Employee ID
Begin Date
Key Person Record
MDM ID
Name Display

Person - Last Action

This table stores the name and MDM ID of the person who last took action on the travel request.

MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.

Last Action Person First Name
Last Action Person Last Name
Last Action Person MDM ID
Last Action Person Middle Name
Last Action Person Name
Person - Last Send Back

This table stores the name and MDM ID of the person who last sent back the travel request to obtain more information.

MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.

Last Send Back Person First Name
Last Send Back Person Last Name
Last Send Back Person MDM ID
Last Send Back Person Middle Name
Last Send Back Person Name

Person - Traveler

This table contains the details of the traveler, including department, employee ID, and name.

MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.

Traveler Department
Traveler Department ID
Traveler First Name
Traveler GEMS Employee ID
Traveler Middle Name
Traveler Last Name
Traveler MDM ID
Traveler Middle Name
Traveler Name
Travel Begin Date
This table stores date information regarding the beginning date of the person's travel.Begin Day
Calendar Month
Calendar Quarter
Calendar Year
Day of Week
Days in Calendar Year
Fiscal Month
Fiscal Quarter
Fiscal Year
FYYY Quarter
KEY Day
Leap Year
Monday Thru Friday Flag
Month - YYYYMM
Month Abbreviation
Month Name
Saturday Date Of Week
Sunday Date Of Week
Week Label Sunday Saturday
Week of Cal Year
Week of Month
Week Sunday to Saturday Since FY00
Travel End Date
This table stores date information regarding the end date of the person's travel.Bucket Range Days After Travel
Calendar Month
Calendar Quarter
Calendar Year
Day of Week
Days After Travel
Days in Calendar Year
Fiscal Month
Fiscal Quarter
Fiscal Year
FYYY Quarter
KEY Day
Leap Year
Monday thru Friday Flag
Month Abbreviation
Month Name
Month YYYYMM
Saturday Of Week
Sunday Of Week
Todays date
Travel End Day
Week Label Sunday to Saturday
Week Of Calendar Year
Week Of Month
Week Sunday to Saturday Since FY00
Travel Request Details
This table maps information about the travel request to information about the expense report. It is updated as action is taken on the request and the expense.Archivum Number
Details ID
ER Description
ER SB Action Description
ER SB Action Type
ER SB Note
ER Type
Expense Report ID
KEY Travel Details
Last Action Description
Last Action Note
Last Action Type
TAR Number
TER Number
TR Description
TR PTA Preventative Action
TR PTA Reason
TR SB Action Description
TR SB Action Type
TR SB Note
Travel Title
Type
Travel Request Expenses
This table contains the code and description of the travel expense.Code
Description
ID
Location Category
Travel Request Line Item Details
This table holds information on a specific travel request, including department, expense ID, payment type, and account code.Chartfield KEY Department Profile
Chartfield KEY Global Department
Chartfield KEY USF Department
Initiative Code
KEY Travel Expense ID
KEY Travel Location ID
KEY USF Fund ID
KEY USF Project ID
KEY USF Project Profile
OU
USF Account Code
Percentage
Travel Request Amount
Travel Request ID
USF Account Code
Travel Request Location
This table contains details on the travel location, such as city, state, and country.City
City State Country
Code
Country
Country Code
Group
ID
KEY Travel Location
State
Travel Request Status
This table contains the current status of the travel request.ID
Status
Travel Request Summary
This table contains the details of the travel request, including travel dates, purpose, request submitter, traveler employee ID, and travel location. It is updated as the request is processed to include status of the request, last action, and send back details.Bucket Range Days After Travel
Days Before Travel
Days Between
Days in Last Status
ER Exists Flag
KEY Last Action Day
KEY Person Record
KEY TR Begin Day
KEY TR Create Day
KEY TR End Day
KEY TR Last Modification Day
KEY Travel Details
KEY Travel Doc Status
KEY Travel Location
KEY Travel Purpose
Key Person Record
Last Send Back by MDM ID
Send Back Datetime
Send Back Flag
Send Back KEY Day
Submitter Name
TAR Number
TR Created by MDM ID
TR Total Amount
TR Updated by MDM ID
TR_TOTAL_AMOUNT_ZERO
Travel Request ID
Traveler Appt Global Department KEY
Traveler Employee Record
Traveler GEMS Employee ID
Traveler GEMS KEY Department Profile
Traveler College-Global Department
This table contains the college and department information for the traveler as indicated in Archivum.

College Code Abbreviation
College Name
Global Department ID
Traveler College Code
Traveler Department Name HSC Directory
Traveler Global Department Abbreviated Name - Travel
Traveler Global Department Name
Traveler Global Department Type Indicator

USF FAST Department
This table stores the FAST code and name.FAST Department Code
FAST Department Name
USF Fund Codes
This table provides information on fund codes.Fund Code
Fund Description
Fund Group Type
USF Products
Contains list of FAST product codes and descriptionKEY USF Product
USF Product
USF Product Description
USF Projects
Contains list of FAST projects and associated details such as start and end dates, associated employees, total amounts, award status, and purpose.Adjusted Award Amount
Adjustment Amount
Amount Total
Award Status
Award Type
Awards Date
Contract Begin Date
Contract Business Unit
Contract Description
Contract Employee ID
Contract Employee Name
Contract End Date
Contract Number
Contract Title
Contract Type
Cust ID
Department ID
GM Begin Date
GM CFDA Num
GM Source Income
GM Source Income
GR Source Funding
Initial Award Amount
KEY USF Project
Primary Sponsor ID
Project Begin Date
Project CFDA
Project End Date
Project ID
Project PI Employee ID
Project PI MDM ID
Project PI Name
Proposal ID
Purpose
Ref Award Number
Ref Awd Num2
Sponsor Award ID
Sponsor Name
USF Business Unit
USF Operating Unit
Version ID
 Travel - Pending Status Expense Report Data Set

This data set includes information about Expense Reports, no matter their status in Archivum. Once an Expense Report is submitted, the information for that ER is available in this data set the next day.


Table nameTable typeTable descriptionColumns / Fields

Chartfield College-Global Department


This table contains college and global department information, including code, abbreviation, name, and ID for the chartfield information provided.

College Code
College Code Abbreviation
College Name
Department Name HSC Directory
Global Department Abbreviated Name
Global Department ID
Global Department Name
Global Department Type Indicator

Data Refresh


This table contains the details regarding the date and time that data was last refreshed from the source system. Last Refresh Date
Last Run Timestamp
Day - Last Action Date
This table contains date and time information captured for the expense when the expense report was initiated.

Calendar Month
Calendar Quarter
Calendar Year
Last Action Day
Fiscal Month
Fiscal Quarter
Fiscal Year
FYYY-Quarter
Month Name

Day - ER Created Date
This table contains date and time information captured for the expense when the expense report was initiated.

Calendar Month
Calendar Quarter
Calendar Year
Day of the Week
ER Created Day
Fiscal Month
Fiscal Quarter
Fiscal Year
FYYY-Quarter
Month Abbreviation
Month Name
YYYYMM

GEMS Department Profile View

View

This table contains a list of GEMS departments and its associated global department (links via Global Department ID).


This can be used to determine which GEMS departments are grouped under a given Global Department.

Abbreviated Name
College Code
GEMS Department Code
GEMS Department Description
Global Department Name
KEY Global Department
Type Indicator

MDM Current Employee View

View

This table contains appointment data for employees.

Department ID
Employee ID
Begin Date
Key Person Record
MDM ID
Name Display

Person - Last Action Person

This table stores the name and ID of the person who last took action on the travel expense report.


MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.


Person MDM ID
Last Action Person Name
Person - Last Send Back

This table stores the name and ID of the person who last sent back the travel expense report to obtain more information.

MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.

Last Send Back Person MDM ID
Last Send Back Person Name

Person - Traveler Name

This table contains the details of the traveler, including department, employee ID, and name.

MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.

Traveler EMPLID
Traveler First Name
Traveler GEMS ID
Traveler Middle Name
Traveler Last Name
Traveler MDM ID
Traveler Middle Name
Traveler Name
Person - Submitter

This table contains the details of the submitter, including department, employee ID, and name.

Submitter EMPLID
Submitter GEMS ID
Submitter GEMS End Date
Submitter GEMS Start Date
Submitter Hire Date
Submitter Termination Date
Submitter MDM ID
Submitter Name
Day - Travel Begin Date
This table stores date information regarding the beginning date of the person's travel.Begin Day
Calendar Month
Calendar Quarter
Calendar Year
Fiscal Month
Fiscal Quarter
Fiscal Year
FYYY Quarter
Month Name
Calendar Day
YYYYMM
Day - Travel End Date
This table stores date information regarding the end date of the person's travel.Calendar Month
Calendar Quarter
Calendar Year
Day of Week
Fiscal Month
Fiscal Quarter
Fiscal Year
FYYY Quarter
Month Abbreviation
Month Name
YYYYMM
Travel End Day
Expense Report Details
This table contains details on the expense report details and is updated as action is taken on the request and the expense.

ER Description
ER SB Action Description
ER SB Action Type
ER SB Note
ER Type
Expense Report ID
Last Action Description
Last Action Note
Last Action Type
TAR Number
TER Number
TR PTA Preventative Action
TR PTA Reason
TR SB Action Description
TR SB Action Type
TR SB Note

Travel Description
Travel Title
TR Type
Travel Expense ID
Expense Report Travel Expenses
This table contains the code and description of the travel expense.Expense Code
Expense Description
Expense ID
Expense Type
Expense Report Line Item Details
This table holds information on a specific travel expense, include department, expense ID, payment type, and account code.Chartfield Amount
Chartfield Percentage
Expense ID
Expense Report ID
Initiative Code
OU
Payment Type
Percentage
Traveler EMPLID
Traveler EMPLRCD
USF Account Code
Expense Report Travel Location
This table contains details on the travel location, such as city, state, and country.City
City State Country
Location Code
Country
Country Code
Location Group
Location ID
State
Expense Report Status
This table contains the current status of the travel request.Status ID
Status
Expense Report Summary
This table contains the details of the travel expense report, include last status, submission, last action, traveler employee ID, and traveler employee record.Bucket Range
Cash Advance
Days Between Modified
Days Between
Days in Last Status
Days in Last Status by ER
Days Since Last Action
ER Completed Flag
ER Created by MDM ID
ER Modified by MDM ID
ER Total Expenses
Expense Report ID
Fiscal Review
FR Sendback
Hold Count
Last Action MDM ID
Last Send Back by MDM ID
On Hold Duration
On Hold Flag
ER Prepay Auditor Approved Datetime
Send Back Flag
Submission
Submission to Approved
Supervisor Review
Supervisor Sendback
Total Sendbacks
TR Exists Flag
Travel End to Approved
Travel End to Submission
Traveler Empl Rcd
Traveler EMPLID
Traveler College-Global Department
This table contains the college and department information for the traveler as indicated in Archivum.

College Code
College Name
Global Department Name
HSC Directory Dept Name
Type Indicator

USF FAST Department
This table stores the FAST code and name.FAST Department Code
FAST Department Name
USF Fund Codes
This table provides information on fund codes.Fund Code
Fund Description
Fund Group Type
USF Products
Contains list of FAST product codes and descriptionKEY USF Product
USF Product
USF Product Description
USF Projects
Contains list of FAST projects and associated details such as start and end dates, associated employees, total amounts, award status, and purpose.Adjusted Award Amount
Adjustment Amount
Amount Total
Award Status
Award Type
Awards Date
Contract Begin Date
Contract Business Unit
Contract Description
Contract Employee ID
Contract Employee Name
Contract End Date
Contract Number
Contract Title
Contract Type
Cust ID
Department ID
GM Begin Date
GM CFDA Num
GM Source Income
GM Source Income
GR Source Funding
Initial Award Amount
KEY USF Project
Primary Sponsor ID
Project Begin Date
Project CFDA
Project End Date
Project ID
Project PI Employee ID
Project PI MDM ID
Project PI Name
Proposal ID
Purpose
Ref Award Number
Ref Awd Num2
Sponsor Award ID
Sponsor Name
USF Business Unit
USF Operating Unit
Version ID