Managing the Project Resource Query

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 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.

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.

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

  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.

  1. The Query from Project Resource page displays.
  2. Enter the Business Unit.
  3. Enter or look up the Project ID (Optional to leave it blank to view all active Project numbers).
  4. Enter the Beginning Acctg Date.
  5. Enter the Ending Acctg Date.
  6. Select View Results.

For this example, we will be using this query to isolate 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.

  1. Select the lookup tool to search for the Project#.
  2. The Query criteria window opens.
    1. Enter the PC Business Unit.
    2. Use the drop down menu and set the Project field to contains.
    3. Enter the Project#.
    4. Select the Look Up button.
    5. Select the displayed results.
Looking up Project Number window displays
  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.
Project 2498 results are displays in HTML providing a preivew of the results. Select download results in Excel Spreadsheet
  1. The Downloaded results will appear at the bottom left of your screen.
  2. Select the CTC_PC_PROJ_REC....xlsx to open the Excel spreadsheet results in a new window.
Select the download excel spreadsheet link at the bottom of the page
  1. The Project Resource Query results will be displayed in Excel.
  2. Select Enable Editing.
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.

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

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. This link will open in a new tab/window