Introduction

The Reports section, accessed via the Reservations Portal, provides a central space for building, managing, and exporting custom data reports across the Expian platform.

Rather than offering a fixed set of operational reports, Expian enables clients to design their own, giving complete flexibility to extract, analyse, and visualise data in ways that suit individual business processes and reporting needs.

While this approach offers a high level of control, it also requires a solid understanding of how your data is structured within Expian.

Each report is built using data relationships drawn from the system’s core entities, such as orders, tickets, payments, venues, and users, meaning accuracy depends on both how the data is configured in the Admin Portal and how filters are applied during report creation.

In a new Expian instance, this area will initially appear blank. Reports must be created manually or imported from templates shared by Expian or other users within your organisation. Once created, reports can be saved, refreshed, configured, or downloaded directly from this interface.

Custom reports are powerful tools for finance, operations, and marketing teams, supporting analysis across revenue, capacity, product performance, and customer trends, but care should be taken to ensure filters and joins reflect the correct business logic before relying on results for decision-making.

This modular approach ensures that each organisation can measure performance in ways that align with its specific operational model, whether managing attractions, travel routes, or membership programmes.

Reports are built dynamically from the platform’s core data sources,Orders, Tickets, Payments, Users, Venues, and Configured Entities, allowing users to create real-time insights tailored to their business workflows.

Why It Matters

The Reports module transforms Expian from a transactional system into a data-driven decision platform.

Accurate, configurable reporting enables teams to:

• Monitor revenue performance, capacity, and customer behaviour in real time.
• Track financial compliance, reconciliation, and refund activity.
• Analyse sales channel performance across POS, web, and reservations.
• Audit booking changes and lifecycle activity via Orders and Revisions data sources.
• Enable self-service analytics without relying on IT or external BI tools.

By connecting operational teams directly with live, replicated system data, Reports ensures that decisions are grounded in evidence, trends are identified early, and organisational strategy remains aligned with real-world performance.

Create a report

• Click on the graph icon on the left hand side menu.
• click on the green 'New Query' button in the bttom left.

Executing a query

To use an existing query, click on its title in the left-hand navigation pane. The query will automatically run, and the results will be shown in the paginated table.

Previewing results

By default, the query will show the first 25 rows of the results. To see more, use the pagination controls at the bottom of the table. The number of rows per page can be changed using the dropdown to the left of the pagination controls.

Downloading results

To download the full results, click the 'Download' button at the bottom of the table. You will be prompted to choose a format:

Filtering results

Filters can be applied to the results to narrow down the data. The filters are present at the top right of the page, above the results table.

Quick filter

There is a funnel icon above the results table. This is a general filter can be used to narrow down the results. Click on the icon to view the current filter rules, and make changes.

Multiple rules can be defined, and combined in groups that with an 'And' or 'Or' option, which controls whether all conditions must be true, or just one at least. Each group can be optionally negated with the 'not' toggle.

Rule groups can be nested and combined in complex ways, and apply on top of any general filter rules already configured in the saved query.

Valid values are shown in a dropdown as suggestions, and these are refined as you type. The values are based on the data in the column, but you can enter any value you like. You must click on one of the suggestions to apply the filter, or use the keyboard arrows and press enter.

Date filtering

For any data sources that relate to specific times, the results can be filtered with the date and time filter above the results table. The filter has a dropdown built in, with which you can specify which date you want to filter by:

• Created at - This filters by the time that the records were created. For example, this can be the time a payment was made, or an order was placed.
• For any data source with 'Revision' in the name, this will be the time that each specific revision was created.
• Start date - This filters by the time of the event or journey. It is the time that would be printed on the customer's tickets.
• The start date is always the local time where the event is actually taking place.

Date filters only appear on relevant data sources. For example, 'Tickets' have both filters, but 'Ticket Types' have neither.

Sorting results

To sort a column, click on the column header. Clicking once sorts the column by ascending order, and clicking again reverses the order. Clicking once more removes the sort.

You can sort by multiple columns by holding down the 'alt' key, and clicking on multiple headers.

The sort order can be saved by clicking the 'Configure' button, and then 'Save'.

Configuring queries

In the left-hand navigation pane, queries can be created using the 'New query' button. This opens a panel where the new query can be configured.

The query can then be viewed by clicking on its title in the navigation pane, and then updated using the 'Configure' button.

Below, the configuration options are explained in detail.

Name, category and description

The query can be given a name, category and description.

• The name shows in the navigation pane, and is also included in the title of any downloaded results.
• The category is used to group queries in the navigation pane.
• The description is shown in the query view, and can be used to explain the purpose of the query to other users.

Data sources

The data source is the heart of the query. It can be selected from a dropdown. All other configuration options below the 'Data Source' selector (such as the columns and filter) depend on the data source, and will reset if the data source is changed.

The available data sources are grouped into categories:

• Orders - The main tables containing all purchase information. The main data source is 'Orders'. All orders contain one or more 'Bookings', which in turn contain one or more 'Line Items'.
• In addition, 'Orders' relate to 'Payments'.
• Orders (Drafts) - Orders that have not been completed yet (AKA 'Carts'). Pending amendments or cancellations to existing orders are also included here until they are confirmed, or abandoned. Drafts expire if not paid for before the expiry date. When using this data source, you may use the 'Expires at' filter to find drafts that are still relevant. You can also use the 'Status' column to distinguish between draft (carts), amending and cancelling.
• Orders (Revisions) - A version of each order for when it was created, and each time it was amended. This is useful for tracking changes to orders over time, which is usually needed for financial reporting.
• This also includes a 'Line Items Diff' table, which is like 'Line Items', but shows only the changes made in each revision. For example, if a booking for 4 adult and 1 child was made, and then amended to 5 adults, the diff would show a line item for +1 adult and -1 child.
• Tickets - Tables related to tickets and ticket redemptions. Tickets are generated or updated whenever a booking is created or changed.
• Users - Tables related to users, including staff and customers.
• Configured Entities - Tables related to the setup of the system, such as ticket types, events, venues and more.
• Configured Entities (Revisions) - A version of each configured entity for when it was created, and each time it was amended. This is useful for tracking changes to the setup over time, which is usually needed for auditing purposes.
• Custom: Custom tables configured per client. For cases where the standard data sources do not provide the necessary information, custom data sources may be created.
• Note, if you're using direct data access, these will not show up as regular tables to query like the other tables. They are not stored as tables or views in the database, but rather are stored SQL queries that the system runs when using them. If you need to see the SQL for these, you can download the full SQL by clicking the 'Download' button.

SQL

Since the queries are ultimately translated to SQL to be executed, the configuration options are similar to those in a SQL query

Knowledge of SQL is not necessary to use the data queries. But, if you know some SQL, it's a great mental model to have in mind when configuring the data queries:

• The 'data source' is equivalent to the table specified in a FROM clause.
• The columns are equivalent to the columns in the SELECT clause. But in addition to the data source's own columns (like 'id'), columns from related tables can be included (like 'booking.id'), and the relevant data JOINs are automatically made.
• The column grouping options are equivalent to the GROUP BY clause, and aggregation functions.
• The filters are equivalent to the WHERE clause.
• The sort order is equivalent to the ORDER BY clause.

You can click 'Download > SQL Query' at any time to see the raw SQL query that is generated from your configuration. When downloading results in any of the other formats, or viewing in the paginated preview, the data is the result of the raw SQL, unmodified except for basic formatting (date, numbers, currency, etc).

Columns

Columns are the fields that will be shown in the results table. They can be added, removed and reordered.

For every column, there are three configuration options that appear in order from left to right: the title, the field selection, and the grouping.

Column title

The column title is the name that will be shown in the results table. It can enter any text you like. Each title must be unique.

Column field

The field references a column in the data source. It can be selected from a dropdown. The dropdown shows all available fields in the data source, and also includes fields from related tables.

Special fields

Some fields are computed. Where a normal field (like 'title') might be equivalent to the SQL SELECT title, a computed column (like 'Total Net') might be equivalent to SELECT total_gross - vat. Basic calculations are done this way.

Such fields are marked in the dropdown interface as 'Computed'. This is relevant for two reasons:

• If you have direct access configured, you won't find these fields in the database. They are calculated on the fly. They may also not be present in general API calls.
• They are dynamic. If you update a value that the calculation relies upon, the computed field will update automatically.

Other types of fields exist, such as extension fields, and date modifier fields, like 'Created at (Date)'. These also have special behaviour. If you need to know more, just click on 'Download SQL Query' to see the raw SQL that is generated from your configuration.

Relational behaviour

The dropdown shows the data source's own fields at the top. Under that, grouped by the related data source name, are the fields from related tables. You can select these to get related information.

As an example, when using the 'Order' data source, you can access the 'User Email' field from the related 'Users' table. This is a simple one-to-one relationship, and there are no complications to be aware of.

Other relationships are considered one-to-many. For example, with the same 'Orders' data source, if you select 'Ticket ID' you will end up with one row per ticket, of which there are potentially multiple per order. Generally, if you're using the 'Orders' data source, you intend to see a row per orders, so in this case it makes sense to use an aggregation like 'List', or 'Count', which are described further below.

Column grouping and aggregation

The column grouping option is used to control how the data is grouped and aggregated. It defaults to 'Group', meaning that each unique value in the column will be shown as a separate row in the results table.

Options include:

• Group: Each unique value in the column will be shown as a separate row in the results table.
• Sum: The sum of all values in the column will be shown.
• Count unique: The number of unique values in the column will be shown.
• Average: The average of all values in the column will be shown.
• Min: The minimum value in the column will be shown.
• Max: The maximum value in the column will be shown.
• List unique: All unique values in the column will be shown as a comma-separated list.

In addition, filters can be applied to the aggregation by clicking on the funnel icon. This allows you to count only certain values, or sum only certain values, for example.

Grouping gotchas

The default option 'Group' is usually a good choice, but can act in unexpected ways if you're not used to SQL.

Consider you're setting up a financial report. You select the source 'Line Items Diff', and add columns for the fields 'Title', 'Qty' and 'Total Gross'. You leave the default 'Group' option for all columns. At first glance, it looks good.

In this example, the output might look like this:

The issue is, if multiple orders contain the same quantity of a particular line item, sold at the same price, they might be grouped together, hiding important information from us. If we add a column for a unique column, like the 'Booking ID', we see the full picture:

There are actually 5 'Adult' line items in total, not 3. With the above addition of the 'Booking ID' column, we get the correct results. This is because the rows are no longer identical, as they differ on the 'Booking ID' column that we added.

Now that we can see the issue, we can remove the 'Booking ID' column, and change the 'Qty' and 'Total Gross' columns from 'Group' to 'Sum':

This now displays correctly! Keep this behaviour in mind when crafting your queries. If in doubt, add more columns to understand the data better.

Filters

Filters can be saved with the query to narrow down the results. They work the same way as the quick filters detailed elsewhere in this documents, but are saved with the query and applied automatically. This is in contrast to quick filters, which are forgotten when reloading the browser, or closing the tab.

Quick filter defaults

Although the quick filter selection that a user makes are forgotten each time the page is reloaded, you can configure default quick filters that are applied when the query is first loaded.

An example use of this is to set up a default quick filter like Trade Partner ID is <empty> on a B2B reporting query. Then, when a user loads that query, they need only select the trade partner they want to report on from the dropdown. This speeds up the process, and reduces the room for human error.

Deleting queries

Queries can be deleted. Click 'Configure', and then 'Delete'. This removes the query from the left-hand navigation.

Archiving

Deleted queries actually become 'archived'. The history of that query is still available through the Data Query system itself, by choosing the 'Data Query Revision' data source, and the query can be restored if necessary, though this is currently not available in the UI.

Duplicating queries

Queries can be duplicated, saving time when you want to set up a query that is similar to one that already exists. Click 'Configure', and then 'Save as copy'.

Direct connection

The data that powers the data queries is stored in a relational SQL database. To get full access to the data, Expian offers the ability to connect to the database directly, using a read-only user account. This can be plugged into external Business Intelligence tools, or simply used to craft SQL queries directly.

The data offered is a public-facing schema which has curated views over the internal data that drives Expian, which are guaranteed to remain stable over time.

If you wish to make use of this feature, contact your customer services representative.

Key Features

Feature AppendixReports

Best Practice

• Plan your data model first: Before building a report, decide which entity (Orders, Tickets, or Users) represents the 'one row = one record' level you want to analyse.
• Use Revisions for audits: When tracking changes or financial adjustments, use Orders (Revisions) or Configured Entities (Revisions) to capture historical context.
• Start small, then expand: Build simple queries first, verify results, then add filters and aggregations once you confirm accuracy.
• Apply grouping carefully: Understand how ‘Group’ vs. ‘Sum’ affects your totals to avoid undercounting or duplicate rows.
• Document each query: Use the Name, Category, and Description fields to clearly identify what the report measures and who uses it.
• Validate before publishing: Cross-check totals against known financial data to ensure calculations are correct before sharing or exporting.
• Secure direct access: If enabling external database connections, restrict credentials to read-only users and store them securely.
• Archive unused queries: Regularly clean up or archive old reports to maintain clarity and improve system performance.

Summary

The Reports section in the Reservations Portal provides an advanced analytics layer that allows organisations to turn their operational data into actionable intelligence.

By combining flexible data modelling with robust exporting and filtering tools, it empowers users to create meaningful insights without technical overhead.

Used correctly, Reports bridge the gap between day-to-day operations and strategic decision-making, ensuring that every booking, transaction, and customer interaction contributes to a complete, accurate picture of business performance.

Query Builder

Create, name, and categorise your own reports using visual query controls.

1

£10

JavaScript Object Notation.

Delivers richer contextual data in a single export.

CSV

Create a daily visitor count by event or location.

Description

Apply layered filters, nested rule groups, and date/time filters to isolate the exact dataset required.

Export data to Power BI or Excel for monthly reporting.

Quick Filters & Nested Rules

Pull fields from related tables (e.g., include User Email when querying Orders).

£50

Essential for auditing, compliance, and financial reporting accuracy.

Access the generated SQL for each report.

Consolidate Ticket, Route, and Fare data to monitor service usage.

Multi-Format Exporting

Apply temporary filters or set reusable defaults for recurring reports.

Enables enterprise-grade analytics while maintaining data security.

Apply layered filters to target specific data sets (by channel, venue, partner, or status).

Data Access

Track changes to Orders and Configurations over time.

2

Provides unified visibility across all operational and financial data.

Data Sources

DVI1111111111

Title

Custom Query Configuration

Multi-Format Export (CSV, JSON, SQL)

Direct Database Connection

£5

Combine Admissions, Donations, and Refunds into a single revenue summary.

Review logic behind Gift Aid or Membership reports.

CSV (Raw)

Filter by ticket type or promo campaign.

Aggregations

Feature

Adult

Simplifies integration with BI platforms and ensures compatibility with external reporting tools.

Core Data Entities

Automatically compiles each report into PostgreSQL-flavour SQL that can be viewed or downloaded.

Export to SQL for route performance dashboard.

Identify when ticket prices or capacity changed.

Adult

DVI2222222222

Provides complete flexibility to model data around unique business needs.

Automatically calculate fields such as Total Net or VAT.

Computed Fields

Description

Comma-separated values without headers and with raw currency values.

Adult

Connect to Expian’s read-only database schema using external BI tools such as Power BI or Tableau.

Improves transparency and helps users learn data relationships.

2

Direct Data Access (Optional)

Summarise total passenger numbers per route.

Child

5

Why It Matters

Total Gross

Why It Matters

SQL Query

Feature

Example Use – Attractions

Qty

Share route occupancy report between operations and marketing.

Download data in multiple formats for further analysis or system import.

Enhances financial accuracy and consistency.

Child

£20

Data Revisions Tracking

1

Comma-separated values.

Produce monthly income summaries by site.

1

Enables self-service analytics without technical knowledge.

Generate a list of bookings by route and sailing date.

Derived Metrics

Total Gross

Optional read-only access to Expian’s shared schema for external BI tools.

Quick Filters & Defaults

View or Download SQL Query

£10

1

DVI1111111111

Aggregations & Computed Fields

Simplifies creation of financial and operational summaries without manual data manipulation.

Filter by departure port or vessel.

Enables precise analysis and reduces reporting noise.

Historical Data Tracking

SQL Query Visibility

Supports audit, compliance, and version control.

Share revenue summary between ticketing and finance teams.

Description

Integrates easily with finance, BI, and analytics tools.

Connect to Tableau for fleet-wide travel data.

2

SQL Visibility

Generate net vs. gross ticket income per event.

Validate refund calculations and reconciliation queries.

Enables advanced data visualisation and enterprise-level analytics.

Booking ID

£5

Custom Data Queries

£20

Qty

Format

Duplicate or Template Queries

Eliminates manual spreadsheet calculations.

Download results as CSV, JSON, or raw SQL. Choose between formatted and raw numeric exports for analytics or finance tools.

Relational Data Linking

£20

Build bespoke reports using configurable data sources and relationships between Orders, Tickets, and Users.

Group, Sum, Count, Average

Revisions

Adult

Allows precise data slicing for operational decision-making.

Total Gross

JSON

£5

Sharing

DVI2222222222

Adult

Calculate concession discounts and tax breakdowns.

Qty

Apply instant aggregation logic to totals, capacity, or revenue data.

Group, sum, count, or average values automatically. Includes computed fields such as Net, Gross, or VAT differences.

Exports

Use ‘Revisions’ sources to view historical changes to Orders or Configurations over time.

Verify when sailing schedules or fares were updated.

Improves transparency and supports validation of report logic.

Saves time and ensures reporting consistency.

Example Use – Travel

Child

Title

Copy and reuse existing reports across departments or sites.

Filtering

The raw SQL query that powers this Data Query.

1

Reduces user error and speeds up regular reporting.

Adult

Title

Category

Dynamic Filtering & Sorting

Choose from Orders, Tickets, Payments, Users, and Configured Entities to build flexible data models.

Connect to Power BI for multi-site visitor trends.

Each 'Data Query' compiles to PostgreSQL-flavour SQL, which is responsible for the entirety of the data fetching, filtering and ordering behind a Data Query. This option downloads an SQL file with that query.

Values are outputted raw. For example, £10,000 is outputted as 1000000, and 10% is outputted as 0.1.

This format is ideal for data processing and analysis. Currency values are exported as raw numbers (e.g., 100000 instead of £1,000.00), while other data types maintain their formatting. No column headers are included in the output.

Data is formatted according to the column types.

If you have configured direct access to the 'shared' schema, you can run this query directly against the database in your own SQL client to receive the same results. This can be a great way to understand the data and relations under the hood.

The response also includes column titles and data types. The data model is documented fully in the API documentation, along with details of a json-paginated format, which is the same but paginated, for driving interactive user interfaces.