Attendance Report - Database Table

The Attendance Report also gives users the option to download their data in a different structure, known as a Database Table, by ticking the below box. 

In the database table download of the Attendance Report, each row represents a unique attendance, whilst there are columns for each piece of information about that attendance, such as location, date, activity and attendee.

This is different from a cross tabulation of data, such as the standard Attendance Report, which holds data about attendances both within rows and columns.

This format can be helpful for internal analysis, while also allowing users to feed data directly into external data visualisation tools such as Google Data Studio, Power BI and Tableau.

• Key Points
• Utilising this format within Excel
• Utilising this format with external Data Visualisation Tools
• Key Terms

Key Points

• Each row is an attendance, rather than an attendee - therefore attendees appear multiple times, with a new row for every session they attend. Note the unique Attendee ID is presented in column S to allow you to link your data together for one particular participant if required. Similarly the Session ID in column M can be used to highlight the information for one particular session.
• The column order is grouped into 3 main parts:
  • Session information (Columns A - O) showing project, activity type, activity group, activity, location, date, time, tags, session registrar and notes
  • Attendee basic information (Columns P – S) showing name fields and attendee ID
  • Personal details (Columns T onwards if selected, number of columns depends on registration form)

Utilising this format within Excel

The database table download gives users options to manipulate their data further using Excel, this may initially be done by applying filters to the download but can also become more powerful and insightful when using PivotTables.

The database table download allows users to generate complex queries of their data, but also run reports not possible via the system tools. 

For example, with the downloaded report and the use of PivotTables, users can generate information about the amount of attendances in multiple time frames and have these all visible at once. This example is outlined below.

In the below example, there is a download of the Attendance Report for two projects (Employment & Community Wellbeing and Sports Project). 

Please see the video demonstration and steps on how to recreate your own PivotTables to analyse this report below.

To generate a PivotTable: 

• Click on any cell within the database table download
• Click Insert > PivotTable
• The pop up generated should have the whole table/range automatically selected and will automatically select to generate this on a 'New Worksheet' click OK.
• This is where users can then choose which fields (column headers) to add to their PivotTable report.

The PivotTable below has the field of Date applied to the Rows and Project applied to the Columns. The Value is the Count of Attendee ID. 

This allows the user to see how many attendances there have been in the projects for each of the months of the year in 2020. Allowing organisations to see the peaks and troughs in terms of attendances and perhaps query internally why that is the case. 

This could be turned into the following chart that maps out the attendances of both projects across the course of the year:

With PivotTables users can also quickly change how the values are displayed to highlight that just over 37% of all attendances for the projects combined took place in March and August.

This is done by clicking on Value Field Settings in the drop down under the Values selection. Then selecting the tab 'Show Values As' and changing to a %.

Users could also take this further by applying additional criteria to their Rows / Columns. 

Additionally, by choosing to Include Personal Details when downloading the initial Attendance Report users would then have demographic information around attendance that could be used here as well.

By applying an additional field of Gender under Columns and instead choosing the value to be Count of Gender this could generate the following:

Here we can now see that November had many more attendances by female attendees of the Employment & Community Wellbeing project than other months of the year.

Note: If looking to find the amount of unique attendees that meet a certain criteria, users should Save their downloaded report as a Workbook (*.xlsx). When creating the PivotTable they will be presented with the option to 'Add this data to the Data Model' and this should be ticked. This will give users the option to 'Count distinct' values for any of the PivotTable fields picked later. I.e. count the amount of distinct Attendee ID's = amount of unique attendees.

As highlighted, PivotTables can be powerful ways of manipulating and analysing your data. For their use on Upshot data and other reports the system generates please refer to the following guide. There are also countless external resources that highlight how to generate PivotTables that can be found online. 

Utilising this format with External Data Visualisation Tools

The database table download option for the Attendance Report can be particularly useful to visualise your Upshot data with external data visualisation tools. These help create a visual representation of data sets by plotting your data into charts, reports and dashboards.

There are many different data visualisation tools available, often requiring a licence paid licence. Some popular tools include:

• Tableau – One of the most popular data visualisation tools with many options for reporting and importing data. Requires a paid licence in order to use private data visualisations.
• Power BI – Microsoft’s tool Power BI is technically a Business Intelligence tool, with more of a focus on analysing data than visualising it, but can be used as an effective data visualisation tool. Requires a paid licence to share reports.
• Data Studio – Google’s visualisation tool which allows free use and sharing options. Upshot CSV downloads can be imported into a Google Sheet and easily used as a data source within Data Studio.

Key Terms

In a database, tables have a specific structure to ensure that data can be read by a computer. Within a table data is logically organised, with each row representing a unique record and each column representing a field in the record. For example, in the Database Table download of the Attendance Report, each row represents a unique attendance, whilst there are columns, or fields, for each piece of information about that attendance, such as location, date, activity and attendee.

This is different from a cross tabulation of data, such as the standard attendance report, which holds data about attendances both within rows and columns.