Grants Streamlined Detailed Universe Guide

Grants Streamlined Detailed Universe Guide May 2008 What is Grants data? UI Decision Support Data Ed: Grants DUG-2008 2 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 DUG-2008 3 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 • Grant data quality issues are documented in the Quick Reference Guide. Please review them carefully. UI Decision Support Data Ed: Grants DUG-2008 4 Grant Universe Overview UI Decision Support Data Ed: Grants DUG-2008 5 Finance Grants Streamlined Universe A universe to list Grants and answer questions like: •How many grant do I have associated with my department? •What was the total grant expenditure by PI in my college over the last state fiscal year? •How many grants are currently active for a Principal Investigator? •On how many Grants is a particular Investigator currently listed? •How many grant dollars did we spend last year from a sponsor? Found in EDW Universe: Finance – Grants Streamlined UI Decision Support Data Ed: Grants DUG-2008 6 Finance Grants Streamlined Description This Universe contains: – – – – – – C-FOAPAL Grant start & end dates Grant budget, revenue & expenses Grant sponsors/agencies Principal Investigator information C-FOAPAL reflects current information only UI Decision Support Data Ed: Grants DUG-2008 7 Finance Grants Streamlined – Intended Uses Intended use(s) of this Universe: • Listings of Grants and related information • Analysis of Grant Expenses • Analysis of Grants by research area or sponsor UI Decision Support Data Ed: Grants DUG-2008 8 Finance Grants Streamlined – Universe Limitations This Universe is NOT designed to: Combine Operating Ledger transaction detail & Grant Summary Ledger in the same query Original Document Number should only be used with the Grant Ledger Detail class UI Decision Support Data Ed: Grants DUG-2008 9 EDW - Finance Grants Streamlined Universe UI Decision Support Data Ed: Grants DUG-2008 10 Grants Data – 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 Principal Investigator Every Banner grant has an assigned principal investigator. In addition, grant funds also have an assigned principal investigator and may also have co-investigators. UI Decision Support Data Ed: Grants DUG-2008 11 Grants Data - universe objects Grant Year Each grant has its own year based on its start date. NOTE: The first fiscal period begins in the month and year of the start date. Relative Grant Year The year, starting at 1, of the grant relative to its start date. Grant Period A two-digit code corresponding to the month relative to the grant year. –Period 00 of the first grant year summarizes pre-award activity –Periods 01-12 are relative to the grant year Calendar Year The calendar year of a particular grant period. If this record is for pre-award activity (i.e., where Grant Period is '00' for the first year of the grant) then calendar year is set to 'PRE'. Calendar Month The calendar month (2 digit number; ’01’=Jan, ’02’=Feb…’12’=Dec) corresponding to a particular grant period. 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'. UI Decision Support Data Ed: Grants DUG-2008 12 Grants Data Concepts Say we have two grants: Grant A starts in August 2003 Grant Period 01=August 2003 Grant Period 02=September 2003 Grant Period 03=October 2003 Grant Period 04=November 2003 Grant B starts in November 2003 Grant Period 01=November 2003 Grant Period 02=December 2003 Grant Period 03=January 2004 Selecting Grant Period 03 for these two grants would select fiscal activity from different periods of time. Selecting Calendar Month= 11 (November) and Calendar Year= 2003 compares data from the same time period. Tips & Tricks: When selecting Grant Ledger data for several grants, select by calendar year and month, not grant period. UI Decision Support Data Ed: Grants DUG-2008 13 Grant Data – universe object classes Grant Ledger Detail There is no transaction level data in the Grant ledger. 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. Grant Ledger Summary The Banner grant/research ledger contains summaries of operating ledger transactions and groups them by Grant period and grant year. USAGE NOTE: It is important to note that lack of activity on a Grant CFOAPAL when crossing grant years will make some CFOAPALs appear to drop out of the summary data. Use the summary data with caution. UI Decision Support Data Ed: Grants DUG-2008 14 Grants Ledger - universe objects Grant Summary Ledger Dollar Buckets On the grant ledger you will find similar “dollar buckets” as on the Operating Ledger Summary –Permanent Original Budget Period Total –Permanent Budget Adjustments Period Total –Temporary Budget Adjustments Period Total –Revenue Period Total –Expense Period Total –Reservation Period Total –Encumbrance Period Total NOTE: Period totals are derived by subtracting the Banner ITD balance for the grant period from the ITD balance for the previous period UI Decision Support Data Ed: Grants DUG-2008 15 Grants Ledger – additional data Grant Summary Ledger Additional Dollar Buckets On the grant ledger you will also find additional “dollar buckets” for ITD (Inception To Date). These columns are NOT currently available in the universe. –Permanent Original Budget ITD Balance –Permanent Budget Adjustments ITD Balance –Temporary Budget Adjustments ITD Balance –Revenue ITD Balance –Expense ITD Balance –Reservation ITD Balance –Encumbrance ITD Balance UI Decision Support Data Ed: Grants DUG-2008 16 Grant Ledger YTD “Buckets” •Grant BBA •Grant Unencumbered BBA •Grant Permanent Budget This is equal to the Permanent Original Budget plus Permanent Budget Adjustments. •Grant Operating Budget This is equal to the Permanent Original Budget plus Permanent Budget Adjustments plus Temporary Budget Adjustments. **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 DUG-2008 17 Grant Ledger YTD “Buckets” •Grant BBA •Grant Unencumbered BBA 2 Usage Caveats: •Remember Labor Encumbrances are calculated on the fiscal year. This includes grant labor encumbrances. •Lack of activity on a Grant CFOAPAL when crossing grant years will make some CFOAPALs appear to drop out of the summary data **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 DUG-2008 18 Calculating BBA‟s in the EDW 2 Budget Balance Available (BBA) calculations BBA = PERMANENT ORIGINAL BUDGET + PERMANENT BUDGET ADJUSTMENTS + TEMPORARY BUDGET ADJUSTMENTS -YEAR TO DATE EXPENSES - ENCUMBRANCES - RESERVATIONS UNENCUMBERED_BBA = PERMANENT ORIGINAL BUDGET + PERMANENT BUDGET ADJUSTMENTS + TEMPORARY BUDGET ADJUSTMENTS -YEAR TO DATE EXPENSES ** Available in the Ledger Summary – only calculated for expense accounts **Remember to specify a Year and a Period when using BBA’s UI Decision Support Data Ed: Grants DUG-2008 19 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 DUG-2008 20 Business Objects: Desktop Intelligence Sample Report UI Decision Support Data Ed: Grants DUG-2008 21 EDW - Finance Grants Streamlined Universe Example Query: Business Question: I would like a listing of Grants in my unit by PI name with funds 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 DUG-2008 22 Finance Grants Streamlined Universe Example: UI Decision Support Data Ed: Grants DUG-2008 23 Finance Grants Streamlined Universe Example: UI Decision Support Data Ed: Grants DUG-2008 24 Finance Grants Streamlined 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 DUG-2008 25 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 DUG-2008 26 Objects in the Universe The Grant Agency, Grant Persons, and Grants folders contain all the basic information to list grants. This includes data like sponsor name, principal investigator, grant number, grant title, responsible organizations, start and end dates, grant fund type (federal, state, private, etc), 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 Grant Ledger summary folder contains summary amounts from the Banner Grants ledger amounts with state fiscal month & year, calendar month & year, and grant month and year. *Use this data with CAUTION* UI Decision Support Data Ed: Grants DUG-2008 27 Tips for Combining Objects in the Universe You can use objects in the CFOAPAL folder with most 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 Grant 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 the following objects (located in Grant Ledger Summary) BBA, Unencumbered BBA, Permanent Budget, Operating Budget, you MUST specify a Period AND a Year. UI Decision Support Data Ed: Grants DUG-2008 28 Grants Universe – predefined condition For the Grants Universe, we created a predefined condition: Active Grants –When you use this condition, only grants with an End date greater than or equal to today’s date will be returned. UI Decision Support Data Ed: Grants DUG-2008 29 Tips for Conditioning in the Universe If you want to place a condition on a single Investigator (PI or CoI) name or UIN, use the Principal Investigator UIN object in your conditions. If you want to place a condition on a particular grant(s), use the Grant Code object in your conditions. If you want to see a list of grants in your college or department, Responsible College or Responsible Department. There is no need to use them both. Choose one and combine it with the Responsible Chart. If you want to categorize your grants by the type of funding (Federal, Private, State or Other) utilize the Grant Fund Type Code object. UI Decision Support Data Ed: Grants DUG-2008 30 Consider your Question: ‘Grants’ is most likely defined with Grant code and title.. Choose a Responsible Chart and College or Department. I would like a listing of Grants in my unit by PI, with funds and Start and End dates Investigators can be Primary/Principal or Co. Grant start and end dates are located under Grants>Grant Dates UI Decision Support Data Ed: Grants DUG-2008 31 Finance Grants Streamlined Universe Example Results Objects (what you want to see) Grant Code Grant Long Title Financial Fund Code Financial Fund Title Grant Start Date Grant End Date Principal Investigator Name Condition Objects (how you limit what you see) Responsible Campus Code Responsible Unit Org Code Financial Fund Code Data Entry Ind Active Grants Equal to: (1 digit Chart code) Equal to: (6 digit Org code) Equal to: ‘Y’ Predefined Condition (yellow funnel bottom left) UI Decision Support Data Ed: Grants DUG-2008 32 Finance Grants Streamlined Universe Example UI Decision Support Data Ed: Grants DUG-2008 33 Finance Grants Streamlined Universe Results UI Decision Support Data Ed: Grants DUG-2008 34 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 DUG-2008 35 Saving your Document UI Decision Support Data Ed: Grants DUG-2008 36 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 DUG-2008 37 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 DUG-2008 38 Retrieving your Document UI Decision Support Data Ed: Grants DUG-2008 39 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 DUG-2008 40 Solution Library and Query Clearing House UI Decision Support Data Ed: Grants DUG-2008 41 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 DUG-2008 42 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 DUG-2008 43 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 DUG-2008 44 Accessing Solution Library UI Decision Support Data Ed: Grants DUG-2008 45 Accessing Solution Library UI Decision Support Data Ed: Grants DUG-2008 46 Available Solutions on the DS website UI Decision Support Data Ed: Grants DUG-2008 47 Available Solutions on the DS website UI Decision Support Data Ed: Grants DUG-2008 48 No DS Solution? Try Query Clearinghouse! UI Decision Support Data Ed: Grants DUG-2008 49 Press on the “Login Using Bluestem” UI Decision Support Data Ed: Grants DUG-2008 50 Query Clearinghouse UI Decision Support Data Ed: Grants DUG-2008 51 Query Clearinghouse - Finance UI Decision Support Data Ed: Grants DUG-2008 52 QCH – Finance > Grants Try „All Grants for a PI‟. It‟s a great report! UI Decision Support Data Ed: Grants DUG-2008 53 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 DUG-2008 55 Who ya going to call? UI Decision Support Data Ed: Grants DUG-2008 56 Who ya going to call? Check for dates and times of scheduled monthly practice labs (help sessions) UI Decision Support Data Ed: Grants DUG-2008 57 Who ya going to call? Check for dates and times of scheduled monthly practice labs (help sessions) UI Decision Support Data Ed: Grants DUG-2008 58 Email a FAC (Functional Area Coordinator): DSFAC@helpdesk.uillinois.edu Have you emailed your FAC today? UI Decision Support Data Ed: Grants DUG-2008 59 But I really want to talk to you: Beth Ladd – Finance FAC hessgill@uillinois.edu UI Decision Support Data Ed: Grants DUG-2008 60 Appendix: Handy Accounting Concepts UI Decision Support Data Ed: Grants DUG-2008 61 General Ledger Concepts Assets: Things that we have or own that add value to the University. Some examples are: •Cash •Accounts Receivable ($ people owe us) •Land & Buildings Accounting concepts in layman‟s terms Liabilities: Things that the University owes. Some examples are: •Accounts Payable ($ we owe people) •Loans Fund Balance: •Sum of all Revenue, Expense, Transfers, Fund Additions and FORMULA: Assets = Liabilities + Fund balance Deductions UI Decision Support Data Ed: Grants DUG-2008 62 General Ledger Concepts •In Banner, the General Ledger works with Debits and Credits. •Each type of account has a “Normal Balance indicator” to tell you whether you are adding to or subtracting from its value. Asset Debit (more) Credit (less) Accounting concepts in layman‟s terms Fund Balance Liability Debit (less) Credit (more) Debit Credit (less) (more) FORMULA: Assets = Liabilities + Fund balance UI Decision Support Data Ed: Grants DUG-2008 63 Operating Ledger Concepts Accounting Income or Revenue: Money that the University concepts in receives or will receive. Some examples are: •Tuition & Fees layman‟s terms •Sale of products or services •Gifts Expenses: Things that the University buys or pays for. Some examples are: •Office Supplies •Payroll FORMULA: Income – Expenses = $0 (Profit or Loss) UI Decision Support Data Ed: Grants DUG-2008 64 Operating Ledger Concepts •In Banner, the Operating Ledger works with + and -. •Each type of account has a “Normal Balance indicator” to tell you whether you are adding to or subtracting from its value. Income Debit (less) + Credit (more) + Debit (more) Accounting concepts in layman‟s terms Expense Credit (less) In this case, a – means „less of‟. Do not confuse less of with negative. They are not the same in the world of accounting. FORMULA: Income – Expenses = $0 (Profit or Loss) UI Decision Support Data Ed: Grants DUG-2008 65