Home

Data Analysis Training

1. 02 16 12 Partner Level Data Templates were created on 1 19 2012 and the Access Database and User Manual can be accessed on ITWS through the MHSA Information menu by selecting the User Manuals and Instructions submenu and then clicking to download the DCR Client Level Report Template Page 9 of 128 Reference Materials e Application Notes completed 4 5 2012 Systems MHSA Information f Functions Utilities Support Logout MHSA Training forthe a Home MHSA Training pag User Manuals and Instructions An Application Notes document with instruction for using Microsoft Excel and Microsoft Access to create reports from FSP data downloads was created on 4 5 2012 and the document can be accessed on ITWS through the MHSA Information menu by selecting the User Manuals and Instructions submenu Note The document had not been posted at the time of this publication Page 10 of 128 Page 11 of 128 This Section Covers o Downloading FSP Data Files o CSV Files o XML Files This section covers downloading CSV and XML file types Page 12 of 128 Download File Options e Users can download the data in two file forms o XML Extensible Markup Language 3 Files gt PAF gt 3M gt KET o CSV Comma Separated Values 4 Files gt PAF Residential Fields gt PAF Non Residential Fields gt 3M gt KET The FSP DCR offers a function for users with the CA RW role to downl
2. Page 61 of 128 PARTNER INFORMATION Days in Partnership 4 7 2009 Total 3Ms Due Partner Name John John 063 Partnership Date 3M Assessments AspesamentDate Days in Patnership For Quarter Age Group at Time d Assessment 7 30 2009 aia TAY 10 8 2009 184 TaY 6 25 2010 asa 9 24 2010 535 TAY Tay History of Administrative Changes Change Aser te Provider Ose Program Oze ment Date Orange Change of Change of EPen Program oer jo umo aM acma Waem Atzea Lumines atm iee aneo san ien Coodinsor Dae Stars Discontinue One ABM Change of Reason Chasgeof Stas asau anno Nees an nemo osama veer Confidential Protected Health information This slide displays the 3M Assessments and Administrative Changes page of the report Page 62 of 128 PARTNER INFORMATION Partner Name John john 063 Partnership Date 4 7 2009 Days in Partnership Total 3Ms Due Residential Settings 12 Months Before FSP Resaros Ber Yerecry sce Rexres Pana devesite Ha Camp us Residential Settings During FSP Date Residentia Change 04 09 09 24806 M 04 28 03 BS24BAM 05 21 09 11 5858 AM NNR 4272008 5 1920 This slide displays the Residential Settings Domain page of the report Page 63 of 128 PARTNER INFORMATION Partner Name John John 063 Partnership Date 4 7 2009 Days in Partnership Total 3Ms Due Education Domain 12 Months Before FSP Amenment Ome gCmnge Age TimePeriog oze Com pews Pon Education Dur
3. 15 As seen in this slide move to the datasheet view to inspect the results of the query This query will display one record for all continuous partnerships and one record for all discontinuous partnerships in the entire system of data Write down the number of records in the query as reference Save and close the query Page 106 of 128 ep 5 Total by Provid Program and System e Create Union Query o Select Create gt Query Design o Select Close on Show Tables o Select View gt SQL or Union o Type SELECT FROM APi_So5_001_Total_ByProvider UNION SELECT FROM APi_So5_002_Total_ByProgram UNION SELECT FROM AP1_So5_003_Total_System 16 Create one last query to combine the rows of the previous three queries in this step Select Create gt Query Design Select the Close button in the Show Tables pop up and do not add any tables to the query Select the View gt SQL or the Union option for the query Either of these options will redirect you to the SQL View Type or paste the following SQL code to combine the rows in the previous three queries with a union SELECT FROM AP1_S05_001_Total_ByProvider UNION SELECT FROM AP1_S05_002_Total_ByProgram UNION SELECT FROM AP1_S05_003_Total_System Page 107 of 128 Program and System e Inspect the results of the query e Write down the number of records e Save query as AP1_So5_004_Total_ALL ProvidersitelD Continuous CountOfGlobalID AVgOfDA
4. Doe John As seen in this slide the BPD template will open in a new tab titled Partner_Info_SearchbyName with partnerships matching the search criteria displayed The number of partnerships matching the search can be seen in the Record box in the lower left hand corner of the screen Page 57 of 128 Run Partner Report Template o Run one partner report at a time by ID Run Partner Report View Partner Report Enter Parameter Value cac Search by ID Enter CSI Number or press OK to skip and use Global ID on next screen Export PDF Report Search by ID Go cance Enter Parameter Value O immo Enter Global ID or press OK to skip C eS Geass FrontPage S Partner_Level_Report The RPR template includes two options to generate a partnership report which can be accessed through the two buttons in the Run Partner Report section of the front page as seen in this slide The RPR Template can be viewed in Microsoft Access as an Access report or it can be exported as a PDF file The View Partner Report Search by ID button will display a report within Microsoft Access which can be viewed electronically or printed from Microsoft Access A report for a partnership is displayed based on matching the criteria entered for a partner ID There are two options for entering partner IDs You can enter a CSI number in the first pop up window or enter a Global ID in the second pop up window To display a
5. Table DCR_3M DCR_3M Total Group By Count Sort v Show a 4 Criteria Step 2 involves calculating the number of 3Ms ever submitted for each partner If a partner has never had a 3M submitted then the partner will not exist in the results of this query This query will show the number of 3Ms submitted for all partners with at least one 3M 1 With the PLD Templates open in Microsoft Access select Create gt Query Design 2 In the Show Tables pop up window select and add the following table to the query DCR_3M table Then select the Close button 3 Right click in the query definition datasheet in the bottom half of the window and choose to add a Total row to the query definition 4 As seen in this slide add the GloballD and choose Group By from the drop down selections in the Total row 5 As seen in this slide add the AssessmentDate field to the query Select the Count operator from the Total row drop down box Rename the field by typing 3Ms_Completed in front of the original field name Page 93 of 128 Step 2 Calculate Num Complete by Partner 3Ms_Completed e Inspect the results of 000026d9 02d4 48c4 8cea th e qu ery 0002aa56 3478 411c 9b37 000332a0 b752 43e2 a284 00036466 5344 48c9 a2ba 00063f91 73bb 470f 9997 3 number of records 000d82a2 0ffc 40f0 804a 8 000ec902 acad 4e87 8104 0013afb6 53e5 4721 8e26 e Save the query as 0017532f 04d5 47ae a7b4 1 AP1 So2 oo1 0
6. on page 8 as a concise reference for these topics Page 87 of 128 Report Create a list of partners who have been discontinued and reestablished using KET status Count the number of 3Ms completed for each partner Calculate the length in service for each partner Combine lists and create additional variables by partner Calculate Totals Total across providers Total across programs Total across county system wide Perform a union query to combine all totals queries Create a report using the MS Access Report Wizard Once the Microsoft Access database contains the building blocks described previously in this section the report can be constructed through the six steps listed in this slide Page 88 of 128 SQL to Support Queries e Appendix C of Application Notes document contains the code in support 1 of all queries we are about to create E j Ea Datasheet View e Copying and pasting SQL code from r amen Appendix C into the SQL View window can quickly recreate the queries BIB rrorcrar view SQL SQL View Appendix C from the Application Notes document provides the Microsoft Access SQL code for all of the queries created in the Application Notes The SQL code assumes the user is utilizing the PLD Templates database offered by the MHSOAC This code also assumes the user has imported the CODES_TIMEFRAME from Appendix A and the CODES_DATES from Appendix B tables into the PLD Templates Copying and pasting t
7. Full Service Partnership FSP Data Collection amp Reporting DCR Data Analysis Training Training Slides and Presenter s Notes for Analyzing Data and Creating Reports from DCR Data Extracts The following document was nde by the Mental Health Services Oversight and Acrountahilisy Commission Yes minnie Bee ANNIN RAPETE VN VEA ENS oa aa eb aA AA BP 1 hiai AINEA p DAR yr EA be eet if YOM EAN yG i AI ES AE ivy SS eo 2 gt R PE U aty fe oe is Sis n Sar ESA OAIE Sats a RAND He Ok Aaah wae VE UR Te US OO A NE A ain he Sheba Table of Contents click to hyperlink to section Acknowledgements 2 Definitions and Abbreviations 3 Introduction FSP Data Analysis Training Slides 5 Training Overview 6 Reference Materials 7 Downloading FSP Data from DCR 11 Section Overview 12 Downloading Files 14 File Types 16 Importing CSV Files into PLD Templates 18 Section Overview 19 PLD Templates Overview 20 Encryption 26 Importing New Data 27 Generating Standard Reports in PLD Templates 37 Section Overview 38 Browse Partner Data Template 39 Run Partner Report Template 58 Application Notes to Create New Reports in PLD Templates 71 Section Overview 72 Application Notes Overview 13 Before Creating a New Report 77 Working in Microsoft Access 87 Steps to Create Application Note 1 88 Using Microsoft Excel to Analyze FSP Data 120 User Resources 127 Page 1 of 128 Acknowledgements We would like to acknowledge the inpu
8. completed 1 17 2012 The FSP DCR Administrator and User Training Curriculum is available for download as a PowerPoint presentation and a handout with presenter s notes Trainers can use this curriculum to provide training internally to new staff using the FSP DCR Page 127 of 128 This training was funded by the Mental Health Services Oversight and Accountability Commission MHSOAC Page 128 of 128
9. e Provides a Data Extract Warehouse e Facilitates Data File Import e Contains Pre Defined Tables and Field Definitions e Contains an Automated Process for Replacing Old Extracts with New Extracts e Provides Form Layout of Each Partner s Complete Data e Provides Reports of Each Partner s Complete Data e Provides an Environment for Creating New Reports The Partner Level Data PLD Templates act as a data extract warehouse and provide fully defined tables to which the DCR CSV files can be easily imported The PLD Templates also provide tools and instructions to facilitate the import of the DCR CSV files into Microsoft Access The PLD Templates contain pre defined tables which incorporate the coding values for the fields within the CSV files according to the DCR FSP Data Dictionary The PLD Templates also include dimension tables which can be used to translate the coding values within the FSP data fields into useful terms and categories All of these tables are defined already for users utilizing the PLD Templates The PLD Templates also provide an automated process for clearing and updating data for new DCR CSV downloads The PLD Templates are designed to provide a comprehensive summary of a partner s Full Service Partnership FSP Data Collection and Reporting System DCR partnership data for the purposes of data quality The PLD Templates display and organize domain data for the partnership s current status and history in the
10. Api_sos_001_Totals_ByProvider AP S05 004 1H AP1_s05_002_Totals_ByProgram 3 api_sos_003_Totals_system _Total ALL GD AP1_S05_004_Totals_All BH open a AP2_501_001_Discontinuous wz Design View a AP2_S02_001_DiscMaxDate Se jprm G meememmmmmmtannc gal Lae 2 tH ap2_so2_002_DiscMaxDate_Createdlast Be Collect and Update Data via E mail SharePoint List tS Ap2_s02_003_DiscReason_Last af Rename Word RIF File CH AP2_s03_001_DaysinService Hide in this Group POF or XPS m ap2_s04_001_DaysinService_ExciDisconti Delete Access rat AP2_S05_001_DaysInService_DiscReason Text File Cut XML File ODBC Database HTML Document amp 13 AP2_s06_001_Variables_ByPartner a copy AP2_S07_001_Totals_ByProvider_n AP2_S07_002_Totals_ByProgram_n Object Properties dBASE File AP2_S07_003_Totals_System_n Check Web Compatibility Fe Ss eePUBeea Word Merge AP2_S07_004_Totals_All_n 1 With the PLD Templates open in Microsoft Access right mouse click on the AP1_S05_004_Total_ALL and choose Export gt Excel as seen in this slide 2 Provide a file name and browse to a location to save the Excel file 3 Select Export data with formatting and layout and select Open the destination file after the export operation is complete Click OK 4 Your data is now in Microsoft Excel and can be formatted for inclusion in a Microsoft Word report Page 110 of 128 Step 6 Design Report Access Create gt Report Wizard Repo
11. ep 4 Combine All Variat Partner gt TS La AP1_S02_001_3MsCompleted AP1_SO1_001_Discontinuous GlobalID AP1_S03_001_DaysiInService GlobalID 3Ms_Completed obal GloballD KETStatus ProgramDesc Description ProviderSitelD PartnershipDate End_Date r DaysInService CODES _DATES r CODES_TIMEFRAME 8 DATE_VALUE i MONTH_NO DAYS_IN_SERVICE MONTH_NAME MONTHS_COMPLETED_EXACT DAY_OF_MONTH MONTHS_COMPLETED_INT YEAR_NO QRTS_COMPLETED_EXACT MONTH_AND_DAY QRTS_COMPLETED_INT DAY_OF_WEEK_NO QRT_CURRENT DAY_OF_WEEK_NAME YEARS_COMPLETED_EXACT WEEKEND_OR_WEEKDAY YEARS_COMPLETED_INT FY TIME_FRAME QRT TIME_CAT_6MOS FY_QRT TIME_CAT_3MOS CY_AND_QRT 3MS_DUE FY_AND_QRT Continuous Step 4 involves joining information from all of the previously created queries This query will result in one record per partner 1 With the PLD Templates open in Microsoft Access select Create gt Query Design 2 In the Show Tables pop up window select and add the following two tables from the Tables tab CODES _TIMEFRAME CODES_DATES Select the Queries tab and add the following three queries AP1_S03_001_DaysInService AP1_S02_001_3MsCompleted AP1_S01_001_Discontinuous Then select the Close button 3 As seen in this slide create the following joins between the five tables queries e Inner join between AP1_S03_001_DaysInService PartnershipDate and CODES_DATE DATE_VALUE e Inner join between
12. lower section separator bar e Drag the ProgramDesc field from the Detail section to the ProgramDesc Header section e Select on the ProgramDesc Header bar and right click and select Properties to display the properties window on the right side of the screen In the Format tab select the Back Color to be gray Set the Alternate Back Color to be No Color e Select on the Detail bar and in the Format tab of the Properties window select the Page 117 of 128 Alternate Back Color to be No Color e Select the ProgramDesc field and in the Properties window set the Width to 4 and set the Back Style to Transparent e Select each field in the Detail section and click and drag the left edges to resize the fields to resemble this slide e Align the fields in the Detail section underneath the corresponding label from the Page Header section Page 118 of 128 Step 6 Design Report Access Average Length of Enrollment and 3Ms Completed for Partnerships Ever Served by Program Program Provider FSP Youth 434G 438C ALL PROVIDERS AU PROVIDERS AU PROVIDERS AU PROVIDERS FSP Adults amp Older Adult 3622 362 3688 3688 3581 3681 3504 3604 3607 3811 AU PROVIDERS AU PROVIDERS 36CH 3604 AU PROVIDERS 9 View the report in print preview If formatted following the directions listed previously the report will resemble the example displayed in this slide Adjust the orienta
13. one page in the Zoom section of the ribbon The Close Print Preview option will exit the partner level report and return you to the front page Note The margins of the partner report are set for printing on standard printers Adjusting the margins may affect the quality of the report However if the report prints extra blank pages on your printer then slightly adjusting the margins may solve the issue Page 60 of 128 Partner Level Report Data Displayed Through 1 15 2012 PARTNER INFORMATION Partner Name John John 063 Partnership Date 4 7 2009 Daysin Partnership Total 3Ms Ove County Partner ID 1000000063 Partnership Status Inactive Global 10 Sta 760s c3 16 4e05 9001 481807 ac7 1c DateStatusChange 11 23 2010 CSI Number oo0o00063 Program Wraparound Gender M Starting Age Group TAY Ending AgeGroup TAY DCR Date of Birth 5 7 1982 CSI Date of Birth 5 7 1992 CSINewRace American Native or Alaska Native Coordinator ID laura Laura Provider Ste I0 1234 PAF Status Pending Referred 8y Social Services Agency CSI Old Race American Native Hispanic Y Cortidential Protected Health Information Page1d9 The first page of a nine page example report is displayed in this slide At the top of the RPR template the Data Displayed Through date signifies the last time the data in the PLD templates was updated from FSP DCR data file downloads The bottom right hand corner of the report will display the page number
14. 128 o D6 Emergency 3Ms Admin Changes l D1 Residential D2 Education D3 Employment D4 Financial D5 Legal D6 Emergency D7 D8 Health Substance D9 ADL D10 1an EMERGENCY INTERVENTIONS IN 12 MONTHS BEFORE FSP Assessment Age Group Time Period No of Physical No of Mental Related Date Related Emergencies Emergencies Past 12 1 EMERGENCY INTERVENTIONS DURING FSP Assessment Age Group Time Period Daysin FSP Date of Emergency Type Emergency 1 16 2009 Adult During FSP KET 144 1 12 2009 Mental Substance 2 9 2009 Adult During FSP KET 166 2 3 2009 Physical Health 2 9 2009 Adult During FSP KET 170 2 7 2009 Physical Health The D6 Emergency tab displays the past 12 months of emergency interventions and a list of all emergency intervention KET assessments during the program In this example the partner reported one mental substance related emergency in the 12 months before the partnership During the partnership the partner had a mental substance related emergency at 144 days in the partnership and the partner had physical health emergencies on days 166 and 170 in the partnership Page 51 of 128 Browse Partner Data Template o D7 amp D8 Health and Substance 3Ms Admin Changes D1 Residential D2 Education D3 Employment Da Financial ps Legal D6 Emergency D7 D8 Health Substance D9 ADL D10 iant HEALTH AND SUBSTANCE ABUSE IN 12 MONTHS BEFORE FSP Assessme
15. 91 of 128 Reestablished Partners e Inspect the Query e Write down the number of records on worksheet Save the query as GlobaliD KeTStatus Description Continuous AP1 Sol 001 000026d9 02d4 4804 8cea f0c0 1 Reestablished No P aes 003acbd3 c8b5 4a48 b2a3 bfO 1 Reestablished No Discontinuous 003b079b 4187 4290 ala7 509 1 Reestablished No 0041866b ce51 41e0 bfd6 669 1 Reestablished No 0048ae1d 8465 4654 9ee6 f7e 1 Reestablished No 00507b13 c3f0 449d 9f4a f89ai 1 Reestablished No 005462e2 f24f 4d85 b15f laf1 1 Reestablished No 0069add3 7072 4f5a a56b 5ae 1 Reestablished No 00952c94 b507 47ef b849 859t 1 Reestablished No 00c3e5f9 017c 4a2d 9576 1166 1 Reestablished No 00ee975b 6436 48b3 aede cl 1 Reestablished No 0118ee70 dd7a 40c4 b223 0fd 1 Reestablished No 014c0569 1317 437d 9cb0 6ea 1 Reestablished No __019a20c3 a855 4888 8556 2c13 1 Reestablished No As seen in this slide move to the datasheet view to inspect the results of the query Write down the number of records i e partnerships ever discontinued and reestablished on the worksheet from Appendix D of the Application Notes document as reference Save the query as AP1_S01_001_Discontinuous Page 92 of 128 ep 2 Calculate Number e Show the number of 3Ms cero submitted for all partners or with at least one 3M e If a partner has never had a 3M submitted then the ann partner will not exist in the sana results of this query a
16. AP1_S03_001_DaysInService DaysInService and CODES_TIMEFRAME DAYS_IN_SERVICE e Outer join from AP1_S03_001_DaysInService GloballD to AP1_S02_001_3MsCompleted GloballD e Outer join from AP1_S03_001_DaysInService GloballD to AP1_S01_001_Discontinuous GloballD After you have created a join on two fields right click on the line indicating the join and change it to an outer join as indicated by a directional arrow as needed Page 98 of 128 ep 4 Combine All Variz by Partner e Add fields to query definition e Enter formulas as new fields in query Continuous_Enrolled IIf Continuous Is Not Null Continuous Yes gt 3Ms_Complete IIf 3Ms_Completed Is Not Null 3Ms_Completed o 3Ms_Complete Iif 3Ms_Completed Is Not Null 3Ms_Completed 0 Continuous_Enrolled If Continuous Is Not Null Continuous Yes _FY_Enrolled FY CODES_DATES 12 z 4 Add all of the fields from the AP1_S03_001_DaysInService to the query definition 5 Add the CODES_TIMEFRAME 3MS_DUE field Note Optionally add any other fields from the CODES_DATE table such as FY_AND_QRT i e fiscal year and quarter by which you may later want to aggregate the data 6 Identify the partners who were not continuously enrolled and set the remaining partners to Yes for the Continuous field Select an empty column of the query definition datasheet and enter the following formula to identify continuous or discontinu
17. Access recommends uniquely identify each record in your table It allows you to retrieve data more quiddy HS COMPLETED INT QRTS COMPLETED EXACT fons p p p p p p p p p p p zi gt Sa Select to Choose my own primary key and select the field DAYS_IN_SERVICE from the drop down menu Select Next Page 85 of 128 Import Excel File to Access DB In the Import to Table box type the table name CODES_TIMEFRAME or CODES_DATES for the appropriate file Select Finish The table is now imported into the PLD templates Page 86 of 128 Working in Microsoft Access e Beginning users can reference Chapter 1 for review of gt Types of Queries Used gt Select Query gt Total Query gt Crosstab Query gt Union Query o Joining Tables and Sub Queries gt Inner Join gt Outer Join Adding Criteria to the Query Definition gt Renaming Fields o Defining New Formulas in the Query Definition gt Query View Options The step by step instructions in these Application Notes are designed as a simplified modular approach to utilizing Microsoft Access for beginning users The step by step instructions for the Application Notes assume the users have some basic knowledge of Microsoft Access These topics will be covered during the process of creating the reports in this presentation Refer to the Application Notes completed 4 5 2012 at Chapter 1 and section Working in Microsoft Access
18. HreWk Wis Hesk Wager employed 45 o o o Twe EMPLOYMENT DURING FSP Assessment Change Date Age Time Daysin Competitive Employment Supported Employment Transitional Employment In House Employment Non Paid Other Employment Date Period FSP Wks HraWk Wager Wks HrsWk Wager Wks HrsWk Wager Wks HrsWk Wager Wks Hra Wk Wks HrsWk Wager employed 8 24 2007 8 24 2007 TAY art of o FSP gt PAF 9 21 2007 9 19 2007 TAY During 26 FSP KET The D3 Employment tab displays the past 12 months of employment information and a list of all employment KET assessments during the program In this example the partner was unemployed for 45 of 52 weeks in the past 12 months and spent 7 weeks in non paid employment for 18 hours per week On day 26 in the FSP a KET assessment reported that the partner began work at 25 hours a week for 7 00 per hour Page 48 of 128 Browse Partner Data Template o D4 Financial ams admin Changes D1 Residential D2 Education D3 Employment 04 Financial DS Legal D6 Emergency D7 D8 Health Substance D3 ADL D10 ADL FINANCIAL SUPPORT IN 12 MONTHS BEFORE FSP Assess Age Time Period Any Loan Any Any Any Any ment Date Caregiver Wages Retire Veteran ment Any Any Any Any Any Housing General TANF SSi SSDI 3 18 2008 Adult Past 12 FINANCIAL SUPPORT DURING FSP Assess Age Time Period Days InFSP Any Any ment Date Caregiver Wages 3 18 2008 Adult Start of FSP PAF 6 1
19. Information Technology Web Services Key Event Tracking Mental Health Mental Health Services Act Mental Health Plan Older Adults of Ages 60 Partnership Assessment Form Protected Health Information Partner Level Data A Client of the Full Service Partnership Read Only Run Partner Report Read Write Security Breach Structured Query Language State System Administrator Transitional Age Youth Ages 16 25 Table of Contents Page 3 of 128 Introduction This training curriculum is designed to be used as a tool for training staff to use the DCR data extract files to evaluate and analyze FSP data Trainers can use the PowerPoint presentations available together with this document s presenter s notes to perform employee trainings Alternatively employees can follow this manual s slide handouts with text narrative for guided self instruction to perform the analyses This training curriculum covers aspects related to extracting data from the DCR importing data into Microsoft Access or Microsoft Excel and building analyses and reports A separate training curriculum addresses using the features of the FSP DCR to submit FSP data This training curriculum is intended for any person who utilizes the FSP DCR data extracts This training requires users to download the Partner Level Data PLD Templates from the Information Technology Web Services ITWS website and is best suited for analysts who have general knowledge of Microsoft
20. PLD Templates are designed to work with one entire set of the four downloaded FSP DCR CSV files PAF Residence PAF Non Residence KET and 3M at a time Therefore when a new updated set of files is downloaded for use with the templates the old data must be cleared from the database With the PLD Template open in Microsoft Access as seen in this slide select the Instructions button from the front page Page 27 of 128 Importing New Data Extracts 3 FrontPage peal w0a 7 Instructions for Importing New Data These instructions allow users to update the data in this database utilizing the four comma separated values CSV data files downloaded from the FSP DCR The four CSV files required to perform this data import are 1 PAF Residential 2 PAF Non residential 3 KET 4 3M Refer to the Chapter 12 of the FSP DCR User Guide for further information regarding downloading the DCR CSV files Before beginning the instructions below close all open forms queries and reports except the FrontPage form Step 1 Remove Old Data click on the button below to run a program ensure the database is cleared of any current data E All of the data is dear from the database Step 2 Compact and Repair the Database click tabase button in the File Menu E i information about DCR_Client Level Report After opening the instructions for importing new data perform Step 1 from the instructions to clear the data from the data
21. Templates open in Microsoft Access select Create gt Query Design 2 In the Show Tables pop up window select the Queries tab and then select and add the query from previous step AP1_S04_001 Variables ByPartner Then select the Close button 3 Right click in the query definition spreadsheet and add the Total row to the query definition 4 As seen in this slide add the following variables and select the following operator in the Total row ProgramDesc Group By ProviderSiteID Group By Continuous_Enrolled Group By GlobalID Count DaysInService Avg 3Ms_Due Sum 3Ms_Complete Sum Page 101 of 128 ep 5 Total by Provide Program and System e Add fields to query definition e Enter formulas as new fields in query e Inspect the results of the query e Write down the number of records e Save the query as AP1_S05_o001_Total_ByProvider Programi ProviderSite Continuous_ CountOfGloballD AvgOfDaysinService 3Ms_Due Sum 3Ms_Comp_Sum 3Ms_PctnComp_ByProvider 1100 Senec 1376790840 Yes oat 28 1100 Senec 1447439906 No 702 7 1100 Senec 1447439906 Yes 536 11 1100 Senec 1497061873 Yes 198 1100 Senec 1699924654 461 1100 Senec 1730345760 1100 Senec 1942490487 1100 Senec 1942490487 5 Now calculate the percent of 3Ms completed for all partnerships aggregated by each combination of program provider and continuously enrolled status Select an empty column of the query definition datasheet and en
22. current data files of FSP DCR data Select from the menu External Data and then Text File in the Import amp Link section Page 30 of 128 Get External Data Text Me Speofy the source of the dats Speoty how and where you want to store the data in the current database C Jmport the source data into a new table in the current database If the speotied table does not exist Access wil create it If the specified table already exists Access might overwrite its contents with the imported dats Changes made to the source data wil not be reflected in the database Append a copy of the records to the table OR partes tothe source data md not be reflected in the database C Unk to the data source by creating a linked table Access wil create a table thot wil mantan a link to the source data You cannot change or delete data that is inked to a text fle However you can add new records As seen in this slide select Browse to locate the PAF Residential file you downloaded from ITWS to your drive network Select to append the data to the existing DCR_PAFRes table from the drop down menu Select OK Page 31 of 128 Importing New Data Extracts 3 import Text Wizard Your data seems to be in a Delimited format If it isn t choose the format that more correctly descrbes your data As seen in this slide select the Delimited toggle in the following screen Select Next Page 32 of 128 Importing New Da
23. forms to include in the download As seen in this slide as step 4 enter a date range for data to be returned The date range for the PAF restricts records based on Partnership Date The date range for the KET and 3M records restricts based on the date the form was completed Date Completed field If a date range is not entered all data will be returned As seen in this slide as step 5 select the Run button to return the file You will notice that the web browser will indicate the request is running It may take several minutes for the data file to download Currently the FSP DCR will only support downloads of files of approximately lt 40 MB Larger files may cause the FSP DCR to produce an error after several minutes Reduce the size of the file by restricting to a smaller date range and retry the download Save the file and return to the menu to download the other data files For XML file type download 3 files including the PAF KET and 3M For CSV file type download 4 files including the PAF Residential PAF Non Residential KET and 3M Refer to the FSP DCR User Manual completed 1 17 2012 at Chapter 12 Data Extraction beginning on page 155 for step by step instructions Page 15 of 128 CSV Data File e Values are separated by commas e Can export into Microsoft Excel Access SPSS SAS etc sessmentGUIO CountyI0 CSINuUmber GlobalID CountyFSPIO Name Par tnershipDate AssessmentDate Dat Birt twelve Homeless_Pas
24. id Sove ARRE Information about DCR_Client Level Report j Save Database As S Save as Adobe POF y Compact amp Repair QF Open Eb Help prevent and correct database file problems by using Compact and pss ap Close Database Compact amp Repair Database QD OCR _chient Level R OCR chient Level R Encrypt with Password Use a password to restrict access to your database Files that use the 2007 T OCR_chient Levet R Microsoft Access file format or later are encrypted BD OCR _client Level R The PLD templates come empty of data and equipped with an encrypted password which will be provided to each Mental Health Director The encrypted password can be changed It is most efficient to decrypt and re encrypt the PLD templates when they are clear of data Refer to the PLD Templates User Manual completed 1 19 2012 at Chapter 1 Changing the Encrypted Password beginning on page 10 for step by step instructions Page 26 of 128 Import New Data Download from DCR csv Files As mentioned previously the PLD Templates come empty of data It is recommended that users store an empty copy of the PLD Templates as a backup in the event that Microsoft Access file corruption issues arise while utilizing the PLD Templates The PLD Templates are designed for use with the DCR CSV files which can be downloaded from the online FSP DCR Users must use the downloaded data files from the online FSP DCR application The
25. in a survey rating the utility of the section of the reports The Application Notes are constructed for the report sections which received the highest score based on the results of the survey and provide instruction to reproduce two of the sub reports from the Data Quality Reports by utilizing the county s downloaded FSP DCR data files at any point in time Page 73 of 128 Application Note 1 Report Average Length of Enrollment and 3Ms Completed for Partnerships Ever Served by Program Provider rovider Continuously Enrolled No Of Partnerships Ave Length in Pem to Date Days 3M Complete FSP Youth 4346 438C ALL PROVIDERS ALL PROVIDERS AU PROVIDERS ALL PROVIDERS FSP Adults amp Older Adult 3622 3622 3688 3688 3681 3681 3608 3604 360v 360v 3811 AU PROVIDERS AU PROVIDERS 36CH ALL PROVIDERS Application Note 1 explains how to create the report Average Length of Enrollment and 3Ms Completed for Partnerships Ever Served by Program Provider similar to Section 2 2 in the Data Quality Reports An example of this report can be seen in this slide The report summarizes the number of continuous or discontinuous partners the average length of partnerships and percent of 3Ms completed by provider and by program Page 74 of 128 Application Note 2 Reports Number of Partnerships by Discontinued Reason for Each Program Provider Tiange ony continous ervoted mmes E
26. information security policies and procedures Perform Step 4 by selecting the gt gt Run Update lt lt button This will run a macro which will make the data available to the templates When prompted enter the date the data was downloaded from the DCR or the date to which the data represents current information for the partner and select OK Note The new data will not be available for viewing through the PLD Templates until the gt gt Run Update lt lt macro is run The macro generates the necessary temporary tables based on a series of Microsoft Access queries Page 36 of 128 Page 37 of 128 This Section Covers o Using the Browse Partner Data Template gt Browse All Partners Data gt Find All Partners Data by Coordinator ID gt Find a Partner s Data Search by ID gt Find a Partner s Data Search by Name o Using the Run Partner Report Template gt View Partner Report Search by ID gt Export PDF Report Search by ID This section covers the utility and features of the Browse Partner Data and the Run Partner Report Templates Page 38 of 128 Browse Partner Data Template o Select a button and enter criteria to find partners Browse Partner Data The BPD template includes several options to browse partners data which can be accessed through the four buttons in the Browse Partner Data section of the front page as seen in this slide You can browse through all pa
27. partner in the pivot table In the same row at column E type in the formula int 100 D C where where the previous formulas were entered Page 126 of 128 User Resources e Getting data in Users have the following resources gt FPS DCR Administrator and User Training Curriculum completed 3 12 2012 o FSP DCR Forms gt FSP DCR Data Dictionary completed 9 15 2011 FSP DCR User Manual completed 1 17 2012 e Getting data out Users have the following resources FPS DCR Data Analysis Training Curriculum completed 4 20 2012 gt FSP DCR User Manual completed 1 17 2012 Data Extraction Instructions gt FSP DCR PLD Templates completed 1 19 2012 o FSP DCR Application Notes completed 4 5 2012 This FSP DCR Data Analysis Training Curriculum covered the data extraction instructions in the FSP DCR User Manual completed 1 17 2012 and the training provided instruction for utilizing the FSP DCR PLD Templates and User Manual completed 1 19 2012 to view a summary of a partner s data The training also reviewed the FSP DCR Application Notes completed 4 5 2012 which provide instruction for creating specific reports from the DCR CSV data extraction files There are additional resources covering aspects of extracting and analyzing the FSP DCR data The FSP DCR Administrator and User Training Curriculum provides an overview of the FSP DCR Forms FSP DCR Data Dictionary completed 9 15 2011 and FSP DCR User Manual
28. report for a specific partnership click on the View Partner Report Search by ID button under the Run Partner Report heading As seen in this slide the first pop up window which appears will allow you to enter search criteria of CSI number If known enter the CSI number and press OK If you would prefer to search by Global ID click OK to skip the search by CSI number Note You can use CTRL V to paste search criteria into the Enter Parameter Value pop up windows Page 58 of 128 Run Partner Report Template Print Preview Acrol As seen in this slide the report will open in print preview mode As seen in this slide the Page selector in the lower left hand corner will allow you to scroll or jump to any page in the report As seen in this slide a zoom slide bar in the lower right hand corner allows you to quickly enlarge or shrink the view of the report on the screen Page 59 of 128 Run Partner Report Template o The Print Preview ribbon for the report Print Preview Acrobat fa m a mm a es M S D a a Ss JJ 33 sm i aaga B Size Margins F Print Data Only Portrait Columns Page Zoom One Two More Excel Text PDF E mail More v up Close Print Page Pages Pages File or XPS Preview om Data As seen in this slide The Print Preview ribbon heading has print options Clicking Print will print the partnership report You can also zoom in or out and view the report by more than
29. the second pop up window To export a report for a specific partnership click on the Export PDF Report Search by ID button under the Run Partner Report heading As seen in this slide an Output To window will appear which is used to designate a location and file name for the report The default name of the report Partner_Level_ Report can be changed Use the navigation pane on the left hand side of the window to select a folder to which the PDF report will be saved Select the Autostart checkbox if you would like the report to be opened automatically when the export and save process is complete Then click OK Selecting Cancel will return you to the front page without exporting the report After the partner ID search criteria are entered the PDF file will be generated and the file will be saved in the selected location If the Autostart checkbox was marked a copy of the report will open in a PDF viewing application Page 70 of 128 Page 71 of 128 This Section Covers o Scope of Application Notes o Overview of Application Notes Reports o Overview of Application Notes Document Appendices o Importing Date and Timeframe Dimension Tables o Steps to Create Application Note 1 in Access This section provides an overview of the Application Notes document and appendices and it covers the detailed steps to create the Application Note 1 report in Microsoft Access Page 72 of 128 plication Notes
30. 01e3d28 57af 4a26 9005 001e9e18 1351 47b0 807f 3MsCompleted 001efb0d d731 431f 9ae4 002220ed 04f8 48ca 9746 c PRN AUN amp OOU H NNNN EB As seen in this slide move to the datasheet view to inspect the results of the query Write down the number of records i e partnerships with at least one or more 3Ms on the worksheet from Appendix D of the Application Notes document as reference Save the query as AP1_S02_001_3MsCompleted Page 94 of 128 nae ep 3 Calculate Length in Program by Partner DCR_PAFnonRes AssessmentGUIO uni Field GlobaltiO ProgramDesc ProviderSitelD PartnershipDate End_Date lif PartnershipStatus 0 C DaysinService DateDiff d Partne a Table DCR_PAFnonRes _PAFnonRes DCR_PAFnonRes DCR_PAFnonRes Sort Show a Criteria or Step 3 involves calculating the number of days a partnership existed Some partnerships may have been discontinued and some may still be active Therefore for active partnerships you will need to identify an end date for the calculation The end date is usually determined as the end date defined when the CSV files was extracted from the FSP DCR The extraction is a static snapshot of all of the partnerships up to the date of extraction 1 With the PLD Templates open in Microsoft Access select Create gt Query Design 2 In the Show Tables pop up window select and add the following tables to the query DCR_PAFnonRes and the DateDat
31. 15 2011 and it can be accessed on ITWS through the MHSA Information menu by selecting the Technical Information submenu and then clicking to download the FSP DCR Data Dictionary Page 7 of 128 Reference Materials e FSP DCR User Manual completed 1 17 2012 HESAN Depercment M Menti Heath e a a Dapartmans ef Masts Currently the DMH Performance Outcomes and Quality Improveme Q ies who are preparing to collect and submit Full Service Partnership FSP Outcomes Assessment data County staff will not t pchnology Web Services ITWS Mental Health Services Act MHSA data system and hasra data until they are certified as having received th MHSA Training page at http www dmh ca gov POQI trainingMHSA asp for more inform 02 16 12 A FSP DCR User Manual was created on 1 17 2012 and it can be accessed on ITWS through the MHSA Information menu by selecting the User Manuals and Instructions submenu and then clicking to download the DCR User Manual Page 8 of 128 Reference Materials e PLD Templates and User Manual 1 19 2012 Currently the DMH Performance Outcomes and Quality Imy s who are preparing to collect and submit Full Service Partnership FSP Outcomes Assessment data County staff well nott hnology Web Services ITWS Mental Health Services Act MHSA data system and recor data until they are certified as having received th MHSA Training page at http www dmh ca gow POQI training MHSA asp for more inform
32. 5 4eer 944 6 15 2009 1 15 2012 Wraparound 1234 8 Yes 1d554b0d e177 4bb 94 6 13 2007 9 15 2007 Wraparound 1234 1 No 1f32bb6c add9 4d2e 10 13 2009 1 15 2012 Older Adult FSP 1234 0 Yes 2353a1d1 35f9 4921 5 18 2011 1 15 2012 Wraparound 1234 1 Yes 23917306 e067 4b8 7 14 2009 1 15 2012 TAY FSP 1234 7 Yes A va SOnwo rounspeanBeanunb a Move to the datasheet view to inspect the results of the query Write down the number of records i e total number of partnerships on the worksheet from Appendix D of the Application Notes document as reference The number of records in this query should match the number of records in the AP1_S03_001_DaysInService query Save the query as AP1_S04_001_ Variables _ByPartner Page 100 of 128 Program and System AP1_S04_001_Variables_ByPartner GloballD DaysinService PartnershipDate End_Date ProgramDesc ProvidersitelD 3MS_DUE 3Ms_Complete 3Ms_PctnComp_ByPartner Continuous_Enrolled FY_Enrolled FY_Qrt_Enrolled QRT_CURRENT ProgramDesc ProviderSitelD Continuous_Enrolled GloballD DaysinService 3MS_DUE 3Ms_Complete APL_SO4_001_Variable AP1_SO4_001_Variable AP1_SO4_001_Variable AP1_SO4_001_Variable AP1_SO4_001_Variable AP1_SO4_001_Variable AP1_S04_001_Variable Group By Group By Group By Ascending Ascending Ascending 4 4 4 4 Step 5 involves aggregating the data from step 4 into summaries by provider program and the entire system i e county 1 With the PLD
33. 8 2008 Aduit During FSP 2 3M The D4 Financial tab displays the past 12 months of financial support information and a list of all financial support 3M assessments during the program In this example the partner had financial support in the form of spouse general and food stamps in the 12 months before the FSP At the start of the FSP the partner was receiving financial support from spouse and food stamps At the second 3M assessment the partner reported financial support from wages and SSI Page 49 of 128 Browse Partner Data Template o Ds Legal 3Ms Admin Changes l D1 Residential D2 Education D3 Employment D4 Financial DS Legal D6 Emergency D7 D8 Health Substance l D9 ADL D10 ADL LEGAL ISSUES IN 12 MONTHS BEFORE FSP Assess Age Time Period ment Date 6 7 2007 Child Past 12 LEGAL ISSUES DURING FSP Assess Age Time Period Days In ment Date FSP 6 7 2007 Start of 0 FSP PAF 9 7 2007 During FSP 3M 12 7 2007 During FSP 3M During 197 12 21 2007 FSP KET The D5 Legal tab displays the past 12 months of legal issues and a list of all KET and 3M assessment legal issues during the program In this example the partner was arrested one time in the 12 months before partnership and was on probation upon entering the FSP No changes were reported on the first two 3M assessments On day 197 in the FSP a KET assessment reported that the partner went off probation Page 50 of
34. 8c92 89 000000091 23ef ad97 f f 6f 49e1 9bf c 6011ed007834 000000091 b7 539ef 2 6f Sc 4890 abf f 74a7707e6c45 89 000000047 244 5ca8c bba2 4ff0 941b d68784892Fd4 00000004 Z a62bed8b df22 4d5f baed 18628f76701b 89 000000098 2579cd57 9100 4949 8bb5 f7d94aa4a167 00000009 020 19 27 2010 9 27 014b4931 6153 4a5b 84 5f dbb5e7b9c79a 89 000000109 261a339a 0712 4a13 90eb 5e667 412ca9 000000109 031 3 19 2009 3 1 jaSG6ad6f 2 f Sbb 4410 876b 40d6e10ce401 89 000000148 2e1437b4 c1b4 4be5 a8b0 e24 5ecc2b7a5 00000014 160551f 54 8313 440d a247 467f0a552784 89 000000140 2e17d1d8 fa2d 4 56e bba4 5f 32dd19686c 000000140 if 6a3ca8d c781 4a13 89a2 2852Fc4c5a96 89 000000093 2e480185 b867 4204 bc09 c13a57a290e8 000000093 180830ca5 7fd6 4907 853c 00a96462f 348 89 000000023 343ef4e2 8883 4618 8e56 amp c91a9089fe0 000000023 John 023 7 936778b7 3d38 4d50 8481 a7dddb559623 89 000000060 3624de62 735f 4c8a 8655 7ad37a6f1254 000000060 3ohn 060 10 1 2008 10 1 This slide displays an example of the CSV file contents opened in the Notepad application Page 16 of 128 XML Data File e Values and data structure defined e Can import into Microsoft Access SQL Server or other relational database supported environment Ej 0cR_3M_FY0809 xml Notepad E Fie Edt Format View Help lt OCR gt lt xs schema id 0cR xmlns xmIns xs http www w3 org 2001 xMLSchema xmlns msdata urn schemas microsoft com xml msdat
35. Download the Application Notes Full Service Partnership FSP document from Data Collection amp Reporting DCR Data Extract Analysis Traini ITWS and follow nE tr ctions for Training Slides and Presenter s Notes for instru Analyzing Data and Creating Reports from DCR creating new reports Data Extracts The Slowing Cocument wes tunded Dy the Mente Hesith Services Oversignt end Accouneability Commission The Application Notes provide instruction for using Microsoft Access and or Microsoft Excel applications to analyze the Full Service Partnership FSP DCR CSV data downloads These instructions provide two examples for analyzing the DCR CSV files to create reports for partners length of service by provider In the first Application Note instructions are provided for creating a report which identifies the average length of service for partners by program and provider This report also identifies the percent of quarterly 3M assessments which were completed for the partners The second Application Note provides instruction to create a report for the average length of service for partners based on reason of discontinuation from partnership In January 2012 County Data Quality Reports were provided for each county region based on the data reported to the FSP DCR The purpose of the County Data Quality Report was to describe all of each county s data and to identify inconsistent or outlying data points Counties participated
36. Excel Microsoft Access and the formulas used by these applications An outline of the training curriculum appears below gt FSP Data Analysis Training Curriculum Outline o Downloading FSP DCR Data Data Extraction Overview of DCR Data Extract Files o Utilizing the Partner Level Report Templates Importing New Data Utilizing the Browse Partner Template Utilizing the Run Partner Report Template o Developing the Analytical Report in Access from Application Note 1 Creating Supporting Queries Basic Statistical Analysis Formulas Presenting Data in a Report o Developing the Analytical Report in Excel from Application Note 1 Creating New Fields Generating a Pivot Table Statistical Measures by Partner Statistical Measures for All Page 4 of 128 This training was funded by the Mental Health Services Oversight and Accountability Commission MHSOAC Page 5 of 128 Training Overview Download FSP Data from DCR Import FSP Data to PLD Templates Generate Standard Reports Create New Reports This training covers these main areas of the FSP DCR User Manual completed 1 17 2012 the Partner Level Data PLD Templates User Manual completed 1 19 2012 and the Application Notes completed 4 5 2012 Page 6 of 128 Reference Materials e FSP DCR Data Dictionary 9 15 2011 04 09 07 08 14 07 07 10 07 01 26 06 02 08 06 02 16 12 An updated version of the FSP DCR Data Dictionary was completed on 9
37. ID Provider Site ID PAF Status Complete Referred By Jail Prison CSI Old Race Hispanic N DOMAIN DATA FOR Total 3Ms Due 3 3Ms Admin Changes D1 Residential D2 Education D3 Employment D4 Financial 05 Legal D6 Emergency 07 D8 Health Substance 09 ADL D10 ADL The BPD template allows users to browse through the data of all of the partnerships As seen in this slide the BPD template displays an overview of the partnership information and a tab for each domain By selecting each tab users can review all of the FSP data reported for a partnership Alternatively users can select one tab of domain information such as the D5 Legal domain for example and then scroll through each partnership to browse just the data reported for that domain e g legal for each partnership Page 22 of 128 Browse Partner Data Template e Four Ways to Access the BPD Template Browse Partner Data Run Partner Report Import New Data Download from DCR csv Files There are four buttons on the front page to access the BPD Template which will help filter the number of partnerships displayed in the template The Four Ways to Access the BPD Template 1 Browse All Partners displays all partnerships downloaded from the FSP DCR 2 Find All Partners Data for a Coordinator displays all partnerships for a coordinator based on entering any part of the coordinator ID 3 Find a Partner s Data Search by ID displays th
38. Period Ed for Ed for Attend Grades Suspen Expul Ed Dist Reas Goals Past 12 N A N A Good Average 0 0 CURRENT EDUCATION STATUS Date of Age Time Period Daysin Highest Ed for Ed for Change FSP Grade Emo Anoth Dist Reas 7 29 2008 Child Start of FSP O Shgrade No No PAF 10 14 2008 10 8 2008 Child During FSP 10 17 2008 10 17 2008 Child During FSP 3Ms 5 4 2009 5 4 2009 Child During FSP 3Ms The D2 Education tab displays the past 12 months of education information and a list of all education KET and 3M assessments during the program In this example the partner entered the FSP in 8t grade with very good grades The partner had average grades during the previous year The partner was suspended after 71 days in the partnership as reported on a KET and the partner s grades were below average at the first 3M assessment at day 80 of the partnership The next 3M assessment submitted was at 279 days into the partnership and the partner s grades were good Page 47 of 128 Browse Partner Data Template o D3 Employment 3Ms Admin Changes D1 Residential D2 Education 03 Employment Da Financial D5 Legal D6 Emergency D7 D8 Health Substance D9 ADL D10 iaou EMPLOYMENT IN 12 MONTHS BEFORE FSP Competitive Employment Supported Employment Transitional Employment In House Employment Non Paid Other Employment Un Ws Hok Wis Hra Wk Wageltir Wis HrwWk WagelHr Wks HrsWk Wager Wks
39. TS KETStatus Codes_KetStatus Description fields to the query definition spreadsheet 5 As seen in this slide create a fourth variable by typing the following into a blank column of the query design Continuous No This will create a variable in the query named Continuous and assign the value No to all records 6 As seen in this slide add the criteria of 1 to the KETStatus field of the query This will limit the criteria to pull back only those records in which a partnership was reestablished Page 90 of 128 Reestablished Partners e In the SQL View add the word Distinct to the query DCR_KETs GlobalID DCR_KETs KETStatus Codes_KetStatus Description No AS Continuous FROM Codes_KetStatus INNER JOIN DCR_KETs ON Codes_KetStatus Code DCR_KETs KETStatus WHERE OCR_KETs KETStatus 1 Running the query now will bring back all KET records for a reestablishment activity A partnership may have been discontinued and reestablished multiple times However we only want a list of unique partnership identifiers for partnerships with any reestablishment activity regardless of how many times it occurred Therefore to remove duplicates we will choose to select distinct records As seen in this slide move to the SQL View and insert the word DISTINCT after the word SELECT This will remove any duplicate rows from our query resulting in one record per GlobalID with a KETStatus equal to 1 Page
40. YSINSERVICE 3Ms_Due Sum 3Ms Comp Sum 3Ms_PctnComp_ByProvider Adult FSP 1234 Yes 61 498 310 202 65 2 Adult FSP 22 ALLPROVIDERS zz No 2 1 Adult FSP 22 ALL PROVIDERS zz 310 202 FSP 1234 11 FSP 2z ALL PROVIDERS zz 11 Older Adult FSP 1234 Older Adult FSP 27 ALL PROVIDERS zz TAY FSP 1234 TAY FSP 1234 TAY FSP 22 ALL PROVIDERS zz TAY FSP zz ALL PROVIDERS zz Wraparound 1234 Wraparound 1234 Wraparound 22 ALLPROVIDERS zz No Wraparound zz ALL PROVIDERS zz Yes 27 ALLPROGRAMS zz z7 ALL PROVIDERS zz No 22 ALLPROGRAMS zz 2z ALL PROVIDERS zz Yes wo w o SwSwowren Be Bu er G Su 17 Move to the datasheet view to inspect the results of the query The number of records should equal the sum of records from the previous three queries created in this step 18 Save the query as AP1_S05_004_Total_ALL Page 108 of 128 Step 6 Design Report e Export last query to Excel or e Create Access report The last step in this process is to design the report The final query AP1_S05_004_Total_ALL holds all of the data for the final table This query can be exported into Microsoft Excel for formatting or into Microsoft Access to create a report template Page 109 of 128 Step 6 Design Report Excel i AP1_S01_001_Discontinuous Right mouse E api_so2_001_3MsCompleted 3 AP1_s03_001_DaysinService click on the 3 Api_so4_001_variables_ByPartner tS
41. a gt lt xs element name DCR msdata IsDataSet true msdata Locale en uS msdata Enforceconstraints False gt Ie COMP AGB ee lt xs choice minoccurs 0 maxoccurs unbounded gt lt xs element name Assessment gt lt xs complexType gt lt xs Sequence gt lt xs element name AssessmentID type xs long minoccurs 0 gt lt xs element name AssessmentGUID msdata DataType System Guid mscorlib version 2 0 0 0 Culture neutral Publ lt xs element name CountyID minoccurs 0 gt lt xs simpleType gt lt xsirestriction base xs string gt lt xs maxLength value 2 gt lt xs restriction gt lt xs simpletype gt lt xs element gt lt xs element name CSINumber minoccurs 0 gt lt xs simpleType gt lt xsirestriction base xs string gt lt xs maxLength value 9 gt lt xs restriction gt lt xs simpleType gt lt xs element gt lt xs element name GlobalID msdata DataType System Guid mscorlib Version 2 0 0 0 Culture neutral Publickey7 This slide displays an example of the SML file contents opened in the Notepad application Page 17 of 128 Page 18 of 128 This Section Covers o Overview of the PLD Templates Browse Partner Data Template Run Partner Report Template oEncryption of PLD Templates o Importing Data Extracts to PLD Templates This section covers an overview encryption and data importing for the PLD Templates Page 19 of 128 PLD Templates Overview
42. aDownload table Then select the Close button Do not join the tables 3 As seen in this slide select and add the following fields to add to the query DCR_PAFnonRes GloballD DCR_PAFnonRes ProgramDesc DCR_PAFnonRes ProviderSiteID DCR_PAFnonRes PartnershipDate 4 If the DCR_PAFnonRes PartnershipStatus 0 then the partnership is currently discontinued and the date the partnership was discontinued is stored in the DCR_PAFnonRes DatePartnershipStatusChange field Otherwise the partnership is active and the end date defined when the CSV files was extracted should be used to calculate the length of partnership As seen in this slide select an empty column of the query definition datasheet and enter the following formula to identify the correct end date Page 95 of 128 to use when calculating the length of partnership End_Date IIf PartnershipStatus 0 DatePartnershipStatusChange DateDataDownload DateDownload 5 Now calculate the length the partnership was in service by counting the number of days between the partnership start date and the end date As seen in this slide select an empty column of the query definition datasheet and enter the following formula to calculate the number of days the partnership existed DaysInService DateDiff d PartnershipDate End_Date Note DateDiff is a MS Access function which helps calculate the difference between two dates The function takes three parameters The first p
43. al Support During FSP Amesonet ap Time Period Cone Caw Ga ARS TAY o 7 20 2008 TAY ae 20 8 2008 TAY me v 9 23 2020 TAY rs 9 24 2020 TAY s am Wages Moase faving Cho Ome Rere Weena tan Kos Gemes Food e Wages pase Baring Cms OMe Rere vena Lan oe Soort Faniy mee Days in Partnership Partnership Date Total 3Ms Due 4 7 2009 Amy Am Am Am Amy Amy ay Any Any an any Am Am Aam am Am Aam ay ay Ang Hos e Page 6of9 This slide displays the Financial Domain page of the report Page 66 of 128 PARTNER INFORMATION Partner Name John john 063 Partnership Date 4 7 2009 Days in Partnership Total 3Ms Ove Legal Domain 12 Months Before FSP DOLA ae Time Petiog menoz 4 7 2008 vay Pest a2 Legal Issues During FSP anes Age Time Petiog Oms Wico Wico No Oepen No Depen No No metoe r ss sas Yar cesom emh Doeners Depercers Cos FomerCawe Rentes Aomes 4 7 2008 YO sno o v No o Fap Par zanos Drag 20 FSP aT 7 30 2009 i uring a m 30 8 2008 Owing i gt vaso y Owing ate a m sanoo orig 333 F m Page 70f9 This slide displays the Legal Domain page of the report Page 67 of 128 PARTNER INFORMATION Partner Name John John 063 Partnership Oste 4 7 2009 Days in partnership 595 Total 3Ms Ove 6 Emergency Intervention Domain 12 Months Before FSP AO AgO Group Dato j Time Period No of icai Related No Of Mental Related Emergences Emergences ann Tar Poe 2 o
44. arameter d defines the calculation should return the number of days The second parameter is the start date for the calculation and the third parameter is the end date for the calculation Page 96 of 128 sp 3 Calcula Program by Partner e Inspect the results of the query e Write down the number of records e Save the query as AP1_S03_o001_DaysInService GlobalID ProgramDesc ProviderSitelD PartnershipDate End_Date DaysinService 074f58f7 9aa0 498c TAY FSP 1234 11 2 2010 1 15 2012 439 Ob9cf2cb c724 41ck Wraparound 1234 4 7 2009 8 18 2010 498 Oecb79a5 1190 4b6 Wraparound 1234 6 7 2007 6 20 2008 379 Of3e5f66 89ef 4657 Adult FSP 1234 12 3 2010 1 15 2012 408 115a7401 4320 48c Adult FSP 1234 7 8 2008 1 15 2012 15d2873f 0591 4db TAY FSP 1234 3 12 2008 11 20 2008 253 19cb7956 3373 48d Adult FSP 1234 12 17 2008 2 10 2010 1a312cba 37e5 4e2 Wraparound 1234 1 11 2011 1 15 2012 1bSeaee0 9b4d 45 Wraparound 1234 9 1 2006 1 17 2008 1b9ce01c 8d75 4ee Wraparound 1234 6 15 2009 1 15 2012 1d554b0d e177 4bt Wraparound 1234 6 13 2007 9 15 2007 1f32bb6c add9 4d2 Older Adult FSP 1234 10 13 2009 1 15 2012 2353a1d1 35f9 492 Wraparound 1234 5 18 2011 1 15 2012 Move to the datasheet view to inspect the results of the query Write down the number of records i e total number of partnerships on the worksheet from Appendix D of the Application Notes document as reference Save the query as AP1_S03_001_DaysInService Page 97 of 128
45. ata Search by ID Find All Partners Data Find a Partner s for a Coordinator Search by Name Enter Parameter Value i Enter part of coordinator ID 3 FrontPage 3 Partner_Info_SearchbyCoor The Find All Partner s Data for a Coordinator will apply a filter to the BPD template based on matching the criteria entered with a coordinator ID As seen in this slide selecting this button will bring up the Enter Parameter Value pop up window Typing in any portion of a coordinator ID in the box will apply the filter to the BPD template The BPD template will then display only those partnerships with associated coordinator IDs containing the entered text as any consecutive part of the coordinator ID For example entering Smith in the Enter Parameter Value pop up window will display partnerships for all coordinator IDs with any portion of Smith as part of the ID As seen in this slide the BPD template will open in a new tab titled Partner_Info_SearchbyCoor with partnership records that are exclusive to the coordinator ID search performed The number of partnerships available can be seen in the Record box in the lower left hand corner of the screen Page 55 of 128 Browse Partner Data Template o Find a Partner s Data Search by ID Button Browse Partner Data Browse All Partners Find a Partner s Data Data Search by ID Find All Partners Data Find Enter Parameter Value 2 limesa
46. base By selecting the gt gt Clear Data lt lt button a macro will run to clear all of the existing data in the database If no data exists in the database performing this step will not harm the PLD Templates As seen in this slide a pop up message will alert you when the process is complete Note Existing data must be cleared before using the PLD Templates with new data downloads If it is desired to use the PLD Templates with both the historic and current sets of downloaded DCR CSV files then a copy of the PLD Templates file can be made before clearing the historic data such that one file will contain the historic data and the other will be cleared and used to import the current data Page 28 of 128 Step 1 Remove Old Data click on the button below to run a program ensure the database is cleared of any current data gt gt Clear Data lt lt Compact and Repair the Database click on the Compact and Repair Database button in the File Menu OCR Geert cee Rapert 2011109 neryeted Ontabal Creme Ces ima Information about DCR_Client Level Report Campra amp Repair Map percent and coorect otabera fe prvbiews by wang Compact ent tapae Step 3 Import New Data Files previously downloaded from the FSP DCR m As seen in the image below select the External Data tab above and click on the Text File to invoke the import wizard o 3 99 9 SERRE oIR 58 KS Y 4 XM File vw ava t Me Saved inked Table Excel Acces
47. ce ris EAEE Enter Date of Data File Download 15 201 Repeat Step 3 to import the remaining files into the tables as follows 1 15 2014 gt Import the PAF Non residential file into the DCR_PAFnonRes table gt Import the KET file into the DCR_KETs table gt Import the 3M file into the DCR_3M table Note If you make a mistake importing the files start over from step 1 to clear all data from the database Step 4 Run Data Update click on the button below to run the database update to utilize the new data files Enter the date the new data files were downloaded from the FSP DCR when prompted Soa TORU freee This may take several minutes A message will appear when the process is complete application as well as any reports generated from this data contain Protected Health Information PHI which must be protected with administrative technical and physical safeguards as required by law pursuant to the Health Insurance Portability and Accountability Act of 1996 HIPAA Security Rule 45 CFR Part 160 and Part 164 Subparts A and E the Health Information Technology for Economic and Clinical Health Act HITECH Act the California Security Breach Information Act SB 1386 and other state and federal law Unauthorized use or disclosure is prohibited It is the responsibility of the user of the PLD Templates to ensure the confidentiality of the PHI of partners in accordance with the aforementioned laws and the user s organization s
48. creating reports One way to quickly define partnership timeframes e g number of quarters a partnership has completed or date ranges e g fiscal years is to create a table with definitions of the several combinations of groupings which may be required depending on the purpose of the report Reference Kimball Ralph Ross Margy 2002 The Data Warehouse Toolkit The Complete Guide to Dimensional Modeling Second Edition New York Wiley Computer Publishing Before starting load the four DCR CSV files into the PLD Templates Import the tables from Appendix A required and Appendix B optional of the Application Notes document into the database and name them as noted in this slide As seen in this slide the data tables can be accessed by selecting the paperclip icon in the PDF Application Notes document and double clicking on the file Page 77 of 128 Print Worksheet in Appendix D Appendix D Query Record Count Worksheet Application Note 1 Query Name Descriptio AP1_S01_001_Discontinuous Partnerships discontinued and reestablished AP1_SO2_001_3MsCompleted Partnerships with at least one or more 3Ms AP1_S03_001_DaysinService Total number of partnerships AP1_S04_001_Variables_ByPartner Total number of partnerships AP1_S05_001 Totals_ByProvider Total number of providers by program by continuous partnership status AP1_SOS_002 Totals_ByProgram Total number of programs by continuous partnership status One record for all cont
49. e p D1_ResChangeDates_Thl If the specified table exists Aereas or the rece fo bre tie If the table does not exist Access will create it Changes made to the source data will not be reflected in the C Link to the data source by creating a linked table Access will create a table that will maintain a link to the source data in Excel Changes made to the source data in Excel will be reflected in the linked table However the source data cannot be changed from within Access Select Browse to locate the Excel file Select to Import the source data into a new table in the current database Select OK Page 82 of 128 Import Excel File to Access DB Sy Import Spreadsheet Wizard z Your spreadsheet file contains more than one worksheet or range Which worksheet or range would you like Show Worksheets ONTHS COMPLETED INT QRTS_COMPLETED_EXACT 000 011 022 033 044 055 066 077 088 099 110 Select the worksheet and select Next Page 83 of 128 Import Excel File to Access DB a Import Spreadsheet Wizard z Microsoft Access can use your column headings as field names for your table Does the first Check the First Row Contains Column Headings and select Next Select Next again on the next screen Page 84 of 128 Import Excel File to Access DB Ea Import Spreadsheet Wizard z Ez that you define a primary key for your new table A primary key is used to Microsoft
50. e partnership data for a partner by CSI number or by FSP DCR Global ID 4 Find a Partner s Data Search by Name displays the partnership data for a partner based on entering any part of a partner s name Page 23 of 128 The RPR template produces a printable report of one partnership s data As seen in this slide the RPR template displays an overview of the partnership information in a multi page report which is separated into sections by domain Page 24 of 128 Run Partner Report Template e Two Ways to Access the RPR Template Import New Data Download from DCR csv Files Users can choose to view the partnership report online print the report or save a PDF of the report on their secure drive network A user must have available either the CSI number or the Global ID of a partnership in order to access this template There are two buttons to launch the RPR template The Two Ways to Access the RPR Template 1 View Partner Report Search by ID Displays a printable partnership data report for a partnership by CSI number or by FSP DCR Global ID 2 Export PDF Report Search by ID Saves a partnership data report as a PDF at a chosen location on the user s drive network Page 25 of 128 Encryption Before importing data into the PLD Templates you can encrypt the database with a password Access 2010 or later OCR_Client Level Report_20111026 Encrypted Copy Datat Home External Data Acrobat
51. e6dfa863d 187 5 Select anywhere within the data in the spreadsheet and choose to insert a pivot table into a new worksheet by selecting Insert gt Pivot Table from the menus The data selection will expand to the entire table of data within the spreadsheet 6 This step will summarize the number of 3Ms completed and 3Ms due for each partner Add the field Age_Group to the Report Filter box Add the field GloballD to the Row Labels box Add the DaysInService to the Values box and change the Value Field setting to Average Add the 3MsDue to the Values box and change the Value Field setting to Average Add the AssessmentDate to the Values box and change the Value Field setting to Count Page 124 of 128 Using Excel for Analysis e Calculate Percent of 3Ms Complete by Individual A AgerGroup Aa z Row Labels v Average of InService Ay eof 3MsDue Count of AssessmentDate Percent of 3Ms Complete 020ce66b aeel 481e b9aa cc 317 3 1 INT 100 D4 C4 0226f76d 3169 421b b31b bft 143 1 1 02311209 29bd 48cc a5dd 35 200 023f178d 9e28 4f4c ac81 be4 114 02417723 e442 470b a740 4b 1204 024f8841 8e95 4be0 Sea8 221 025e870c 92e0 402d ab79 65 025ffa36 735d 4058 8959 7f2 028ad70f f363 4e72 94a5 206 02984f79 94c6 4e62 bcb6 0be 02a8c9f9 caea 4686 a280 a3fc 02be468d 3661 4c18 b2f0 004 p Lond BeustdnuBen m owwe nwt w 7 To calculate the individual percent of 3Ms completed by partner divide the value in column D b
52. for a Coordinator Se Enter CSI Number or press OK to skip and use Global ID on next screen Loc _concet y Enter Parameter Value e jitas Enter Global ID or press OK to skip Ex ca D 3 FrontPage 3 Partner_Info_SearchbyID The Find a Partner s Data Search by ID button will apply a filter to the BPD template based on matching the criteria entered with a partner ID There are two options for entering partner IDs You can enter a CSI number in the first pop up window or enter a Global ID in the second window To search for a specific partnership by partner ID number click on the Find a Partner s Data Search ID button under the Browse Partner Data heading As seen in this slide the first pop up window that appears will allow you to enter search criteria of CSI number If known enter the CSI number and press OK If you would prefer to search by Global ID enter nothing in this first window and click OK to skip the search by CSI number As seen in this slide the second pop up window that appears will allow you to enter search criteria of Global ID If a CSI Number was already entered in the previous pop up window then click OK to skip the search by Global ID If the CSI Number was not entered then enter a Global ID for a partnership and press OK to continue As seen in this slide the BPD template will open in a new tab titled Partner_Info_SearchbyID with the partnership information for the partner
53. h are still active Under the subheading of Partner Information the first partnership s partner information will be displayed In this slide the example partnership was still active and had existed for 117 days at the time of data download on 1 15 2012 There may be many partnerships available to browse The total amount of partnerships and the current order of the partnership being viewed are displayed at the bottom left hand corner of the report in the Record navigation box As seen in this slide the first of 157 total partnerships is displayed in this example You can navigate to the next or previous partnership by clicking on the arrows to the left and right of the Record navigation box Additionally you can locate a partnership in a specific order by clicking on the Record navigation box and manually typing in a number within the range of the available partnership count and pressing Enter The form will jump to the partnership associated with that order number This may be useful when the partnerships are sorted in the form by a specific order By default the partnerships are Page 40 of 128 sorted by partnership status and partnership date such that the active partnerships with the most recent partnership dates appear first However the partnerships can be sorted by other fields in the template Refer to the PLD Templates User Manual completed 1 19 2012 at Chapter 3 The Browse Partner Data BPD Template beginning
54. his code into the SQL view of an empty Microsoft Access query within the PLD Templates will quickly recreate the queries designed in these Application Notes This code is provided for user convenience in testing and troubleshooting or recreating the design of the queries Page 89 of 128 ep 1 Create List o Reestablished Partners DateProgmChange DatePsclDChange CoordinatoriD DateKETStatusChange PartnerShipStatus DatePartnershipStatusChange DiscontReason AB2034ChangeDate AB2034 GHIChangeDate GHI Field GloballD KeTstatus Di Table DCR_KETs DCR_KETs Codes_KetStatus Sort Show ica B Criteria Ey or The first step for this report is to create a list of all of the partners who were discontinued and reestablished for the same partnership These partners will be counted as No in the Continuously Enrolled column of the report Partners not included in this list will be counted as Yes for that column 1 With the PLD Templates open in Microsoft Access select Create gt Query Design 2 In the Show Tables pop up window select and add the following two tables to the query DCR_KETs Codes_KetStatus Then select the Close button 3 As seen in this slide select the KETStatus field within the DCR_KETs table and drag and drop the field onto the Code field of the Codes_KetStatus table to create an inner join between the tables 4 As seen in this slide add the DCR_KETS GloballD DCR_KE
55. ing FSP Aseme Daeof Age TimePeid Om Highest Grades Suspen Eph Nth bh b b b b Com Date range hfs Gade von Son School Hgh Tech Comm Grad Other pleted School Shool College Schoo School Pom 477008 4 7 2008 tav osn grace 7 20 2009 7 30 2009 TaY 20 8 2009 10 8 2009 TaY waso 6 23 2020 TAY 9 24 2020 8 29 2020 tav S 24 2010 24 2010 TAY Confid ential Protected Hearth information Page 4ofs This slide displays the Education Domain page of the report Page 64 of 128 PARTNER INFORMATION Partner Name John John 063 Partnership Date 4 7 2009 Days in Partnership Total 3Ms Ove Emplovment Domain 12 Months Before FSP Assessment Change Age Time Days Compete Emo Sepoorted Emo TransBional Emo ieHowseEmplownest NowPaid Or rer E moloymest Date Dae Pesod FSP Wio Haik Waset Wks Heal Wis Heal Waselic Wes Hirsti W Wks Wis Malik Wace annos vay Pesta Employment During FSP 47 2008 amotar sane Amessmest Change Age Time Days Compatie Ero Supported Emo Transitional Emo intiouzeEmoloymest NowPaid Other Employment Ue Dare Date Petod FSP Wic Hak Wacetir Wiks HealWk WacelMr Wks Halk Wacehr Wks HealWk Wacett Wis HaWk Wis Minx W ves FSP Par Page Sofs This slide displays the Employment Domain page of the report Page 65 of 128 PARTNER INFORMATION Partner Name John John 063 Financial Domain 12 Months Before FSP Assesoment ap Tine Periog Cate 4 7 2008 TAY Peni Financi
56. inuous and one record for all discontinuous partnerships in AP1_S05_003_Totals_Syste entire system AP1_S05_004 Totals ALL Combination of providers prog 1s and system queries Application Note 2 Query Name Description AP2_S01_001_Discontinuous Number of partnerships discontinued and reestablished AP2_S03_001_DaysinService Total number of partnerships AP2_S02_001_DiscMaxDate Number of partnerships currently discontinued AP2_S02_002_DiscMaxDate_CreatedLast Number of partnerships currently discontinued AP2_S02_003_DiscReason_Last Number of partnerships currently discontinued AP2_S04_001_DaysinService_ExciDiscontinuous Number of continuous partnerships AP2_SOS_001_DaysinService_DiscReason Number of continuous partnerships AP2_S06_001_Variables_ByPartner Number of continuous partnerships AP2_S07_001_Totals_ByProvider_n Total number of providers by program AP2_SO7_002_Totals_ByProgram_n Total number of programs AP2_S07_003_Totals_Syst n One record for entire AP2_S07_004_Totals_All_n Combination of providers pro Is and syst AP2_ 08_001_Totals_ByProvider_Length Total number of providers by program AP2_S08_002 Totals _ByProgram_Length Total number of programs AP2_S08_003_Totals_Syst ti One record for entire AP2_S08_004 Totals_All_Length Combination of providers programs and system queries H a LIMA LIL FSP Partner Level Data Templates User Manual Appendix D contains a helpful work
57. ithin the table embedded in the tab In this example the partner had five 3Ms due and all five 3Ms were completed Page 44 of 128 Browse Partner Data Template o Admin Changes Tab Example 3Ms Admin Changes o1 Residential D2 Education D3 Employment pa Financial D5 Legal D6 Emergency D7 D8 Health Substance D9 ADL pio 1an HISTORY OF CHANGES TO ADMINISTRATIVE DATA Assessment Date Change Provider DateChange Program Date Change Coordinator Date Change Status Discontinue Date Change AB2034 Date Change Date of Program of Coor of Status Reason of AB2024 Status of GHI 2202008 2202008 12 2720 2008 Wraparou UNNOS Joe Joe 2202003 Actve 2202008 Na UWE Not 2202008 Not Enrotes Erected Erected GHI Status Date Change MHSA Status HSA 60 2008 Bob Bod 8 18 2010 Discontinued Met Goals The Admin Changes tab displays a list of all administrative changes reported for the partnership including assessment date and dates of change In this example the partnership began on 2 20 2008 and changed coordinators from Joe to Bob to Herald The partnership was discontinued on 8 18 2010 for having met goals Page 45 of 128 Browse Partner Data Template o D1 Residential tab example 3Ms Admin Changes D1 Residential D2 Education l D3 Employment D4 Financial D5 Legal ps Emergency D7 D8 Health Substance D9 ADL D10 IADL DAYS AT RESIDENTIAL SETTINGS IN 12 MONTHS BEFORE FSP Re
58. last 12 months before joining the FSP Information on the time prior to the last 12 months before joining the FSP is not included in these templates All administrative changes such as program or coordinator changes are also displayed within PLD Templates The Application Notes covered later in this training will exemplify how the PLD Templates provide an environment for creating new reports from the FSP data files Page 20 of 128 PLD Templates Overview e Interface for Importing and Viewing Partner Data Browse Partner Data Run Partner Report Import New Data Download from DCR csv Files When the PLD Templates file is first opened a front page is displayed From the front page there are two main templates which can be accessed through the PLD Templates There is also a section for clearing the old data extract and importing a new data extract The two templates available in the PLD Templates are 1 Browse Partner Data BPD Template 2 Run Partner Report RPR Template Page 21 of 128 Browse Partner Data Template a San Francisco Partner Level Report Data Displayed Through 10 06 2011 PARTNER INFORMATION Partner Name Doe John Partnership Date 2 20 2010 Daysin 281 County Partner 10 Partnership Status Inactive Partnership Global ID Date Status Change 11 28 2010 CSI Number Program 3200 Citywide Adult Gender M Age Group Adult DCR Date of Birth 1 1 1965 CSI Date of Birth 1 1 1965 CSI New Race Coordinator
59. lert you that there were errors on import and the table of errors can be reviewed A review of the table of errors will show that the only field with error was the CreatedDate field because of a Type Conversion Failure Errors that occur during data import can be reviewed in the Microsoft Access table generated from the import process Refer to the PLD Templates User Manual completed 1 19 2012 at Chapter 2 at the section Reviewing Data Import Errors beginning on page 19 for step by step instructions Page 34 of 128 Importing New Data Extracts Repeat Step 3 until all CSV files are imported e Import PAF Residential File into DCR_PAFRes table e Import PAF Non residential file into DCR_PAFnonRes table e Import KET file into DCR_KETs table e Import 3M file into DCR_3M table Step 3 will be repeated until all four CSV files have been uploaded into the appropriate tables in the PLD Templates e Import the PAF Residential File into the DCR_PAFRes table e Import the PAF Non residential file into the DCR_PAFnonRes table e Import the KET file into the DCR_KETs table e Import the 3M file into the DCR_3M table Page 35 of 128 Importing New Data Extracts e Very Important Don t forget to click button to Run Update a ne Wht detest senarates your fds Select the accraprate Gelevier and see how your text a affected nthe preview below Chocne the delevter tnt peparates your fds eco
60. n Report Access e Resize rename and relocate fields Report Header Page Header Average Length ot IM f r PEE crtinyously Encolled No Of Partnership ProgramDesc Header SSF SS ProgramDesc Detail Providersiteia Continous tnrotted Countoretdeaiod TSN SERVICE Provider Ps Page Footer Now Page amp Page amp of amp Pages Report Footer 8 As seen in this slide you can resize rename and relocate the fields and labels to match the following design or another format as desired The following steps provide a quick overview of the steps required to change the report from the layout in the previous slide to this slide Click in the label in the Report Header section and rename the report title e Click on each of the labels in the Page Header section and rename each label to match the new label titles in this slide e Hold the Shift key to simultaneously select all labels in the Page Header section and move the labels down in the section to make room for the report title label Click and drag to arrange each label in the Page Header section to match the layout in this slide e Drag and drop the report title from the Report Header section to the Page Header section e Minimize the size of the Report Header section by clicking and dragging upward on the lower section separator bar e Lengthen the ProgramDesc Header section by clicking and dragging downward on the
61. ner had three 3M assessments during the program and the ADL status were captured on days 270 355 and 445 in the program Page 53 of 128 Browse Partner Data Template o Dio Instrumental Activities of Daily Living IADL 3Ms Admin Changes D1 Residential D2 Education D3 Employment D4 Financial D5 Legal D6 Emergency D7 D8 Health Substance l D9 ADL D10 IADL INSTRUMENTAL ACTIVITIES OF DAILY LIVING IADL DURING FSP Assessment Age Group Time Period Days in Telephone Walking Groceries Meals Housework Handyman Laundry Medication Money Date FSP Distance 11 9 2009 Older During FSP 3M 270 1 WithoutHelp 1 WithoutHelp 1 WithoutHelp 1 WithoutHelp 1 WithoutHelp 2 WithSome 1 WithoutHelp 1 WithoutHelp 1 Without Help Adult Help 2 2 2010 Older During FSP 3M 355 1 WithoutHelp 1 WithoutHelp 1 WithoutHelp 1 WithoutHelp 1 WithoutHelp 2 WithSome 1 WithoutHelp 1 WithoutHelp 1 Without Help Adult Help 5 3 2010 Older During FSP 3M 445 Adult The D10 IADL tab displays the IADL 3M assessments for older adults during the program In this example the partner had three 3M assessments during the program and the IADL status was captured on days 270 and 355 The IADL information was not completed for the 3M assessment on day 445 in the program Page 54 of 128 Browse Partner Data Template o Find All Partner s Data for a Coordinator Button Browse Partner Data Browse All Partners Find a Partner s Data D
62. new query in Design View 10 Now aggregate the data in this new query by all providers for each program As seen in this slide replace the ProviderSiteld field in the second column of the query definition with the following constant ProviderSitelD zz ALL PROVIDERS zz The z s in the constant will help sort this summary row to the bottom of all other provider values Alternatively a space before the term will sort the row to the top of all providers if there are no null provider values in your dataset Page 103 of 128 Program and System e Inspect the results of the query e Write down the number of records e Save and close the query ProgramDe _ ProviderSiteID 3200 Citywide zz ALL PROVIDERS zz 3300 SF First It zz ALL PROVIDERS zz 3300 SF First It zz ALL PROVIDERS zz 4000 FSA Olde zz ALL PROVIDERS zz 4000 FSA Olde 2z ALL PROVIDERS zz 45RH Shasta R 2z ALL PROVIDERS zz 45RH Shasta R 2z ALL PROVIDERS zz 169 2 240 2 95 6 34 412 908 785 952 567 749 496 678 19 2007 20 557 47 173 660 15 1803 20 473 45 126 Continuous CountOfGloballD AvgOfDAYSINSERVICE SUMOf3MS_DL SumOf3Ms_Cor 3Ms PctnCom 97 3 78 9 89 8 100 84 9 95 7 72 8 11 As seen in this slide move to the datasheet view to inspect the results of the query The query should display one record for each combination of program and discontinuous or continuous partne
63. nt Age Group Time Period Date 12 17 2008 TAY Past 12 HEALTH AND SUBSTANCE ABUSE DURING FSP Assessment Time Period Date 12 17 2008 Start of FSP 3 10 2009 During FSP 3M 6 4 2009 T During FSP 3M 10 6 2009 During FSP 3M 12 14 2009 During FSP 3M The D7 D8 Health Substance tab displays the past 12 months of health and substance abuse history and a list of all health and substance abuse 3M assessments during the program In this example the partner did not have a physician and had a substance abuse problem in the 12 months before and at the start of the FSP The partner was not receiving substance abuse services at the start of the FSP At the first 3M assessment on day 83 the partner reported having a physician At the second 3M assessment on day 169 the partner reported receiving substance abuse services Page 52 of 128 Browse Partner Data Template o Dg Activities of Daily Living ADL 3Ms Admin Changes D1 Residential D2 Education D3 Employment oa Financial DS Legal D6 Emergency D7 D8 Health Substance 09 ADL D10 ADL ACTIVITIES OF DAILY LIVING ADL DURING FSP Assessment Age Group Time Period Days in Bathing Dressing Toileting Date FSP 11 9 2009 Older During FSP 3M 270 b ult 2 2 2010 Older During FSP 3M Adult 5 3 2010 Older During FSP 3M Adult The D9 ADL tab displays the ADL 3M assessments for older adults during the program In this example the part
64. o Page Sofs This slide displays the Emergency Intervention Domain page of the report Page 68 of 128 PARTNER INFORMATION Partner Name John John 063 Partnership Oate 4 7 2009 Days in Partnership Total 3Ms Due Heakh and Substance Abuse Domains eee a 12 Months Before FSP AgeGrow Time Period adPrysican Evernad Sutetance Paz i2 Yes Health and Substance Abuse During FSP Assessment Age Group Time Period Cate annoa TaY Start of FSP 7 30R008 TaY During FSP 3M 10782008 TaY During FSP 3M 6252010 TAY During FSP 3M 9 24010 TAY During FSP 3M Page 9of9 This slide displays the Health and Substance Abuse Domains page of the report Page 69 of 128 Run Partner Report Template o Exporting a PDF will prompt to save the output file Run Partner Report GY output To xl GO E gt Coronter 05 6 Users OFC MyVideo r KEA search vy vaeos 2 View Partner Report Sasa otis Bae 3 Search by ID E Ji Adobe eLearning sf No items match your search UB Report _MH_0_Ape Export PDF Report Search by ID Fle name Partner_Level Report pdf Save as type POF Format pdf T Autostart A Hide Folders The Export PDF Report Search by ID button will export a PDF file of a partnership report to a desired location on your network There are two options for identifying the partnership for the report You can enter a CSI number in the first pop up window or enter a Global ID in
65. o 31 Year Number Four digit year 1984 to 2024 Month and Day Formatted as Month Day 1 1 to 12 31 Day of Week Weekday Number Starting on Sunday 1 to 7 Weekend or Weekday Flag for Weekend or Weekday Weekend Weekday Fiscal Year FY Fiscal Year Formatted as Year Year 1984 1985 to 2024 2025 Calendar Year CY Quarter QRT 1 to 4 10 FY Qrt 1 to 4 11 CY and QRT Formatted as Year_Q 1985 _Q1 to 2024 Q4 12 FY and QRT Formatted as FY_Q 1984 1985 Q3 to 2024 2025 Q2 WOON DUN KRWHN FP Page 80 of 128 Import Excel File to Access DB e From the PLD Templates o Select External Data Menu o Select Excel Button Home Create Database Tools Acrobat m x mae W Text File ett H Pa FAB y Bp Access ml E g E f rig X XML File lw Gx Ra vai 53 E Word Merge a Saved Linked ccess ODBC Saved Excel Text XML POF E mail ate Manage I Imports Man Database i More Exports File File or XPS amp More E mail Replies Import amp Link Export Collect Data After saving the Timeframe and Date Dimension tables to a location on your network open the PLD Templates and select the External Data menu item and then the Excel button to begin the import process Page 81 of 128 Import Excel File to Access DB Get External Data Excel Spreadsheet 2 x Bile name Spedfy how and where you want to store the data in the current database C Append a copy of the records to the tabl
66. oad all of the partner data to which they have access through their group The data can be downloaded as XML or CSV files The XML data downloads into three files a PAF a KET and a 3M file The CSV data downloads into four files a PAF Non Residential with all domains EXCEPT Residential a PAF Residential with residential domain information only a KET and a 3M file The XML or CSV files can then be used to perform data analysis and data reporting in other applications such as MS Excel MS Access SPSS or SAS Page 13 of 128 Download Files e Log into ITWS and access the FSP DCR application Transfers menu to download FSP data Department of Mental Health IMHSA Data Collection and Reporting Partnerships Transfers Admin Help Download FSP Data Fies As seen in this slide navigate to the Admin menu and Download FSP Data Files submenu and select the New Download button Refer to the FSP DCR User Manual completed 1 17 2012 at Chapter 12 Data Extraction beginning on page 155 for step by step instructions Page 14 of 128 Download Files e Select File Type e Select Form Type e Enter Date Range optional Department of Mental Health MHSA Data Collection and Reporting Home Partnerships Transfers Admin Help As seen in this slide as step 3 select the CSV Flat File or XML file type from the Download Format drop down box Make a selection in the Form Type drop down box to select one of the available
67. on page 22 for step by step instructions of scrolling through partners in the BPD Template Page 41 of 128 Browse Partner Data Template o Sorting Partners A ad Md OCR_Chient Level Report_20111026 Database Access z A Z Descending Y etres v Al Pa Find GoTo X Select As mentioned the partnerships are sorted by partnership status and partnership date by default such that the active partnerships with the most recent partnership dates appear first However the partnerships can be sorted by other fields in the template To sort the order of the partnerships in a different order place your cursor in the field in the Partner Information section of the BPD template which you would like to use to sort the partnerships e g Age Group As seen in this slide select the Home menu and select either the Ascending or Descending button to sort the partnerships by the selected field Page 42 of 128 Browse Partner Data Template o Finding Partners Select a field to search in the partner information section A id gt OCR_Clhent Level Report_20111026 Database Access om External Data Acrobat ty Sh ascending Y X j i Z Descending Y i k GoTo View J r x k John orrentfed gt Whole Field 7 far X I Match Case V Search Fields As Formatted To find a particular partner place your cursor in the field in the Partner Information section of the BPD template which you
68. ous partnerships Continuous_Enrolled IIf Continuous Is Not Null Continuous Yes 7 Now you will need to select the number of 3Ms completed which was previously calculated for partnerships with at least one 3M completed However some partners may not have had a 3M completed and a formula will need to set the value for this variable to zero for these partnerships Select an empty column of the query definition datasheet and enter the following formula to set the number of 3Ms completed for all partnerships 3Ms_Complete Ilf 3Ms_Completed Is Not Null 3Ms_Completed 0 Page 99 of 128 by Partner e Inspect the results of the query e Write down the number of records e Save the query as AP1_S04_001_Variables_ByPartner GlobalID DaysinService PartnershipDate End_Date ProgramDesc ProviderSitelC 3MS_DUE 3Ms_Complete Continuous 074f58f7 9aa0 498d 439 11 2 2010 1 15 2012 TAY FSP 1234 0 Yes Ob9cf2cb c724 41cb 498 4 7 2009 8 18 2010 Wraparound 1234 3 Yes Oecb79aS 1190 4b6t 379 6 7 2007 6 20 2008 Wraparound 1234 4 Yes Of3e5f66 89ef 4657 408 12 3 2010 1 15 2012 Adult FSP 1234 3 Yes 115a7401 4320 48c5 1286 7 8 2008 1 15 2012 Adult FSP 1234 12 Yes 15d2873f 0591 4dbc 253 3 12 2008 11 20 2008 TAY FSP 1234 2 Yes 19cb7956 3373 48dC 420 12 17 2008 2 10 2010 Adult FSP 1234 4 Yes 1a312cba 37e5 4e2f 369 1 11 2011 1 15 2012 Wraparound 1234 0 Yes 1bSeaee0 9b4d 45b 503 9 1 2006 1 17 2008 Wraparound 1234 5 Yes 1b9ce01c 8d7
69. r and returns the integer INT of completed quarters in service Formula Definition INT DaysInService 365 25 4 Formula Example INT BY2 365 25 4 Page 123 of 128 Using Excel for Analysis e Insert Pivot Table on new worksheet B c D E a PivotTable Field Ust 1 Age_Group ail 2 Choose fields to add to report Average of DaysinService Average of 3MsDue Count of AssessmentDate DiAssessmentsuid 4 000332a0 b752 43e2 a284 Sded60c3e6cb 429 Oconto 5 000d82a2 0ffc 40f0 8c4a 8cfe8208cbb2 208 Ered 6 0013afb6 53e5 4721 8e26 9a8b32c4cc10 462 Bawas 7 001e3d28 57af 4a26 9005 834aeb57072f 192 Drane 8 003acbd3 c8b5 4348 b2a3 bf045da5b16f 709 DParmnershiposte 9 0048ae1d 8465 4654 9ee6 f7e344396c7a 529 BAssessmentdate 10 00507b13 c3f0 449d 9f4a f89acSce5396 566 DDateofrth 11 006bc32b 8e0a 4182 b6ed b2beb3c45ccd 457 Cicsibateotarth 12 0070bc87 f8cb 4389 a60c de36f65fd37d 104 D Gender 13 00898385 e00e 4fbf b4e6 721c4c4eedb0 434 14 0090e52a 14ab 4e50 9d1f 57c23410ab36 2947 15 009a3dcf 9822 4b8e 9dd9 e25e8e661076 132 Drag fields between areas below 16 00ac7348 8649 40d1 adb4 d7113a159eda 507 Y Report Fiter 17 00aec7d1 c001 488a b5b4 fd864bc7793d 110 Aae 18 00b03517 1daf 4741 aaa7 4d3765046da6 n7 19 00c25fa8 1eaf 43d7 8576 881e6ebc72cd 450 20 00c7fcea 307e 4f0f 9df5 2898e3387629 248 21 O0c8faed 65df 4f93 8915 15f1b7a73319 3009 22 00d6ebdf eds9 4d78 a7a6 f96e1bSbf61a 182 23 00dcda1b 909b 448c 9591 c224831ef401 1139 24 00dde551 f9bb 4bf9 bf8e f3c
70. rship Write down the number of records in the query as reference Save and close the query Page 104 of 128 Program and System e Copy and paste query as AP1_So5_003_Total_System APL_SO4_001_Variables ByPartner GlobalID PartnershipDate e Open in Design View End_Date a e Replace the ProgramDesc field 3MS_DUE k 3Ms_Complete with 3Ms_PctnComp_ByPartner Caeimuseaines ProgramDesc Z ALL FY_Enrolled ong PROGRAMS zz QRT_CURRENT Field ere eel A ProviderSitelD zz ALL PROVIDERS Continuous_Enrolled GlobalID Table AP1_S04_001_Variable AP1_S04_001_Variable Total Group By Group By Count Sort ng Ascending Ascending Show ca Criteria or 12 Copy and paste the query just created and name the new query AP1_S05_003_Total_System 13 Open the new query in Design View 14 Now aggregate the data in this new query by the entire system of data Replace the ProgramDesc field in the first column of the query definition with the following constant ProgramDesc zz ALL PROGRAMS zz Page 105 of 128 Program and System e Inspect the results of the query e Write down the number of records e Save and close the query ProgramDesc ProviderSitelD S Continuous_Enrolle CountOfGlobalID AvgOfDAYSINSERVICE 3Ms_Due Sum 3Ms_Comp_Sum 27 ALLPROGRAMS zz zz ALLPROVIDERSNO 2037 S 9641 22 ALLPROGRAMS zz 22 ALL PROVIDERS Yes 277987 156232 45111
71. rt Which fields do you want on your report You can choose from more than one table or query Wizard Tables Queries Query AP1_SO5_004 Totals_All Available Fields Alternatively a report template can be created in Microsoft Access 1 With the PLD Templates open in Microsoft Access click once on the AP1_S05_004_Total_ALL query to highlight the query Choose Create gt Report Wizard in the report section of the menus As seen in this slide the selected query should show in the wizard If the desired query is not showing in the drop down box then select the drop down and locate the correct query 2 As seen in this slide select the gt gt button to move the entire set of fields into the Selected Fields box Click Next Page 111 of 128 Step 6 Design Report Access Group by ProgramDesc 3 As seen in this slide select the ProgramDesc and then select the gt button to group the report sections by program Select Next Page 112 of 128 Step 6 Design Report Access Sort by Report Wizard What sort order and summary information do you want for detail records You can sort records by up to four fields in either or descending order 1 ProviderstelD J Ascending 2 Continuous Enroled Ascending H n Se E Ascending 4 As seen in this slide select to sort by ProviderSitelD and then Continuous_Enrolled fields from the drop down boxe
72. rtners partnership data or search by partner ID partner name or coordinator ID Selecting any of the four buttons and entering appropriate criteria when required will display the BPD template The Browse All Partners Data requires no criteria and will display every partner Page 39 of 128 Browse Partner Data Template t 1 15 2012 PARTNER INFORMATION Partner Name Jane Jane 079 Pi p Date 9 20 2011 County Partner D 000000157 P pStatus Active Global 10 b8258eaS Se9c 4a66 a8d2 d393c1883676 s Change 9 20 2011 CSI Number 000000157 Adult FSP Gender F Starting Age Group Adult Ending Age Group Adult DCR Date of Birth 7 24 1963 CSI Date of Birth 7 24 1963 CSI New Race Homeless Shelter CSI Old Race Hispanic N DOMAIN DATA FOR Jane Jane 079 Total 3Ms Due 1 Admin Changes D1 Residential D2 Education 03 Employment D4 Financial DS Legal D6 Emergency D7 D8 Health Substance D9 ADL 010 IADL After opening the BPD template notice that in Microsoft Access 2010 by default the template opens as another tab in the application At the top of the BPD template the Data Displayed Through date signifies the last time the data in the PLD templates was updated from FSP DCR data file downloads This is the date which was entered when selecting the Run Update button from the import page It is also the date to which the number of active days in partnership is calculated for partnerships whic
73. s Simple grouping example based on Timeframe field 3Ms Due The number of 3Ms which would be due based on the number of days in service WOON DUN KRWHN FP Page 79 of 128 Before Creating a New Report e Dates Dimension Table A ea D E F G H i J 1 DATE VALUE MONTH_NO MONTH_NAME DAY_OF MONTH YEAR NO MONTH AND DAY DAY_OF_WEEK_NO DAY_OF_ WEEK NAME WEEKEND OR WEEKDAY FY 9 29 2008 9 Sep 29 2008 9 29 2 Mon Weekday 2008 2009 8675 9 30 2008 9 Sep 3 2008 9 30 3 Tue Weekday 2008 2009 8676 10 1 2008 10 Oct 2008 10 1 4 Wed Weekday 2008 2009 8677 10 2 2008 10 Oct 2008 10 2 5 Thu Weekday 2008 2009 8678 10 3 2008 10 Oct 2008 10 3 6 Fri Weekday 2008 2009 10 4 2008 10 Oct 2008 10 4 7 Sat Weekend 2008 2009 10 5 2008 10 Oct 2008 10 5 1 Sun Weekend 2008 2009 10 6 2008 10 Oct 2008 10 6 2 Mon Weekday 2008 2009 10 7 2008 10 Oct 2008 10 7 3 Tue Weekday 2008 2009 10 8 2008 10 Oct 2008 10 8 4 Wed Weekday 2008 2009 10 9 2008 10 Oct 2008 10 9 5 Thu Weekday 2008 2009 10 10 2008 10 Oct 2008 10 10 6 Fri Weekday 2008 2009 10 11 2008 10 Oct 2008 10 11 7 Sat Weekend 2008 2009 The Microsoft Excel file displayed in this slide defines the following groupings of dates Import this file as a new table in a Microsoft Access database to assist in aggregating and summarizing data based on any date field in the FSP DCR data Date Groupings Defined Month Number Month Number in Year 1 to 12 Month Name Name of Month Jan Dec Day of Month 1 t
74. s Click Next Page 113 of 128 Step 6 Design Report Access Blockand Landscape How would you like to lay out your report IV Adjust the field width so alll fields fit on a page cancel lt Back Next gt nish 5 As seen in this slide select the Block layout and select the Landscape orientation Click Next Page 114 of 128 Name the What tite do you want for your report Report JaP1_S05_004 Totals AI Rpt That s all the information the wizard needs to create your report Do you want to preview the report or modify the report s design Preview the report C Modify the report s design 6 As seen in this slide name the report AP1_S05_004_Total_ALL_Rpt Click Finish Page 115 of 128 Step 6 Design Report Access e Design View PrSgraeibe i Provspersaein Kefitinugus Enrolka i feballDp Ra veal Seaver aad 5ue Sun Sed ECORp PEER RE 2 sel a eeecercors siete sf Detail ProgramDesc ProvidersitelD Continuous _Enrolled CountC AvgOfDAYSINSER 3Ms_Due_Sum 3Ms_Comp_Sum 3Ms_Pctr Page Footer FENow HE J Page amp Page amp of amp Pages Report Footer 7 As seen in this slide move to the Design View to change the design of the report After the wizard creates the report the report format can be optimized by rearranging the location of the fields Page 116 of 128 Step 6 Desig
75. s ODBC Saved Excel Tet XML POF Email eate Manage Imports Manage Database More Exports File File or XPS By More Replies Import amp Link Export Collect Data Perform Step 2 to compact and repair the empty PLD Templates Microsoft Access may close the Instructions tab during the compact and repair process Simply re open the tab by selecting the Instructions button on the front page and continue the data import process Page 29 of 128 Step 3 Import New Data Files previously downloaded from the FSP DCR m As seen in the image below select the External Data tab above and click on the Text File to invoke the import wizard Acrobat ble Excel Access ODBC Saved Excel Tet XML POF E mail ole Manage Database Wl More Exports File File or XPS Ty More E mail Replies Import amp Link Export Collect Data Z a OOF eee ok A eee mAs seen in the image below select Browse to locate the PAF Residential file on your drive network Select to append the data to the existing DCR_PAFRes table from the drop down menu Select OK Sooty he source of the cata a Sooty how ard where you manit to store Pe Gate nthe current database Import the source data into a new table in the current database H fe rected table does not mst Acoma wil create t 1 he roeohed table ready oats Acones might overende s contents et te moored sta Ouroes made Pe source datas mi not de refected n Pa database Perform Step 3 to import the
76. scerta Seeng Yerecay Toys Win we AK Sige Foswr Fo Em Home ow Asse ON Jai Prison Ore UR Reomes pawis Omer Acre Room Reade No Seter wss Pome Living Comm vedca Psyon State Comm Mes Nursing Nissing we Cae Hos Hosp Psy m m Pya Hasi seneg Sesing Past12 Family ow Sese cam Homeless 365 27 218 120 DAYS AT RESIDENTIAL SETTINGS DURING FSP Date Residential Days in Residential Setting Lengthat Assessment Date Record Created Change FSP Setting Date 11 20 2008 0 Homeless 11 11 20 2008 12 03 08 9 57 06 AM 12 1 2008 11 Residential Treatment 12 5 2008 02 09 09 1 54 54 PM 3 2 2009 102 Emergency Shelter 3 11 2009 03 12 09 2 52 05 PM 4 7 2009 138 Apartment Alone 6 19 2009 06 19 09 1 19 31 PM The D1 Residential tab displays the past 12 months of residential information and a list of all residential status changes during the program In this example the partner spent 218 days and 120 days in emergency shelter and homeless respectively in the 12 months before the partnership During the partnership the partner spent 11 days homeless 91 days in residential treatment 36 days in an emergency shelter and spent the final 482 days in an apartment alone Page 46 of 128 Browse Partner Data Template o D2 Education example ji r ae 5j 3Ms Admin Changes D1 Residential D2 Education D3 Employment D4 Financial D5 Legal D6 Emergency D7 D8 Health Substance D9 ADL D10 IADL EDUCATION IN 12 MONTHS BEFORE FSP Age Time
77. sheet to use while tracking and validating the number of records in each query while creating the reports Page 78 of 128 Before Creating a New Report e Timeframe Dimension table A c D E F G DAYS_IN_SERVICE MONTHS_COMPLETED_EXACT MONTHS_COMPLETED_INT QRTS_COMPLETED_EXACT QRTS_COMPLETED_INT QRT_CURRENT YEARS_COMPLETED_EXACT o 0 0 0 0 032854209 0 010951403 0 002737851 0 065708419 0 021902806 0 005475702 0 098562628 0 032854209 0 008213552 0 131416838 0 043805613 0 010951403 0 164271047 0 054757016 0 013689254 0 197125257 0 065708419 0 016427105 0 229979466 0 076659822 0 019164956 0 262833676 0 087611225 0 021902806 0 295687885 0 098562628 0 024640657 0 328542094 0 109514031 0 027378508 0 361396304 0 120465435 0 030116359 The Microsoft Excel file displayed in this slide defines the following groupings of partnership length timeframes based on the number of days in partnership service Import this file as a new table in a Microsoft Access database to assist in aggregating and summarizing data based on the length of service for a partnership Timeframes Defined based on Days in Service Months Exact Fraction Completed Months Rounded to Total Completed Quarters Exact Fraction Completed Quarters Rounded to Total Completed Years Exact Fraction Completed Years Rounded to Total Completed Timeframe Three Month Labels e g 3Months 6Months etc Timeframe Categorie
78. ship matching the partner ID entered into the search box The number of partnerships for partners with matching IDs is displayed in the Record box in the lower left hand corner of the screen Page 56 of 128 Browse Partner Data Template o Find a Partner s Data Search by Name Button Browse Partner Data Browse All Partners Find a Partner s Data Data Search by ID y Find All Partners Data Find a Partner s Data 3 s for a Coordinator Search by Name 2x Enter any part or all of a parnter s name in the format Last First The Find a Partner s Data Search by Name will apply a filter to the BPD template based on matching the criteria entered with a partner s name As seen in this slide selecting this button will bring up the Enter Parameter Value pop up window Typing in any portion of a partner s name in the box will apply the filter to the BPD template The BPD template will then display only those partnerships which contain the entered text as any consecutive part of the partner name For example entering Martin in the Enter Parameter Value pop up window will display partnership data for all partnerships with any portion of Martin as either their first or last name The standard FSP DCR format for the name field is Last First Therefore you can conduct a search for a partnership using the full name with that format As seen in this slide for example John Doe would be found using
79. st empty column of the spreadsheet e g BY and create a new heading called DaysInService In the cell below the heading type in a formula to calculate the number of days in service to the end date Since Microsoft Excel uses cell locations and not field names to perform calculations the formula appears below with field definitions and then as an actual example formula used in Microsoft Excel The formula first identifies if the partnership status is 0 signifying the partnership has ended and in this case calculates the days in service by subtracting the partnership date from the date the partnership ended Then in the cases in which the partnership has not ended the days in service are calculated by subtracting partnership date from the end date defined when the CSV file was downloaded from the DCR Formula Definition IF PartnershipStatus 0 DatePartnershipStatusChange PartnershipDate Date of Download PartnershipDate Formula Example IF U2 0 T2 G2 10 6 2011 G2 3 As seen in this slide copy and paste the formula down all of the rows of data 4 As seen in this slide select the next empty column of the spreadsheet e g BZ and create a new heading called 3Ms Due In the cell below the heading type the formula below to calculate the approximate amount of 3Ms due The following formula divides the Page 122 of 128 number of days in service by the number of days in a quarter 365 25 4 the number of days in a quarte
80. t Twelveoccurences Home ess_PastTwelveDays Homeless_Priortwelve IndividualPlacement_PastTwelveoccurences any t _PastTwelveDays Communi tyTreatment_t Priortwelve Residentia Treatment_PastTwelveoccurences ResidentialTreatment _PastTwelveDays Ri leda6d966 24a8 421d bad5 99845d8ba208 89 000000123 074f 58f7 9aa0 498d 987a 36fa4db02797 000000123 Jane Jane 045 11 2 2010 tiv 276 d ad7 3 75fd2ea63116 89 000000064 Ob9cf2cb 724 41cb 98a9 John 064 4 7 2009 4 7 2 John 015 6 7 2007 6 7 z Jane 028 12 3 2010 12 a os 17 8 2008 7 8 z Jol 02595886 e7be 4905 adsa 11cfa37397c9 89 000000046 119cb7956 3373 48d0 8c19 f10346cfa657 00000004 085248a7 8c74 4154 a781 e673243b4585 89 000000134 1a312cba 37e5 4e2f bc4f b3f9628b138f 000000134 Jane b894d2ff 921c 4d3f 9a7e 82a668362cf7 89 000000020 1b5eaee0 9b4d 45b9 b9da 28f0c94b980e 000000020 John c1f57c23 f078 423b 97a0 dc5ea160f51f 89 000000072 1b9ce01c 8d75 4eee 9898 2a70b18d7eb1 000000072 John 1921 eb07b ac96 40de acf4 1b32eee89f ea 89 000000026 1d554b0d e177 4bb5 a514 e0ee5e0528b0 1000000026 John 7 6d1013 78b4 48f7 9a32 222e42e30bed 89 000000129 1f 32bb6c add9 4d2e a77a 7f Sab9b623F4 000000129 9 122b39886 0b62 466d 84be 8a57 56e7 Sbb2 89 000000142 2353a1d1 35f9 4921 bebe 39ed2140a35c 00000014 c4a1 Sdae 682d 44 39 a67a 75062d0905F6 89 000000110 23917306 e067 4b88 a018 3 5d297d0cb2e 00000011 2f bO2c1b 6e63 4b95 91a8 47313e17
81. t and materials from various people and organizations Many documents created by the California Department of Mental Health DMH were used as sources and resources for the production of this training Due to the Governor s Proposed Budget for FY 2012 13 to eliminate the Department of Mental Health DMH MHSA programs and functions will be transferred to other departments It is proposed that the DCR system will be administered by another department Since this document was developed at the time DMH maintained the DCR system DMH is named in the references and links throughout this document Because the new state entity is not known whenever DMP is used in this document it is intended to also include the new state entity that will take on DMH s responsibilities Page 2 of 128 Definitions and Abbreviations Term 3M ADL Adult BPD CA RW Child Youth CSI CSV DCR DMH FSP FY HIPAA IADL IIF ITWS KET MH MHSA MHP Older Adult PAF PHI PLD Partner RO RPR RW SB SQL SSA TAY TOC Meaning Quarterly Assessment Activities of Daily Living Adults of Ages 26 59 Browse Partner Data County Administrator Read Write Children of Ages 0 15 Client Services Information Comma Separated Values Data Collection and Reporting California Department of Mental Health Full Service Partnership Fiscal Year Health Insurance Portability and Accountability Act Instrumental Activities of Daily Living Immediate If Function
82. ta Extracts What delimiter separates your fields Select the appropriate delimiter and see how your text is affected in the preview below r Choose the delimiter that separates your fields Pi teen Cree Joe a As seen in this slide select the Comma radio button check the First Row Contains Field Names box and set the Text Qualifier to the quotes symbol Select Finish Page 33 of 128 Importing New Data Extracts e You will be alerted here if there were errors in the import Get External Data Text File 2x Fished importing fie C Users OAC 1Doauments OAC_LEI_Contract Pata 20111006_AllProductionData CSV PCR_PAFres_ALL csv to table _PAFRes Do you want to save these import steps This will alow you to quickly repeat the operation without using the wizard I Saye import steps As seen in this slide after each file is imported a pop up screen will alert you that the process is complete The pop up screen will also alert you if any errors were present in the import Any errors from the data import will be saved in a table noted on the pop up screen It is not necessary to save import steps Note The CreatedDate from the 3M CSV file may be corrupt in the FSP DCR data downloaded file Although all of the other fields will import correctly the CreatedDate field may not import with each 3M record This is okay and it will not affect the use of the PLD Templates If this occurs the pop up screen will a
83. ter the following formula In order to avoid dividing by zero when no 3Ms are due for an aggregated group this formula uses an IIF statement in the formula to insert null values in the results for those cases 3Ms_PctnComp_ByProvider IIf Sum AP1_S04_001_Variables_ByPartner 3MS_DUE 0 Null Round 100 Sum AP1_S04_001_Variables_ByPartner 3Ms_Complete Sum AP1_S04_001_Variables_ByPartner 3MS_DUE 1 Select the Expression option in the drop down box of the Total row 6 As seen in this slide move to the datasheet view to inspect the results of the query The query should display one record for each combination of provider program and continuous or discontinuous partnership Write down the number of records in the query 7 Save the query as AP1_S05_001_Total_ByProvider Page 102 of 128 Step 5 Total by Provid Program and System AP 0401 Vana Byrne e Copy the query just created and save as pean AP1_So5_002_Total_ByProgram PartnershipDate pasad e Open in Design View ProviderSiteID 3MS_DUE ga e Replace the ProviderSiteld field 3Ms_PctnComp_ByPartner p Continuous _Enrolled h FY_Enrolled with FY_Qrt_Enrolled QRT_CURRENT ProviderSiteID ZZ ALL PROVIDERS zz Continuous_Enrolled GloballD AP1_S04_001_Variables_ByPartner AP1_S04_001_Variable Group By Count Ascending j 4 8 Copy and paste the query just created and name the new query AP1_S05_002_Total_ByProgram 9 Open the
84. tion and margins for printing as desired Page 119 of 128 Page 120 of 128 Using Excel for Analysis e Analyze one CSV file at a time e Use formulas to create new fields e Use Pivot Tables to aggregate data e Has many limitations A limited amount of analysis can be performed in Microsoft Excel alone A single CSV file can be imported and Microsoft Excel s pivot table can aggregate and summarize the information While Microsoft Excel does not have the functionality to create report templates from the information in multiple CSV files Microsoft Excel can calculate the length in program number of 3Ms due and completed and percent of 3Ms complete for all partners in a file However since the following analysis is from the 3M CSV file a partner must have had at least one 3M completed to exist in the file and be included in this analysis Partners without 3Ms would need to be identified separately from the PAF file Page 121 of 128 Using Excel for Analysis e Open 3M file in Excel e Create new fields BR BS BT BU BV Bw BX BZ AssessmentType Age_Group AssessmentSource CreatedDate QtrlyCntyUse1 QtrlyCntyUse2 QtrlyCntyUse3 DaysinService 13MsDue 11 1 5 27 2010 13 34 429 11 1 9 13 2010 11 27 429 11 1 9 13 2010 11 21 11 1 10 11 2010 13 48 2 1 8 4 2010 10 05 2 1 7 28 2010 7 56 11 1 8 9 2011 13 37 8 1 9 10 2010 7 47 NUNN SS SS 1 Open the DCR 3M CSV file in Microsoft Excel 2 As seen in this slide select the fir
85. would like to use to find a partnership e g Partner Name As seen in this slide select the Home menu and then select the Find button As seen in this slide enter the criteria for the search In this example we are searching the partner name field to find a specific partner by name The Find box will remain open and you can select the Find Next button to jump to the next partnership with matching search criteria Note Selecting to search information under the Domain Data For sections in the lower half of the BPD template will only search for data within the partnership which is currently being displayed The search will not bring up other partnership s matching data Page 43 of 128 Browse Partner Data Template o Data for the partnership is displayed in the tabs o 3Ms Administrative Changes and all Domain Data o This image displays the 3M Data DOMAIN DATA FOR Jane Jane 009 Total 3Ms Due 5 Total 3Ms Due 5 Assessment Date Days in Partnership Age Group at Time of Assessment 4 14 2010 77 Child 8 4 2010 189 Child 10 27 2010 273 Child 1 27 2011 365 Child 4 19 2011 447 Child Under the Domain Data For subheading there are tabs for the partnership s information such as their legal financial and employment data All domain options are displayed in this slide Data associated with a tab will be displayed by clicking on the tab A vertical scroll bar will appear if necessary to allow scrolling w
86. xcuses partner who were Sacortnue dand reactivated afer iessthan i year Application Note 2 explains how to make two related reports the Number of Partnerships by Discontinued Reason for Each Program Provider similar to Section 2 7 in the Data Quality Reports and the Average Length Days in Partnership by Program Provider and Discontinued Reason similar to Section 2 8 in the Data Quality Reports An example of these reports can be seen in this slide and the next slide The first report summarizes the number of partnerships by discontinue reason for each provider and program Page 75 of 128 Application Note 2 Reports 2 8 Average Length Days In Partnerhsip by Program Provider and Discontinued Reason neies only continveusy emotes garners Eachutes partner who were Sacertinueandresctivates sher estan 1 year The second report summarizes the average length of partnership by discontinue reason for each provider and program Page 76 of 128 Before Creating a New Report e Load DCR CSV files into PLD Templates e Print the Worksheet from Appendix D e Import Tables from Appendix A and B Import the Table of Timeframe as CODES_TIMEFRAME 2 Import the Table of Dates as CODES_DATES optional Attachments g 16 amp G Name Descriptio ES CODES_DATES xls E CODES_TIMEFRAME xls It is often desired to separate data into length timeframes or into date ranges when
87. y the value in column C for each partner multiply by 100 and take the integer As seen in this slide select cell E4 and type the following formula INT 100 D4 C4 Select Enter to exit the cell and then copy the formula down column E to the end of the dataset Page 125 of 128 Using Excel for Analysis e Calculate Percent of 3Ms Complete for All A Cum c Age_Group All bd Row Labels Average of DaysinService Average of 3MsDue Count of AssessmentDate Percent of 3Ms Complete ff5c56b4 e30b 4416 b226 225 163 1 100 ff645f72 7a5e 49de 8c0c 025 118 1 100 ff77e397 e87f 4222 a22a 91b 567 2 33 ff7884ca 911c 4d51 bbb6 3b7 33 ff811de7 0d75 4397 866a 421 279 66 ff822cc2 1260 4ef7 82d6 5737 237 100 ff89dd28 Se0e 41d5 9e69 4ft 100 ff8aflb4 47e3 437d 93d8 3a4 604 66 ff916a06 843b 4227 bfic a76 250 100 ffcb59ce d4ad 471b be9a 73 amp 342 100 ffddbaff 9dc3 4604 a0d5 4d5 345 100 ffed743c a991 410c b677 6ad 100 ffede3c0 1001 4b10 98b8 b2 amp 99 100 Grand Total N rN wWWrh WN HD 20878 TiNT a00 Da644 C464 o w rPNWWNHAWNWAATeH 8 To calculate the total percent of 3Ms completed for all partners sum all of the values in column D and divide by the sum of values in column C As seen in this slide the end of the data in column C type the formula sum C4 C where is the row number for the last partner in the pivot table At the end of the data in column D type the formula sum D4 D where is the row number for the last

Data Analysis Training

Contents

Download Pdf Manuals

Related Search

Related Contents