Corporate Reports in the Business Objects Repository - Financial Data

The Business Objects repository contains reports which users can use to retrieve data to which they have access. The reports available vary by data collection and business use, as listed below.

• General Use
• Grants Management

To import reports for your own use:

Using InfoView:

The InfoView web page allows you to view the list of corporate documents in the Business Objects repository and refresh and download those documents via the web. To access the corporate documents using InfoView (Business Objects users only):

1. Point your browser to
   https://bobjprod.isc-seo.upenn.edu/InfoViewApp/logon.jsp
2. Enter your Business Objects ID and password in the User Name and Password fields that appear. Click OK to continue to the InfoView Welcome page.
3. The InfoView web portal allows you to view the list of reports in the Business Objects Public Folders, refresh those documents via the web, and if desired, save them to your desktop as Excel or pdf files. The Public Folders are listed in InfoView when you navigate to "Document List." You will only be able to view or use reports in the public folders that you have access to. Documents should be renamed and saved to your own "My Favorites" folder on the portal if you need to edit them using Webi. Documents can only be edited in Webi when placed in your "My Favorites" folder.

General Use

CForm Transactions Detail presents information from the General Ledger Journal detail data available in the FINQUERY universe, and can be used to monitor C-Form payment journals (petty cash transactions and payments for items purchased by purchase order are excluded). Access to the General Ledger data in the Warehouse is required to run this report.

FINQRCM - Budget and Actuals displays a crosstab of year to date budget balances and actuals by month for RCM Categories for a given Center and Fund. It makes use of a union, which allows users to retrieve year to date budget balances in one column, along with actuals by month as the columns in the crosstab. It uses the RCM OBJECT TREE table in the Data Warehouse to mimic object groupings used in BEN Reports and BEN Financials reports.

FINQRCM - Previous comparison uses a simple crosstab, along with the Business Objects Previous function and categories from RCM_OBJECT_TREE to present a crosstab of RCM categories compared to previous periods. The three tabs of the report show the progression from raw data, to a simple crosstab (columns sorted by fiscal year and fiscal month, rows by RCM summary and category sequences), to the final crosstab including a column with variable comparing the data to the previous period. Note that the Previous function allows for comparison to the previous row (in a standard table format), or the previous column (in a crosstab format). The comparison could be between anything that occurs in consecutive rows or columns - this query just uses it to compare accounting periods year to year.

FINQUERY - Encumbrances by Org shows encumbrances by org, fund, object and PO number, for a given fiscal year and org(s).

FINQUERY - Year over year comparison is actually comprised of two separate queries (Current Period and Comparison Period), linked by the Balances COA_Account segment to allow comparison of balances between two periods. The queries are each represented separately on the appropriately named tabs, and, once linked, the combined results are presented on the Combined tab. Data in this report can be verified using BEN Financials online account inquiry, or comparing to BEN Financials 10X or 15X reports.

FINQUERY Template Universe - RCM 210 intended to allow users to mimic the BEN Financials RCM 210 report using the Data Warehouse BALANCES and GL_REPORT_TEMPLATE tables. Two queries (Financial Data and RCM Labels) are linked together (on the Sequence column), to join balances with the full skeleton of the RCM report. Therefore, the file consists of three tabs - the first (Financial Data) shows only the template lines for which the query retrieves balances. The second tab (RCM Labels) shows the skeleton of the RCM template. The final tab (RCM report) shows the entire RCM template, with balances populated where available. The Financial Data query makes use of a union, to allow users to retrieve all rows in the template - those represented by a parent object, and those represented by COA objects which do not roll directly to a K-parent. For more on the template table, refer to http://www.upenn.edu/computing/da/dw/gen led/gl_report_template.t.html. Data in this report can be verified using the BEN Financials 210 report.

Market Value: Displays current balances (available cash, book value, expense) for endowment funds as of a particular accounting period. In addition to retrieving the actual balances from the Warehouse, the report also calculates Expected Income for the fund based on an input AIF rate.

RSALMGT - Payments and Encumbrances is actually comprised of two separate queries (Payments and Encumbrances), linked by the posted account, job class and SSN. The Payments query retrieves all fiscal year to date payments for employees paid from a given ORG, while the Encumbrances query retrieves the remaining encumbrance balances for employees from the same posted ORG. The queries are each represented separately on the appropriately named tabs, and, once linked (common elements are Job Class, Posted COA Account, and SSN), the combined results are presented on the Combined tab to show the fiscal year commitment for each person (by account and job class). Data in this report can be verified using Salary Management reports, or at a higher level (by account segments) using BEN Financials reports

Grants Management

Fund Summary (fundsummary.rep) gives a summary of the entire grant fund similar to the BEN Financials 115 report (listing key attributes) and also lists totals by object code for Budget, Month, Fiscal Year, PJTD Actual Expenditures, Encumbrances and BBA. A separate tab is provided to break down expenditures further by ORG/CREF. Another tab is used to project what the unobligated balance as a percentage of the current year budget will be at the end of the current budget year. The calculation is based on the average expenditures per day. This report is useful for administrators preparing NIH noncompeting continuations since NIH will want to know if an unobligated balance greater than 25% exists. If the projection shows a balance greater than 25% of the current year's budget, the administrator needs to investigate further before answering "no" to the question about whether an unobligated balance greater than 25% exists. This is the quickest way to look at any fund in its entirety.**

Fund Detail (Fund Detail.rep) Provides accounting period summaries of all expenditures and payroll for a given fund and any period of time. Separate tabs provided for: Accounting Period Summary, Payroll Period Summary, Payroll by Object Code, Payroll by Person, Exp Detail by Obj, Detail by ORG, Detail by CREF, Detail by Acct Period, and Detail by PO/User. [NOTE: This report is comprised of two separate queries, one of which queries the Salary Management data collection. If you do not have access to Salary Management data in the Warehouse, you will receive errors when trying to view the "Payroll" tabs of the report.]*

ORG Summary (ORG Summary.rep) gives a summary of all grant funds for which the ORG is the RESP ORG. Results, listed by fund, include fund description, PI name, account end date, and balances for PBIL, Operating Budget, Sum Grant Expenditures, Surplus/(Deficit), Revenue, Receivable, and Cash Received. Separate tabs are provided to identify grants sorted by fund, PI, account end date, Receivables only, Overdrafts only, funds due to freeze in the next 90 days, analysis of expired funds, late or due FSRs, funds with illegal object code charges, and a comparison of budgeted payroll to actual payments for each fund. This report can be used to identify a variety of potential problems (i.e.accounts with large overdrafts, receivables, revenue recognition, etc.) as well as provide the user with a quick summary of all his/her organization's grant funds. Additional queries have also been developed to list all funds where another ORG has charged funds of the RESP ORG (otherorgschgs.rep) and where the ORG has charged funds not under its RESP ORG (subbudgets.rep); when running those reports, the user should specify his/her own org at both prompts. *

ORG Wayward Summary (ORG Wayward Summary.rep) is the first of two queries required to identify wayward transactions. The other query is ORG Wayward Detail. ORG Wayward Summary is a query that is used to alert the user of the ORG/Fund wayward transactions that have occurred and require attention.ORG Wayward Detail (ORG Wayward Detail.rep) provides the line item detail for the ORG/Fund combination shown in the Org Wayward Summary Query.*

Detail Reports are used to provide transaction detail for any range of accounting periods after FY2000 for a given object code (DETAIL OBJECT RPT.rep ) or for all objects (DETAIL RPT.Rep) for a particular fund. Separate queries are used for Operating (DETAIL OBJECT RPT(Operating Bdgt).rep ) and Special Budget (DETAIL OBJECT RPT(Special Bdgt).rep), Cash (DETAILOBJRPTCASH.rep ) and Subcontracts (DETAILsubcontract.rep ) entries. Also available are reports by PI (DETAIL RPT (All grants of PI).rep) or RespOrg (DETAIL RPT (All grants of RESP ORG).rep), which are ideal for running monthly detail for all funds for a PI or RespOrg (since these queries bring back data for several funds we recommend that you run them for periods less than 3 months).*

Encumbrances (Encumbrances(RESP ORG).rep) identifies all currently open encumbrances for your Resp Org. Separate tabs are provided for All Outstanding, Outstanding on Unfrozen Funds, Outstanding on Frozen Funds, and Outstanding by PI. All Encumbrances and PO's need to be closed on frozen funds because a fund can not be disabled unless there are no encumbrances remaining on the fund. Encumbrances(FUND).rep provides all current outstanding encumbrances for one particular fund.*

717 Report (717 Org Report.rep) is tailored after the BEN Financials 717 report used by Research Services to identify funds that have been reported (or have no reporting requirement), but have not yet been disabled due to varying reasons. ***

4822 Writeoffs (4822writeoffs.rep) is used to identify all grant funds (by ORG) for which there has been a credit entry to object code 4822 during the current fiscal year (both MTD and FYTD totals are provided). This report indicates funds where there have been sharing entries or a write off of the direct cost overdraft (separate Object Codes are not used to distinguish between the two). 4822 writeoffs are to departmental unrestricted funds. *

Funds Reported includes two reports: Rptsissued.rep lists all funds (by ORG) that have been reported on by Research Services, with the amount reported. FSRDI.rep lists the current differences between actual Direct and Indirect amounts and what was reported by Research Services. *

Funds Disabled (disablebyorg.rep) lists all funds (by ORG) that have been disabled by Research Services. This report can be very helpful, as there is no option in BEN Financials currently to print only enabled funds; BEN Financials will generate reports for all active grants for each ORG. *

PBIL Violate (PBIL Violate.rep) can be used to identify all funds for which PBIL does not equal PBUD. Accounts where PBUD has a value and PBIL doesn't usually indicates the existence of an advance account. *

Payroll/Personnel Reports:
Payroll.ep is used to identify all salary charges made on a specific fund (listed by person or by object code) for any period of time.
Payroll(all grants of RESP ORG) is ideal for running monthly detail for all funds for a RespOrg. Similarly, Payroll(all grants of PI) can be used for retrieving detail for all funds for a PI. Since these queries can bring back data for several funds we recommend that you run them for periods less than 3 months.
Personnel.rep is used to identify all payments made to a specific person for a period of time, and provides a cumulative percentage of all payments over that period of time that were charged to a specific fund. These reports are used extensively in reviewing the validity of effort on non-competing proposals.
ORGPersonnel.rep displays current payroll distributions, including percentages and fund descriptions, for monthly and weekly paid employees for a given home organization. When the salary is above the NIH cap (for NIH-sponsored funds only), the report also displays the percentage the distribution contributes to the current cap. Additionally, separate tabs are provided to display current allocations hitting expired grants, as well as allocations hitting suspense because the grant is frozen. *
Note: you must have access to the Salary Management collection in the Data Warehouse to retrieve results for these reports.

Payments after Account End: Paylate.rep is used to identify identify all grants that which have been used in employee payments where the check date or pay period end date is after the fund's Account End. The information is summarized by ORG, detail provided by fund, with a separate tab indicating funds that can be worked on prior to freezing. *
Note: you must have access to the Salary Management collection in the Data Warehouse to retrieve results for this report.

Proposal Reports (proposal.rep) can be used to identify all proposals processed for any given period of time, and whether they were awarded or are pending. The report can also be used to identify all advance account proposals processed. *
Note: you must have access to the Sponsored Projects collection in the Data Warehouse to retrieve results for this report.

Grant Monitor (GrantMonitor.rep) is a series of reports that alert the user of funds which may need attention, ie., overdrafts, discrepancies between PBIL/PBUD and Budget, unallowable charges, NIH noncompeting continuations which are due, potential problems with clinical trial cash, clinical trials which may have residual balances, receivables, funds where AISs may need to be updated because the sponsor sends out one notice for multiyear awards, or funds sponsored by the federal government which may have problems with cash draw-down. Click here to view more detailed documentation of each report tab.*

*Reports contributed by Marilyn Becker and Rich Snyder, of the Office of Research Support Services in the School of Medicine.
**Report contributed by Marilyn Becker and Rich Snyder, of the Office of Research Support Services in the School of Medicine; original development by Jason Molli, of the Office of Research Services.
***Report contributed by Marilyn Becker and Rich Snyder, of the Office of Research Support Services in the School of Medicine; original development by Nick Majuri, in the School of Medicine.
****Report contributed by Jenny Sherwood, Department of Medicine, Division of Rheumatology; original development by Rich Snyder, of the Office of Research Support Services in the School of Medicine.