Introduction to Building Custom Reports

Reporting is one of the most powerful features in your Spektrix system. Each system comes with a set of Standard Reports which help you visualise and export data relating to Sales, Accounting and Analysis.

Along with Standard Reports, you can also build your own Custom Reports that export as unformatted CSV files. The steps to creating a custom CSV report are straightforward, but there are some considerations and limitations to understand before you start.

Before reading this article, make sure you’re familiar with:

• Reports in Spektrix
• Criteria Sets

TIP: If you are new to building custom reports, take a look at our Spotlight Session: How to build your own reports.

What is a Custom Report?

All Spektrix systems come with a wide range of Standard Reports. However, if the data you want can’t be accessed using these, you can create Custom Reports.

Custom Reports allow you to combine data in different ways that may meet your needs more closely than a Standard Report. You can build your own Custom Reports or you can request help with Reports from Spektrix.

Formatted vs Unformatted Reports

As well as being Standard or Custom, Reports in Spektrix will either be presented with or without a Template, this is also called formatted or unformatted. 

Templates apply styling to a Report and often have calculations built into them which will format the data to be displayed in a certain way.

All of the Spektrix Standard Reports, and some of the Custom Reports that we might have built for you, will have PDF or Excel templates attached. 

A formatted report has a template that uses code to rearrange the look and/or calculate more complex expressions with the underlying data.

• Formatted Reports can be run as a PDF or formatted Excel file.
• All Standard Reports in the system are formatted.
• Formatted Reports can include calculations and visualisations.

An unformatted report is the untouched data from your system that outputs into an unformatted Excel file.

• Unformatted Reports have no styling.
• All reports can be run as an unformatted Excel file.
• Only reports with a Template can be run as a PDF or formatted Excel file.

How do Templates relate to Custom and Standard Reports?

The table below shows how this works:

When you build your own Custom Report, the raw data will be exported to a CSV file which will not have any formatting. You can open your CSV report in Excel or Google Sheets.

TIP: You can request help with Reports from our Support Team by reading the How to request help with Reports article.

The Spektrix Database

To understand more about Reporting in Spektrix, it’s first important to know how data is stored in the system.

The data in your system is stored in a database, which is a collection of tables. 

A table is made up of rows and columns. Each row represents an individual item, while each column represents a property of that item. The intersection between a row and a column contains a specific property of a single item.

Each table holds a particular type of data. For example, the Customer table will display one line (row) per customer and contains data only relating to that customer (such as address fields, dates of birth etc.). It won’t contain anything related to what the customer has booked for, because that information would be stored in the Sales table.

The Sales table has a line of data per Seat that could be sold across all of your Instances, and contains information like this:

• • Customer Jane Doodle creates an account online, and the Customer table is updated with her information.
  • She then buys a $100 ticket to Funny Girl. Both the Order Items table and Tickets table are updated with new data.
  • Finally, she donates $50 to the “Support Us” Fund. The Order Items table is updated along with the Donations table.

   

  

TIP: Tables are updated whenever a user or customer interacts with Spektrix.

Report Types

Each Report that exists in your system belongs to a Report Type which has access to a different range of tables in your database, each containing a different range of information.

The information you want to retrieve dictates the data tables that need to be accessed and hence the Report Type required.  You need to choose your Report Type based on the Table(s) from which you want to get data.

Data is outputted as a single item per line, depending on the Report Type. For example, Customer Reports return one line per Customer, Event Instance reports show a row for each Event Instance, and so on.

The Metrics available for each Report Type correspond to the Tables available to that Report Type.

Below is a table of all Report Types with a description and examples of Standard Reports that are part of that Report type. The text in the 'Useful for' column is also available when you choose the Report Type as part of building a Custom Report.

• There are 18 different Report Types. The primary and secondary tables show which Wrappers are available for each Report Type. 

   

  Report Type	Useful for…	Tables
  Sales	Sales Reports show a row for each seat. They are normally filtered to show the seats for a particular Event Instance. Sales Reports are generally used by the Box Office or producers. Example Standard Reports include: Event Instances Occupancy Report, Event Sales Report, and the Post Show Analysis Report.	Primary: Seats
  Customer	Customer Reports show a row for each customer in the database. They are often used to guide Marketing decisions. Example Standard Reports include: Customer &Household Analysis and the Individuals & Contacts Report.	Primary: Customers Secondary: Individuals, Organisations
  Analysis	Analysis Reports show a row for each item sold or returned. Items include Tickets, Reservations, Merchandise and Gift Vouchers. Analysis Reports are usually used by the Box Office and Marketing teams. Analysis Reports also have the largest number of secondary tables (Wrappers) available. Example Standard Reports include: Offer Analysis, Customer Demographic Analysis and the Donations Report.	Primary: Order Items Secondary: Memberships, Merchandise, Tickets, Reservations, Commissionable Items, Payments, Card Payments, Cash Payments, Account Payments, Deliveries, COBO/Will Call Deliveries, Postal Deliviers, Transaction Commissions, Donations, Fixed Subscriptions, Held Fixed Subscriptions, Gift Vouchers, Ticket Voucher Payments.
  Accounting	Accounting Reports show a row for each transaction. They show how much money was taken and the end financial result. Accounting Reports are generally used by the Finance and Box Office teams. Accounting Reports can be used for financial reconciliation. Examples of Standard Reports include: Accounting Date Audit and Payments & Activities Report.	Primary: Transaction Items. Secondary: Memberships, Merchandise, Tickets, Reservations, Commissionable Item, Payments, Account Payments, Gift Vouchers, Credit Refunds, Donations, Ticket Voucher Payments.
  Event Instances	Event Instance Reports show a row for each Event Instance, providing summary data about each one such as number of seats sold, masked, locked etc.	Primary: Event Instances
  Memberships	Membership Reports show a row for each membership. They can be filtered to show active or expired Memberships. Membership reports are generally used by Marketing and Development teams. Example Standard Reports include: Current Members, Membership Activity in the last 30 Days and Monthly Membership Activity.	Primary: Memberships
  Opportunities	Opportunity Reports show a row for each Opportunity. Example Standard Reports include: Opportunities Funnel and Opportunities Pipeline by Month. This Report is only available to Opportunities Interface users.	Primary: Opportunities
  Campaigns	Campaign Reports show a row for every item related to each Campaign. This Report is only available to Opportunities Interface users.	Primary: Campaigns
  Opportunity Stages	Opportunity Stage Reports show a row for each stage of an Opportunity. Example Standard Reports include: Campaign Detail and Campaign Summary. This Report is only available to Opportunities Interface users.	Primary: Opportunity Stages
  Activities	The Activities Report shows a row for each Activity and for each link to an Individual/Organisation. Example Standard Reports include: Activities Report and the Activities Timeline. This Report is intended for Opportunities Interface users.	Primary: Activities
  Pledges & Legacies	A Pledges & Legacies Report shows a row for each Pledge / Legacy. An example Standard Report: Pledge Installments by Campaign. This Report is only available to Opportunities Interface users.	Primary: Pledges & Legacies. Secondary: Pledges, Legacies.
  Customer Audits	A Customer Audit Report shows a row for each Customer Audit. This is useful for tracking changes made to Customer accounts.	Primary: Customer Audit Criteria
  Attendee	Attendee Reports show a row for each Attendee. This Report is only available to Opportunities Interface users.	Primary: Attendees
  Invitees	Invitee Reports show a row for each Invitation. This Report is only available to Opportunities Interface users.	Primary: Invitees
  Relationships	Relationship Reports show a row for each relationship to an entity. This Report is only available to Opportunities Interface users.	Primary: Relationships
  Soft Credits	Soft Credit Reports show a line per Order Item for the actual Order as well as a line per Order Item for each entity the order has been soft credited to. This Report is only available to Opportunities Interface users.	Primary: Soft Credits. Secondary: Donations, Memberships.
  Mailing ROI	Mailing ROI Reports show a line per Mailing per related Event, as well as summary data to do with the success of the mailing. Useful for the Marketing team. Used in the Standard Report: ROI Report by Mailing.	Primary: Mailings & Events
  Ticket Vouchers	This Report shows a breakdown of Ticket Subscription vouchers owned by Customers. Useful for those reporting on Ticket Subscriptions. Used in the Standard Report: Ticket Subscription Analysis	Primary: Ticket Vouchers

Next, we’ll look at the five most commonly used Report Types in more detail.

Learn More About Sales Reports…

• • Work on Instances using Reserved and Unreserved Seating Plans. Reserved plans will have the individual seat names indicated, while Unreserved (general admission) plans will be listed as 0, but will still have a row for each seat.
  • This is the only type of Report that will report on unsold seats. Therefore Sales Reports are the only reports that can identify how many Locked and Available seats an Event Instance has.
  • Always output information relating to the current status of seats in the system. For example, if a seat was purchased, returned and then purchased by a different customer, a Sales report will only show the most recent information. This is a key difference between Sales Reports and Analysis and Accounting Reports.

  REMINDER: It is not possible to create a Sales Report based on Ticket Type. However, you can achieve this through an Analysis Report.

  An example excerpt of an Sales Report looks like this, with a line per Seat:
  
  
  

Learn more about Customer Reports…

• • Contain information such as contact information, addresses, memberships, tags etc., as well as some Calculated Metrics.

  • Are best to look at data for customers and whether they’ve made a purchase or not. Analysis and Accounting Reports can output customer information but these reports are only concerned with items that have been sold in the system. 

  A Customer Report looks like this, with one line per customer:

  

Learn more about Accounting Reports…

• • Contain details about the actions in a transaction including sale or return of tickets and commissions as well as data on payments and refunds. 
  • The Transaction Items Table records information from transactions as soon as they are confirmed and retains this information permanently. 
  • Unlike Sales reports, running an Accounting Report on a Date Confirmed criteria that is in the past should always return the same data. Using a criteria like this week or today will change because of ongoing transactions. Also, if you are using Accounting Date as a criteria, this may also change as it is possible to backdate this.

  An example excerpt of an Accounting Report looks like this, with a line per payment:

  
  

Learn more about Analysis Reports…

• • Can report on any item that can be sold in a transaction, such as tickets, commissions, donations, memberships etc. 

  • Contain only one row per item sold, therefore should never be used for financial reconciliation purposes. This is because any returns which have taken place will not balance against their corresponding sales.

  • Can be used to give an indication of how well tickets for an Event are selling, or the amount of donations received for a particular Fund.

  An example of an Analysis Report looks like this, with a line per item:

  
  
  
  

   

Learn more about Memberships Reports

• • Include Calculated Metrics such as whether the Member previously held any other kind of membership.
  • Include detailed information on Memberships. Analysis and Accounting reports also output some information regarding memberships, such as purchase date and start/expiry dates. 

  An example of a Memberships Report looks like this, with a single line per Membership:

  
  
  
  

Criteria Sets and Reporting Output Columns

• Criteria Sets allow you to choose which information is included or excluded from a Report. They do this by filtering the rows in the relevant data table.
  
  Criteria Sets in Custom Reports work in the same way as they do in Standard Reports. To create your Criteria Set, you will need to choose the appropriate Metrics, many of which are stored in Wrappers.
  
  

• Output columns define what is displayed in your Report. They determine which columns from a data table are read. Each Metric selected will add a new column onto the Report when it is run as a CSV.
  
  

  REMINDER: It is only possible to add new output columns to a Custom Report. If the Custom Report is formatted (i.e. has a PDF template attached to it), any extra outputs will not appear in the formatted version of the Report but can be seen by running the Report as a CSV.

Wrappers and Metrics

• Wrappers appear with a + icon. They can be expanded to show one or multiple Metrics.
• Metrics appear in rectangles. They represent a single property of the Wrapper.

For example, the Date Confirmed of a transaction is a Metric and sits in the Order Items wrapper:

Normal vs Calculated Metrics

There are two types of Metrics you can choose in your output columns: Normal and Calculated.

Normal Metrics:

• Represent data that is stored in the system when things are created or sold.
• For example, the Seat Status Metric indicates whether the Ticket is sold, available etc. .
• Other normal Metrics include: Event Name, Seat Number, Customer Name.

Calculated Metrics:

• Are created using a calculation.

• Metric	Description
  Potential Duplicate	Applies true to any groups of customers where their first initial, last name and EITHER first line of address or postcode (not including null values) is the same.
  Never Booked	Applies true to customers who have no records of purchasing unreturned tickets for an event instance included in metrics.
  First Visit	Per customer, applies true to the ticket with the earliest instance included in metrics a customer has purchased. Will recalculate every night taking into account any new transactions.
  First Time Booker	Applies true to customers who have purchased an unreturned ticket for only one event where the instance is included in metrics.

REMINDER: while some Calculated Metrics update at the point of sale or return, others calculate overnight meaning they won’t include transactions which happened on that day.

Single Item or Grouped Mode

Custom Reports can export data in either Single Item Mode or Group Mode.

By default, all reports are set to Single Item Mode. 

• A report in Single Item Mode outputs data line by line.
  
  In this example, you can see how in Single Item Mode there are lots of duplicate lines.

• Grouped Mode groups duplicate lines together. The example above in Grouped Mode would display like this.
  
  

A report in Grouped Mode outputs data grouped by similarity.

In Grouped Mode, you can also add a total using the Count function. This can show you the number of items, Events or Instances that have been summarised by the grouping.

• The Event and Instance counts will total the number of Events and Instances contained within the output data.
• The Show Count checkbox will provide a count for all the items you are outputting. This may be Tickets, Customers, postcodes or Donations—whatever item you have decided to group the data on will be counted here.
• Changing which output columns show will affect the count because the count shows the number of rows that have been combined. In the above example, if we were to remove the Instance date column, the result would be a single Othello row with a count of 30.

When you export your CSV file, count will appear in a new, separate column called Count.

How to Build a Custom Report

Now you understand some of the basic concepts related to building a report, you’re ready to try building your own Report.

Follow the steps in How to Build a Custom Report to get started.