Data Warehouse Financial Reporting Overview

1. Data WarehouseFinancial Reporting Overview Sources: Office of Budget & Resource Planning Chancellor’s Office

2. Data Warehouse • All campuses have access to the Data Warehouse. This features : • Summarized reports by all ChartFields, with drill down. • Open PO’s, with drill down. • Transactions by “posted date”. • Department Hierarchy. • Report by Division, by College, by Department. • Can select by one and report at lower level. • NOTE: Data is one day old!

3. Data Warehouse Access to the Data Warehouse will NOT be granted until you have attended a formal training class. A bulletin will be sent out by August 1st, identifying your class options. Classes will be limited in size and offerings– so don’t delay signing up.

4. Login to the Data Warehouse Log into My Fresno State • At “My Menu” select “CFS Data Warehouse”

5. Login to the Data Warehouse Default Screen

6. Getting Started - Dashboards Five Dashboard Choices: Firms GAAP Manage My Budget Management Reporting Operations Sponsored Programs Your primary GO –TO’s: Manage My Budget Operations

7. Getting Started - Establish Your Default Settings You will need to establish your default settings for every tab on the Dashboard.

8. Getting Started - Establish Your Default Settings Step 3: Go! Step 2: Select Standard Budget Step1: Select Business Unit

9. Getting Started - Saving Your Default Settings Page Options

10. Getting Started – Saving Your Default Settings Select Page Options, & Save Current Selection Save for Me Keep the default name or enter a new name. Click "Make this my default for this page”. Click OK!

11. Getting Started – Additional Saved Selection Select Page Options, & Save Current Selection Save for Me Keep the default name or enter a new name. Do not choose the “Make this My Default” checkbox. Click OK!

12. Getting Started – Change Page Default Select Page Options, & Save Current Selection Choose Select the option that you want to save, as the new default. Click "Make this my default for this page”. Click OK!

13. Getting Started – Apply Saved Selection Apply Saved Selection Choose a saved selection

14. FIVE STEPS TO REPORT RESULTS After you establish your dashboard defaults, you are ready to generate a report. There are five main activities involved with report production: • Select the report • Set the report filters • Set the report format • Drill through report data • Capture report results (print /download)

15. Manage My Budget Manage My Budget “Dashboard” has 9 pages (tabs): Department Fund Program Class Project Organization Organization Between Fiscal Periods Open PO Open Reqs

16. Manage My Budget Under “Manage My Budget” Select “Department”

17. Revenue and Expense Summary by Department, Fund, Account The Revenue and Expense Summary, by Department, Fund and Account , can be run by 2 , 3 or 4columns

18. Revenue and Expense Summary by Department, Fund, Account Note: Although multiple years are available for selection, the first populated year is 2011 for Fiscal Year 2011/12. Then GO! Enter the Date, Period, Fund and Department

19. Report Filters = Selection Criteria Multi-Select has continuous dots … Prompt filter has down arrow Don’t forget to hit GO!

20. Report Filters = Selection Criteria • Double-click on any value to move it into or out of the Selected box. • Hold down the Shift key and click on the first and last values to select a continuous range of items. • Hold down the Ctrl key and click to select on non-continuous items. • For Alphanumeric search – the search is case sensitive. Use caps. • There is limited criteria in the selection box. If your search contains more than 256 results, hit the “More” button to display the next 256 choices.

21. Revenue and Expense Summary by Department, Fund, Class This is the default report.

22. Revenue and Expense Summary by Department Choose your criteria for the first column, from the “Column 1” drop down

23. Revenue and Expense Summary by Department Choose your criteria for the second column, from the “Column 2” drop down Then Hit GO!

24. Revenue and Expense Summary by Department, Class, Account “Class” for the 1st Column, and “Acct Fdescr” for the 2cd Column.

25. Revenue and Expense Summary by Department, Class, Account Use “Select Report View” to choose from a variety of reports.

26. Drill Down You can drill down for detail information, in any field that shows an underline when you scroll over it.

27. Drill Down of Actuals Note that the drill-down criteria is described in the header. • Sort data by individual columns or multiple columns. • Sort by multiple columns by holding down the control key while selecting the column titles. Release the control key to execute the sort.

28. Drill Down of Budget Note that the drill-down criteria is described in the header. Return-Refresh-Print-Download- Create Bookmark Link. Click Return link to return to the parent report.

29. Download and Print

30. Print There are two print actions on every page. One is a hyperlink located beneath each report. The other is a print icon located at the bottom of every dashboard page. The hyperlink located beneath the reportwill print the individual report. The icon located at the bottom of the page will print all page content including section headings and report filters.

31. Download

32. Download to Excel • Reports downloaded to Excel will include the formatting, but will NOT include the formulas. • Columns that are numeric and contain values with leading zeros lose the leading zeros. • Monetary columns download as numbers and will retain formatting.

33. Download Data Downloads all data in the request, including fields not displayed in the current view, as a TAB Delimited file, this file will open in Excel without any formatting and can then be saved as an Excel file.

34. Data Warehouse Basics • Establish and save your default settings. You will need to save your default settings for each tab. • Don’t forget to input the date for the report. Remember that the only prior year available through the Data Warehouse is 2011, for fiscal year 2011/12. • “GO”: If you don’t hit Go, you’ll GO nowhere! • What’s taking so long? These reports are lightening fast. If you find yourself wondering what’s taking so long – you’ve been kicked out of the system. There’s no real rhyme or reason for it.

35. Operations – Organization - Department Levels “Operations“Dashboard has 13 pages (tabs): Home, Ledger Reporting, Organization, Actuals Trial Balance, Projects Cash, Fund Balance, Actuals Transactions, Budget Transactions Open PO’s , PO Transactions, Open Reqs , Req transactions

36. Operations – Organization - Department Levels The Ledger Summary by Organization can be run by 2 , 3,4 or 5 columns

37. Operations – Organization – Report Filters This is the default screen. Note that the current year and active period are displayed.

38. Operations – Organization – Report Filters CSU FUND Generally, you will select : “485 “- TF- CSU Operating Fund”. Note: If you are running a Trust Fund Report leave this field blank.

39. Operations – Organization – Report FiltersFUND Generally, you will select the General Fund - “90000- TF- CSU Operating Fund”. Do NOT scroll down and select 01011- General Fund Support

40. Operations – Organization – Report FiltersASSET TYPE Generally, you will select : “50-Revenues” “60-Expenditures”

41. Operations – Organization – Report FiltersAccount, Account Category, Program, Project, Class You can specify : Account Account Category Program Project Class

42. Operations – Organization – Report FiltersDEPARTMENT Departments levels have been established to follow the “trees” in PeopleSoft.

43. Operations – Organization - Department Use Department to select SPECIFIC Departments Note the More… Button

44. Operations – Organization - Department Level 1 Use Department Level 1 for campus wide Note only two choices returned.

45. Operations – Organization - Department Level 2 Use Department Level 2 for Divisions Note only 8 choices returned.

46. Operations – Organization - Department Levels Use Department Level 3 for Schools Note only 48 choices returned.

47. Operations – Organization - Department Level 3 Ex: Department 35300 - Level 3 School of Arts and Humanities.

48. Operations – Organization - Department Level 3 Ex: Department 35300 - Level 3 School of Arts and Humanities. Roll-Up of all Departments under 35300

49. Operations – Organization - Department Level 3 Ex: Department 35300 - Level 3 - School of Arts and Humanities.

50. Operations – Organization - Department Level 3 Ex: Department 35300 - Level 3 - School of Arts and Humanities.