Grants ITD Detailed Universe Guide
December 2008
What is Grants data?
UI Decision Support Data Ed: Grants ITD DUG2008
Grants Data – General information
• Contains Banner Grants data beginning in July 2003 to present • Converted Grant data: At June 30, 2003 budgets were entered into the system equal to expenses (rendering the balance available equal to $0). The remaining unexpended budget was entered in FY04 -- period 00 resulting in the correct balance available starting July 1, 2003 • All Banner Grants have a fund code that is the same as the grant code. Therefore, when listing funds on a grant, consider using a condition on the Financial Fund data entry indicator = Y. This will help to ensure you obtain a list of 6-digit data enterable funds
UI Decision Support Data Ed: Grants ITD DUG2008
Grants Data – General information
• Grant transaction detail is sourced from the operating ledger detail table. Period, YTD, and ITD balances are sourced from an independent table called: ITDOPAL. Therefore, in the current open month, it is possible summaries between these two sources may be out of sync due to timing differences. • Adding objects from the Grant person(s) folder to your query can cause duplication of result rows. These objects should be utilized with care. • The principal investigator on the Grant may or may not be the same person as the principal investigator on the grant fund(s). • Exercise good judgment when including the YTD BBA object. BBA is a calculated field that utilizes encumbrances. Labor encumbrances, which make up 60-80% of the average units’ encumbrances, are calculated on the State Fiscal year, not on Grant year.
UI Decision Support Data Ed: Grants ITD DUG2008
Grants Data – General information
• Banner Grants data represents the efforts of University researchers. Please treat the available data with respect on behalf of the investigators and intellectual credit it represents. • Data is loaded every evening-please verify finance data is loaded before running reports!
http://www.ds.uillinois.edu/web/Home/AboutData/Data.aspx
• Any Grant data quality issues are documented in the Quick Reference Guide. Please review them carefully.
UI Decision Support Data Ed: Grants ITD DUG2008
Grant ITD Universe Overview
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe
A universe to list Grants transactions answering questions like:
•What transactions have occurred on PI Bob’s grants? •What was the total grant expenditure by PI in my college over the last state fiscal year? •What are the YTD expenditures for all grants in my department? •How many grant dollars did we spend last year by sponsor?
Found in EDW Universe: Finance – Grants ITD
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD - Description
This Universe contains:
– C-FOAPAL – Grant start & end dates – Grant transactions: budget, revenue & expenses – Grant sponsors/agencies – Grant pass thru agency – Principal Investigator information – C-FOAPAL reflects current information only
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD – Intended Uses
Intended use(s) of this Universe:
• Listings of Grants transactions and related information • In-depth review of Grant Expenses • Analysis of Grants by research area or sponsor
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants Streamlined – Universe Limitations
This Universe is NOT designed to:
Combine Operating Ledger transaction detail & ITD Ledger Summary in the same query Original Document Number should only be used with the Grant Ledger Detail class
UI Decision Support Data Ed: Grants ITD DUG2008
EDW - Finance Grants ITD Universe
UI Decision Support Data Ed: Grants ITD DUG2008
Grants ITD – universe objects
Grant Code
The Banner assigned code that identifies each grant. Format is Q1234. Where the first letter indicates a responsible campus and the category of grant funding.
Grant Fund Category Responsible Campus Federal/Gov't/WorkStudy State Private Urbana A D C Chicago E H G Springfield J M L
Other B F K
Grant Long Title
The character title of each Grant assigned in the Banner system. Grant Start Date The date the UofI and the sponsor agree that the grant should officially begin. For grants in anticipation status, this will be the date that anticipation spending may begin. When the grant moves out of anticipation, the start date is reset forward to reflect the agreed upon official start date.
UI Decision Support Data Ed: Grants ITD DUG2008
Grants ITD – universe objects
Grant End Date
The date the grant officially ends. Spending may occur beyond the end date up to the Termination date. After that time, only the office of G&C may affect the grant.
Principal Investigator UIN and Name
Every Banner grant has an assigned principal investigator. In addition, grant funds also have an assigned principal investigator and may also have co-investigators.
Grant Fund type
A grouping for the type of agency affiliation sponsoring the grant. Example valid values are F=Federal, S=State, P=Private, O=Other Government
Responsible Campus/College/Dept/Org
Every Banner grant has an assigned 6 digit organization and campus. That organization is part of a department and a college which are also represented here.
Sponsor ID and Name
The 9 digit identification number and name of the grant agency/sponsor.
Sponsor Identifier of Grant
This is the identifier (number and/or letters) for the grant utilized by the sponsor.
UI Decision Support Data Ed: Grants ITD DUG2008
Grants ITD – universe objects
Pass Through Agency Name
For sub-awards that UofI investigators have received, this field contains the name of the sponsoring agency of the original award. For example, if a UofI PI recieves a subaward from a researcher at Purdue, this field records the information about the agency that granted the award to the Purdue researcher.
Grant Persons (folder)
Banner grants can have one or more investigators assigned to them. In addition, a grant may have an assigned financial manager, unit contact or GCO post-award contact. Please utilize these names with an associated role code and description. Please note: When using these with transactions, duplication of rows may occur.
Grant Attributes (folder)
Additional objects related to grants. These objects are grouped here as they are only occasionally used.
UI Decision Support Data Ed: Grants ITD DUG2008
Grants ITD - time related objects
State Fiscal Year
The four digit fiscal year to which the grant transaction applies.
State Fiscal Period
A two-digit month relative to the state fiscal year where 01=July, 02=August…12=June.
Calendar Year
The four digit calendar year of a particular grant period.
Calendar Month
The two digit calendar month (2 digit number; ’01’=Jan, ’02’=Feb…’12’=Dec) corresponding to a particular grant period. Please note: If this record is for pre-award activity (i.e., where Grant Period is '00' for the first year of the grant) then calendar month is set to 'PRE'. If this record is for trail out grant activity, then the calendar month is set to ‘POST’.
UI Decision Support Data Ed: Grants ITD DUG2008
Grant ITD – universe object classes
Grant Ledger Detail folder
All grants transactions take place in the Operating Ledger (which creates data in state fiscal year and period) . Therefore, grant transaction detail objects are identical to operating ledger detail objects.
ITD Ledger Summary folder
This group includes period/month, Year To Date, and Inception To Date dollars. For best results, please use a condition on a year and month(s) when utilizing these objects.
UI Decision Support Data Ed: Grants ITD DUG2008
Grants Ledger Detail - universe objects
Grant transactions
Grants transactions come from the operating ledger. Example amounts are: –Permanent Original Budget amount –Permanent Budget Adjustments –Temporary Budget Adjustments –Revenue amount –Expense amount –Reservation amount –Encumbrance amount –Accounted Budget amount
UI Decision Support Data Ed: Grants ITD DUG2008
Grants Ledger Summary – universe objects
Grant ITD Ledger Summary Dollar Buckets
These are grouped into 3 folders: ITD (inception to date), YTD (year to date) and Period (month). For ease of use, most budget summaries are only calculated for expense account codes. –Permanent Original Budget ITD & YTD Balance –Permanent Budget Adjustments ITD & YTD Balance –Temporary Budget Adjustments ITD & YTD Balance –Revenue ITD & YTD Balance –Expense ITD & YTD Balance –Reservation YTD Balance –Encumbrance YTD Balance
UI Decision Support Data Ed: Grants ITD DUG2008
Grant Ledger YTD calculations
• YTD BBA • YTD Unencumbered BBA • YTD Permanent Budget
This is equal to the Permanent Original Budget plus Permanent Budget Adjustments.
• YTD Operating Budget
This is equal to the Permanent Original Budget plus Permanent Budget Adjustments plus Temporary Budget Adjustments.
**Remember Labor Encumbrances are calculated on the fiscal year. This includes grant labor encumbrances.
**Remember to specify a Year and a Period(s) when using these objects
**Note: These amounts are only calculated for expense accounts
UI Decision Support Data Ed: Grants ITD DUG2008
Information about Labor Encumbrances
•Banner encumbers labor on the State Fiscal year – July 1st thru June 30th
•Exception is a University modification to Banner for „9 over 12‟ salaried employees that sets encumbrance end dates to June 15th
•Labor encumbrances represent academic salaried & civil service employees
•Labor encumbrances for salaried employees will end on June 15th or 30th regardless of a Fund end date
Example: For a fund related to a grant, the labor obligation will generally begin on July 1st and end on June 30th - regardless of the grant dates.
•Generally speaking, HR Labor encumbrances are performed by Banner as follows:
Start date is the latest of: Beginning of the State Fiscal Year (July 1) Job Start date End Date is the earliest of: Ending of the State Fiscal Year (June 15th or 30th) Job End date
UI Decision Support Data Ed: Grants ITD DUG2008
Business Objects: Desktop Intelligence Sample Report
UI Decision Support Data Ed: Grants ITD DUG2008
EDW - Finance Grants ITD Universe Example Query:
Business Question: I would like a listing of Grants transactions for the current month in my unit by Grant. This list should include: PI name, sponsor, and start & end dates. Helpful Hints:
A Grant code can have multiple fund codes associated with it Utilize the Active Grants predefined condition to obtain grants that have not yet ended. Responsible Unit Organization code denotes the unit associated with the Grant – NOT the “O” in CFOAPAL
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe Example:
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe Example:
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe:
Make sure all three buttons are pressed. The first button allows the Classes and Objects window to be seen (usually depressed by default). The second button allows you to see the definitions for the objects that are selected. The third button will permit selected objects to wrap in the “Result Objects” window.
UI Decision Support Data Ed: Grants ITD DUG2008
Tips for Understanding Objects in the Universe
Toggle this button
When you single click to highlight an object, there is often a definition of the object as well as usage tips. (see definition box below). To reveal this definition box, toggle it open by pressing the button with the little blue „i‟ in the upper left hand corner of your query panel. Reveal object definitions & usage tips
UI Decision Support Data Ed: Grants ITD DUG2008
Objects in the Universe
The Grant, Grant Persons, and Grant attributes folders contain the most frequently used information about grants. This includes data like sponsor name, principal investigator, grant code, grant title, responsible organizations, start and end dates, grant fund type, grant research area, etc.
The Grant Ledger detail folder contains operating ledger transactions for budget, revenue, expense, encumbrance, etc. amounts with state fiscal month & year as well as calendar month & year.
The ITD Ledger summary folder contains summary amounts for inception to date, year to date and period/month. Please be sure to apply conditions on year and month when utilizing these objects.
UI Decision Support Data Ed: Grants ITD DUG2008
Tips for Combining Objects in the Universe
You can use objects in the CFOAPAL folder with all other objects in the universe Document number should be used with Grant Ledger Detail objects only Of the last two object classes, do NOT combine Grant Ledger Detail and ITD Ledger Summary objects in the same query. When using „period‟ objects in the Grant Ledger Summary folder, you will generally want to specify a year and one or more periods. When using any of the ITD or YTD objects (located in ITD Ledger Summary) you MUST specify a Year AND specify or display a period/month to ensure correct results.
UI Decision Support Data Ed: Grants ITD DUG2008
Grants ITD Universe – predefined conditions & prompts
For the Grants ITD Universe, we created several useful predefined prompts and conditions:
Select PI Name – upon running the report, will prompt user for a principal investigator name Select PI UIN - upon running the report, will prompt user for a principal investigator UIN Select Fund PI Name - upon running the report, will prompt user for fund principal investigator name Select Fund PI UIN - upon running the report, will prompt user for fund principal investigator UIN Select Grant Code - upon running the report, will prompt user for a grant code(s) Select Responsible Campus code - upon running the report, will prompt user for a single digit campus Select Responsible College code - upon running the report, will prompt user for a two alpha character college code(s) Select Responsible Department code - upon running the report, will prompt user for a 3 digit department number(s) Active Grants –When you use this condition, only grants with an End date greater than or equal to today’s date will be returned. Direct Expense – This condition limits expense transactions to those with a type of 1A – Indirect Cost pool.
UI Decision Support Data Ed: Grants ITD DUG2008
Consider your Question:
‘Grants’ is most likely defined with Grant code and title.. Choose a Responsible Chart and College or Chart and Department.
I would like a listing of Grants transactions for the current month in my unit by Grant. This list should include: PI name, sponsor, and start & end dates.
Investigators can be Primary/Principal or Co. Grant sponsor as well as start and end dates are located in the Grants folder
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe Example
To begin, double click or click and drag the following objects from the Grants folder into the results pane: Grant Code, Grant Long title, Grant Start date, Grant End date, Principal Investigator Name, Sponsor name.
From the Grant ledger detail folder, double click or drag the following objects into the results pane: OL Detail Descriptive Text, OL Detail Expense Amt, Original Document number, OL Detail Transaction date.
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe Example
Next, click on the little yellow funnel at the bottom left of your screen. This will switch you to the predefined conditions and prompts. From the Prompts folder, double click or drag the following objects into the conditions pane: Responsible Campus Code and Responsible Department Code.
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe Example
Finally, click on the little group of shapes at the bottom left of your screen. This will switch you back to the classes and objects screen.
From the Grant ledger detail folder, double click or drag the following objects into the conditions pane: OL Detail State Posted Period and OL Detail State Fiscal Year .
Please Note: You will need to set each of these objects ‘equal to’ and then ‘type a new constant’. This will allow you to enter your chosen two digit month and four digit fiscal year.
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe Example
Click Run!
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe Example
Enter your single digit chart of accounts code and your three digit department code and hit OK.
UI Decision Support Data Ed: Grants ITD DUG2008
Finance Grants ITD Universe Results
You will then obtain your basic results – a list of all grant transaction for your department.
Format as desired.
UI Decision Support Data Ed: Grants ITD DUG2008
Business Objects Universe –
Common Report formatting
A to Z sort Sum or Calculator Section Break
Use the slice & dice panel apply sorts, breaks, and sums. Once you have selected your format, you must click the Apply button!
USAGE NOTE: Be sure you apply section breaks BEFORE you apply the sum/calculator. If you don’t, you may get strange format results.
UI Decision Support Data Ed: Grants ITD DUG2008
Saving your Document
UI Decision Support Data Ed: Grants ITD DUG2008
3 Options for Saving your document
Option 1: File > Save
• This is the Business Objects default. This saves the document to your hard drive. The location is under: My Documents > My Business Objects Documents > User Docs
Option 2: File > Save As
• This allows you to choose the location to save the document. This location could be on your hard drive, on a CD or USB drive or on a network share, for example. This is also the option used to save to a different file format – like an Excel .xls file.
Option 3: File > Export to Repository
• This sends the document to be stored on the Decision Support server. You can then retrieve the document from any location or computer. • This option is very useful when you have created the document at a workstation that is not your own and you want to be able to retrieve it back at your desk or you create the document at your desk and want to be able to retrieve it in a lab setting.
UI Decision Support Data Ed: Grants ITD DUG2008
Option 3: Export to Repository
Select „File‟ Select „Export to Repository‟ You will then be able to retrieve this document from any desk.
UI Decision Support Data Ed: Grants ITD DUG2008
Retrieving your Document
UI Decision Support Data Ed: Grants ITD DUG2008
To retrieve a Document
Select „File‟ Select „Import from Repository‟
You will then be able to retrieve a saved document.
UI Decision Support Data Ed: Grants ITD DUG2008
Solution Library and Query Clearing House
UI Decision Support Data Ed: Grants ITD DUG2008
When getting information from the Data Warehouse, you have these options:
1. Write your own report from scratch 2. Look for a pre-authored report to use or modify in: •Solution Library •Query Clearinghouse
UI Decision Support Data Ed: Grants ITD DUG2008
What is Solution Library? DS Solution Library
• Users asked for pre-assembled report samples to use as a starting point for learning and customization • Frequently asked user reporting questions • DS Authored step by step instructions and sample Business Objects reports designed to meet specific reporting needs
• Solution Library grows based on user feedback
Solution Library link:
http://www.ds.uillinois.edu/web/Home/SolLib.aspx
UI Decision Support Data Ed: Grants ITD DUG2008
What is Query Clearinghouse (QCH)? DS Query Clearinghouse
• A place for university users to share report templates
• Any data warehouse user can post Business Objects files for others to use as a starting point
• QCH contains everything in the Solution Library and more!
• Share and work together!
Query Clearinghouse link:
https://www.ds.uillinois.edu/Reports/Authentication/Login.aspx? ReturnUrl=%2freports%2fQCH%2fQCHBrowser.aspx
UI Decision Support Data Ed: Grants ITD DUG2008
Accessing Solution Library
UI Decision Support Data Ed: Grants ITD DUG2008
Accessing Solution Library
UI Decision Support Data Ed: Grants ITD DUG2008
Available Solutions on the DS website
UI Decision Support Data Ed: Grants ITD DUG2008
Available Solutions on the DS website
UI Decision Support Data Ed: Grants ITD DUG2008
No DS Solution? Try Query Clearinghouse!
UI Decision Support Data Ed: Grants ITD DUG2008
Press on the “Login Using Bluestem”
UI Decision Support Data Ed: Grants ITD DUG2008
Query Clearinghouse
UI Decision Support Data Ed: Grants ITD DUG2008
Query Clearinghouse - Finance
UI Decision Support Data Ed: Grants ITD DUG2008
QCH – Finance > Grants
Try „All Grants for a PI‟. It‟s a great report!
UI Decision Support Data Ed: Grants ITD DUG2008
DS Resources & Help with Report writing
What if there is no pre-authored report that answers your question?
1. Write your own report from scratch 2. Look for a pre-authored report to use or modify in: • Solution Library • Query Clearinghouse
UI Decision Support Data Ed: Grants ITD DUG2008
Who ya going to call?
UI Decision Support Data Ed: Grants ITD DUG2008
Who ya going to call?
Check for dates and times of scheduled monthly practice labs (help sessions)
UI Decision Support Data Ed: Grants ITD DUG2008
Who ya going to call?
Check for dates and times of scheduled monthly practice labs (help sessions)
UI Decision Support Data Ed: Grants ITD DUG2008
Email a FAC (Functional Area Coordinator):
DSFAC@helpdesk.uillinois.edu
Have you emailed your FAC today?
UI Decision Support Data Ed: Grants ITD DUG2008 61
But I really want to talk to you:
Beth Ladd – Finance FAC hessgill@uillinois.edu
UI Decision Support Data Ed: Grants ITD DUG2008