To prepare your data in order to run the ESG CAPER or HUD APR Aggregate Report, you will need to follow these steps:

  1. Run Q4a and export it
  2. Edit the feeder reports and enter the report start and end dates, as well as the program
  3. Run the aggregate report and review the results
  4. Export the results in CSV file format and submit them to your funder

1. Run and export Q4a

Q4a is the first section of both the ESG CAPER and the HUD APR reports, and it is located in the "Feeders for HUD APR/CAPER 2020" category in My Reports.

  1. In the My Apricot tab, locate "My Apricot Tools" in the left-side navigation panel
  2. Expand "My Apricot Tools"
  3. Select "My Reports", which will take you to the list of standard reports
  4. Locate the "Feeders for HUD APR/CAPER 2020" category and expand it by clicking the black down arrow
  5. Locate "Q4a" in the list, hover your mouse over the grey "Actions", and select "Run"

Populate all the global value fields in the "Global Value" section:

  1. Report Start Date
  2. Report End Date
  3. Project Type
    Project Types are found in the "Project Type" field on Program Enrollment records if you need to review the list of options
  4. Service Site
  5. Click "Export" in the Report Actions panel on the right side of the page

2. Edit the feeder reports

 The feeder reports are located at My Apricot > My Reports > Feeders for HUD APR/CAPER 2020:

  • 1 - Demographics
  • 2 - Contacts & Engagements
  • 3 - PIT Counts
  • 4 - Chronically Homeless
  • 5 - 19 (a1 - a3)
  • 6 - Miscellaneous

For each feeder report, hover over the grey "Actions" to the right of the report's name and click "Edit". 

Edit each feeder report and enter the Report Start Date, Report End Date, and Program in the corresponding Global Values fields in the Global Values section at the top of the report. 

You can leave the Funder Global Value blank if you do not wish to narrow your results to a specific Funder. Once the Global Values are correct, publish the feeder report. 

IMPORTANT TIP:
The Report Start Date and Report End Date Global Values MUST be populated.


All feeders must have matching Global Values unless the Global Value instructs otherwise. Here are the two exceptions:

For the "3 - PIT Counts" feeder report there are four additional Global Value dates to enter. Each corresponds to the date of the last Wednesday of a fiscal quarter. You will need to consult a calendar to be sure you have the correct date for each Point-in-Time date during your reporting start and end dates.

In the "2 - Contacts & Engagements" feeder, the Program is set to Street Outreach and MUST NOT be changed. Also, only in this report, the Report End Date Global Value asks the users to extend the Report End Date by one day.

After editing the Global Values and publishing the five feeder reports, you are ready to run the aggregate ESG CAPER or HUD APR report.

2. Run the aggregate ESG CAPER, HUD APR or HUD Data Quality Report

The 2020 ESG CAPER is divided into four parts. Find them at My Apricot > My Aggregate Reports > CAPER 2020:

  • CAPER 5a - 9b
  • CAPER 10 - 20
  • CAPER 21 - 22
  • CAPER 23 - 26

The 2020 HUD APR is divided into four parts. Find them at My Apricot > My Aggregate Reports > HUD APR 2020:

  • HUD APR A 2020
  • HUD APR B 2020
  • HUD APR C 2020
  • HUD APR D 2020

The HUD Data Quality 2020 report is at My Apricot > My Aggregate Reports > HUD Data Quality Report 2020.

For each part of an aggregate report, hover over the grey "Actions" and click 

  • "Run" if you wish to review your data, or 
  • "Export" if you wish to bypass the data review step.

Reviewing the aggregate results

Hover over the grey "Actions" and click "Run". The aggregate report will compile and load its results, which you can review for data quality. Each cell in the report holds a number which is clickable and shows row results for that count.

When the number is clicked, a popup window shows the row results. You can click into any row in the results to edit a record if necessary.

After editing any records in the results, you will need to refresh the report results.

Refreshing the report results

Find the Report Actions panel on the right side of the page and click the "Refresh Data Sources" button. After a brief pause the report will recompile its results. Once you are happy with the results, you may choose to export the data.

3. Exporting the results

Aggregate report grids can be exported individually as CSV files for submission to your funder. 

TIMESAVER TIP! 

  • Create a folder on your desktop to export your CSVs into.  
  • Set your browser to ask where to save downloads. Here are the instructions for Apricot's recommended browsers, Chrome and Firefox.) The report will download the grid and ask you what to name it and where to save it.
  1. Find the Report Actions panel on the right side of the page and click the "Export" button. A popup window offers you the option to export "All Grids" or "One Grid" 
  2. Pick "One Grid" to export your data for submission to your funder
  3. Pick the grid from the dropdown list
  4. Select "CSV" for the File Format
  5. Click the "Export" button

TIMESAVER TIP! 

Set your browser to ask where to save downloads:
Here are the instructions for Apricot's recommended browsers, Chrome and Firefox.

The report will download the grid and ask you what to name it and where to save it.


Name the document "Q" followed by the number. For example, grid 22a2 would be named "Q22a2". See the Naming Convention visual aid below.

Repeat these steps for each grid in the report. When you finish exporting each grid, you should have 39 CSVs for a CAPER, or 69 CSVs for an APR, in the folder you created on your desktop.

Edit the exported Q5a CSV and delete the first row in the file. Save and close Q5a.

Zip the folder of CSVs
After editing Q5a, select all the CSV files in the folder, right-click on them, and select "Send to Compressed (Zipped) Folder". This will create a zipped folder inside the folder with your exported CSVs. Move the zipped folder out of your CSV folder in order to test it in Sage or send it to your funder.

Data Check Reports

If you find discrepancies or inaccuracies in your aggregate results, you may need to do some data cleanup. 

There are data check reports you can run which will help you quickly locate any records which are missing data necessary for accurate results. 

*Unlike the Feeders, you do not need to edit the Data Check reports, just run them instead. 

You can find them at My Apricot > My Apricot Tools > My Reports > Feeders for HUD APR/CAPER 2020:

  • HMIS Entry and Exit Check 
  • Household Check 
  • Chronic Homelessness Check 
  • October 1, 2017 Specs Checks 
  • Common Data Errors Check 
  • HMIS Entry and Exit Check This report locates clients who are missing either an Entry or an Exit status HMIS record. 
  1. Run this report and enter the Report Start Date, Report End Date, and Program in the corresponding Global Value fields located in the Global Values section at the top of the report.
  2. For any results found in the report, click into the red cell and create the missing HMIS record according to the instructions in the section's header bar.
  3. Refresh the report's page and then the flagged record should no longer be included in the results.
  4. Repeat these steps until the report finds no results.

  • Household Check This report locates clients who are missing a Household record.
  1. Run this report and enter the Report Start Date, Report End Date, and Program in the corresponding Global Value fields located in the Global Values section at the top of the report.
  2. For any results found in the report, click into the red cell and create a Household record or add the client to an existing Household record.
  3. When you refresh the report's page, the flagged client should no longer be included in the results.
  4. Repeat these steps until the report finds no results.

  • Chronic Homelessness Check This report locates any HMIS records which are missing data needed for the Chronic Homelessness status algorithm.
  1. Run this report and enter the Report Start Date, Report End Date, and Program in the corresponding Global Value fields located in the Global Values section at the top of the report.
  2. For any results found in the report, click into the red cell and edit the HMIS record to correct the data.
  3. When you refresh the report's page, the flagged record should no longer be included in the results.
  4. Repeat these steps until the report finds no results.

  • October 1, 2017 Specs Checks This report locates any active client's records created before Oct 1, 2017 which need to be updated. 
  1. Run this report and enter the Report Start Date, Report End Date, and Program in the corresponding Global Value fields located in the Global Values section at the top of the report.
  2. For any results found in the report, click into the red cell and edit the record to correct the data.
  3. When you refresh the report's page, the flagged record should no longer be included in the results.
  4. Repeat these steps until the report finds no results.

  • Common Data Errors Check This report identifies records containing common data entry errors which would adversely affect the report totals. 
  1. Run this report and enter the Report Start Date, Report End Date, and Program in the corresponding Global Value fields located in the Global Values section at the top of the report.
  2. For any results found in the report, click into the red cell and edit the record to correct the data.
  3. When you refresh the report's page, the flagged record should no longer be included in the results.
  4. Repeat these steps until the report finds no results.
Did this answer your question?