Procurement Academy FMS Data Analysis

1
Procurement Academy FMS Data Analysis

2
Objectives

• Understand the difference between reports and
  queries in FMS
• Understand how to run reports
• Understand how to run public queries
• Understand how to develop and run private queries
• Understand how to refine data in Excel using
  pivot table reports

3
FMS Reports Queries

• Reports provide data in a standard output format
• Some reports are standard with FMS while others
  are developed by the IS staff
• Users can input variable information into
  prompts in order to customize the report
• Date ranges, DeptID, Fund Code, etc.
• Requires the user to establish a run control
• Queries provide data in a list of rows and
  columns
• Users can customize the criteria to meet very
  specific needs
• Requires knowledge of FMS tables in order to
  join tables and produce valid results
• Public queries can be saved as private queries
  and then modified
• Can be exported to Excel or a text file for
  additional analysis of the data and presentation
  of results

4
Running Reports
5
Non-Salary Spending ReportNavigation
PurchasinggtPurchase OrdersgtReportsgtNon-Salary
Spending Report
6
Select Server
7
Report Output PDF File
8
(No Transcript)
9
Useful Reports

• Non-Salary Spending Report
• PurchasinggtPurchase OrdersgtReportsgtNon-Salary
  Spending Report
• Open Encumbrance Reports
• Commitment ControlgtBudget ReportsgtOpen PO
  Encumbrance Report
• Departmental HUB Report
• http//buy.uth.tmc.edu/hub_report_dept.htm

10
HUB Report Access thru Procurement
WebsiteNavigation via HUB page of Procurement
WebsiteRequires LDAP Login
11
Parameters Date Range Department Range
12
HUB Report OutputReport can be printed to PDF
13
Running Queries
14
Query Training

• 8.8 Query Manager (7 hours)  Prerequisite
  Financials Overview General Ledger for Processor
  or End Users
• Intended for users who need to create adhoc
  reports from FMS.  It includes an overview of the
  PeopleSoft 8.8 Query Manager reporting tool and
  how to run public queries, build basic queries,
  format and sort fields, add criteria to your
  query, build prompts, join related tables, and
  join non-related tables.
• Link to Training Classes and Descriptions
  http//is.uth.tmc.edu/training/psfmsclas.htm
• IT Helpdesk x. 4848 Website http//is.hsc.uth.t
  mc.edu/css/

15
Public versus Private Queries

• Public Queries
• Available to all FMS Users
• Users enter information into prompts to focus
  the results
• Developed by IS to support broad user data
  requirements
• Private Queries
• Developed by individual users
• Can be tailored to provide very specific
  information
• Can be copied to other FMS users

16
Useful Public Queries

• BuyCard Transactions
• CC_TRANS_DEPT
• CC_TRANS_EMPLID
• CC_TRANS_VOUCHER
• Encumbrances
• POX_OPEN_ENCUMBRANCES_BY_BUYER
• POX_OPEN_ENCUMBRANCES_BY_PO
• POX_OPEN_ENCUMBRANCES_BY_CF

17
Useful Public Queries

• Commitment Control (Project Non-Project)
• KK_NONPRJ_ENC_BY_CF
• KK_NONPRJ_EXP_BY_CF
• KK_NONPRJ_PREENC_BY_CF
• KK_PRJ_ENC_BY_CF
• KK_PRJ_EXP_BY_CF
• KK_PRJ_PREENC_BY_CF

18
Developing Private QueriesFMS Tables
Relational Database
19
Joining Multiple Tables Requires Data Validation
20
A More Reliable SolutionNon-Salary Spending
Table (UT_AP_SPENDING)

• One table that contains all related payment data
  for a rolling 15-month period
• No need to join tables and be concerned about
  validity
• 55 fields of data related to payments
• PO and Non-PO vouchers
• Includes travel, employee reimbursement and
  internal services
• Includes Supply Mall purchases and eShip Global
  charges
• Requisition information
• Purchase Order information
• Vendor information
• Merchant field helps when there is a mix of
  BuyCard and non-BuyCard transactions
• Chartfield information
• Includes project names and Principle Investigator

21
Query Exercise
22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
Select View All if all fields are not visible
Select fields to include in query
26
Edit fields if required
27
(No Transcript)
28
Prompts allow us to vary certain criteria each
time we run the query
29
(No Transcript)
30
Search for field name
31
Select correct field
32
Select Heading Type Text
Enter Heading Text Begin Date
33
Completed Prompt for Begin Date
34
Completed prompts for this query.
35
Create criteria to use information that will be
inserted into to prompts
36
Chose field
Set condition
Choose prompts to define the expression
37
Save the query.
38
Run query from query manager
39
Fill in prompts
Click on View Results
40
Download to Excel
Number of Records
41
Delete this Row
42
Pivot Tables in Excel
43
Pivot Tables

• Pivot Tables is a powerful tool in Microsoft
  Excel
• Permits analysis and summarization of large
  amounts of data in lists and tables
• Output to a worksheet independent of the original
  data layout in your spreadsheet
• Pivot table reports are created by dragging and
  dropping field names into areas on the pivot
  table template
• Report Filter, Column Labels, Row Labels, Values
• Excel 2007 has a more intuitive method for
  creating pivot table reports
• Excel 2007 provides significantly more rows and
  columns for data
• Over 1 million rows in 2007 vs. 65,000 rows in
  2003

44
Pivot Table Instructions

• Download query to Excel
• Delete top row(s) with header information so that
  field names appear on Row 1
• Open the Insert tab and select Pivot Table on
  the upper left
• Click on Pivot Table
• Verify data range select new worksheet
• Drag field values into appropriate area on
  template
• Fields can be moved and rearranged
• Row and column fields can be further filtered to
  focus the report
• Add desired formatting
• Can be printed to PDF for distribution

45
For Excel 2003
Pivot Tables Exercise
46
(No Transcript)
47
(No Transcript)
48
(No Transcript)
49
Drag Drop Fields to Template Or Select and
assign to area
50
(No Transcript)
51
Select Filters
52
(No Transcript)
53
Add Merchant Field to Row Area
54
(No Transcript)
55
(No Transcript)