Body
This Guide Will Cover:
Information
Pre-built reports by division are run after each month close by Financial Affairs. To access these pre-built reports instead of running the report yourself, you can access the Account Analysis folder within the UW Shotgun directory by clicking this link: \\warehouse\shotgun$\Financial_Management_Reports\ (If clicking the link does not work, copy the link into your computer's search.)
Additionally, users can run this report themselves as well for any period or date range for any segment of a chart string. The steps outlined below are instructions on how to pull the data from the General Ledger via the Account Analysis Report and pull the Budget by Account report. Using these two files, the macro excel workbook attached to the knowledge base is used to combine the files into pivot tables and format the report.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=56bc7e9a-1c95-4206-824a-b06c3e2c49e5.png&beidInt=166)
|
This guide has three parts. Steps 1-5 outline running the Account Analysis Report to show only actuals/encumbrances. If you would like to also view budgeted amounts (including remaining budget authority), continue onto the remaining steps. At the end is an addendum for advanced features and information to aid in reading the completed report.
|
Upon accessing the WyoCloud homepage, Navigate to Scheduled Processes
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=9d472f3b-43a3-41d9-9db0-4e700f7bfff1.png)
- First click on Tools to expand (you may need to scroll within the navigator), then click Scheduled Processes.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=58c4b473-d7f7-423f-a97b-b28c01ea3ee6.png)
- The Scheduled Processes page is displayed.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=fdfba520-8c1a-46b7-a1f6-2ef07f9cbd30.png)
Step Two
Access the Account Analysis Report
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=e5233bfc-4874-4ff7-9f26-9db53c22b701.png)
- In the Schedule New Process pop up window, click on the arrow to the right of Name.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=7058d928-f664-4999-9823-ea5108b2b49c.png)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=bffdb2dc-7751-4c4e-a40a-9b85cb49e53c.png)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=bee58202-3d50-468c-816a-6b77d1110447.png&beidInt=166)
|
Note: Although you will likely see a number of options within Scheduled Processes, the Account Analysis is the only process campus users are able to run.
|
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=fdfb67c1-2282-4e60-b6eb-47569930b60a.png)
- Highlight Account Analysis and click OK.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=cda0eb02-1714-45bf-ad8d-8caca8d73585.png)
- Click OK in the Schedule New Process window.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=eb8e8bc6-2b2a-4b67-93c5-dc17bfe57665.png)
Step Three
Schedule the account analysis report. In this step you will specify the parameters you’d like to run the report by
-
In the pop-up window, complete the following fields:
-
Subledger dropdown: blank (leaving this field blank pulls all transactions from the subledger)
-
From Accounting Period: Select the first period you wish to run the period from
-
To Accounting Period: Most recent closed accounting period
-
Budget Type: Actual OR leave blank. If left blank, both actuals and encumbrances will be included.
-
In the same window, click on the blue filter icon next to Account Filter Conditions Defined.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=666859e4-a6c4-40f9-8d3a-f4706791644a.png)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=42bc473d-ac59-44d8-b496-0dd053844eb6.png)
- Complete the fields per the Organization data you’d like returned. Click OK.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=8abac4b8-70d8-4fd0-9bb7-711b3393c201.png)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=ba5b75b9-a698-442b-9739-7e51329c70e4.png)
- Click Output, then the plus sign.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=6773c293-382e-4e6b-aa96-fe5202a268a0.png)
- In the confirmation window, click OK.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=5d4e4e2e-dff9-4a60-b19f-5654dc97169f.png)
Step Four
Retrieve and Download the Report
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=f9b57a6e-0487-4a0f-ad43-e8b08aeb54be.png)
- Once the Account Analysis Report appears with a Status of Succeeded, it has completed running.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=b76614f7-163d-49a8-a088-1da99e045676.png)
- Double click on Account Analysis Report to open additional details at the bottom of the screen.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=82d8266f-2e83-4303-a1bf-0fcb525a9ff2.png)
- Scroll down to the Output area on the lower half of the screen, then click on the Output Name.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=32f45cbf-9105-4681-88d2-2e18e5e917d8.png)
- If an excel pop-up window appears, you can select Open with or Save File. It is recommended to just save the file. Click Ok.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=434b9f5b-93d7-477d-8720-ecf062314890.png)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=f858c677-020d-4a1b-aff3-ffcb994f9310.png&beidInt=166)
|
Note: If Microsoft Excel doesn’t show as an option in the Open with field, you will need to change your computer’s default settings for opening .csv files. For details on doing this see this Knowledge Base article.
|
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=27a66d81-1315-41fd-89d5-abe0b0269432.png&beidInt=166)
|
Note: The symbol “&” cannot be used when naming a CSV file.
|
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=7daa4445-6201-4837-a762-55693280136b.png)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=b91db652-d36c-4e2b-a7b8-dfea78772845.png&beidInt=166)
|
If you would like to include both actual/encumbrance AND budget data, skip to Step Six. If you would like to run the Account Analysis including ONLY actual and encumbrance data, continue to Step Five.
|
In this step you will download and run the Account Analysis Pivot Macro to properly format the report for use.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=5778f2e4-05b9-4486-ba77-4e143382c554.png)
- Upon opening the Account Analysis Pivot Macro, you will first need to select Enable Content in the yellow ribbon at the top. Then click the grey Generate Pivot Account Analysis Report button (located in the Process tab).
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=a90a2da3-2516-424c-ad56-55470e047710.png)
- In the Instructions pop up window, click OK.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=199cdfd3-b832-46c6-ba85-6869465c337b.png)
- Navigate to where you saved the exported Account Analysis Report from WyoCloud Financial Management Scheduled Processes on your desktop (obtained in step 1-4). Select it then click Open. (Note: It will likely take several moments for the macro to process.)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=0e134f90-a858-4c65-b721-0d3c9ca1c03e.png)
- Once complete, the report will look similar to this:
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=c9876dfe-7b45-479a-a834-921d87c37cf1.png)
- Use Save As to rename and save the report to your desired location for future reference.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=6e38321d-bbed-4504-812a-2e64996510ec.png&beidInt=166)
|
If you would like only actuals and encumbrances (not budget information) included in your report, you may stop here. Continue to Step 6 to add budget information to the report.
|
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=8ba83da0-f556-4e35-b448-4752fb3f23b1.png&beidInt=166)
|
For tips on reading the Account Analysis Report, see the Appendix: Reading the Report.
|
Run Budget Details by Period report by navigating to the Reporting Table of Contents
- Navigate to the "Reporting Table of Contents" under My Profile > Reporting Table of Contents within WyoCloud.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=dbf1fdad-8537-4dfc-9f91-78611c9ed138.png&beidInt=166)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=b598a7fe-8637-464c-8963-6e75375fe012.png)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=010ce1aa-ead7-4d78-8285-69aa206563ba.png)
-
To add the report to the Account Analysis Pivot Macro, first export it to Excel.
-
Click on the view report icon in the upper right corner, then click Excel(*xlsx).
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=92febe65-df2a-471f-b1a9-01f71555fc7f.png)
-
Once the file is open in Excel on your computer, click File then Save As.
-
Save the file to your desktop and close the file.
- Back in the Account Analysis Workbook on the Process Tab, click the grey Generate Pivot Budget by Account Report button.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=cbbed1ab-d7a6-4aba-86dc-c963610886f4.png)
- In the Instructions pop-up window, click OK.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=74edcd76-d795-4aa3-9e26-e7d3f924dd78.png)
-
Navigate to where you saved the exported Budget by Account Report from the Transactional Reporting Table of Contents. Select the file then click Open.
-
The report may take a few moments to run, but will open the Budget Summary tab in the excel workbook once completed.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=dfa294bd-5721-4f46-9879-afebc00b9480.png)
This concludes running the reports yourself. Continue on to read a few advance features, tips, and information about reading the report.
Consolidated Report Running
The following steps outline how to run both the Account Analysis (actuals and encumbrances) and Budget Details by Period Report into the macro at the same time to view both data sets side by side.*
*Note: If you would only like to review actual/encumbrance data, use Step Five.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=1bba302e-d9b0-4b60-8fec-0935e79923fe.png)
-
Upon opening the Account Analysis Pivot Macro, you will first need to select Enable Content in the yellow ribbon at the top.
-
Then click the grey Run All button below the two brown boxes (located in the Process tab).
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=fc2d758e-873b-4c16-b461-bf0bf841ad2c.png)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=d172bbb5-93d5-46d9-a8b7-358d3d1eaeb3.png)
- Navigate to where you saved the exported Account Analysis report. Select it then click Open.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=22f282b8-b665-423a-b772-8ca219766dbb.png)
- The macro will likely take several moments to process prior to showing a second instruction window. This window will instruct you to select the exported Budget by Account report.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=bd02f14b-8798-4efd-afd4-deaebc51c15e.png)
- Navigate to where you saved the exported Budget by Account Report from WyoCloud Financial Management on your desktop (obtained in step 6). Select it then click Open.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=de6e0553-28e4-4efb-b1a7-077640071e5d.png)
- Once complete, the report will automatically open the Budget Summary tab. This tab allows for viewing budget and actual data side-by-side.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=8f261f91-ec77-4a33-a082-b50364d766c1.png)
- You may also review only actual/encumbrance information using the AASummary tab.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=b2b53f33-82b9-48ab-9390-8d1f12582023.png)
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=0e049a6a-e285-416f-ae0b-a9d22158a17e.png&beidInt=166)
|
Note: For tips Advanced Features of the Macro Pivot, see the appendix below.
|
Tips and advanced features of the Account Analysis Pivot Macro
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=c7232c74-a565-4652-b3dc-7deb6a1161d3.png&beidInt=166)
|
This section covers features available within Account Analysis Pivot Macro. In order to utilize these features, you must first run the macro per the directions outlined in the Running the Account Analysis guide. Features which require budget information to be included when running the report are noted below.
|
Expanding details of pivot tables
There are two pivot tables included within the Account Analysis Report, one located on the AASummary tab, and one on the BudgetSummary tab (if budget information is included when running the report). From either tab, simply double click on any numerical value to open a new tab with the full details.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=398fbd4f-dbbf-46dd-b6ee-b96eabd1401b.png)
- The full details of what makes up that number will open into a new tab. See Appendix: Column Definitions for details.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=16049f88-cedb-491d-813c-b7554cca8a8e.png)
Customizing Details Shown in Expanded View
By default all available data for transactions is displayed when expanding transactional details by double clicking on a numerical value within a pivot table. However, you may not find all columns helpful for your needs. For example, you may only want to show the combined charge string of the transaction, not each segment displayed in its own column. You may adjust the columns displayed by using the Data Select tab.
- The Data Select tab includes all column headers available. To indicate whether you’d like a given column included in your expanded transactional details, click on the cell under the column header (in row 5). Use the drop down arrow to toggle between 1 and 0. Selecting 1 indicates that you would like to view this column when viewing expanded details. 0 indicates you’d like this column hidden. See Appendix: Column Definitions to assist in identifying which columns to include.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=8088bbf6-f4e2-4e32-9d5f-bd4198092945.png)
- After selecting which columns you would like included, return to a tab which includes a pivot table (AASummary or BudgetSummary). Select the numerical field you’d like expanded details on, then select Show Selected Details at the top of the page.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=5db1938f-31b2-4fdf-8a6e-a73338dd09e8.png)
- This opens the transactional details in a new tab, only including the data columns you indicated on the Data Select tab.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=cac81dcb-ae0c-4732-aeea-e96983470219.png)
Adding Data Not Currently in WyoCloud
The Account Analysis report includes the most complete and up to date transactional data available within WyoCloud. However there maybe circumstances where you are aware of transactions, such as IDT journal entries, that have not yet been posted in the system. You may use the Add Data feature to review your accounts with these transactions taken into account.
- From the Process tab, select Add Data.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=de7a4bd1-af63-41dd-93e8-d14dbbb56af8.png)
- In the Add Data pop up window, enter all information for the transaction you’d like to add to the report. When complete, click Insert Line.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=cd5f57af-a857-464b-b16f-4672fd830146.png)
- This adds the line to the full report and will be included within the details on all tabs. The Add Data window remains open to add additional lines. The lines will be shown as encumbrances Click Exit when you are done adding lines.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=9583e704-6b45-4b98-b7c6-eaacef14b9fb.png&beidInt=166)
|
Note: For tips on reading the completed report, see the appendix below.
|
After running the macro, the report opens to the AASummary tab
- From this tab you can filter to specific segments of the chart string by using the filters at the top:
Please note a few items:
-
Balance sheet items (natural accounts under 39999) are NOT filtered out of the totals and will be displayed on this tab.
-
Fund Source 000000, which is only used internally by Accounting, is NOT filtered out on this tab, but can be ignored for your viewing.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=ed41d289-8240-4e47-aaea-2de7ffbc0758.png)
- You can expand the details of all transactions included within a specific amount by double clicking an amount within the Accounted DR, Accounted CR or Total columns.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=800106a5-433b-48b2-baf2-c38728f1cf4f.png)
- The full details of what makes up that number will open into a new tab. See Appendix: Column Definitions for details.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=6fb54b7d-8845-4c26-8017-631ce00bd73c.png)
Reading the Report: BudgetSummary Tab
Similar to the AASummary tab, you can click on any number in columns: Accounted DR, Accounted CR Budget or Variance and the full details of what makes up that number will open into a new tab. The % Remaining column shows as a percentage how much of the budget has been used. See the appendix for column definitions.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=18de6d9b-fc9a-4ec0-afb6-4051b8fdd3f9.png)
Please note a few items:
-
Balance sheet items (natural accounts under 39999) are filtered out of the totals and only displays those natural accounts that can have a budget to them.
-
Fund Source 000000, which is only used internally by Accounting, is also filtered out on this tab so it doesn’t impact the view of the spending authority for your division/department.
![](https://uwyo.teamdynamix.com/TDPortal/Images/Viewer?fileName=15c3131a-a4d1-4792-a8ad-e03710c6aceb.png)
Appendix: Column Definitions
After clicking on a value to expand full details or the Combined Data tab, the following columns are included:
Column Name
|
Description
|
P_Period_From
|
Reflects the starting month of the report (as input when scheduled).
|
P_Period_To
|
Reflects the ending month of the report (as input when scheduled).
|
Balance_Type
|
Will show if transaction represents an actual or encumbrance (if encumbrances were included when running report)
|
Accounting_Code_Combination
|
Full accounting chart string.
|
Code_Combination_Description
|
Written version of the accounting chart string.
|
Period_Year
|
Year of the period.
|
Period_Number
|
Corresponds with the month of the fiscal year (i.e. July = 1, August = 2).
|
Period_Name
|
Name of the period (month).
|
Period_Start_Date
|
First day of the period the transaction falls into.
|
Period_End_Date
|
Last day of the period the transaction falls into.
|
Balancing_Segment
|
Entity
|
Natural_Account_Segment
|
Natural Account
|
Cost_Center_Segment
|
Organization
|
Balancing_Segment_Desc
|
Written version of Entity.
|
Natural_Account_Segment_Desc
|
Written version of Natural Account.
|
EnTITY
|
Entity
|
Natural Account
|
Natural account number.
|
Natural_Account_Desc
|
Written version of Natural Account.
|
fund class
|
Fund Class
|
fund source
|
Fund Source
|
Expense class
|
Expense Class
|
program
|
Program
|
activity
|
Activity
|
future
|
Future
|
GL_Date
|
The date the transaction was posted to the General Ledger (GL).
|
Created_By
|
Who entered into the GL. Note if this column shows serviceuw.integration this is a feeder file such as Banner, Telecom, Copier, Postal or Payroll.
|
Creation_Date
|
When the transaction was initially entered into the system.
|
Last_Update_Date
|
When the transaction was last updated.
|
Header_ID
|
This is a system generated number.
|
Header_Description
|
Description which was entered at the header level when the user entered the transaction. The exception is those that say Journal Import indicate integrations or journal entries.
|
Integration from Banner, IT Billing, Copier Services, Payroll, Postal Services
|
JE_Category_Name
|
Type of entry the transaction was given when it was entered.
|
JE_Source_Name
|
The source of the journal entry.
|
GL_Batch_Name
|
This is the name of the batch in the GL which can be utilized to search for more details within the system.
|
Posted_Date
|
The date the transaction first occurred within the system.
|
GL_JE_Name
|
This is the kind of journal entry.
|
GL_Line_Number
|
Journal line number.
|
Line_Number
|
The line number of the journal entry.
|
Orig_Line_Number
|
Original journal or subledger line number.
|
Line_Description
|
Description which was entered at the line level when the user entered the transaction.
|
GL_Transfer_Date
|
Date when the journal entry is transferred to the GL.
|
Completed_Date
|
Subledger journal entry completion date.
|
Transaction_Number
|
The number of the transaction or the invoice number depending on the type of transaction.
|
Application_Name
|
Indicates the application in the system which was used to process the transaction. (i.e., Payables: Invoices, Purchasing: Reqs and POs, Project Costing: PPM (grants).
|
Event_ID
|
This is system generated information.
|
Event_Date
|
This is system generated information.
|
Event_Class_Name
|
The accounting event that originated the journal entry (i.e., credit memo, period end accrual).
|
Event_Type_Name
|
The action taken relating to the accounting event that originated the journal entry (i.e. credit memo validated, invoice cancelled, invoice validated).
|
Accounting_Class_Name
|
Accounting class assigned to the subledger journal entry line.
|
Party_Number
|
Supplier Number.
|
Party_Name
|
Supplier Name.
|
Party_Site_Number
|
Number assigned to the supplier’s address.
|
Party_Site_Name
|
Supplier’s address. Note: this appears as Order / Remit for transactions paid electronically.
|
INCLUDE_ACCT_WITH_NO_ACT
|
This has the filter options you selected when you scheduled the report.
|
BALANCE_TYPE_DSP
|
This has the filter options you selected when you scheduled the report.
|
FILTER_CONDITIONS_ATT
|
This has the filter options you selected when you scheduled the report.
|
FILTER_CONDITIONS_OPT
|
This has the filter options you selected when you scheduled the report
|
Accounted_DR
|
A debit posted to the General Ledger (GL)
|
Accounted_CR
|
A credit posted to the General Ledger (GL)
|
COST_CENTER_DESC
|
Written Organization name
|
ORG
|
Numerical Organization
|
Balance
|
Balance of Debits minus Credits
|
Appendix: Where Can I Find...
Where Can I Find...
|
Column Name
|
IDT Number
|
GL_Batch_Name or GL_JE_Name
|
Expense Report Number
|
Transaction_Number
|
Requisition/PO (not paid yet, encumbered)
|
Transaction_Number
|
Requisition/PO (paid, actual)
|
PO # only will show at the end of Line_Description
|
IT, Copier Services, Postal Services, Payroll Transactions
|
JE_Catergory_Name or GL_JE_Name
|
Invoice Number
|
Transaction_Number
|
Deposits/Touchnet Transactions
|
JE_Category_Name or GL_JE_Name
|
Project Number
|
Will only show at the front of the Line_Description for the applicable transactions
|