Reporting and analytics on SharePoint list data

Last updated 23 October 2026

Power BI is slow, refresh fails, and lookups arrive as opaque IDs the report has to resolve itself.

Every reporting tool that reads a SharePoint list directly pays the same cost: rows fetched through the list API on each refresh, lookups turned back into names one at a time, and a refresh that slows as the list grows. A Power BI dashboard of open work orders, an Excel workbook of supplier spend and a Tableau view for regional managers all hit the same wall, each in its own way.

Replicate once, report from tables

Point SPSync at a site, pick the lists and libraries, and it creates the destination schema. A lookup becomes a key column into the table for the list it points at, with its display value beside it, and a multi-choice column gets a child table, so it never collapses into a delimited string. Multi-lookup and multi-person columns take the same shape, and live tenant verification for them is still in hand. The first run copies everything. Each run after it is a delta, so the tables Power BI, Excel and Tableau query keep in step with the lists on the schedule you set. Every one of those tools already queries a SQL table quickly.

Pages for each reporting tool

Most reporting questions are tool-specific, so the detail lives on its own pages:

• Power BI on SharePoint lists, for slow refreshes and a list connector with no DirectQuery.
• Excel reports from SharePoint, for workbooks and Access databases with linked lists.
• Tableau, Looker, Metabase and Grafana, for BI tools that expect a database or warehouse. Tableau’s own SharePoint Lists connector is retired, and Tableau on SharePoint lists after the connector deprecation covers its JDBC replacement and the database route.
• SSRS and Crystal Reports, for paginated reports on a report server.

The SharePoint list to SQL Server and Power BI walkthrough goes from connecting and picking lists to the replicated schema and a Power BI report on DirectQuery, shown for the desktop app, SPSync in your Azure and the command line.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name reporting_and_analytics --prefix reporting_and_analytics --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name reporting_and_analytics --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: reporting_and_analytics
prefix: reporting_and_analytics
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: reporting_and_analytics
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