Warehouse Reports

New and/or Updated Databases

The following databases have been updated and revised for automatic linking to the warehouse. These databases do not require use of the CreateAcctInfoTbl database, as these databases link to the Current Header and the History Header warehouse tables. When you select a database below, choose "Open" and the database will be automatically saved into your C:\AccessReports folder on your PC. If you do not have an AccessReports folder currently in your C drive, you should create it before opening these databases.

Many of the databases on this site require the 32-bit version of Office in order to be able to function with the built-in programming.

(Note: If you have a "D" drive partitioned on your PC for file storage, please change the directory location to match your current configuration.)

  1. Funds Comparison - Multi Year Database - (April 2012) This database provides a four-year comparison of funds by type. Comparisons may be done for any report month (1-12). Data can also be summarized by account or by mnemonic. Report was updated in April 2012 by Cathy Helsel to revise queries to use account Fund Type.
  2. SAP Concur DW Reports Database - (June 2009) This database provides summary and detailed reporting for all SAP Ccncur transactions from the SAP CcncurS Datawarehouse. Includes expense report menu with employee-based and account-based reports. Includes purchasing card report menu with employee-based and account-based reports with the option to view by transaction status. Provides delegate and delegator reports
  3. SAP Ccncur - P-Card Unassigned Excel Application - (June 2009) Excel file linked to the Warehouse that provides support for unsubmitted P-Card charges including those that are assigned or unassigned
  4. Campus Tuition Income Database - (March 2009) This report provides tuition income by Semester and Location (i.e., BD, NK, etc.) Once you have downloaded the report into your AccessReports folder, make sure you re-link the tables (via "Linked Table Manager")
    • Instructions to import data and run the report:
    • Double click on “frmTuitionReportsMenu” under the “forms” section;
    • Click on the "Data Warehouse Import" button, click "yes", click "yes" again, Type FY (ex: 20082009)
    • NOTE: You may or may not get these messages:
    • If you get an error message that the connection failed, click on "OK", Uncheck "trusted Connection", enter your user Id and DW password, click "OK" (you will probably get the same error message a couple of times, follow the same instructions).
    • The queries will start running, after the queries finish running you will need to click on "yes" many times.
    • You will be returned to the Menu, click on either "Fall/Spring Tuition Income Accounts" or "Summer Tuition Income Accounts"
    • Enter your Location code (2 letter code, such as: AA or YK)
    • Your Campus tuition income will be displayed, you can either print the report or export as pdf and save the file (NOTE: The database is cumulative, so if you want to compare last year at the same time, you will need to look at your saved files)
    • The first time you run the report check against IBIS to make sure they match, if they don’t let Jody Heckman know
  5. PCard Reports Database - (February 2006) Provides Purchasing Card reports: Fiscal Year for all transactions for an individual reconciler/cardholder or for all reconcilers/cardholders; Transaction Date range for all transactions for an individual reconciler/cardholder or for all reconcilers/cardholders;
    Status reports for New, Viewed, or Autopost transactions for all cardholders or for an individual cardholder; and Administrative reports for inactive cardholders with transactions, for cardholders with a suspended card, for inactive cardholders with an active purchasing card, and the SIC exceptions report.
  6. PCard Reconciler Volume Report - (March 2009) Provides Purchasing Card Reconciler reports, volume by Admin area and/or by reconciler.
  7. Deferred Student Billing - (April 2008) This report provides a list of students with defrred payment plans and the balances they still owe, by Location (i.e., UP, BD, NK, etc.).
  8. ObjectCodesSummary Database - (May 2004) This database provides an object code summary report by Administrative Area for a specified fiscal year. Various reports are provided for general accounts and/or restricted accounts. Summarized reports for equipment purchases (capital and non-capital) and travel (professional development and business travel) are provided.
  9. ObjectCodeSummaryRptMnth Database - (September 2005) This database provides summarized object code expense reports based on your selection of a begin and end month. Various reports are provided for general accounts and/or restricted accounts. Summarized reports for equipment purchases (capital and non-capital) and travel (professional development and business) are provided.
  10. ObjectCodeSummary-4-Year Comparison Database (June 2005) This database provides summarized object code expenses for four years (based upon your selection of fiscal year and report month). You may also choose to view various reports for general and/or restricted funds. Reports can be displayed in account order or by mnemonic order.
  11. One Object Code Database - (September 2003) This database provides a listing of all details for one object code. You may view reports for current or previous fiscal years. You may also choose to view x-coded amounts on your reports.
  12. Annual X-Code Review Database for University Park and Hershey locations - (August 2003) This database reports expense details for those accounts applicable to overhead accounting review. All expense details are listed, and those portions that were x-coded are listed in a separate column.
  13. GIA (Grant-in-Aid) Reports Database - (October 2005) This database was developed by Larry Bell (Eberly College of Science) and provides various reports on GIA and stipend by semester and cost center. It also allows for comparisons of IBIS GIA to Pay-and-Effort GIA.
  14. Graduate Assistantship/Tuition Database - (March 2004) This database provides a listing of stipend and tuition expenses for your administrative area. You may choose to report by department, by employee name, or by account.
  15. Pay and Effort Database - (November 2003) This database provides a listing of salary distributions for your administrative area. You can obtain distribution reports for monthly and bi-weekly employees. Reports can be obtained by all accounts, by one account, or by one employee.
  16. Cost Sharing - (March 2005) This database contains two queries to obtain a list of all accounts with cost sharing requirements (as indicated in the AURA form). Current and prior year cost share information can be obtained.
  17. Purchasing Card Spend/Limit - (June 2010) This database provides the user with Purchasing Cardholder spending information from both the SAP Concur warehouse and the IBIS Financial warehouse. Because these are two different databases, separate reports were created for Goods and Services and Travel. Employee monthly maximum spending reports are available for both categories.

Existing Databases

NOTE: With any database you download that is connected to the warehouse you should refresh the links. For existing databases, both Access 97 and Access 2000 have a "Linked Table Manager" wizard which makes this process very easy - this keeps you from having to delete each table and re-connect each one.

You can find the wizard as follows:
For Access 97: On the toolbar select TOOLS, ADD-INS, LINKED TABLE MANAGER.
For Access 2000: On the toolbar select TOOLS, DATABASE UTILITIES, LINKED TABLE MANAGER.
Select the files to re-link and click OK. You will get a message that the selected linked tables were refreshed.

  1. Download (updated on 7/7/08) I retitled Rebecca Woomer's report database and called it Generic Reports. This is a zipped file. You will also have to download the CreateAccountInfoTbl database. You need to run the CreateAcctInfo query, then link that table to the GenericReports
  2. Download the CreateAcctInfoTbldatabase to create the tblAcctInfo which is used in many of the database you will be using. This file is NOT zipped - it is ready to run - just save it in the folder where you keep your other files. This version is 97 - 2000 users will need to convert it. You will need to run the query to create the table so you can link it to other databases.
  3. Download the JTDPBrowse database. Use this database to browse your JTDP transactions. You can select to look at your details sorted by the JNumber for the entire Administrative Area, or sorted by the Mnemonic. You can also make a selection to look at an account or a mnemonic. NOTE: This database also requires you to have the CreateAcctInfoTbl database. You will need to link the tblAcctInfo. This database is NOT zipped. THIS DATABASE ALSO HAS A SWITCHBOARD! You need to hold down the SHIFT key to open the database so you can link to the tblAcctInfo.
  4. Download the BudgetAmendmentsDesc database. This database uses the Budget Number from the Description Field from OAS. If the budget amendment is from outside your area, it will have a "01" or a "99" number and not your budget amendment number. You can look at budget amendments with or without the IOB amounts. You can also look at budget amendments for the entire Administrative Area or by Account. There is also a Allocation Fund Report. NOTE: This database also requires you to link the tblAcctInfo table from the CreateAcctInfoTbl database. THIS DATABASE ALSO HAS A SWITCHBOARD! Initially, you need to hold down the SHIFT key to open the database so you can link to the tblAcctInfo.
  5. Download the BudgetAmendmentsFreeSpace database. This database uses the Budget Number from the Free Space which you need to enter. You can look at budget amendments with or without the IOB amounts. You can also look at budget amendments for the entire Administrative Area or by Account. There is also an Allocation Fund Report. NOTE: This database also requires you to link the tblAcctInfo table from the CreateAcctInfoTbl database. THIS DATABASE ALSO HAS A SWITCHBOARD! Initially, you need to hold down the SHIFT key to open the database so you can link to the tblAcctInfo.
  6. Download theAdminAreaName database. This database is used in numerous databases to allow the name of the Administrative Area to be printed on the report.