If you’re looking for something more than what’s available in a Standard Report, you can 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:
In this article you’ll learn how to:
- Build and run unformatted Custom Reports.
- Use Criteria Sets to filter the data you need and choose the Output Columns which determine the contents of an unformatted Custom Report.
REMINDER: To be able to build a Custom Report, you’ll need the Report Administrator User Role.
How to build a Custom Report
To build a Custom Report, you’ll need to:
- Know what question you want to ask the data.
- Decide which Report Type is best to ascertain this information.
- Decide how you want to filter the data by selecting the appropriate Metrics to create a Criteria Set.
- Decide which Outputs you want.
- Decide if you want similar items grouped on a single line by using Grouped Mode or have every individual item appear on their own line in Single Item Mode.
For example, you might ask - “What were ticket sales for the performance of Funny Girl yesterday?”
- To see Ticket Sales for a specific Instance use an Analysis Report.
- Then you'll need to create a Criteria Set filtering the data for the Event name ‘Funny Girl’ and the Instance yesterday.
- Finally, choose the columns of data that you want to be included.
For example, you might want to see columns for…
- Event
- Event Date/Time
- Order ID
- The value of the item
Follow the steps laid out in the next sections of this article to create your own Custom Report.
What question do you want to ask the data?
Before you build a Custom Report, you’ll need to think about what sort of information you want to be included.
For example, you might want to know:
- What payments were taken over the phone?
- Which Memberships expire tomorrow?
- Which Donations were made to the ‘Education’ fund?
- Which Comp offers have been used on tickets?
Once you have a question in mind, you’ll next need to think about where to find that data in the system.
In the example running through this article we’re going to look at the question: “What was the number of sold and available tickets for yesterday’s performance of Funny Girl?”
Creating a new Report
Before starting a new Report, you’ll need to know which Report Type to choose. This will depend on the type of information you need.
REMINDER: To find out which type of Report you need, head to the Introduction to Building Reports article
Go to Insights and Mailings > Report Explorer and select New Report.
Next you’ll see the New Report screen.
- Choose your Report Type.
- Give your new Report a name and description.
- Check the box Make available in Sales Interface if you want to make this Report available to users in the Sales Interface. You can also choose to lock this to only be available to Sales Administrators.
- Select Next to continue.
In our example, “What was the number of sold and available tickets for yesterday’s performance of Funny Girl?” we want to show information about both sold and unsold seats, therefore, we need the Sales Report.
Create a Criteria Set
Criteria Sets allow you to choose which information is included or excluded from a Report. When building Reports, Criteria Sets work in the same way as creating new Criteria Sets for Standard Reports.
REMINDER: The Metrics available for your Criteria Set are dictated by the Report Type you choose. This is because the Report Type dictates the data table the Report accesses. This means, for example, that you can't filter a Sales Report by Donation Fund because it doesn't access that data table.
- Expand the Wrapper which contains the Metric you would like to use in your Criteria Set.
TIP: Uncheck the box marked Only show commonly used Criteria to see the full range of Metrics.
- Drag and drop the Metric you wish to use to filter your data into the box where it says Drop Criteria here…
- You can add more Metrics using the AND, and OR rules.
- When you are happy with your Criteria Set, click Next.
In our example, “What was the number of sold and available tickets for yesterday’s performance of Funny Girl?”, we would select the Criteria of yesterday’s Instance of Funny Girl.
Output Columns
Next, you’ll need to choose your output columns.
REMINDER: Make sure you have read Introduction to Building Reports to understand which Metrics are available to output into Columns.
- Expand the Wrapper which contains the Metric you would like to use in your Criteria Set.
TIP: Uncheck the box marked Only show commonly used Criteria to see the full range of Metrics.
- Drag and drop the Metric you wish to use to filter your data into the box where it says Output Columns.
- The order in which you place your Metrics will dictate the order in which they are exported in your CSV file.
REMINDER: Adding output columns may require some trial and error. Adding output columns that are not associated with the Report Type you have selected will result in some blank entries.
In our example, “What was the number of sold and available tickets for yesterday’s performance of Funny Girl?”, we would want to see the output column as the Seat Status.
Single Item or Grouped Mode
Next, you’ll need to choose if you want to export your data in Single Item Mode or Group Mode.
You can switch between modes on the Output Columns tab.
By default, all reports are set to Single Item Mode.
- A report in Single Item Mode outputs data line by line.
- A report in Grouped Mode outputs data grouped by similarity.
Returning to our question, “What was the number of sold and available tickets for yesterday’s performance of Funny Girl?”, this image shows the data output in Single Item Mode.
There is one column for Seat Status and a line for every Seat. To get the two figures, you’d need to count the number of rows for Sold, Locked and Available.
If you need more detail then use Single Item Mode. Grouped Mode is great for giving an overview and once exported into CSV it can be easier to sort and filter your data. You can also use the Show Count feature in Grouped Mode.
Groups
Once you have switched to Group Mode, you can choose to group the results based on the following options.
- Date - gives the date at midnight (06/06/2023 00:00)
TIP: Each result that is a date value in this mode will return as midnight at the start of that time period.
- Value - the time the instance or transaction was confirmed (09/06/2023 17:30)
- Week - returns as midnight at the start of the week
- Month - returns as midnight at the start of the month
- Year - gives the start date and time of the year (01/01/2023 00:00)
- Quarter - returns as midnight at the start of the quarter
- ay of the Week
- Day of Month
- Day of Year
- Month of Year
- Week of Year
- Quarter of Year
Aggregate
You can use the Aggregate function to add a calculation into your Report.
To do this, drag and drop any Metric which is a number into the Aggregate box.
For example, you may wish to Aggregate using a Metric such as Price.
By default, the Aggregate column will calculate using Sum.
From the dropdown you can choose:
- Average
- Sum
- Minimum
- Maximum
- Std Deviation
- Variance
You can also choose to limit the Report by only including values within a certain range. You can do this by specifying the From and To limits.
Leave the Include Column box checked to include the Aggregate column.
TIP: If you try to Aggregate on a Metric that it is not possible to Aggregate on, you will see an error message which reads: "You can only aggregate on 'Seat' and columns that contain numbers.
Show Count
If you are using Grouped Mode, you can 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.
Check the Show Count box to add these totals to your Report. When you export your CSV file, count will appear in a new, separate column called Count.
- In our example, “What was the number of sold and available tickets for yesterday’s performance of Funny Girl?”, we could use Grouped Mode and check the box Show Count to see totals for Sold, Locked and Available Seats.
Running your Report
Once you are happy with your Criteria Set and Output columns, you are ready to run your Report.
- Press OK to complete your Custom Report set up.
- Find your Report in your Report Explorer.
- Select the + icon next to your Custom Report.
- Select the green play icon next to your Criteria Set.
- Choose Run as Excel (unformatted .xlsx) to export your data.
TIP: While it is only possible to export Custom Reports as unformatted XLS, CSV, or XML files, you can request help with Reports by taking a look at the article How to Request Help with Reports.
How to build a Report - summary
To recap, to get data into a Report we…
- Determine our question and choose the Report Type that suits it.
- Create a Criteria Set to filter the data.
- Dictate the Output Columns we want to see.
- Decide if we want to see similar items grouped on one line in Grouped Mode or have all items appear on their own line in Single Item Mode.
Watch this video to see an example of building a Custom Report from start to finish in action.
Further Reading
To continue learning about Reports in Spektrix, take a look at the following articles:
You can find more articles on running and configuring Reports in the Reporting section of the Support Centre.