UD Financials Grants Reporting

1
UD Financials Grants Reporting

• www.udel.edu/financials

2
Grants Data and Queries added to Reporting
Database

• Grants data and tables will supplement existing
  reporting capabilities (EZQs, etc.)
• Pre-written queries will give you more reporting
  choices (GMQs and PIQs)

3
Session Objectives

• For Query Viewers/Managers
• Review Report and Production Databases
• Explain Access/Security in the Report Database
• Grants Revenue Recognition and Payments
• Use the Pre-Written Queries (EZQs, GMQs, PIQs)
• Look at BUDDY
• For Query Managers
• Review Additional Info Needed to Create Queries
• Explain Design and Security of the New UOD Grants
  Tables
• Data Dictionary

4
The report instance for the Query Viewer/Manager

• Report database Grants Reporting ready now
• A copy of the production database
• Updated nightly Example JV you do today will
  be there tomorrow
• Security is based on Purposes or DeptID (for
  proposals)see handout
• Security changes/updates take one day to be
  reflected in Reporting database
• Where can I find the report instance?
• www.udel.edu/financials

5
Production vs. Report Access for the Query
Viewer/Manager

• Proposals entry/review - FIPRD (production
  database). www.udel.edu/grants
• Run reports/queries on grants data - FIRPT
  (report database). www.udel.edu/financials
• Some tables are secured by Purpose and others by
  Dept ID, your access will impact query results
• Staff who enter proposals will need access to
  related Dept ID or Purposes in order to query the
  proposal/award
• This is especially important for Grants with
  multiple projects and departments

6
Viewing your Reporting Access

• Run these queries to see your current access
• UDQ_USERID_DEPT_ACCESS
• Enter your user id to see DEPT IDs you have
  access to view
• UDQ_ROW_BY_ADMIN
• Enter your last name with the (wildcard) to see
  the Purposes you have access to view
• To request new or additional access
• e-mail your Deans or Directors Office contact
  with DeptIDs and/or Purposes. Your contact will
  forward approval to ovpr-access_at_udel.edu.

7
Understanding Grants Revenue Recognition AKA
Wheres my cash???

• In legacy system, grant revenue was recognized
  when cash was received
• In new Financials System, grant revenue is
  recognized as expenses are incurred not when
  cash is received.
• 3 GMQs together provide the total cash for a
  project or contract
• GMQ_CASH_NOT_PS_BILLED
• GMQ_CASH_PS_BILLED
• GMA_CASH_THRU_2002

8
Reportsfor the Query Viewer/Manager

• Pre-written reports/queries
• EZQs provide Purpose fund balances
• GMQs provide Proposal or Project detail
• PIQs provide PI related information
• See Handout Grants Reporting Queries
• BUDDY Quickly access transactions and add
  additional information to transactions (tag
  data)

9
PI Snapshot

• Statement of all projects for a PI, returning the
  expenses in the major categories and current
  balance as well as obligations.
• Runs on-request under the UD Processes menu
• Returns in an Excel spreadsheet
• Results found in Report Manager
• Being refined nowshould be ready by end of
  Summer 2005

10
BUDDY
Bringing University Data Directly to You

• A web based dynamic report using UD People Soft
  financial data

11
Why would I use BUDDY Instead of UDFinancials
Query?

• BUDDY decreases response time for inquiry
• For routine inquiries to transactions and
  balances
• Easy way for PIs and Chairs to get Purpose
  balances
• Ability to tag/comment on transactions
• 3 additional free form userfields (Comments) can
  be added after transaction is posted to UD
  Financials.
• UD Chartfield (User Field) is updateable in BUDDY
  (does not write back to UDFinancials!!!!)

12
When is BUDDY data updated?

• Daily at 900 am from FIRPT.
• Update process can take some time and is
  completed by noon.
• A JV you process today will show up in BUDDY
  tomorrow afternoon.
• www.udel.edu/buddy

• Where do I find BUDDY?

13
Summary for the Query Viewer/Manager

• Know thy Instance FIPRD vs. FIRPT
• Know thy EZQ and GMQ and PIQ
• Know thy BUDDY
• Know thy Grants Trainer report problems

14
Writing Reportsfor the Query Manager

• How do I make my own queries?
• Query Manager access needed
• Take Query Class before access granted
• Query Training on web (www.udel.edu/UDFS/info
  (click training tab)
• Start with existing public query (GMQ) and save
  as your own using your naming convention
• Or start from scratch with create new query

15
Create New Queryfor the Query Manager

• Data is stored in tables. UOD Views were
  created by combining the most commonly used
  Grants tables (records).
• The UOD Views have row level security by DeptID
  or Purpose

16
The UOD Viewsfor the Query Manager

• Five UOD Views in the Report Instance for Grants
• (See the Data Dictionary at www.udel.edu/UDFS/info
  )
• Award Attributes View - UOD_AWD_ATRB_VW
• Proposal Budget View - UOD_PRP_BUD_VW
• Proposal Budget Details - UOD_PBUD_DTL_VW
• Proposal Attributes View- UOD_PRP_ATRB_VW
• Project View - UOD_PRJ_VW
• Project Resource View - UOD_PRJ_RES_VW
• Project Funding View - UOD_PROJ_FUND_VW

17
The Grants Datafor the Query Manager

• Learn about the data in the UOD Views
  www.udel.edu/UDFS/info
• Data dictionary (Data Terminology tab)
• Defines fields within the Views
• Provides field format, length and other
  attributes
• Best to brush up on data before writing or
  running queries

18
Important Codesfor the Query Manager

• Analysis Types see Handout
• Critical to querying on UOD_PRJ_RES_VW
• Multiple rows created for transactions,
  duplication and erroneous balances will occur in
  queries if Analysis Types not considered
• Other Important Codes see Handout
• Award Status
• Project Status
• Proposal Status

19
Lets Run an EZQ! Query Viewer/Manager

• Log on to the Report Instance
• www.udel.edu/financials
• Select Reporting Tools/Query/Query Viewer
• Type in EZQ
• Select EZQ_BAL_PROJ_SUMMARY
• Fill in prompts
• Returns project-to-date balance summarized by
  account category

20
Lets Run a GMQ! Query Viewer

• Log on to the Report Instance
• www.udel.edu/financials
• Select Reporting/Query/Query Viewer
• Type in GMQ
• Follow the handout Grants Reporting Queries
• OR
• Select GMQ_PROP_PROJS_BUD
• Use a proposal with multiple projects
• Returns all budget info for Projects in a
  proposal