Tag: SoF

AFAOC Shares Air Force Status of Funds Using Power BI

The AFAOC, in coordination with the DEAMS FMO, has created a BETA version of cloud reporting capability based on an Integrated Status of Funds (e.g. DEAMS and Legacy data).

  • This reporting tool will serve as a stopgap while the DEAMS System Integrator continues to work towards resolving issues with the Status of Funds reports in DEAMS Reports.
  • Please follow hyperlink to access this report and be sure to enter your “us.af.mil” email address if prompted

Instructions:

Once at the site:

  • Click on Data Download
  • Filter Required Data (OAC, OE, Main_Limit, etc)
    • Saves the filter criteria for future reports – YEAH!!!!)
  • Click the big downward arrow in the gray box
    • Click on More Options
    • Click Export Data (if you want to use Excel)

Note: Chad Kukorola and James Johnson are amongst some of the smartest data-wonks I know. I bounce and share ideas with Chad on a semi-regular basis. I am going to highlight some of the great tools he’s built as a Air National Guardsman. If you know other “dataheads”, please share in the comments. I’m trying to compile a list and create an email listserve to share ideas and coordinate our efforts.

There will be a lot of additional Power BI info coming in the near future.

Tracking Documents Through the Stages of Accounting in DEAMS

After pulling a Status of Funds – Near Real Time report, use the Selective Transaction History Report to research any transactions.  Using the Discoverer Viewer report titled “R12 01 General Ledger Transactions STH – New_Payment Voucher Num” (STH report) returns all updates to documents in the system.  By pulling the data into a pivot table (snippet of the pivot parameters provided) users can see the dates and document details as it has moved through the system. 

Below are basic research details to follow:

Parameters used for the STH Report

Recommend pulling data by OE, FY and AAI (387700) to limit file size.

Insert the budget-affecting GL Account Codes (GLAC) (found at GLAC Parameter Strings (Cell A4)) to eliminate unnecessary General Ledger entries.

Update on the recent STH duplicates issue

The R12 General Ledger Transactions STH in Discoverer Viewer no longer contains duplicate lines of summary entries. Over the past month, DEAMS advised users to remove duplicate lines identified under the Je Header Id and Je Line Num fields. The STH now includes entries with more complete data. Although you may still see lines with matching Je Header Id and Je Line Num fields, those rows of information now include differences in other columns used to track documents from origination to payment. You no longer need to remove duplicates.

Power BI can handle years of STH data

In a future YouTube video I plan on demonstrating how to build an STH Tool using Power BI. I’ve pulled in every accounting transaction at my wing over the past five years and can finally harness the time intelligence functions available with DAX. You can do impressive day-over-day, week-over-week, month-over-month, or year-over-year analysis amongst other things. Chad Kukorola deserves all the credit for this idea. I had been dreaming of how to do this for years, but Excel always bogged down on me. After reviewing some of Chad’s Power Query code, I was able to make it work in Power BI. Until I get the YouTube video postd, reach out and let me know if you’re interested in seeing what the tool is capable of.