Risk Management - Viewing Alt-ID-2

/
May 23, 2011

Abstract

This Job Aid describes a method to view and create lists of consumers showing the Alt-ID-2 data element, which represents a consumer's Risk Level. The requirement for ASAPs to maintain a Risk Level for each of their consumers is described in Program Instruction PI-11-06 Risk Management, dated April 29, 2011.

This Job Aid is primarilyaddressed to novice SAMS users. The method described here uses Format Columns to place a new column in the user's Consumer List, and then Filtering and Sorting the Consumer List. Finally, it uses Export List to export the Consumer List to Excel.

Additionally, there is an appendix that contains a SQL query to extract consumer information with ALT-ID-2 from the SIMS Agency Reporting Tool (ART). This is not appropriate for novice or intermediate SAMS users.

Contents

Abstract

Introduction

View Alt-ID-2 in Consumer Record

Configuring SAMS Consumer List to SHOW Alt-ID-2

Format Columns is used to add & subtract Columns

Adding, Subtracting & Rearranging Columns

Sorting on Columns

Filtering the Consumer List

Exporting SAMS Consumer List to Excel

Open the .CSV file in Excel

Revision History

Date / Version / Description / Author
May 23, 2011 / 1.0 / First public draft / Jim Ospenson

Introduction

The data element Alt-ID-2 was selected to store the value for a Consumer's Risk Level. The requirement to record this is defined in PI PI-11-06 Risk Management. PI-11-06 also defines the specifics of assessment criteria used to determine a Consumer's Risk Level; the maintenance of current information; and provider communication requirements.

While there are no SAMS reports that can utilize this data element (filtering, grouping, or displaying the element in report output), the data element can be displayed as a column in a SAMS user's Consumer List, or in a SAMS Custom Search.

Notes regarding the use of alt-ID-2 field for Risk Level:

  • ASAP staff will see this important data element for each consumer each time they work in SAMS, oncethe Consumer List is configured as in this Job Aid.
  • A list of consumers can easily be generated
  • This element is visible to providers through Harmony Provider Direct. As in SAMS, Provider Direct can be configured so that Alt-ID-2 is a column on the user's Consumer List.

This Job Aid seeks to provide some general tools for users to manage their consumers, keeping Risk Level in the foreground. It is not intended to provide basic SAMS training, or training in Excel or ART.

View Alt-ID-2 in Consumer Record

Details > General > Other


Note: screen shots do not use real consumer names.

Configuring SAMS Consumer List to SHOW Alt-ID-2

Format Columns is used to add & subtract Columns

Add Alt-ID-2 to the Consumer list via [Format Columns], which is used to add or subtract columns to be displayed. Two methods to use [Format Columns] are shown below.

Format Columns 1: Right click on the Column Heading

Format Columns 2: From the File Menu

Adding, SubtractingRearranging Columns

Below, the Format Columns interface. Here, the right-hand panel shows the columns that will display in the user's Consumer List. Suggestion: remove the columns that you don't wish to see, or move them to the right.

Sorting on Columns

You can set a sort on particular column in the Format Columns screen shown above. However, the easiest way is to simply click on the column heading you wish to sort by (a-z). Click twice to sort in the opposite direction (z-a).

Filtering the Consumer List

Filtering allows you to set the consumers that will appear in the Consumer List. You can reduce the number of consumers that will display so that it matches your desired view. There are many ways that your list can be filtered: you may wish to experiment to see what works best for you.

Example 1: a single case manager's list of active consumers

Filter by:

  • Status = Active
  • Primary Case Manager = Madeline Kase-Manager (CM)
  • Enrollment Status = Active

Example 2: view all active consumers for the agency WestMass

Filter by:

  • Status = active
  • Default Agency = WestMass
  • Enrollment Status = Active

Note 1: you cannot filter Consumer List by Alt-ID-2. If your goal is a list of all active Home Care consumers coded "1risk" and "2risk" (for example), then export all active consumers to Excel, and only print the desired consumers to meet your need.

Note 2: note the [Clear Filter] button - use this to clear (remove) all filters.

Note 3: Use only one Care Program at a time to filter the Consumer List. We do not recommend you filter the Consumer List by multiple care programs, although SAMS allows this. If you check multiple Care Programs, the filtered list will display only consumers who have *all* checked programs, not *any* program.

Exporting SAMS Consumer List to Excel

Since Alt-ID-2 does not appear on standard SAMS reports, you may wish to keep an up-to-date list of your consumers' Risk Levels. Once you've organized your Consumer List so that the columns are as desired, and you've sorted and filtered the list so it's useful, you can export the Consumer List to Excel.

  • Visible columns, such as Alt-ID-2, are exported
  • Filtered by Active Status and Primary Case Manager
  • Ready to export
  1. Click on [Export List] button. It may be hidden.
  1. Consumer List will be exported as .csv file. This can be opened in Excel.
  1. Name the file, and store it in a place where you can find it.
  1. NOTE: Under Citrix, V: designates the user's C: drive. In the screenshot above, V: / Documents and Settings / <username> / Desktop will save the file to the user's desktop.

Open the .CSV file in Excel

  1. Open file in Excel
  1. Adjust column widths.In the example above, the Consumer ID and Status Date columns are too narrow to display properly.
  1. Sort, color-code, print as desired

.

Sample ART Query for Consumer Risk Level

At this writing, each ASAP has one (1) user authorized to access SIMS Agency Reporting Tool (ART). A Program Director may request that this user occasionally generate a list from ART data.

Notes on use of ART data to generate lists of consumers by Risk Level:

  • ALT-ID-2 is present in ART data, part of the Consumers table.
  • ART data is refreshed from SAMS on Monday, Wednesday, and Friday evenings (at present). The list you generate therefore may not reflect the most up-to-date data.
  • There is a great deal of flexibility possible, you may wish to customize the suggested report.

Plain English Statement of sample ART query

Show all Consumers of Elder Services of Merrimack Valley who have a Care Plan in one of these programs ("Home Care Basic / Non-Waiver" Or "Choices / Waiver" Or "ECOP / Non-Waiver" Or "Respite / Over-Income" Or "Home Care Basic / Waiver" Or "ECOP / Waiver"). The Care Plan must have a status of Active and a Care Plan End Date greater than May 12, 2011 (today).

Sort these consumers by Alt-ID-2 and by Town.

Show these columns in the report:

  • CLIENT ID
  • FULL NAME
  • GENDER
  • AGE
  • ALT ID 2
  • HOME PHONE
  • PRIMARY PHONE
  • PRIMARY CARE MANAGER
  • RES ADDRESS1
  • RES TOWN NAME
  • RES ZIP
  • CARE PROGRAM NAME
  • CARE PLAN START DATE
  • CARE PLAN END DATE
  • CARE PLAN STATUS

Sample SQL Code (paste into ART Query window)

Update the highlighted fields to make it specific to your agency's needs. You may wish to update the Care Programs if you want to track SCO, NAPIS, or other consumers too.

SELECT CONSUMERS.CLIENT_ID, CONSUMERS.FULL_NAME, CONSUMERS.GENDER, CONSUMERS.AGE, CONSUMERS.ALT_ID_2, CONSUMERS.HOME_PHONE, CONSUMERS.PRIMARY_PHONE, CONSUMERS.PRIMARY_CARE_MANAGER, CONSUMERS.RES_ADDRESS1, CONSUMERS.RES_TOWN_NAME, CONSUMERS.RES_ZIP, SERVICE_PLANS.CARE_PROGRAM_NAME, SERVICE_PLANS.CARE_PLAN_START_DATE, SERVICE_PLANS.CARE_PLAN_END_DATE, SERVICE_PLANS.CARE_PLAN_STATUS

FROM CONSUMERS LEFT JOIN SERVICE_PLANS ON CONSUMERS.CONSUMER_UUID = SERVICE_PLANS.CONSUMER_UUID

GROUP BY CONSUMERS.CLIENT_ID, CONSUMERS.FULL_NAME, CONSUMERS.GENDER, CONSUMERS.AGE, CONSUMERS.ALT_ID_2, CONSUMERS.HOME_PHONE, CONSUMERS.PRIMARY_PHONE, CONSUMERS.PRIMARY_CARE_MANAGER, CONSUMERS.RES_ADDRESS1, CONSUMERS.RES_TOWN_NAME, CONSUMERS.RES_ZIP, CONSUMERS.CONSUMER_AGENCY_DES, SERVICE_PLANS.CARE_PROGRAM_NAME, SERVICE_PLANS.CARE_PLAN_START_DATE, SERVICE_PLANS.CARE_PLAN_END_DATE, SERVICE_PLANS.CARE_PLAN_STATUS

HAVING (((CONSUMERS.CONSUMER_AGENCY_DES)="Elder Services of Merrimack Valley, Inc.") AND ((SERVICE_PLANS.CARE_PROGRAM_NAME)="Home Care Basic / Non-Waiver" Or (SERVICE_PLANS.CARE_PROGRAM_NAME)="Choices / Waiver" Or (SERVICE_PLANS.CARE_PROGRAM_NAME)="ECOP / Non-Waiver" Or (SERVICE_PLANS.CARE_PROGRAM_NAME)="Respite / Over-Income" Or (SERVICE_PLANS.CARE_PROGRAM_NAME)="Home Care Basic / Waiver" Or (SERVICE_PLANS.CARE_PROGRAM_NAME)="ECOP / Waiver") AND ((SERVICE_PLANS.CARE_PLAN_END_DATE)>#5/11/2011#) AND ((SERVICE_PLANS.CARE_PLAN_STATUS)="Active"))

ORDER BY CONSUMERS.ALT_ID_2, CONSUMERS.RES_TOWN_NAME;

File:Job Aid - Displaying Alt-ID-2 and Creating Lists - v1.0 FINAL - 2011-05-23.doc / Page 1 of 9