25 May 2012

Hints & Help: Analysis of New Intake by Postal Area


Recently we were asked if there was any way that Ensemble could show an analysis of new intake by postal area so. Clearly it’s important to know where your new students are coming from and perhaps even more important to know where they are not coming from so that steps can be put in place to address the lack of interest.

With Ensemble it’s a simple process to get a list of the entire new intake for a period of time. From the Pupils menu select the Pupil Extract option. On the second tab entitled Pupils, select the option for One Record per Pupil Activity. In the Starters/Leavers box select New Starters and select the relevant date range. Note you can select a specific period (term), a date range or a specific date. You should also select the type of record to include, i.e. new starters at school activities, centre activities etc.


You can add other criteria to the extract by selecting other options on this or other tabs but once you have set all the required options click on the Extract Data button. This will select all the matching records and display in Insight.

Note in the screen shot below I have used the Column Selector to remove all the columns we don’t need leaving me with just the Gender, Post Code and Activity. Notice also that as well as providing a column of information containing Post Codes the system has also provided a column of just post code areas. I have kept Gender and Activity so that we can also analyse by these values.


Now that we have the raw data it’s a simple job to group the data by Post Code Area and then create a New Summary Dataset of the result.


Once you have done this you will have a second dataset showing a breakdown of postal areas and the number of new starters.


If you want you can visualise these results in a graph by clicking on the New Chart button and following the steps. An example of this is shown below.


The above provides a good visualisation of new starters per postal area but say you wanted to go one stage further and show how these values are made up between boys and girls.

We have a column called Gender which contains F or M so we just need to reorganise the data we originally extracted to show a count of these for each Post Code area. We can do this in the following way.

Go back to the first tab where the extracted data was first shown and where you grouped the data by Post Code Area. You need to add the Gender column to the grouping but in order to do this you need to show the columns again. (They would have been hidden when you group by Post Code Area). To do this, click on one of the small “+” plus signs over on the left. This will expand the grouping and show the columns. From here drag the Gender column up into the group by area as before and then create another summary dataset by clicking on the summary dataset column. You will now have a new dataset as below.


We now need to Pivot the result so that we get count for each gender. To do this, click on the Pivot Table button and work through the wizard.

Select the Post Code Area as the Row Label.


Select Gender as the Column Label


Finally select _Count as the Summary Label.


Click on the Create button to create the new dataset which should look like this.


You can now use the chart wizard to create a new chart which will visualise this data. You will need to select a type of chart that will display the female and male values for each post code area such as a Bar or Column chart.


Select PostCodeArea as the Label column and tick both the available series columns in order to show both Female and Male values


The result is a chart that clearly shows the new starter counts for each post code area split between Female and Male. You can easily change the breakdown from Gender to some other category such as instrument by group by the appropriate column.

A word of caution though, not all postcode areas have the same number of pupils living in them and so an area showing a large intake may in fact have a large number of possible pupils in that area.

Hints & Help: Teacher CRB Information for Schools


Schools often ask for CRB information on the teachers you schedule to work with their pupils. All this information is stored within Ensemble so it’s a simple process to extract this information an produce on a report.

The first thing you need to do is extract teacher information in order to get at the teacher details including the CRD information and the schools they work at. To do this, select the Extract Teacher option on the Teachers menu. On the Teachers tab click the box to include a record for each lesson. You will also need to select the period of time covering the time the teachers will be at the school.


Clicking on the Extract button will send the extracted information to Insight. This will show a line for each lesson a teacher is schedule to work. This is more information than we need so we can remove a number of columns. To do this click on the “Select Columns” button, this will display a list of columns. Click the “None” button to remove all the columns and then tick the columns required. These are ActivityType, ActivityTypeID, TeacherID, LastName, FirstName, PoliceCheck, PoliceCheckSubmit, PoliceCheckRef, Venue, VenueAddressLine1, VenueAddressLine2, VenueTown, VenueCounty and VenuePostCode.

This will show the column information we required but it currently shows a line for every type of lesson each teacher in involved in. So we need to filter out all but school lessons and organise it so that we have a single line for each school per teacher.

Click on the Filter button and add a filter to the ActivityType column to select just those with a value of “S”. This will select just the school lessons. No drag the ActivityTypeID column heading up into the Group by Area. This will group the data by School but we also need to group by teacher. In order to do this you need to show the columns again. (They would have been hidden when you group by Venue). To do this, click on one of the small “+” plus signs over on the left. This will expand the grouping and show the columns. From here drag the TeacherID column up into the group by area as before and then create a New Dataset.


It’s important to drag the ActivityTypeID first and then the TeacherID column.

After clicking on the Create New Dataset button you will set the following.


As you can see the new dataset has all the same columns but now you only have one line per school/teacher combination.

Now that you have the data organised as you want it it’s a simple case to create a report containing a page for each school with a list of teachers working in the school together with their CRB information.

To create your report click on the Produce New Report button, this will take you through a report wizard consisting of a number of pages, each of which asks questions about the report you require.

On the first page you can enter a name for your report. The second page asks how you want to group the report. The grouping specifies how the report in organised, in this report we want all information to be grouped by school. We can therefore add Venue to the Group By list. You may have more than one school with the same name and so to ensure that each school have their own group we need to add a unique identifier to the grouping. The column ActivityTypeID uniquely identifies each school so select this column and add to the Group By list.


The next page of the wizard asks for the columns we want displayed on the report. We want to list the teachers name and CRB information so select FirstName, LastName, PoliceCheckSubmit, PoliceCheck and PoliceCheckRef columns.


The next page asks for any summaries you want added to the report. You used this to add totals to columns. In this report we don’t require any summaries so we can skip to the next page. This page asks how we want the data in each group ordered. We will order ours by the teacher’s last name so add LastName to the Order By list.

The next page allows you to specify how you want the report sectionalised. The reason for creating sections is to split a report up into areas covering one person or in our case one school. Sections are useful they allow you to email each section out to the related recipient.

With this report we want to have the option to email the information to each school so we select to section it by ActivityTypeID, this is the column that identifies each school.


We now have all the information to create our basic report so we can click on the Create Report button. This will generate the report and display it on the screen. The report has the teacher information but we now need to add the School details and tidy up its look.

To do these we need you switch to the report design view and from here make a few changes. The design view looks like this.


The design is split up into a number of regions.

· Page Header – Defines the area at the top of each page.

· Page Footer – Defines the area at the bottom of each page

· Report Header – Defines a page to include at the beginning of the report

· Report Footer – Defines a page to include at the end of the report.

· Header and Footer for each group – Defines the information to display at the beginning and end of each group area whenever the Group By value changes.

· Detail – Defines what included for each record in the dataset.

Our report has two groups one for ActivityTypeID and one for Venue. We need to hide the first group header and its footer. To do this right click somewhere on group header area and select Hide, then do the same for the group footer.


We now need to expand the heading for ActivityTypeID, do this by hovering over the line along the bottom of the area until the cursor changes then hold down the mouse and drag the line down so as to make the area bigger. You will see that the report wizard placed a field to display the ActivityTypeID within this area. We need to remove this by clicking on the field and then hitting the delete button.

We can now add the School name and address by dragging these fields from the field explorer over on the left hand side. Expand the Database fields node by clicking on the plus sign. Find the field called Venue and drag this into the group area. No repeat this for the other address fields. You can align the address information by dragging the fields around the area. You should end up with something like this.


To make the report look better you can also remove some of the column headings and move some of them down into the group area. The final design looks like this.


If you click back on the View Document button the report will be produced and should look something like this.


If you want to re-use the report at a later date you should switch back to design mode and click on the save definition button.