Reports (Administrator guide)

About the systems' reporting features

For most organisations, the ability to produce reports from the system is probably the most important aspect of the whole system. The system provides many ways to get the data that users enter on the system back out again. The most obvious ones are the preset reports, which are designed for a specific purpose and cannot be customised by the user. These are scattered through the system - for example, reports for the Support Worker module are usually accessible from the same submenu as the rest of the module's functionality. Some of these simple reports are also in the "Reports" submenu, such as the Office Reports, and these are dealt with in the end user manual: Reports.

The rest of these reports are dealt with together with the activity they relate to, so details of the Support Worker reports are in the Administrator guide to the Support Worker module, and so on. This chapter deals with the customisable reports, which are where the real power of the system's reporting lies. This chapter looks mainly at three features: Blue Folder Lookups, KPI Reports and Data Extraction.

Blue Folder Lookups

If you have tried out some of the simple reports, you may have seen blue folders displayed on some of the outputs. For example, running the "Memberships/Skills Report" shows all of the Organisations/People on your system who belong to/have Groups and Skills. The basic output of the report looks something like this:

This output is fine, as long as it contains the information you need, but suppose that you want to cross-reference this output with some other information. The "real world" thought process might go like this -

We ask all of our clients whether they consider themselves computer literate, and we use a skill group called "computers" to log this, putting them in the group if they are computer literate. We also log the disabilities our clients have, if they have any. I wonder what the data would show if I could cross-reference this with the "Computers" group? Does the fact that someone has a disability affect whether they see themselves as computer literate? If it does, does the disability in question matter? A lot of our clients have arthritis - is having arthritis likely to affect your confidence with a computer? Is there anything we can do about this?... and so on.

To answer these questions you could do the following -

Run the "Memberships/Skills Report" to see who is in the "Computers" group.
Export this to a spreadsheet.
Look up the relevant clients and their disabilities.
Add these to the spreadsheet.

Steps 1 and 2 are easy; steps 3 and 4 will take a long time. What you need is an easy way to say to the system, "show me the ouput of this report, and add on the disability data for the relevant clients" - and this is exactly what Blue Folder Lookups allow you to do.

At the bottom of the reporting output is a blue folder.

Clicking this blue folder will take you to the lookup page.

At the bottom of the page, the data is displayed. At the top, however, we have two extra sections: Export Results Through Mailmerge, and Export This Report With Extra Data From.

Export Results Through Mailmerge

This section will allow you to put together the output of your report with a mail merge, which you have previously set up. Mail merges will be covered later in this chapter.

Export This Report With Extra Data From

This section has two ways to output data: as a spreadsheet, using the "Export This Report" button, or on a map, using the "Switch to Map View" button.

Exporting a spreadsheet

The basic spreadsheet to be exported will be roughly the same as the original report output. However, by using the Extra Data section, you can add columns onto the spreadsheet with more data in about the relevant clients.

Extra data from a Data Extraction

See later in this guide for details of setting up Data Extractions.

Once you have a data extraction set up, you can use that data extraction on any reporting output, using this drop-down box. For example, a data extraction has been set up named "EA", which shows the age range and ethnic background of any pool of clients. If you select this data extraction, and click the "Export This Report" button;

The resulting spreadsheet looks like this:

Not particularly tidy, but very informative. What you have here, after only a few clicks, is a spreadsheet showing:

All people that were active in a skill group over a certain date range
The information about the age ranges of those particular people
The information about the ethnic backgrounds of those particular people

You can use this spreadsheet to show:

Whether the people in a particular group are biased towards a particular ethnic background and/or age range

This kind of information is very useful. For example, consider disability again.

If the members of the group "Computers" (meaning the person considers themselves to be computer literate) contains no-one with a disability, does that mean that people with disabilities in your funded area are not being supported to access computers? Can your organisation do anything to help, or signpost people to an organisation which can help?

If the members of the group "Befrienders" are all younger and male, will this have an impact on how many people take up your Befriending service? Is there a need for more female Befrienders, and/or more older Befrienders?

...and so on. If you are using a paper filing system, and/or each department has its own database, you would not be able to answer these questions without a huge amount of manual data collection, counting and tabulating. Using our system means that you can do this easily, in less than a minute.

Extra data from Client-linked Records

You also have the option to add data from Client-linked records. Various options are available. Like data extractions, these will simply add more columns on to the output spreadsheet. You can select more than one set of client-linked records by shift-clicking a range (PC or Mac), or holding the "Ctrl" button (PC) or the "Command" button (Mac) and clicking the options you require.

Extra data from Extension Databases & Extras

Finally, you can add extra data from the Extension Databases you have on your system (providing they are Organisation/Person linked or Personal Tab-linked).

Spreadsheet output size

You can add as many options as you need - there is nothing to stop you using a data extract which gives 20 fields on every result, then adding all the Client-linked records, and all the Extension Database records. However, this will result in an extremely long spreadsheet which is difficult to work with. Consider breaking your requirements down into a series of spreadsheets and then work with these.

Length of spreadsheet output is one of the primary reasons that our system has moved to the .xlsx (Office 2010) output format. The old .xls format only allowed 256 columns, whereas .xlsx allows somewhere over 16,000 (not that you would want or need that many for a output).

Map View

The Blue Folder Lookup screen also allows an output to be displayed on a map. This has various uses, but all of the most obvious are to do with geographical location - for example;

Display all new clients over the last three years, and see where your client base is
Display all new clients over the last year, and see how new projects have affected your clients' uptake of your services
Display clients accessing a particular service/Project - are there holes in the coverage that cannot be explained? Do you need to make more efforts at outreach in those communities?
Check for incorrectly filled Super Output Areas - are there any outliers displayed that might be wrong?

The map view relies on postcodes to place the clients/people, so if there is no postcode entered on their record, the map view will discard them. This is one of the reasons we advise that a name and a postcode should always be a minimum for data capture.

Gathering the data in in the first place

Look again at the spreadsheet output for the data extraction "EA", which added age range and ethnic group information onto the output of the Memberships/Skills Groups report.

This demonstrates the power available in our system, of being able to get out any data you have on the system, cross-referenced with other data. However, this spreadsheet also shows the most common problem with extracting this data - the fact that users fail to enter it in the first place. The column for Ethnic Group shows that while a few people in this group have their Ethnic Group recorded on the system, the majority do not.

This is not a problem for the day-to-day running of the service; you might well imagine that a volunteer manager would not want to explicitly ask every volunteer for their age and which ethnic group they consider themselves to be in - if done wrongly, it could look confrontational and unnecessary. However, from the point of view of the chief executive, or the fundraiser, it becomes very important.

If your Computer Literacy project is only succeeding in serving people under 40, even though you know from market research that there is a real need for computer tuition for people over 60, is this to do with the people that are delivering the service? Are they all under 25? Does this have a bearing on the clients that are willing to take the service up, bearing in mind that the volunteer tutors have to enter the client's home? Are people who live alone particularly unlikely to take up this service?...

If the age range information has not been input by users in the first place, there is no way to answer, or even ask, these questions.

This is why it's so important that an organisation's data capture (and therefore data protection) policy is well thought out, and communicated to staff. Implementing our software often provides a chance for an organisation to do this for the first time - speak to your Implementation Consultant if you have any questions.

The KPI Report Designer

The KPI (Key Performance Indicator) Report Designer allows you to see "certain things by another thing" - for example, "number of new referrals by district". This really means you are asking the system, "show me all of the new referrals, along with the district of the relevant clients, and group the results together by district". The KPI Report Designer takes a 3-step approach, as shown.

You start the process by selecting the date range, Project and/or Project Subcategory you wish to report on. You then tell the system what data you want to be output. You can then proceed to step 3 and look at the data. You may now want to go back to step 2, tweak some aspects of your request, and return to step 3 again. Once you are happy with the output, if you are going to need to run this report again (a quarterly statistics report, for example), you can save the report, and return to it at a later date, changing only the date range.

Step 1: narrow the reporting output

Open the "Reports" submenu, then click on the "KPI Report Designer" menu item.

File:Reports 2.png

This will take you to the first step of the KPI Report Designer.

General Report Criteria

When creating a report you need to be able to narrow it by some fundamentals, otherwise all of the reports produced would be huge (if every report looked at all of your data). Throughout the system you are able to narrow your selections by two fundamental qualities: a date range, and a Project. The same applies here - the "General Report Criteria" tab lets you choose a date range and the Project(s) you want to look at. You can also narrow the report further by Project Subcategory.

Support Options

There are also some options specific to the Support Worker module, and these are shown on the Support Options tab.

Handyperson Options

Handyperson module-specific items are shown on this tab.

Step 2: select KPI items to print

The screen you will now see has a series of tabs, with many tick boxes displayed for each tab. (section of page shown)

The tabs group the output options, and each output option (usually) is one "thing by thing" - "new referrals by district" being an example; one Key Performance Indicator, or KPI. The tabs are as follows.

Raw Data tab

General data extractions - so these are not something by something else - rather, they will be a full data spreadsheet, which you can do with as you wish.

Referrals tab

These are to do with the Referrals on your system - the available items are mainly focused on new referrals, but there are also items for closed referrals and external referrals.

Clients Accessing tab

These items focus on the clients themselves, so even if a client has several referrals ongoing, they will only be counted once.

Contacts tab

These items focus on contacts with clients, so one client who has been into your office for four visits would be counted four times, etc.

In Project tab

Clients In Project items and Termination-specific items. For more information on the concept of "Clients In Project", see Clients In Project.

New Client tab

Items relating to clients that were new to the selected Projects in the time period.

Assessments tab

Items relating to Assessments.

Handyperson tab

Items relating to the Handyperson module.

Clubs & Clinics tab

Items relating to Clubs and Clinics.

Support tab

Items relating to the Support Worker module.

Step 3: View the output (Summary Report Results)

Once you have selected the KPIs you want, there are three options to take.

You can click the "Export Summary" button, which will export a spreadsheet with the resulting numbers on (note that these will only be numbers, and will not show the clients behind the numbers - so if you have selected "Number of new referrals", and there are 367, you will simply get a spreadsheet output with the number "367" in it, along with details of the associated date range and projects).
You can continue to see the detail behind the numbers, using the "Continue to Next Step" button.
You can save the report for later use (but you will probably want to do step 2 first).

So if you continue to the next step, you will see a screen like this one.

In this case, the KPIs requested were:

Number of new referrals
Number of referrals closed
Number of external referrals/signposts, by organisation referred to

So you can see that there were 52 new referrals, 39 close referrals, and 2 external referrals - one to Aardvark Building Services, and one to Mr Spark.

If these numbers give you the data you need, you can print this page, or go back to step 2 (by clicking the "Close Report" button) and export the summary spreadsheet. Alternatively, you may want to change the KPIs you have requested before saving the report.

Once you are happy with the output, you can click the "Save As New Report" button at step 2:

Save the report, giving it a name, description (optional), and setting access control by user group:

Your new report will now be displayed at step 1 when you return to the KPI Report Designer.

Data Extractions and Mail Merges

The third aspect of reporting from the system, is to look at data extractions and mail merges. The main difference between data extractions and other types of reports is that Data Extractions are not limited by date range or by Project. A Data Extraction can be run on any pool of data, but that pool must be defined first, e.g. by a report. Running a data extraction without specifying the input will simply look at all of the organisations/people on the system unless otherwise specified. Sometimes this is useful - this section looks at why.

Click on the "Data Extraction/Mail Merge" menu item.

File:Reports 18.png

The resulting screen will show you the existing Data Extractions set up on your system, if there are any. Click on "Create New Export" to create a new one.

You can now specify:

Name for your data extraction
Description
Name for "Save As" - the name of spreadsheets to be output (this will have some numbers added to distinguish between the same data extraction used over a period of time)
Choose whether to export to a spreadsheet, or create mail-merged letters/emails
- If mail merging, you will need to specify which Standard Letter to merge with.
Choose whether to send emails to people whose preferred contact method is email - applies to mail-merged Standard Letters that can create letters or emails.
Choose whether to override a request not to write to people
Choose whether to Suppress Extract of People Whose Preferred Contact Method is Email? - you would suppress the extract of these people if you are using a data extraction to create labels for a mailing.
Choose whether to include this extraction in the mail-merge audit
Choose whether to hide anonymous clients
Choose whether to send one letter to related/partnered people

From the "Who To Extract?" tab, you can also select the Organisations/People your data extraction will apply to.

To include people from a specified group in the export, set to "Include". To exclude people who are in a specified group, set to "Exclude". If you don't care whether a group is included or not in the lookup, leave the setting as "Not Relevant".

Once you have specified these (all apart from the name of the extraction are optional), you can proceed to actually create the data extraction, which is a three-step process;

Choose the selection criteria
Choose what to print [extract]
Export the results (optional).

Step 1: Choosing Selection Criteria

Click the "Choose Selection Criteria" button.

You can now set up rules for the data extraction.

You have -

Condition - choose WHERE, AND or OR (only WHERE will be available on the first line)
Field name - choose the field name you want to look at
Selection Criteria - choose criteria as shown:

Selection Value - you will be prompted for the value when you select the field name. Selecting the "Gender" field will show the following popup:

So choosing to only extract females would be set up like this:

Clicking "Save Details" will save that line and give the option to add another line. Choosing to extract only females over 55 would look like this:

Using -

Condition AND
Field name "Age Range"
Selection Criteria "IN (this, this this)"
Selection Value "55 to 64, 65 to 74, 75 to 84, 85 to 94, Over 95"

The "Field Name" will control what type of data you are allowed to enter. For example "Forename" or "Address" will accept text, whereas "Postal District" will require a selection of districts from data already held in the system.

You do not necessarily have to make any selection criteria. If you leave the selection criteria blank, the extraction will simply look at all possible organisations or people.

Once you have selected the relevant rules, if any, click "Save and Close" to save the query and proceed to step 2.

Step 2: Choose What To Print

Click the "Choose What To Print" button.

This will display a screen with all available fields on it. Simply tick the ones that you want to be displayed.

Note that you don't need to extract fields that you have already narrowed down to one possibility - for example, there is no point extracting "Gender" if your selection criteria are only to extract people where Gender=F.
Note that you may want to extract names if you need to identify the organisations or people - if you do not select names for export, and apply the data extraction to a report without name data, you will not know who the organisations or people are.
Alternatively, do not select names if you want the report to be anonymous! You may well already need to manually delete names and addresses from a spreadsheet to anonymise it - don't put names in the data extraction too; you will only need to delete them twice.

The screenshot shows name, forename and title selected.

Once you have made your selections, click the "Save and Close" button.

Step 3: Export The Results

At this point you will be returned to the main Data Extraction screen. You can now click the "Save and Close" button, or the "Export the Results" button to run the data extract on your full database. So using the scenario above - having chosen to export females over 55, with name, forename and title data;

Clicking "Export the Results" will create a spreadsheet like this:

On its own, this is not much use, but when put together with another report, this data extraction can perform a useful function, namely -

Discard all of the people in this report's output who are not females over 55, and show me the name, forename and title of all of the remaining people.

Mail Merge from here

Clicking the "Export The Results" button when the data extraction format is set to mail merge with a Standard Letter will create those letters. If you want to write a letter to a certain subset of all your clients, without considering anything Referral, Project or Date-specific, doing this direct from a Data Extraction is the best way to do this. If the data extraction scenario above were changed to a mail merge with a letter, clicking the "Export The Results" would instantly create letters to all of the over-55 females on the system, ready for printing.

Saving a mail merge here will make it available from the Blue Folder Lookup page for users to create letters to all the people in a report.

Using all three approaches

So far this guide has looked at three separate approaches to getting data out of the system - Blue Folder Lookups, Data Extractions and KPI Reports. However, as you might have guessed, the real flexibility of the system's reporting is shown when you start combining these three options together. To recap -

KPI Reports show you numbers - numbers of clients by district, numbers of referrals by age of client, etc.
Blue Folder Lookups can show you the detail of the actual people/actions behind these numbers.
Data Extractions can export these people along with any other details about them that you need, and can also output straight to letters or emails. Note that applying a data extraction to the output of a report will bypass the selection criteria of the data extraction, and simply add the fields that the data extraction is set to add.

Suppose that your organisation runs three weekly drop-in information centres. They have been running for three years, and you want to know how many new people have accessed the service in the last three months.

Select the KPI Report Designer, then specify the Project you want to report on (Information Drop-Ins).

Select a suitable KPI.

View the results - 148 new people have accessed the service in the last three months.

So far so good, but we want to know more details about those people.

Use the Blue Folder Lookup to get more details about these people.

Use the mail merge feature from Blue Folder Lookups to send them all a welcome letter.

and so on...

Use a more targeted Data Extraction/Mail Merge to send a letter to just the 16-25 year old females who accessed the drop-ins, to let them know about our young mums group.

Use a Data Extraction to produce a spreadsheet for funders, showing the ethnic backgrounds and age ranges of the people accessing the service.

Do the same but over the last three years to demonstrate what a diverse range of ethnicities and ages your organisation is benefiting.

By merging the three approaches, you really can do almost anything with your data.

What about the spreadsheets?

The system does not include charting functionality, although some basic charting is in development and will be in a near future release. In general, the system outputs spreadsheets. This is because there is already an industry standard tabulation and charting software package available in Microsoft Excel (or one of the free, open source alternatives). We do not wish to reinvent the wheel, especially when the majority of Third Sector staff have knowledge of Excel already - to demand that they learn to operate a "System Chart module" would add even more learning and training needs to the whole implementation process.

If you have training on reporting, your system trainer will usually show you how to process the outputs in Excel. If your organisation would benefit from some specific training on Excel, to help you interpret reports and outputs, please contact us and we will try and help.

This concludes the Reports section.

Click to go back to the Administrator Manual.

Click to go to the next section, Add-ons.