SSRS, Crystal Reports and paginated reports

Last updated 20 October 2026

SSRS and Crystal Reports expect a SQL data source, and there is no supported, fast way to point either of them at a SharePoint list directly.

The monthly compliance report is an SSRS report that has run on the first working day of every month for six years. Its source used to be a SQL table. Then the team moved its tracking into a SharePoint list, and the report lost its data. SSRS and Crystal Reports are built around SQL data sources: a connection string, a query or stored procedure, and a report definition that expects rows. Neither queries a SharePoint list well. An OData feed works as a generic web-service source, slowly, with lookup columns left as IDs. A SQL table refreshed by hand works until the person who refreshes it is on leave.

Give the report its SQL source back

SPSync replicates the lists a report depends on into SQL Server, Azure SQL, Oracle or Postgres on a schedule and keeps that copy current. The report’s dataset points at a connection string and a table or view, as it did before the move. Parameters, subreports, stored procedures and scheduled subscriptions carry on as they always have, and the data behind them is never more than one sync interval old.

Joins and groups the report designer expects

Each list is a table with a stable primary key. A lookup arrives as a key column into the lookup list’s table plus its display value, so the dataset shows a name without a join. Multi-value columns are child tables, which a paginated report joins and groups like any parent-child relationship. SPSync can also generate a reporting view per list that adds person names, which keeps the report’s own query short. Deleted items keep their row with sp_deleted_at set, so a report of historical totals still counts them.

Repointing the data source

Pick the site and the lists the report needs, including the lookup targets it joins against. Choose the destination the report server already talks to and let SPSync create the schema. Match the sync to how often the report runs; for a monthly report, a daily sync is plenty. Then change the SSRS shared data source, or the Crystal Reports connection, to the replicated tables or a generated view.

A report server is usually always on, and the sync behind it should be too: SPSync in your Azure, or the command line scheduled next to the report server’s own jobs. One limit to plan for: SPSync replicates list and library metadata, not file bytes, so a report that embeds attachments or document content needs those handled separately.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name ssrs_crystal_reports --prefix ssrs_crystal_reports --destination-kind mssql --connection-string-env SPSYNC_DB_CONNECTION --dataset-name ssrs_crystal_reports --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: ssrs_crystal_reports
prefix: ssrs_crystal_reports
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: mssql
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: ssrs_crystal_reports
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist