Managing the Project Resource Query for Grants

Purpose: Use this document as a reference for how to filter, explore and narrow results of a specific Project# using the Project Resource Query.

Audience: Finance/Grant Fiscal Staff.

You must have at least one of these local college managed security roles:

  • ZD_DS_QUERY_VIEWER

If you need assistance with the above security roles, please contact your local college supervisor or IT Admin to request role access.

Note:  Project ID BLANK will pull all Project IDs available and note the query results for the following queries;

  • QFS_PC_PROJ_RESOURCE
  • QFS_PC_PROJ_RESOURCE_PYMNT
Project Resource Query image
Project Resource Query with Payment image
Managing the Project Resource Query

Navigation: Reporting Tools > Query > Query Viewer

  1. The Query Viewer page displays.
  2. Select Search By Query Name criteria.
    1. Enter Query Name begins with QFS_PC_PROJ.
    2. Select Search.
    3. The Search Results will display.
Query Viewer

Select the Favorite hyperlink to Add to Favorites. The Query Name(s) will be saved for quick reference under your My Favorite Queries.

Select the Favorite link
  1. Select the HTML or Excel hyperlink.

By selecting HTML hyperlink, the option to Run to HTML allows you to view results as a preview below the search criteria prior to downloading into Excel. By selecting the Excel hyperlink, the option to Run to Excel will export your results directly into an Excel spreadsheet.

Select HTML for preview
  1. The Query from Project Resource page displays.
  2. Enter the Business Unit.
  3. Optional to enter or look up the Project ID. If left Blank, the system will pull all Project IDs transactions available within the specific time frame.
  4. Enter the Beginning Accounting Date and Ending Accounting Date.
Enter date range
  1. Enter or select the lookup tool to search for the Project#.
  2. The Query criteria window opens.
Optional to enter specific Project ID or blank for all values
  1. The Search Results will display.
  2. Since the selection Run to HTML was selected, a preview of the results is displayed.
  3. After reviewing the results, select Download results in: Excel SpreadSheet or CSV Text File format.
Selet the Excel Spreadsheet link
  1. The Downloaded results will appear at the bottom left of your screen.
  2. Select the QFS_PC_PROJ_REC....xlsx to open the Excel spreadsheet results in a new window.
Open excel results link
  1. The Project Resource Query results will be displayed in Excel.
  2. Select Enable Editing.

The following example looks at the Billable lines for the Early Head Start Grant (Project 0000002498) for a specific time period (January 2020). When the Grant Billing process is initiated, this data will be cost collected and pushed from Contracts to Billing. The Project Resource Query can be useful as backup documentation and also provides line detail of analysis types. You are able to view both Billable (BIL) and already Billed (BLD) lines.

Excel spreadsheet of results displays. Select Enable Editing.
  1. The entire results of the Project Resource Query displays.

The Project Resource Query displays everything for that specific Project# in the system. This query can  useful when troubleshooting an integration issues from other Finance Modules, you can search by Transaction ID description or Transaction Date to view the details.

  1. Use the Hide or Delete Excel options to remove/hide unnecessary columns to provide the desired query results and fit onto your screen.
    1. Select the columns F, G, H, I and J.
    2. Left click mouse to show options.
    3. Select Hide or Delete columns.
Page displaying the excel functions of hide or delete unnecessary columns.
  1. Select the Data tab on the excel spreadsheet.
  2. Select the Acctg Date column drop-down arrow and filter the Accounting Date results as needed.
  3.  Select OK.

For the following example, the Accounting Date selected is for January 2020.

Use the Data tab and filter button to filter results. Use the Accounting Date column to isolate January 2020 expenses are displayed.
  1. The transaction results for that period will be displayed.
  2. Select the An Type or Analysis Type column.
  3. Select only the BIL or Billable Analysis type to view the Billable lines.
  4. Select OK.

Billable lines are created from actual expenses that have been incurred. Only lines with the BIL analysis type will appear on the invoice. The Cost Collection process will create billable rows from actual expenses. If you are missing Billable lines remember that the Cost Collection process runs nightly.

If you have DEF or Deferred Billable lines from a prior period that fall within the selected desired date criteria, the DEF lines will show up as Billable lines when cost collected. DEF lines are billable expenses that are not ready to be invoiced yet. During the Grant Billing process when reviewing the Billing Worksheet, you can manually change a BIL line to DEF and select date to be captured during the next billing cycle.

Page displaying how to filter results by selecting the BIL or billable lines.
  1. The BIL or Billable line results will display.
  2. Total the Amount column.
  3. This total will be reflected on the Billing Worksheet and Finalized Invoice during the Grant Billing process.

Using the total function, you are able to total the Amount column for the specific criteria of January 2020 and Billable Line items. This example totals $11,837.26 of Billable expenses for project# 2498. When the Grants Billing process is initiated under Contract# 507 or Project# 2498, the total Billing Worksheet and Finalized Invoice will reflect this total. If not, return to the Project Resource Query for analysis.

Adding the total of January Billable expenses produces a total of 11,837.26.
Grant Analysis Types

Common Grant Analysis Types:

  • BIL - Billable (Item marked to be invoiced)
  • BLD - Billed (Line items already invoiced in system)
  • BUD - Budget (Line items that are a part of the Award Detail Budget)
  • BAJ - Billing Adjustment (A billing adjustment for Grant Award Invoice)
  • DNC - Do Not Collect (Item that is usually for Cash Balancing or liabilities)
  • DEF - Billing Deferred Amount (Deferred Item that remains Billable)
  • GLE - General Ledger Expense (Item marked in General Ledger)
  • GLR - General Ledger Revenue (Item marked in General Ledger)
  • OTL - Over the Limit (Line items that are over Budget in the Grants Module. These line items will not be cost collected into Billing until the limit is adjusted and lines are re-priced)
  • WTO - Write Off (Line items that is marked to be written off)
  • ACT - Actual Cost

Please see QRG section on Grant Award & Cost Sharing Common Analysis Types for additional information.

Using Pivot Tables

The following provides an advanced analysis option using PivotTables within Excel. You can use the Pivot Table Fields to select, drag and drop fields on the right side of the data to customize to your desired results on the left.

Select the Insert tab on excel spreadsheet to select Pivot Table
Results pulled into a Pivot Table are displayed. Same total of January Billable expenses are displayed; 11,837.26.

Outside of the Pivot Table data area, you can add information such as Grant title and billing period. Within the Pivot Table data area, you can expand the Row Label data, the example below views the totals of salary GL Account types.

Pivot tables can be useful when reporting to an external sponsors, analyzing granulated data, and assisting in year end reconciliations.

Outside of the Pivot Table data, option to add title. Within the Pivot Table data, optional to further drill into data like types of Salaries.
  1. For more information on Queries, please see the QRG Running Reports Job Queries.
  2. Process complete.

Video Tutorial

The video below demonstrates the process actions described in the steps listed above. This video includes audio and closed captioning.  Select the play button to start the video.

Video Tutorial Via Panopto

View the external link to Managing the Project Resource Query for Grants work session. This link will open in a new tab/window

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.