Reporting under CoreCT Sept. 2002

1
(No Transcript)
2
Meeting Purpose and Goals

• Purpose
• Provide overview of financial reporting in
  Core-CT

• Presentation Goals
• Provide understanding of Core-CT Reporting
  Options
• Provide overview of Core-CT On-line Inquiry,
  Standard and Ad-Hoc Reporting
• Provide understanding of training and support to
  be provided to agency users

3
Core-CT Information Access

• Users can obtain information from Core-CT from
• On-line inquiries via search dialogues
• Standard reports included in the menu for each
  functional area
• PeopleSoft delivered (a.k.a. Vanilla) Reports
• Custom Reports developed for Connecticut needs
• Ad-Hoc query reports

4
Core-CT Information Access

Core-CT Information Access
Agencies will have access to Core-CT data through
a variety of capabilities and tools
Capabilities
Tools
SQR
Standard Report
Crystal Reports
Reporting
Ad-Hoc Query
PS-nVision
PS-Query/Excel
On-Line Inquiry
On-Line
PS Application
5
Core-CT ChartFields
6
Core-CT Information Access Online
Users will have single sign-on log-in and will
view their Core-CT Homepage upon entering the
system.
Exhibit 1
7
Core-CT Information Access Online
The users security profile will define
accessible areas of the application as well as
what information they may view on a report.
8
Core-CT Information Access Standard Reports
The My Reports area of Homepage lists the users
most recently run standard reports.
Exhibit 1
9
Core-CT Information Access Standard Reports
Users can also link to Report Manager off the
Home page and view all their standard reports.
10
Core-CT Information Access Standard Reports
Report Manager will list all the reports that
have been executed that a user has access to
view. All standard report types will be
available in Report Manager (e.g. Crystal,
nVision, SQR)
Exhibit 2
11
Core-CT Information Access Ad-Hoc Reports
The Homepage provides access to the Enterprise
Menu.
Exhibit 1
12
Core-CT Information Access Ad-Hoc Reports
Users can access Ad-Hoc Reporting by selecting
Query Manager from Enterprise Menu.
13
Core-CT Information Access Ad-Hoc Reports
Selecting Query Manager takes the user to the
Ad-Hoc Query tool. Users will use this tool to
access reporting tables that are designed to make
Ad-Hoc reporting more intuitive.
Exhibit 3
14
Core-CT Information Access

Agencies will have access to Core-CT data through
a variety of capabilities and tools
15
On-line Inquiry

• Perform a budget inquiry for a specific
  department
• Demonstrate On-line inquiry
• Demonstrate Ability to set criteria
• Demonstrate the ability to readily drill down to
  more specific information

Budget Inquiry
16
Screen Inquiries

• Utilize PeopleSoft
• Menus

Budget Inquiry
17
Entry Screen
REFRESH
18
Enter Search Criteria
Enter Department ID
19
Result Set
Click to View Detail
Exhibit 4
20
Drill Down
Exhibit 5
21
Querying a Purchase Order
On-line information look-up achieved via search
boxes. Users search for information in specific
areas by entering key pieces of data and
selecting search.
22
Core-CT Information Access

Agencies will have access to Core-CT data through
a variety of capabilities and tools
23
Standard Reporting Presentation Goals

• Provide information about reports available in
  Core-CT
• Provide information on how PeopleSofts standard
  reporting works
• Provide information on three types of reports
• SQR
• PS nVision
• Crystal Reports

24
Delivered Reports

• 160 PS-delivered (Vanilla) reports identified by
  functional teams for state use
• Some favorites
• Vouchers (a.k.a. Invoices) by ChartField criteria
• Open Purchase Orders
• Vendor Payment History
• General Ledger Activity by
• ChartField criteria

39 Purchasing 30 Accounts
Payable 52 Accounts Receivable 39 General Ledger
25
Connecticut Reports

• 30 plus custom reports are being developed for
  Connecticut needs
• Some favorites
• CFSR
• Expenditure Detail Report
• Purchase Order Balance Report
• Set Aside Report

20 General Ledger 5 Purchasing 5 Accounts
Payable 1 Accounts Receivable
26
List of Reports

• The list of reports is available at the CoreCt
  website
• http//www.core-ct.state.ct.us

27
SQR Report

• Run a budget status report for a specific
  department
• Demonstrate Screen Navigation
• Demonstrate Ability to set criteria
• Use Prompt Edit
• Show an SQR report printed to Adobe Acrobat

Budget Status Report
28
Report Menu -SQR
Use PeopleSoft Menus
Budget Status
29
Run Control Set-up - SQR
Search or Add a New Value
30
Pull Up a Run Control - SQR
31
Enter Criteria - SQR
Exhibit 6
32
Enter Criteria using Prompt Edit - SQR
Prompt Edit
Exhibit 6
33
Prompt Edit Values - SQR
Select a Department from the list
34
Values transferred from Table - SQR
Department 2402
Exhibit 6
35
Enter Criteria - SQR
Budget Period 2003 Department 2402 Fund Code
- 12003
36
Process Monitor - SQR
37
Report Manager - SQR
Report Details
38
Report Detail - SQR
Click to View Report
39
Report in Adobe Acrobat - SQR
Exhibit 7
40
SQR Reports

• Pros
• Standard Reports
• Handles large amount of data and processes fast
• Written by Programmers so can perform complex
  tasks

• Cons
• No drill down capability
• Formatting is cumbersome and limited
• Need Programmers to make modifications

41
nVision Report

• Show a mock up of the Comprehensive Financial
  Status Report
• Preview a custom report that is being developed
• Show an nVision Layout
• Demonstrate Drill Down Capability

CFSR
001 Personal Services 100000 002 Other expenses
50000 005 Equipment 150000
42
CFSR - nVision
Exhibit 8
43
CFSR - nVision
Pre-Encumbrances YTD Amount
Projected to EOY Amount
Expenditures YTD Amount
Account Name
Total EOY Amount
Encumbrances YTD Amount
Account
SID
Exhibit 8
44
CFSR nVision
Select Value to Drill Down
Exhibit 8
45
CFSR Drill Down-nVision
Drill Business Unit by Period
Drill Business Unit by Account
46
Drill Down by Account - nVision
47
Drill Down by Month - nVision
48
nVision Reports

• Cons
• Needs high skill level to design the data
  gathering
• Can have performance implications
• Potential for user created data consistency issues

• Pros
• Uses Excel format which people know
• Drill down capability is slick
• Can add formulas and values graphs, etc. onto
  nVision Report layout.

49
Crystal Report

• Run a Purchase Order Report
• Show a delivered report which will be modified
• Demonstrate Sorting Options
• Show a Crystal Report

Purchase Order Report
Vendor XYZ Corp BU PO Contract PO
Date DOT 111 02DOT0 7/1/2002
50
Purchase Order Report - Crystal
51
Set Criteria - Crystal
Report Criteria
52
Process Scheduler Sort Order - Crystal
Sort Options
53
Report Manager - Crystal
Report Details
54
Detail on Report Manager - Crystal
Click to View Report
55
Crystal Query Report
Exhibit 9
56
Crystal Reports

• Cons
• Needs high skill level to design
• Limited drill down capability

• Pros
• Pretty formatting
• Can be scheduled in batch
• Allows Grouping and subtotaling not provided by
  tree structure

57
Core-CT Information Access

Core-CT Information Access
Agencies will have access to Core-CT data through
a variety of capabilities and tools
Capabilities
Tools
SQR
Standard Report
Crystal Reports
Reporting
Ad-Hoc Query
PS-nVision
PS-Query/Excel
On-Line Inquiry
On-Line
PS Application
58
Uses of Ad Hoc Reporting

• When the format of standard reports is not
  desired for hardcopy
• When the data provided in standard reports does
  not match your needs
• When the availability of standard reports does
  not allow you to meet deadlines
• Example
• Supervisor requests an analysis of
  plan/forecasted v. actual Personal Services by
  the end of the day (for transfer/FAC)

59
Ad-Hoc Reporting Subject Areas
EPM
Human Resources
Financials
Exhibit 10
60
Data Model

• Financial Tables
• General Ledger Plan v. Actual Budget Journals
• A/R deposit/revenue summaries federal
  receivables
• A/P payment histories by vendor payment
  transactions by type
• Purchasing requisitions linked to POs by
  ChartField
• Commitment Control unencumbered balances by
  ChartField
• HR Tables
• Payable Time OT earnings paid by ChartField
• Applicant Data Job Requisition Health Safety
  Regulatory Time Labor Health/Life Leave
  Plan Dependent Benefits Billing Activity
  Position Employee Paycheck Detail Payroll YTD
  Payroll Additional Pay Employee Deductions
  Employee Tax Garnishment

61
Sample Templates

• Examples -
• Select transaction records from the Accounts
  Receivable reporting tables by Account (standard
  revenue account) by month
• Select expenditure records from General Ledger
  reporting tables by SID/Department/Account by
  month
• Select Allotted, Unencumbered, Unliquidated,
  Expended balances from the Agency1 Budget Ledger
  by Department

62
Financials HR Reporting
Example Overtime hours and dollars paid to
date by employee by month v. budgeted for an
Agency 1 level Department

• HRMS Reporting Table
• PAYABLE TIME
• Select
• Employee ID ALL
• Employee Name ALL
• Earnings Code Overtime
• Pay End Date ALL
• Chart Field String Department

• Financials Reporting Table
• COMMITMENT CONTROL
• BALANCES
• Select
• SID 001
• Account Overtime
• Department Department 1

63
Financials HR Reporting
Financials Data
HR Data
64
Ad Hoc Tools
PS Query
Excel
65
Core-CT Information Access Summary
Users have multiple options to choose from when
deciding how to retrieve information from
Core-CT.
66
Training and Production Support

• Core-CT Training and Production Support

67
Training for Line Agencies

• Instructor led training for line agencies
• Financials timeframe April July 03
• HR timeframe July Oct 03
• Reporting Logistics
• How to set-up and run a report
• How to retrieve a report
• How to print a report
• How to create an Ad-Hoc query
• Standard Report Usage
• Integrated with functional training
• Specific to a module
• Job aids listing all standard reports and queries

68
Reporting Production Support

• Reporting Support Team
• Separate team within Production Support Unit
• Ad-Hoc Query support
• Data model maintenance
• Standard Report Development
• Production change control process