Client Information and Reporting

If you are planning to use the IPS Grow Reporting Tool, we recommend using the IPS Grow Standard Spreadsheet, but it is possible to upload any other spreadsheet providing that:

• The headings you want the Tool to read match the IPS Grow Standard Spreadsheet. It does not have to be in the same order, but the headings must have the exact same wording.
• The header row with the titles of all the columns occurs within the first 10 rows of the sheet.
• The information is contained within 1 tab.
• There is only one row per client.

Should I copy my data to the IPS Grow Standard Spreadsheet or should I adapt my own spreadsheet(s)?
This is not a question with a straightforward answer. We generally recommend you copy your data into the Standard Spreadsheet, but the best route for you depends on your specific situation:

Comma-separated values (CSV) is the format the spreadsheet needs to be to use with the Reporting Tool. To import your Excel spreadsheet to the Reporting Tool, you will need to convert it to a .csv file first.

Before converting your file to CSV save a version in Excel
First, ensure you have saved a copy of your Excel file. You will need to continue using the Excel Spreadsheet in the future, and it is not possible to convert a CSV file back to an Excel file.

How to convert the Excel to CSV
To do this, click on the File tab in your Excel spreadsheet, and then click Save As.
In the ‘Save As’ type box, choose to save your Excel file as CSV (Comma delimited)

Please click here to view the image.

In addition to CSV (Comma delimited), you’ll notice a number of csv formats available:

• CSV (MS-DOS) (*.csv)
• CSV UTF-8 (Comma delimited) (*.csv)
• CSV (Macintosh) (*.csv)
• CSV (MS-DOS) (*.csv)

You can save your spreadsheet in any of these formats to upload to the Tool.

Once you have selected a CSV type, choose the destination folder where you want to save your CSV file, and then click ‘Save’.

After you click ‘Save’, Excel will display two dialogs. Don’t worry, these are not error messages and everything is going right. The first dialog is to remind you that only the active Excel tab will be saved in the CSV file format. This means that if you have more than one tab in the excel file (e.g. the cover sheet in the Standard Spreadsheet), they will not be converted to CSV. Click OK.

Please click here to view the image.

This is fine, simply click Yes.

That’s It! You’ve now converted your Excel to CSV.

The Standard Spreadsheet includes the recommended datapoints for an IPS service to collect. However, your service may collect additional information specific to your contract/local context.

You can customise the IPS Grow Standard Spreadsheet to include the additional information/data your service wishes to record. For example you may wish to track softer outcomes for your clients such as volunteering, or education etc.

Before customising the spreadsheet, what do I need to know?

Heading colours are very important!

Please click here to view the image.

Do not delete “Mandatory” columns (pink)
The IPS Grow Reporting Tool will not work if you delete any of these columns.

The Standard Spreadsheet is password protected
Before you can make changes to the format or fields you will need to unprotect it by entering the password IPSGrow. You do not need to do this to add data to the sheet – this is just if you want to customise the format.

Protect the spreadsheet after you have customised it
This stops mandatory (pink) fields from being deleted by accident in the future and ensures that the format remains consistent across your quarters for better reporting. We recommend keeping the password as IPSGrow so that it is easy to remember for you and future team members.

The IPS Grow Reporting Tool only reads original fields 
New fields that you create will not appear in any service dashboards or reports, however you may still find it useful to track them in your spreadsheet. The IPS Grow Reporting Tool can only be used to measure the data that is entered under the original Mandatory (pink) and Optional (teal) columns.

What changes are safe to make if I want to use the Reporting Tool?

• Remove Optional (teal) or Other (white) columns to suit your service’s need.
• Add new columns to suit your services need.
• Give Optional (teal) or Other (white) columns different headings to the original ones.
• Add additional drop-down options.
• Delete rows of data.

For more support on customising the IPS Grow Standard Spreadsheet to suit your needs, please email reportingtool@ipsgrow.org.uk 

Unprotect the Spreadsheet before making any customisations

1. Open your IPS  Grow Standard Spreadsheet excel document.
2. Select “Review” in the tool bar, then click “Unprotect Sheet”.
3. Type in the password IPSGrow and click “OK” to unprotect the worksheet.

Please click here to view the image.

Protect the Spreadsheet once you’ve made all your required customisations

1. Select “Review” in the tool bar, then click “Protect Sheet”.
2. Type in IPSGrow (same password that was used to unprotect it), and click “OK”.

Please click here to view the image.

1. A new window pops up prompting you to Confirm Password.

Please click here to view the image.

1. Re-enter the password IPSGrow and click “OK”.
2. Your spreadsheet is now be protected again.

Before you change any headings, remember that the IPS Grow Reporting Tool can only read the data that is entered under the Mandatory (pink) and Optional (teal) column headings if you keep the original wording. If you make any changes to wording of the Mandatory (pink) headings the IPS Grow Reporting Tool will not work. You may wish to change the wording of the Optional (teal) or Other (white) columns. However the Tool will no longer be able to capture the data you enter – so you may have blanks in your charts.

1. Follow the steps to Unprotect your Spreadsheet.
2. Double-click on the box that you wish to change the wording of.
3. You will now be able to delete the existing words and enter your preferred wording.
4. Follow the steps to Protect your Spreadsheet again.
5. Remember any changes you make to the original wording of teal or pink headings will impact your ability to use the reporting tool.

How do I delete multiple rows of data?

1. Follow the steps to Unprotect your Spreadsheet.
2. Select the row you want to delete by clicking on the number of the first row and dragging down to the last row you want to delete, this should select your rows (in our example we want to delete rows 7, 8 and 9 highlighted in yellow).
   Please click here to view the image.  All data in this image is dummy data
3. Then right-click and select “Delete” from the dropdown options as shown.
4. Please click here to view the image.
   All data in this image is dummy data
5. Repeat until you have deleted all the columns you needed to.
6. Follow the steps to Protect your Spreadsheet again.

How do I delete columns?
Before you delete any columns, remember that if you delete any Mandatory (pink) columns the IPS Grow Reporting Tool will not work. You may delete Optional (teal) columns but this will impact your reports – some charts may appear blank or incomplete. Deleting Other (white) columns will not impact the tool or reports at all.

1. Follow the steps to Unprotect your Spreadsheet.
2. Select the column you want to delete by right-clicking on the letter at the top of the column (in our case we will right-click E as we are going to delete column E with Date of birth information).
   Please click here to view the image.
3. Click “Delete” from the drop down options.
4. Repeat until you have deleted all the columns you needed to.
5. Follow the steps to Protect your Spreadsheet again.

How do I add new columns?
Before adding columns, remember that the IPS Grow Reporting Tool will not read this information in the reports and charts that it generates. You should only add columns if there is additional information that your service wants to track.

1. Follow the steps to Unprotect your Spreadsheet.
2. Right-click the column to the right of where you want your new column to be – do this by right-clicking on the letter at the top of the column (in our case we will right-click on G as we want to insert a new column between column F and column G).
   Please click here to view the image.
3. Click “Insert”.
4. A new column will be created. Notice that the colour of the header matches the colour of the column you selected in step 2 (in our case column G is teal so our new column is also teal).
5. Change the colour of your new column’s heading to white in order to differentiate it from the coloured columns that are read by the IPS Grow Reporting tool. Do this by selecting “Home” from the tool bar, and selecting the box that you want to change the colour of (in our case cell G3). Then click on the “Paint” button and click on the white square (as shown).
   Please click here to view the image.
6. Double-click on the new white box, and type the heading of your choice. Click enter on your keyboard once done.
7. Follow the steps to Protect your Spreadsheet again.

1. Follow the steps to Unprotect your Spreadsheet.
2. Right-click on “Standard Spreadsheet” tab at the bottom of your spreadsheet and click “Unhide” from the dropdown list as shown.
   Please click here to view the image.
3. Ensure “Hidden Sheet – Dropdowns” is selected and click “OK”.
   Please click here to view the image.
4. Unprotect the new sheet yo uhave just unhidden by clicking “Review” in the top tool bar, and “Unprotect Sheet”, and entering the password IPSGrow and click “OK”.
   Please click here to view the image.
5. You will now be able to enter new dropdown options by adding words to a new cell below the existing list (in our example we will add the option ‘Rather not say’ to the existing Work Related Benefits options list).  Please click here to view the image.
6. Once done, return to the Standard Spreadsheet tab, and select any cell under the column with the header of the list you amended (in our case Work Related Benefits). Then click “Data” from your top tool bar and select “Data validation” (the icon with the green tick and red  circle).
   Please click here to view the image.
7. Extend the Source selection so that it includes your new dropdown options. (In this case this meant updating the 12 at the end of the source code to 13 $C$2:$C$13). Remember to tick the box “Apply these changes to all other cells with the same settings” before clicking “OK”.
   Please click here to view the image.
8. Re-hide the “Hidden Sheet – Dropdowns” tab by right-clicking on the tab at the bottom of the page, (similar to Step 4) and click “Hide”.
   Please click here to view the image.
9. Follow the steps to Protect your Spreadsheet again. 

Need help uploading data to the tool?
Download the IPS Grow Reporting Tool User Guide.

I’ve uploaded my spreadsheet, but it hasn’t populated my dashboard?
There could be many reasons for this:

• Check your column headings. The Reporting Tool will only read the columns headings it recognises, which means they must match the wording in the IPS Grow Standard Spreadsheet.
• Check that you selected the right quarter when uploading the spreadsheet.
• Check the date format of your Excel spreadsheet. Dates should be recorded as DD/MM/YYYY. Please note that entering text in a column requiring a date will throw up an error.
• If you suspect the Reporting Tool is broken, please contact ips.support@bjss.com.

If you would like help fixing or adapting your spreadsheet, please contact the IPS Grow Lead for your region.

Can I upload several spreadsheets to the tool, for example if my employment specialists each have their own spreadsheet?
Yes, the tool will automatically aggregate data from different spreadsheets, as long as you upload them together and they are all compatible with the tool.

How do I upload data from case management systems / clinical systems to the tool?
If your existing case management or clinical system already records all the information you need, you may not want to switch to the IPS Grow Standard Spreadsheet. As long as your case management system allows you to export data in an excel or csv format, you can upload these files to the Reporting Tool to populate your dashboard or quarterly return.

When you use the IPS Grow Standard Spreadsheet to manage your caseload, you may sometimes only want to see your active cases, or if you’re looking for someone that you’ve worked with a year ago, you may want to see only the discharged cases. It can be helpful to know how to use the filtering function in situations like these.

How do I filter by caseload status?

1. Find the ‘Caseload status’ column in your standard spreadsheet. At the moment the column is showing a mix of clients with different caseload statuses. Click on the arrow next to the ‘Caseload status’ and a window will pop up revealing the filter options:
   Please click here to view the image.
   All data in this image is dummy data
2. There is a list of different caseload statuses with ticks next to them. Remove the ticks from all but the statuses you want to see (in our case that is only ‘Actively seeking work’) then click OK.
3. The sheet will now display the rows that have the caseload status ‘Actively seeking work’ and the arrow icon at the top will have a funnel next to it to indicate that you have filtered out some rows.
   Please click here to view the image.
   All data in this image is dummy data
4. Be mindful that the rows in between did not disappear, they are just hidden! Always remove the filter before you add or delete cells in your spreadsheet; you don’t want to accidentally delete hidden rows.
5. You can easily remove the filter by clicking on this arrow/filter icon and choosing “Select All”.
   Please click here to view the image
   All data in this image is dummy data

Date of first attempted contact
This should be the date that the employment specialist first attempted to contact the client via phone, email, text, or face to face. Some may choose to include this date even if the client did not respond to the attempted contact.
The IPS Grow recommendation is that first attempted contact should happen with 5 days.

Date vocational profile started
This is the date that the client and employment specialist started work on the vocational profile.
IPS Grow recommends using this as a measure of the number of clients that have engaged with the service.
The vocational profile is a living document that can be updated as a client develops their employment goals. Therefore, IPS Grow recommends not waiting until a vocational profile is “completed” to count a client as engaged.

Date of first face to face employer contact
Date of the first face to face contact with an employer, either by the client or by the employment specialist if on behalf of the client.

Caseload status:

• On waiting list – clients who have been referred but are on a waiting list due to full caseloads.
• Actively seeking work – clients who have started the job search process.
• In-work support – clients who have gained a job and are being supported to remain in that job. Support may be more intensive in the first month of in-work support.
• On hold – clients who have not been discharged but are not working with the service for a period of time, often due to health reasons.
• Discharged – clients who have been discharged from the service. Remember to enter a discharge date in the next column otherwise they will continue to appear in the employment specialist’s caseload.

If there is unreadable data in your spreadsheet, the Reporting Tool will show an error message which will point out where the error lies.

For example, the error message below highlights various errors in the spreadsheet and gives details so you can make the necessary amendments.
Please click here to view the image.

When you have fixed the errors, you can re-upload your spreadsheet.

Example 1 – freeze rows

When you have a lot of clients and have to scroll down in the IPS Standard Spreadsheet it is useful to freeze the header row – in example 1 we want to freeze rows 1-3;
Please click here to view the image.

1. Before beginning make sure that there are no frozen panes in the sheet! To do this, select “View” in the tool bar and click on “Freeze Panes”; if there are frozen panes in your sheet, the first option in the dropdown list will be “Unfreeze Panes” – click on it.
   Please click here to view the image.
2. In order to freeze rows 1-3, first select cell A4 – we want to select this cell, because everything above and to the left of that cell will be frozen in the next step.
   Please click here to view the image.
3. Then select “View” on the toolbar and click “Freeze Panes” – select the first option from the dropdown menu (also called “Freeze Panes”).
   Please click here to view the image.
4. You should now be able to scroll down with the first three rows frozen at the top!

Example 2 – freeze rows and columns

In addition to the header row, it can also be useful to have information such as Client ID when scrolling to the right – for this we need to freeze rows 1-3 and column A.

1. To do this you need to select cell B4 and then freeze the panes by repeating step 3 from Example 1.
   Please click here to view the image.
2. We have to select cell B4, because the Freeze Pane function freezes everything above and to the left of the selected cell.
3. Another example would be: if we want to freeze the header row (rows 1-3) and freeze the Client ID and Client name, then we have to select cell C4 before selecting Freeze Pane.

First, unprotect the Spreadsheet:

1. Open your IPS  Grow Standard Spreadsheet excel document.
2. Select “Review” in the tool bar, then click “Unprotect Sheet”.
3. Type in the password IPSGrow and click “OK” to unprotect the worksheet.
   Please click here to view the image.

Then, change the referrer name to “any value”:

On the left hand side of your Spreadsheet you should see a list of numbers. Each number represents a row.
If there are numbers missing from the list (e.g. the numbers between 3 and 8 that are missing in the example below) it means that some rows are hidden from views. This could be because you have purposely filtered some rows out in which case you don’t need to do anything. But if you can’t see a filter icon in the headers, it probably means that the rows are hidden.
Please click here to view the image.

How to unhide rows

1. Select the two rows either side of the missing rows by clicking on the last number you can see in the sequence before it breaks, and dragging down to the first number when the sequence returns (in our example this means clicking on the 3 and dragging down to the 8).
   Please click here to view the image.
2. Right-click on the selected region, and click “Unhide”.
   Please click here to view the image.
3. Your hidden rows will now be visible.

Don’t Enter Text into Number or Date Columns
Ensure that you only record dates in date columns (no text). Any notes  you have can be added as a comment or recorded in the comments section at the end of the spreadsheet.

Don’t Use Multiple Rows to Record Information on a Single Client
Everything you need to record about a client should fit in a single row. If you are using the IPS Grow Reporting Tool it is important not to record a client twice in the same Spreadsheet as the Tool will then count them as two people. The Standard Spreadsheet includes a “duplicates check” which will help you pick up on any clients accidentally listed more than once.

Evidence of Job Starts
Some providers collect evidence of job starts. IPS Grow recommend the following forms of evidence for job starts:

• Job offer letter/ contract.
• Confirmation of employment of at least 1 day e.g: a time-sheet.
• Payslip or signed declaration (from client or employer).

The IPS Grow Standard Spreadsheet can record up to five jobs for a single client. If you choose to use these fields, the IPS Grow Reporting Tool can calculate job sustainments for you. Simply make sure columns T to AR are included in your Spreadsheet and delete columns AS and AT before you upload your Spreadsheet to the Tool.

The Reporting Tool uses the IPS Grow recommended calculation for job sustainments which is defined below:

Sustained employment is measured regardless of whether this duration was accrued over the same job or multiple jobs. Breaks in employment for clients with multiple jobs must be less than 6 weeks between jobs. If a gap of more than 6 weeks occurs, the clock will reset and sustainments will be calculated from the start date of the subsequent job. Only one job sustainment outcome of each duration (13, 26 weeks) will be counted for each person referred into the service.

If you would prefer to enter your own sustainment dates simply use columns AS and AT to enter your 13 and 26 week sustainment dates for the client. Remember to still enter the date the client started their first job in column T, so that the tool can calculate the number of job starts.

Why does the spreadsheet only measure up to 26 weeks?
After a number of informed discussions and consultations, most services were in agreement that the optimum period to measure direct outcomes was within the first 26 weeks. Therefore sustainment within the tool is measured up to 26 weeks rather than 52 weeks.