Home‎ > ‎Sitewide‎ > ‎

LSC CARES COVID-19 Reports

Summary



Recipients of CARES act funding for Covid19 related work must report closed case information to LSC quarterly beginning with the second quarter of 2020. Use LegalServer reports to export the spreadsheet of closed cases. With minor changes, this can be uploaded into the LSC grant reporting portal.

Outreach totals are to be reported quarterly, and agencies can optionally report total new opened cases as well.  LegalServer reports can tally the opened case totals needed for the LSC CARES reporting.

Tagging Cases and/or Timeslips as COVID-19 Related

Please see our May 2020 community call on this topic on YouTube here: https://youtu.be/63-PGNSr5KA

Closed Cases Report for LSC CARES COVID-19 - Overview

Timing & Submission

This report is quarterly and not cumulative.  

You’ll report cases by uploading a spreadsheet to the LSC grant portal, currently GrantEase.

Filters

Closed during the reporting period

CSR Eligible

Related to COVID-19

(the field YOUR agency uses for this may vary, but you may use the Disasters field, which is available on every LegalServer site.)

Columns

LSC is asking for these 8 columns in the spreadsheet, in this exact order, with this exact naming convention


Funding Source Code LSC COVID, Other LSC, or Non-LSC, transformed into 001, 002, 003

Staffing Code Staff/PAI, transformed as S or P

Problem Code LPC, like 63

Closing Code A-L

Gender Code M, W, O, U

Veteran Status Code N, V, U

Ethnicity Code A, B, H, N, O, W, U

Year of Birth YYYY

Quirks

  • Note that the columns must be in the order above.

  • The columns in the spreadsheet you submit must have the exact header names designated by LSC.

  • What LSC is calling Ethnicity is probably Race in your system.

  • It is possible, using the Transformer and the logic tools LegalServer has in report column headers, to produce a report exactly as needed by LSC with no further manipulation.

  • Unless or until you transform each of the data items using the Transformer and configure the logic tools we've added to columns in reports, you will need to use find/replace in your spreadsheet program once you export the data. For instance, find/replace ‘Male’ with M, and in the Veteran column, replace ‘Yes’ with ‘V’.


Closed Case Reports for COVID-19 Reporting - Build Help


Planning Your Reports


We suggest making two reports.  One for error checking and one to export and submit. Examples are attached to this article.


CARES Covid19 Quarterly - Closed - Non-Transformed - Error Checking

CARES Covid19 Quarterly - Closed - Non-Transformed - For Submission


Find examples of these two reports here, in the Example Reports list.

You can upload these to your site and use them, if you use the Disasters field to identify COVID-19 cases.


The Error Checking versions will have the convenient Matter/Case ID hyperlink in them, which you don’t submit to LSC.


If you decide to use LegalServer’s transformer to save time using find/replace in the exported spreadsheet, you can use these reports as a basis to build two more reports in the future with less find/replace action needed after export:


CARES Covid19 Quarterly - Closed - Transformed - Error Checking

CARES Covid19 Quarterly - Closed - Transformed - For Submission


Build a Simple Non-Transformed Error Checking Report

Use a basic cases report and Save As something like: CARES Covid19 Quarterly - Closed - Non-Transformed - Error Checking

Remove or hide all the columns except the required columns and Matter/Case ID#


Before you change the heading names, the columns will look like the list below, since these are the most likely LegalServer source fields you’ll use:


Matter/Case ID#

Fuding Code

PAI Case?

Legal Problem Code

Close Reason

Gender

Veteran

Race

Date of Birth



Filters

Add a filter: Date Closed

Case Disposition: Closed

CSR Eligible: True/Yes

Disasters: Is any of Coronavirus 

(note your agency may have chosen a different way to tag cases as Coronavirus- related)



Build Another Similar Report That You'll Adjust for LSC Covid Reporting

Use a basic cases report or another report you’ve already created and Save As something like:

'CARES Covid19 Quarterly - Closed - Non-Transformed - For Submission'


Eliminate all the columns from the report except the required columns 


Funding Source Code LSC COVID, Other LSC, or Non-LSC, transformed into 001, 002, 003

Staffing Code Staff/PAI, transformed as S or P

Problem Code LPC, like 63

Closing Code         A-L

Gender Code M, W, O, U

Veteran Status Code N, V, U

Ethnicity Code A, B, H, N, O, W, U

Year of Birth YYYY


Filters for Your CARES Covid Report to LSC

Date Closed: (pick a quarter, or Any Selected). Leave viewer-modifiable)

Case Disposition: Is any of Closed

CSR Eligible: True/Yes




Manipulate Columns to Match LSC Template Requirements

Hide any columns other than the 8 columns below

Order the columns:


Funding Source Code
Staffing Code
Problem Code
Closing Code
Gender Code
Veteran Status Code
Ethnicity Code
Year of Birth


Rename the columns if needed

Reformat the columns if needed

Go to Date of Birth column header and change the format to: Year Only


Go to Legal Problem Code and change the format to: First Word



Go to Staffing Code and change the Yes/True Value and the No/False Value


Go to the Veteran Status Code column, edit it, and change the True , False and Null values, like this:



Optional: Use Find/Replace To Conform Values

Export your results to .xls, and find/replace values as needed.


If you don't use the Transformer and the column options tools to conform the report's output, you'll need to use a more manual method of find/replace in your downloaded report spreadsheet. For instance, unless you have added a custom lookup field to your grants to track this data point, all values in the Funding Source column should be changed to 001, 002, or 003 depending on whether the funding code is LSC Covid, LSC (something else), or non-LSC.  LegalServer can’t automate that for you, since we don’t know what you’ve named your LSC grants, and those are not universal.


Essential Step! Modify Your Exported Report

In your spreadsheet editing software, take out all top rows other than the column names

Down at the bottom left of the screen, name the individual sheet in this workbook: Data Entry


Advanced: Use the Transformer Where Needed

You do NOT need to use the transformer if you don’t want to.  You can export your data to .xls and find/replace values in your spreadsheet program, then upload your spreadsheet to the LSC portal.


What is the Transformer?

See our help information here:

http://help.legalserver.org/home/reports/lookup-transform-and-export-index

You do NOT need to use the transformer if you don’t want to.  You can export your data to .xls and find/replace values in your spreadsheet program, then upload your spreadsheet to the LSC portal.


Using the Lookup Transformer For COVID19 Reporting - Webinar Recording


https://us02web.zoom.us/rec/share/55JOH-6zrD1OQ4nu5VyBHaURAb75eaa8h3RL__UImEmoSGvX1oIw3RM9PCZQxHGn


What do I need to Transform for LSC’s Report?


Staffing Code Staff/PAI, transformed as S or P

Gender Code M, W, O, U

Ethnicity Code A, B, H, N, O, W, U


These columns cannot be transformed automatically because they are not lookups:


Funding Source Code 001,002,003  (these can’t be transformed, currently)

Veteran Status Code N, V, U (these can’t be transformed, currently)


Opened Cases Report for LSC CARES - Overview

This report is optional as of 7/2020

Timing & Submission

This report is quarterly, and not cumulative.  This is for cases newly opened in the quarter.

Filters

Opened in the reporting quarter

LSC Eligible = Yes/True

Disasters: Is any of Coronavirus 


Columns

Funding Source Code
Staffing Code
Problem Code
Closing Code this will be blank
Gender Code
Veteran Status Code
Ethnicity Code
Year of Birth

Quirks

Note that the columns must be in the order above


What LSC is calling Ethnicity is probably Race in your system



Outreaches Report for LSC CARES

Timing & Submission

This report is quarterly, and not cumulative. There is no spreadsheet to upload.  Just run the report and put the total number of outreaches and the total number of attendees in the appropriate boxes on the LSC portal.

Filters

Outreach date is in the reporting quarter.

Columns

Number of Outreaches

Number of attendees actual

Number of attendees estimated



Delivering Your LegalServer CARES Reports to LSC

Export to XLS from LegalServer

When your report is ready to submit, run the report and click the Excel icon, which you will find to the right of the screen.  This will download the tabular data to your computer in .xls format


GrantEase Portal

LSC staff will tell you where to report.  As of July, 2020, the reporting portal is here:

https://www.lsc.gov/grants-grantee-resources/our-grant-programs/basic-field-grant/grantease-training


Uploading to LSC's Portal

LSC says you cannot re-submit a quarterly report to LSC unless they determine there are errors! Triple check your data spreadsheet before uploading.


When you upload to LSC, their portal may give you error messages.  If this happens, go back to view your report in LegalServer and figure out which case you need to update.




You can edit the spreadsheet you have generated before uploading to LSC if necessary.