> ## Documentation Index
> Fetch the complete documentation index at: https://help-loyalife.xoxoday.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom reports

> Build SQL-based custom reports in Loyalife with real-time preview, scheduled generation, filtering, and controlled sharing.

Custom Reports let you query your loyalty program data directly using SQL. Unlike standard pre-built reports, custom reports give you full control over which fields to include, how data is filtered, and which date ranges to analyse. You can preview results in real time, schedule recurring exports, and generate one-time reports on demand.

## What custom reports are for

Standard Loyalife reports (transaction, member, communication) cover the most common use cases. Custom reports fill the gaps when you need:

* Metrics that combine data from multiple standard report categories
* Non-standard aggregations (e.g., average basket size by tier by month)
* Ad-hoc analysis for a specific date window without changing your recurring schedule
* Customised exports for stakeholder or compliance reporting

## Accessing custom reports

Navigate to **Reports & Analytics → Data Exports → Custom Reports** tab.

## Creating a custom report

<Steps>
  <Step title="Click Create Report">
    From the Custom Reports tab, click **Create Report**.
  </Step>

  <Step title="Name and describe the report">
    Give the report a clear name and an optional description. The description is visible in the Report Settings panel and helps other users understand the report's purpose.
  </Step>

  <Step title="Write the SQL query">
    Enter your SQL query in the query editor. The query can reference any table available in your Loyalife data schema. Use named parameters — `:start_date` and `:end_date` — if you want the report to support custom date range generation.

    **Example parameter usage:**

    ```sql theme={null}
    SELECT member_id, SUM(points_earned) AS total_earned
    FROM transactions
    WHERE transaction_date BETWEEN :start_date AND :end_date
    GROUP BY member_id
    ```
  </Step>

  <Step title="Set the generation schedule">
    Choose how often the report runs automatically:

    | Schedule    | Frequency                                                                 |
    | ----------- | ------------------------------------------------------------------------- |
    | Daily       | Runs every day                                                            |
    | Weekly      | Runs once per week                                                        |
    | Monthly     | Runs once per month                                                       |
    | Manual only | No automatic generation — report runs only when you explicitly trigger it |
  </Step>

  <Step title="Configure sharing (optional)">
    Use **Manage Report Sharing** to specify which users and roles can view or download this report. Sharing rules control who sees the report in their Custom Reports list.
  </Step>

  <Step title="Save">
    Save the report. It appears in the Custom Reports listing and will begin generating on schedule.
  </Step>
</Steps>

<Info>
  Only Super Admins can create and edit custom reports. Any user with access to the report (as defined by the sharing settings) can view, preview, and generate on-demand exports.
</Info>

## Real-time preview

You can preview the results of a custom report directly inside the dashboard without exporting a file. Click the report to open its detail view, then use the **Preview** panel to see the current data result set displayed as an interactive table.

This eliminates the need to export a file just to check whether a query is returning the right data. Previous behaviour required downloading a CSV for every verification step.

### Report Settings panel

Every custom report has a **Report Settings** button that reveals:

| Setting              | Description                                            |
| -------------------- | ------------------------------------------------------ |
| Description          | The report's purpose and data coverage                 |
| Period of generation | The scheduled frequency                                |
| SQL Query            | The underlying query, readable for review or debugging |

### Manage Report Sharing panel

The **Manage Report Sharing** button shows:

| Setting                   | Description                                |
| ------------------------- | ------------------------------------------ |
| Reports shared with       | Specific users who can access this report  |
| Reports shared with roles | Roles whose members can access this report |

## Search and filter on report data

Custom report outputs now include **AG Grid**-powered real-time search and filtering directly on the loaded CSV data:

### Filtering

| Feature                  | Detail                                                                                                         |
| ------------------------ | -------------------------------------------------------------------------------------------------------------- |
| **Global search**        | Searches across all columns simultaneously                                                                     |
| **Column-level filters** | Applied on top of global search with AND logic; multi-column filtering supported                               |
| **Filter types**         | Auto-assigned: text filter for string columns, number filter for numeric columns, date filter for date columns |
| **Filter reset**         | Clear all active filters with one click                                                                        |

### Sorting and pagination

* Sorting available on string, numeric, and date columns
* Pagination options: **50**, **100**, **500**, or **1,000** rows per page

### Filtered export

Click **Export CSV** to download only the rows matching your current filter state — not the full report. This makes it easy to share a filtered slice of data without modifying the underlying query.

<Info>
  Column headers are auto-detected from the CSV file — no configuration is needed. Filters are client-side only and do not modify the stored report or its schedule.
</Info>

<Warning>
  Search and filter are not available on the Report Data tab — only on the report output view after opening a generated file.
</Warning>

### View links in reports

Custom reports include clickable **View Segment** and **View Campaign** links in applicable report types. These links navigate directly to the relevant Segment or Campaign detail page. If your session has expired, clicking the link redirects to the login page.

## On-demand generation (custom date range)

Any custom report that uses `:start_date` and `:end_date` parameters in its SQL query can be generated for a specific date window on demand, without changing the recurring schedule.

<Steps>
  <Step title="Open the report">
    Navigate to the custom report's detail view.
  </Step>

  <Step title="Click Generate">
    The **Generate** button opens a popup requiring:

    | Field      | Required |
    | ---------- | -------- |
    | Start Date | Yes      |
    | End Date   | Yes      |
  </Step>

  <Step title="Click Generate Now">
    The system executes the saved SQL query, binding `:start_date` and `:end_date` to the values you entered. No SQL validation is performed — if your query does not support these parameters, the generation job will fail.
  </Step>

  <Step title="Download the output">
    The generated file appears in the report's output list tagged as **Manual** with a filename in the format:

    `{ReportName}_{YYYYMMDD}-{YYYYMMDD}.csv`
  </Step>
</Steps>

<Warning>
  If the SQL query does not include `:start_date` and `:end_date` parameters, the generation job will fail. Verify your query uses these exact parameter names before triggering a manual run.
</Warning>

### Manual vs Auto outputs

All outputs — both scheduled and on-demand — appear in the same output list. Manual outputs are tagged **Manual** to distinguish them from scheduled runs.

| Output type                      | Tag    | Naming                                   |
| -------------------------------- | ------ | ---------------------------------------- |
| Scheduled (daily/weekly/monthly) | Auto   | `{ReportName}_{Period}.csv`              |
| On-demand (custom date range)    | Manual | `{ReportName}_{YYYYMMDD}-{YYYYMMDD}.csv` |

## Audit trail

Every manual generation attempt is captured in the audit trail with:

* The user who triggered it
* The start and end dates used
* The run ID
* Status (success or failure)
* Timestamp

Scheduled runs are also logged automatically.

## Permissions reference

| Action                                 | Who can do it                             |
| -------------------------------------- | ----------------------------------------- |
| Create report                          | Super Admins only                         |
| Edit report (query, schedule, sharing) | Super Admins only                         |
| View report and preview data           | Any user with access per sharing settings |
| Generate on-demand (manual run)        | Any user with access per sharing settings |
| Download output                        | Any user with access per sharing settings |

## Troubleshooting

**Manual generation fails immediately.**

* Check that your SQL query contains `:start_date` and `:end_date` parameters. Without them, the job cannot bind the date range and will fail.

**Report preview shows unexpected results.**

* Open Report Settings and review the SQL query. Check filter conditions, date field names, and JOIN logic.

**Report is not visible to a team member.**

* Open Manage Report Sharing and add their username or role.

**Scheduled report is not generating.**

* Verify the generation schedule is set (not Manual only).
* Check the output list for any failed run entries and review error messages.