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.
Report 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.)
Case Report: 8 Columns of Data Needed
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#
and
Funding Code
PAI Case?
Legal Problem Code
Close Reason
Gender
Veteran
Race
Date of Birth
Report 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
What data do I need to Transform / Conform for LSC’s Report?
These values can be transformed automatically with the LegalServer Transformer
Staffing Code Staff/PAI, must be reported as S or P to LSC
Gender Code M, W, O, U are the only values LSC accepts
Ethnicity Code A, B, H, N, O, W, U are the only values LSC accepts
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 (no need to transform- see boolean value options in the report header)
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
Opened Cases Report 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 in the report submitted must be in the order above, with conforming headers
What LSC is calling Ethnicity is probably Race in your system
Outreaches Report for LSC CARES
Outreach Report 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.
Outreach Report Filters
Outreach date is in the reporting quarter.
Outreach Report 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.