FOI: Children in Temporary Accommodation

This article explains how to run reports in Locata to answer Freedom of Information (FOI) requests regarding households with children in temporary accommodation.

You’ll learn how to extract and process data to identify households with children under 5 and under 12, along with how long they’ve been in temporary accommodation, including B&B placements.

If you are unfamiliar with running reports in Locata, you may want to read this article first.

TABLE OF CONTENTS

• What you'll need
• Running the Client in TA report
• Running the People List report
• Preparing your Excel workbook
• Calculating Weeks in TA
• Filtering and counting households
• Counting the number of children
• Creating PivotTables for totals

This article will show you how to fulfil FOI requests, such as the below:

What you'll need

This section outlines the tools and access required before starting.

1. Access to Locata reports.
2. Microsoft Excel.
3. The two reporting dates from the FOI request (e.g. September 2023 and September 2024).

Running the Client in TA report

This report shows households in temporary accommodation and forms the base data for the FOI.

1. Log into Locata and navigate to the Reports tab.
2. Click "Clients in Temporary Accommodation" under Other Reports. When you click this it opens a new default tab with today's date. 
3. Change the date to the first date (e.g. 30 Sept 2023). There are filters to select a certain partner or tenancy type. 
4. Copy the report data into a new Excel workbook. To do this, press Ctrl+A, then Ctrl+C, open excel and press Ctrl+V.
5. Delete the rows above the table, so that the column headings are at the top of the page. To do this, highlight the rows you wish to delete, right click and press "Delete". 
6. Add the following new columns: Weeks in TA, U5, U12, B&B.
7. Press Ctrl+T, then "OK" in the dialog box. This will display the dates. Sometimes copying from the system will export dates as serial numbers into excel, if this step doesn't work, it may be useful to copy into Notepad first then Excel. This will make it more likely for Excel to read it correctly. The try completing this step again. 
8. Optional - Press Ctrl+A, then hold "alt" and press "HOI" to resize the columns.
9. Title the sheet: Clients in TA - Sept 23. Save a copy of the workbook. 
10. Repeat steps 1–10 for the second date (e.g. 30 Sept 2024).

Running the People List report

This report helps identify children under 5 and under 12 linked to the households in TA.

1. In Locata, go to the Reports tab and select the "People List Report".
2. Filter the data to show only children under 5 (U5) and click "Generate List".
3. Export the filtered list to Excel by pressing Ctrl+A, then Ctrl+C, then in Excel create a new sheet and press Ctrl+V.
4. Delete any rows above the column headers. To do this, highlight the rows you wish to delete, right click and press "Delete".
5. Name the new tab U5.
6. Repeat the process for children under 12 (U12) and name the tab U12.

Preparing your Excel workbook

You’ll now link the household records to the child data using Excel formulas.

1. Go to the Clients in TA - Sept 23 sheet.
2. In the U5 column, copy and paste this formula into the first cell of the column and press "Enter". Sometimes you need to click in the cell box above the worksheet and click "Enter" for it to work. If no data matches, it will display 'Not found' : =IFERROR(XLOOKUP([@Code],'U5'!B:B,'U5'!E:E),"Not found")
3. In the U12 column, copy and paste this formula into the first cell of the column and press "Enter". If no data matches, it will display 'Not found': =IFERROR(XLOOKUP([@Code],'U12'!B:B,'U12'!E:E),"Not found")
4. In the B&B column, copy and paste this formula into the first cell of the column and press "Enter". If no data matches, it will display '0': =IFERROR(COUNTIF([@[TA Tenancy Type]],"Privately managed Bed & Breakfast hotels (privately managed, meal/s provided, shared facilities)"), 0)
5. Repeat for Clients in TA - Sept 24. 

Calculating Weeks in TA

This step calculates the total weeks each household has been in temporary accommodation.

1. In the Weeks in TA column, copy and paste this formula into the first cell of the column and press "Enter". This will round the answers it finds to one decimal place: =IFERROR(ROUND([@[Total Days In Tenancy (as of 30 Sep 2023)]]/7, 1), "")
2. For the Client in TA - Sept 24, use this formula: =IFERROR(ROUND([@[Total Days In Tenancy (as of 30 Sep 2024)]]/7, 1), "") 

Filtering and counting households

Use Excel filters to answer questions about length of stay and B&B placements.

1. Filter the U5 column for value 1. To do this, click the arrow in the column heading, select "1" and click "ok". 
2. Count all rows where Weeks in TA is over 6. It may help to rearrange the column from smallest to largest. To do this, click the arrow in the column heading and click "Sort Smallest to Largest". 
3. Count all rows where Weeks in TA is over 26.
4. Repeat the steps for the U12 column.
5. Filter B&B column for value 1, then repeat steps 2 and 3 for these results.

Counting the number of children

This section explains how to calculate the actual number of children—not just households.

1. Open the U5 sheet.
2. Add two new columns titled: No U5 Sept 23 and No U5 Sept 24.
3. Use these formulas respectively: 

=ISNUMBER(MATCH(B2,'Clients in TA - Sept 23'!B:B,0))

=ISNUMBER(MATCH(B2,'Clients in TA - Sept 24'!B:B,0))

        4. Copy the formulas down both columns.

        5. Repeat the process in the U12 tab.

Creating PivotTables for totals

PivotTables allow you to summarise the total number of children per reporting period.

1. In the U5 tab, go to Insert, click "PivotTable".
2. Enter "Table1" and select "New Worksheet", Click "OK". 
3. Rename the sheet PIVOT U5.
4. Drag No U5 Sept 23 into Rows on the right hand side panel.
5. Drag Case ID into Values.
6. Click on Sum of Case ID.
7. Click on "Value Field Settings".
8. Select Count and click "OK".
9. Change the row field to No U5 Sept 24 to get the updated total.
10. Repeat all steps for U12 in the U12 tab.