RAH-AwardDetail-View Quick Start Guide

Owned by Saparnell
Last updated: Jul 12, 2022 by Ernesto Donate
4 min read

This view contains all of the Award and associated information.

Data Loads:

Data is loaded nightly from Kuali.

Granularity:

Granularity is the scale or level of detail present in a set of data.  It identifies where levels of uniqueness exists in the data.  The data lives in the view at the lowest level, but each BI tool totals the levels differently.

Cognos developers: Cognos will aggregate the totals at the correct grain.  You can shut off the aggregation as needed.

Tableau developers:  Tableau will aggregate the total for all levels of the view, this will inflate your numbers.  Level of Detail calculations have been built into the Tableau data source based on the measure's the granularity.


LevelKey (uniqueness / group by)Attributes
1UCSD Award Sequence Number

UCSD Award Number - Full + UCSD Award Sequence Number + Award OP Reporting Date







Tableau report developers:  {FIXED [UCSD Award Number - Full], [UCSD Award Sequence Number]: MIN([Award...])}

Measure:
  • "Award Obligated Direct Cost",
  • "Award Obligated Indirect Cost",
  • "Award Obligated Total Cost",
  • "Award Anticipated Direct Cost",
  • "Award Anticipated Indirect Cost",
  • "Award Anticipated Total Cost",
  • "Award Pending Direct Amount",
  • "Award Pending Indirect Amount",
  • "Award Pending Total Amount",
  • "Award Investigator Count - COI",
  • "Award Investigator Count - PI",
  • "Award Investigator Count - Total"
  • "Award Obligated To Date Direct Cost",
  • "Award Obligated To Date Indirect Cost"
  • "Award Obligated To Date Total"

All dimensions not listed below

2Lower Levels

There are multiple groups of fields that live at a lower grain than the Award + Sequence Number.  These groups are not part of a hierarchy and should be considered separately.

If you add any field from these groups to your report you will see the Measures that live at a higher grain repeated for each value within these groups.  This is expected behavior.


Folders containing these groups of fields include:
  • Award Administrator
  • Award Cost Sharing
  • Award IDC
  • Award IDC Multi
  • Award Investigator
  • Award Investigator Percent Effort
  • Award Key Person
  • Award Key Person Percent Effort
  • Award Method of Payment
  • Award Project Financial
  • Award Special Review
2Lower Level: Cost Sharing Source Account

UCSD Award Number - Full + UCSD Award Sequence Number + Award Cost Sharing Source Account

Tableau report developers:  {FIXED [UCSD Award Number - Full], [UCSD Award Sequence Number], [Award Cost Sharing Source Account]: MIN([Award Cost Sharing...])}

Measures

  • "Award Cost Sharing Amount"
  • "Award Cost Sharing Percentage"
2Lower Level: Award Project Period

UCSD Award Number - Full + Award Fund Number + Award Project Period ID


Tableau report developers: {FIXED [UCSD Award Number - Full], [Award Fund Number], [Award Project Period ID]: MIN([Award Project...])}

Measures

  • "Award Project Direct Cost"
  • "Award Project Indirect Cost"
  • "Award Project Total Cost"
  • "Award Project Period Direct Cost"
  • "Award Project Period Indirect Cost"
  • "Award Project Period Total Cost"

Award IP Costs, Obligated Totals, and Anticipated Totals 

An award can be linked to multiple IP records, and every IP record, along with it's associated Costs is now in the Award View. 

Tableau Report Developers:  

  • MAX(Award IP Direct Cost Total): For calculating the maximum Proposed Direct Cost for all IP records linked to an Award.

Measures

  • Award IP Direct Cost Total
  • Award IP Indirect Cost Total
  • Award IP Total Cost

Obligated Totals, and Anticipated Totals 

In Order to calculate the Award Obligated Totals one can apply the filters below in the "Key Fields Filter on" section, or incorporate the appropriate field filter values into a formula which is essential when the desired outcome is to also show Anticipated totals for records that were migrated from the old system (COEUS) to Kuali.

Tableau Report Developers: 

  • { FIXED [UCSD Award Number - Full],[UCSD Award Sequence Number],[Award Version Status],[Legacy Tier Indicator]:MIN(IF ([Award Version Status] in ('ACTIVE','ARCHIVED') 
    AND [Legacy Pending Transaction]IN ('Y','-') AND [Legacy Tier Indicator] IN ('Tier 2','-')) THEN [Award Anticipated Total Cost] END)}: This formula is used for deriving Anticipated Totals.
  • { FIXED [UCSD Award Number - Full],[UCSD Award Sequence Number],[Award Version Status],[Legacy Tier Indicator]:MIN(IF ([Award Version Status] in ('ACTIVE','ARCHIVED') 
    AND [Legacy Pending Transaction]IN ('-') AND [Legacy Tier Indicator] IN ('Tier 3','-')) THEN [Award Obligated Total Cost] END)}.This formula is used for deriving Obligated Totals
  • Award Obligated Total Cost
  • Award Anticipated Total Cost

The lowest level is one row per associated field value. 

Key Fields to View:

  • UCSD Award Number - Base
  • Award OP Reporting Date = the date of processing in Kuali

Key Fields Filter on:

  • Award Version Status
  • Legacy Tier Indicator
  • Legacy Pending Transaction