Spektrix has several different report types, and each report that exists in your system belongs to one of these types. For example, an Event Sales Report is a Sales report, whereas a Payments and Activities Report is an Accounting report. Each report type looks at different data in the system, and will present seemingly similar information differently, which means that some reports may display figures differently for what initially might seem like the same sales data for an Event.
This article explains the differences between the main report types, and the data that they look at in the system. It also looks at a working example of why some data might be presented differently in different reports. This should give you an idea of which report to use at which time, and also answer some commonly-asked questions regarding the differences between reports.
The Spektrix Database
All Spektrix reports are created by pulling information from the Spektrix database and presenting this information as a basic spreadsheet, or a PDF template. The Spektrix database is made up of different
of data relating to all of the various entities in the system. For example, the Customer table will have one line in the database for each customer that you have on the system, like this:The Customer table contains a lot more data than this (such as address fields, dates of birth etc.) but it won’t contain anything related to what the customer has booked for, because that information couldn’t be stored efficiently against a single line.
Instead, bookings are recorded in other tables. For example, the Seat/Sales table has a line for each seat that could be sold across all of your Instances, and contains information like this:
When a seat is booked, the system records the Customer ID next to that seat as a unique identifier. This means that it can now join the seat row in the Seat/Sales table with the customer information from the Customer table to report on more information:
This is how all information in Spektrix is stored and managed for reporting purposes. Next we’ll look at each of the main report types and the data table they use, and then we can look at a working example of how this data gets outputted into specific reports.
Examples: Event Instance Sales Report, Event Sales Summary Report.
Useful for: displaying information on both sold and unsold (such as Locked or Available) seats.
As outlined above, Sales reports use the Seat/Sales table, and present a line for each seat for every Event Instance in the system. Instances using reserved seating plans will have the individual seat names indicated, while Instances using unreserved (general admission) plans will have all of their seat names simply listed as 0, but will still have a row for each seat and will output all of the other information in the same way.
The Seat/Sales table is the only table that includes information on unsold seats. Other report types that look at sales information, such as Analysis reports, can only include information on objects (such as tickets) that have been sold, reserved or returned in a transaction. Sales reports are therefore the only reports that can identify how many Locked and Available seats an Event Instance has, which is why many reports looking at Event sales are Sales reports as opposed to Analysis or Accounting reports.
Sales reports will always output information relating to the current status of seats in the system. For example, if seat A1 for Macbeth was purchased, returned and then purchased by a different customer, a Sales report will not be able to display information on the original sale as this information does not relate to the current status of that seat. Instead it will only display the latest state of that seat. This point becomes very important when comparing Sales reports to Analysis and Accounting reports (see section below on the differences between Accounting and Sales reports).
Examples: Customer Analysis, Individuals & Contacts.
Useful for: analysis on information kept within customer records.
Customer reports use the Customer table, which holds a line per customer on the system. Each customer has a unique identifier called a Customer ID, so this piece of information is always present on this table and, as seen above, it is the piece of information that is used to join the Customer table to other tables. All other information in the Details pane of a customer account, such as contact information, addresses, memberships, tags etc. are also stored against customers in the Customer table, as well as some calculated metrics (see this article for more information on calculated metrics).
Customer reports are good for analysing data on all customers in the system. Although other report types such as Analysis and Accounting reports can output customer information, these reports are only concerned with items that have been sold in the system. Customer reports are therefore the best report to look at data for customers on the system whether they’ve made a purchase or not. Reports like the Customer Analysis are Customer reports for this reason.
Examples: Payments and Activities Report, Payments Report.
Useful for: tracking all transaction actions for financial reconciliation purposes.
Accounting reports use the Transactions table, and present a line for each action in a transaction. Actions in a transaction include ticket sales or returns, sale or return of commissions, and payments. The Transactions table records information from transactions as soon as they are confirmed and retains this information permanently. This means that, unlike Sales reports, Accounting reports will never change when run on the same criteria.
Using the earlier example of seat A1 for Macbeth being purchased, returned and then purchased by a different customer, an Accounting report will store information about every transaction in that sequence.
For this reason, Accounting reports are the best reports to use for financial reconciliation, as they can track all payments that have been processed in the system and compare these against the price of objects that have been sold such as tickets or merchandise. This is exactly what the Payments and Activities report does. Other Accounting Reports include the Payments report, which outputs detailed information on individual payments such as credit/debit card information, which is only available in Accounting reports.
An example excerpt of an Transactions table looks like this:
Examples: Ticket Sales Analysis, Offer Analysis.
Useful for: looking at sales trends for sellable objects in the system.
Analysis reports use the Order Items table, which lists a line per item that has been sold or returned in the system. Analysis reports can report on any item that can be sold in a transaction, such as tickets, commissions, donations, memberships etc. Analysis reports only contain one line per item in the system which means that, unlike Accounting reports, they can change over time.
This is because the line they are referring to can change in the system. For example, a ticket’s status may be Sold when running a report one day, but then change to Returned the day after. Because of this, unlike a Sales report you may have multiple lines for the same seat (as it could have been sold and returned multiple times).
Analysis reports should therefore never be used for financial reconciliation purposes, as they will not accurately retain information against all actions and items in an order’s history. However, they can be used for (unsurprisingly) analysis purposes, as they can give an indication of how well tickets for an event are selling, or the amount of donations received for a particular Fund.
An example excerpt of an Order Items table looks like this:
Examples: Membership activity in the last 30 days, Current members report.
Useful for: looking at membership information in the system.
Membership reports look at the Memberships table, which lists a line for each membership in the system (including expired memberships). The Memberships table holds information about memberships that is not available in any other table on the database, including whether the membership is a renewal or a brand new membership, and calculated metrics such as whether the member in question previously held any other kind of membership before the one in question.
Whilst Analysis and Accounting reports can output some information regarding memberships, such as purchase date and start/expiry dates, Memberships reports can include more detailed information on memberships in the system such as the above examples. For this reason, reports like Membership activity in the past 30 days are Memberships reports.
An example excerpt of a Memberships table looks like this:
These are the main types of reports on the system. There are other types of report on your system such as Event Instances and Customer Audit reports, and if you use the Opportunities Interface you’ll also have the option to create new reports for items such as Opportunities and Activities. All of these reports use tables that output a line per the respective item they are reporting on. Although it’s not practical to cover all of the lesser-used report types here, the information presented here is still representative of how they function.
The difference between Accounting and Sales
One of the most common queries we receive is why Accounting reports are the only reports that can be used for financial reconciliation purposes. This is touched on above, but to provide a working example of why it’s import to consider the differences between the different report types we can go into this reasoning in more detail.
Here is an example of a basic Accounting report run in Excel, looking at sales information for an Instance of Hamlet:
You can see from this that seats J7-10 and K7-9 were purchased using cash, and then returned using a custom payment type. The same seats were then sold again using mixed payments, in a different order.
The report displays both order IDs - one for the order in which the original sale and return took place, and one for the second order. This is because Accounting Reports will identify all transaction actions taken in the system and can report on these accordingly.
In contrast, here is an excerpt from a Sales report looking at sales information for the same Instance of Hamlet:
This is showing sales information for the same seats, however you can see that information from the original sale and return order (which you saw in the Accounting report) is not shown. This is because the current status of those seats is that they are Sold, in order 18-DZ-01H1. The Sales report cannot find any information that does not relate to the current status of the seats in the events it is looking at.
Therefore, when you are reconciling your finances for this Event, the Accounting report is the correct report to choose, as it can clearly show you the financial movement and history of the data you’re looking at.
For further information regarding reports in Spektrix, have a read of the following articles:
A Guide to Standard Reports in Spektrix
If you have any further questions about the differences between different report types, please don’t hesitate to get in touch with the Spektrix Support team.