Last spring, when DSS converted from using the “old star” to the new EDW tables, we created a new universe called “OF GL Data”. This new universe provides greater functionality than the old star, but has also raised some common questions. We will address those here and provide some tips for using this universe successfully.
The OF GL Data Universe contains data from the Oracle Financials General Ledger. This means it will include assets, liabilities, beginning balances, revenue and expenses.
When using this universe, be sure to add a condition such as “GL Code matches pattern 4%” if you are only looking for revenue data, or “GL Code matches pattern 5%” if you want expenses only.
There are two amount objects (fields) for use in the universe. The “Actual Amt” object is to be used to report on a month to date (monthly) number. If you include the Month Name object in your report you will get amounts by month. This should only be used when looking at revenue, transfers and expenses.
By contrast, to retrieve beginning and available balances you should use the “Year to Date Actual Amt”. The “Year to Date Actual Amt” allows you to get a number that is a point in time such as the end of August 2008. Balances are best looked at as a year to date amount, not monthly.
When using the “Year to Date Actual Amt” object you will always be prompted to enter an ending period (format: MMM-YYYY) after you hit the run button. DO NOT use the Actual Amt and Year to Date Actual Amt in the same report; this will produce an error.
To produce a summary year to date fund statement with beginning and available balances by award try this:
In the Results Objects section include these objects:
- Fiscal Year
- Award #
- Fund Statement Lev 3 Name (located in folder Fund Statement Hierarchy Rollup)
- Fund Statement Lev 5 Name
- Year to Date Actual Amt
In the Conditions Objects section include these conditions: Award# = ABCDE
Fiscal Year = YYYY
You will be prompted to enter a 1 for the Stanford set of books or a 2 for Endowment set of books. You will also be prompted to enter the ending period that you want the report through to such as AUG-2008 or DEC-2008.