Title of Deliverable:____________________

CalSAWS

California Statewide Automated Welfare System

C-IV

Ad Hoc Report Request Process

C-IV

Ad Hoc County Reporting

Page 2 of 9

Table of Contents

Revision History ..........................................................................................................................................................3

Introduction ................................................................................................................................................................4

Process ........................................................................................................................................................................4

Submitting Issues and Questions ...............................................................................................................................6

Appendix A – Ad Hoc Report Request ........................................................................................................................7

Appendix B – Sample Ad Hoc Change Order ..............................................................................................................8

Appendix C – Ad Hoc Report Design Template ..........................................................................................................9

C-IV

Ad Hoc County Reporting

Page 3 of 9

Revision History

DATE

DOCUMENT

VERSION

REVISION DESCRIPTION

AUTHOR

09/17/2015

15-09

Updated UniCenter Templet Instruction

Taras Shemchuk

12/04/2015

15.12

Updated Process

Larry Risser

04/20/2020

20.04

Updated Information

Paul Robertson

C-IV

Ad Hoc County Reporting

Page 4 of 9

Introduction

The CalSAWS Project Team provides Ad Hoc Report support to C-IV Counties. This support is provided by

the Ad Hoc Business Analyst (BA). This resource will gather reporting requirements from the counties to

design and deliver reports. The process includes:

• Working cooperatively with county representatives, as well as CalSAWS contractors to create ad hoc

reports using Oracle APEX, Crystal Reports, SQL and database models. Support for Crystal Reports

will be terminated with CalSAWS migration (09/2021).

o Assisting county report writers in understanding and preparing ad hoc reports.

o Preparing detailed report designs based on county requests.

o Interpreting nontechnical report requests to create detailed and accurate reports.

• Providing expertise related to the C-IV Ad Hoc database including table content and relationships.

o Monitoring data model changes and informing counties of updates to the C-IV data model.

o Distributing updates to the database model diagram of the ad hoc database including

primary and foreign keys upon new release.

o Communicating simplified information on databases changes to the counties. Provide

analysis of what changed and potential impact to existing county created reports, impacts to

the way tables are joined.

o Distributing report templates/queries with explanations and samples via the CalSAWS

Project Web Portal and Info View.

• Supporting the Ad Hoc Reports Admin User Group.

This document and all supporting documents described below are available on the CalSAWS Web Portal.

CalSAWS Web Portal Location: CalSAWS Web Portal > CRFIs & CITs > CalSAWS Information

Transmittals (CIT) > 2020 > CIT XXXX-20 C-IV Ad Hoc Report Request Process

Process

Step

Action

County submits Ad Hoc Report Request with requested criterion.

BA receives request and creates a Change Order; attaching Request form.

BA completes prioritization of the Ad Hoc Report Request form.

BA notifies County via email of CO number & ETA on Ad Hoc Report turnaround

BA updates CO, as needed, throughout lifecycle of report creation.

BA reviews Request form, creates report design document, emails it to Requestor.

Requestor reviews report design and emails approval and confirmation to BA.

Upon approval, BA creates report, uploads final product to APEX or InfoView, and posts final SQL/RPT

do CalSAWS Web Portal Shared Reports folder and Catalog list.

If desired, generate recurring reports.

Counties can submit issues through their existing help desk process.

BA will monitor the help desk tool and research/respond to Ad Hoc issues.

C-IV

Ad Hoc County Reporting

Page 5 of 9

C-IV Counties will submit Ad Hoc Report requests via e-mail to AdHoc.Reque[email protected]. The Consortium

will create a Change Order (CO) and attach the Ad Hoc Report Request form. This form requires the following

information for the request to be considered:

• County Name

• Request Date

• Requester Phone number and Email

• Need By Date

• Report Frequency: Once, Monthly, Weekly or Other (this requires the user specify the desired

frequency)

• Report Type: Detail or Summary

• File Format: PDF or Excel

• Purpose: Audit, Budget Planning, Fiscal, Legal, Policy, Special Project, Staff Management or Other

• Source: All C-IV Counties, Advocate, BOS, County, County Manager/Supervisor, CSAC/CWDA/JPA,

Director, Director, Multiple C-IV Counties, State or Other

• Impact: Fiscal Funding, Business Process Improvement, Legal, Loss of Benefits, QC Errors, Staff

Workflow or None

• Data Capture: Range or Snapshot

• Delivery: CalSAWS Web Portal or Info view

• Required Fields for the report

• Preferred Fields for the report

• Sort Order

• Criteria/Parameters

See Appendix A to view the Ad Hoc Report Request form. See Appendix B for a sample Change Order (CO)

Note: This form may require internal county review prior to a request being submitted. This review should

include looking at the Ad Hoc catalog on the CalSAWS Web Portal to verify the requested report has not

already been created from another request.

If the county has a mockup or initial coding completed by the county for the requested report, this can be

attached to the e-mail with along with the Ad Hoc Report Request form. These items will be reviewed by

the BA along with the Ad Hoc Report Request form to assist in the development of a report that meets the

counties needs and vision.

All Ad Hoc Report Requests will be reviewed by the CalSAWS Project and prioritized based on a set criterion.

Prioritization of the Ad Hoc Report Request form will be completed within 7 to 14 business days. Once this

prioritization is completed the county will be notified via e-mail and an estimated time of completion will be

stated along with the CO number created by the analyst.

Note: Requests will be prioritized based on Purpose, Source, Impact and Need by Date as entered on the Ad

Hoc Report Request form.

C-IV

Ad Hoc County Reporting

Page 6 of 9

In instances where the priority is disputed, counties should reach out to their Regional Manager (RM). In

addition, if two requests have the same priority level the RMs will decide on which one is to be completed

first.

The BA is responsible for updating the CO throughout the report development process. This includes priority

status, clarifying questions, draft report design document and any changes in priority or date of completion.

The BA will review the Ad Hoc Request form and create a report design document from the information

available. The Ad Hoc Report Design document will be the foundation for creating the report. See Appendix

C to view the Ad Hoc Report Design Document template.

This report design document will be e-mailed to the address listed on the Ad Hoc Report Request. The

document must be reviewed and approved by the county before the BA will start creating the report. It is

the requesting county’s responsibility to reply with their approval/confirmation. After approval is received,

the BA will create the report and upload the final product to APEX or InfoView. In addition, the SQL and the

RPT output file created by Crystal Reports (through 09/2021) will be posted on the CalSAWS Web Portal.

CalSAWS Web Portal Location: CalSAWS Web Portal/Resources/Resources by Functional Area/Ad

Hoc/Ad Hoc Shared Reports

All completed reports will also be logged in the Ad Hoc Catalog located in the same location on the CalSAWS

Web Portal. The BA will not generate recurring reports for the counties. Generating recurring reports is a

county responsibility. The BA is responsible for developing and preparing the initial report for distribution.

Submitting Issues and Questions

Counties can submit issues through their existing help desk process.

If a county has questions on a report they are developing or need assistance with report creation, the county

can submit a CA ServiceDesk Manager Request. The Business Analyst will be responsible for researching the

question and providing feedback through the existing CA ServiceDesk Manager Request process.

C-IV

Ad Hoc County Reporting

Page 7 of 9

Appendix A – Ad Hoc Report Request

C-IV

Ad Hoc County Reporting

Page 8 of 9

Appendix B – Sample Ad Hoc Change Order

C-IV

Ad Hoc County Reporting

Page 9 of 9

Appendix C – Ad Hoc Report Design Template