Services Reporting Examples

This guide provides complete SQL queries for building comprehensive worship and volunteer reports. These examples are designed for use in BI tools like Power BI, Tableau, or custom dashboards.

Ready for Production Use

These queries are structured for direct use in reporting tools. They include all necessary joins, calculations, and formatting for professional ministry reports.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center Services module live in the planning_center schema. Always prefix table names with planning_center. in your reports. ✅ CORRECT: SELECT * FROM planning_center.services_plan_people ❌ INCORRECT: SELECT * FROM services_plan_people

Row Level Security (RLS)

Row Level Security automatically manages:

tenant_organization_id – isolates results to your organization
system_status – active records returned by default

Avoid adding these filters manually—RLS already enforces them and redundant predicates can hide data or slow execution:

❌ WHERE tenant_organization_id = 1
❌ WHERE system_status = 'active'

Keep your filters focused on scheduling cadence, volunteer status, and worship planning while relying on RLS for tenancy and system status.

Volunteer Management Dashboard

Complete Volunteer Overview

-- Comprehensive volunteer metrics for dashboard
WITH volunteer_base AS (
    SELECT
        p.person_id,
        p.full_name,
        p.first_name,
        p.last_name,
        p.photo_thumbnail_url,
        p.preferred_max_plans_per_month,
        p.archived,
        p.passed_background_check,
        STRING_AGG(DISTINCT t.name, ', ') as teams,
        COUNT(DISTINCT t.team_id) as team_count
    FROM planning_center.services_people p
    LEFT JOIN planning_center.services_plan_people pp ON p.person_id = pp.person_id
    LEFT JOIN planning_center.services_teams t ON pp.team_id = t.team_id
    WHERE p.archived = false
    GROUP BY p.person_id, p.full_name, p.first_name, p.last_name,
             p.photo_thumbnail_url, p.preferred_max_plans_per_month,
             p.archived, p.passed_background_check
),
recent_activity AS (
    SELECT
        pp.person_id,
        COUNT(DISTINCT pl.plan_id) as services_last_90_days,
        COUNT(CASE WHEN pp.status = 'C' THEN 1 END) as confirmed_last_90,
        COUNT(CASE WHEN pp.status = 'D' THEN 1 END) as declined_last_90,
        MAX(pl.sort_date) as last_scheduled,
        MIN(pl.sort_date) FILTER (WHERE pl.sort_date >= CURRENT_DATE) as next_scheduled
    FROM planning_center.services_plan_people pp
    JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '90 days'
        AND pl.sort_date <= CURRENT_DATE + INTERVAL '30 days'
    GROUP BY pp.person_id
),
blockout_summary AS (
    SELECT
        b.person_id,
        COUNT(DISTINCT bd.date) as blocked_days_next_30
    FROM planning_center.services_blockouts b
    JOIN planning_center.services_blockout_dates bd ON b.blockout_id = bd.blockout_id
    WHERE bd.date >= CURRENT_DATE
        AND bd.date <= CURRENT_DATE + INTERVAL '30 days'
    GROUP BY b.person_id
)
SELECT
    vb.full_name,
    vb.first_name,
    vb.last_name,
    vb.photo_thumbnail_url,
    vb.teams,
    vb.team_count,
    COALESCE(ra.services_last_90_days, 0) as services_last_90_days,
    COALESCE(ra.confirmed_last_90, 0) as confirmed_count,
    COALESCE(ra.declined_last_90, 0) as declined_count,
    CASE
        WHEN ra.services_last_90_days > 0
        THEN ROUND(ra.confirmed_last_90::numeric * 100 / ra.services_last_90_days, 1)
        ELSE 0
    END as confirmation_rate,
    ra.last_scheduled,
    ra.next_scheduled,
    CURRENT_DATE - ra.last_scheduled as days_since_served,
    vb.preferred_max_plans_per_month as monthly_limit,
    COALESCE(bs.blocked_days_next_30, 0) as unavailable_days,
    vb.passed_background_check,
    CASE
        WHEN ra.services_last_90_days = 0 OR ra.last_scheduled < CURRENT_DATE - INTERVAL '90 days'
            THEN 'Inactive'
        WHEN ra.services_last_90_days >= 12 THEN 'Very Active'
        WHEN ra.services_last_90_days >= 6 THEN 'Active'
        WHEN ra.services_last_90_days >= 3 THEN 'Occasional'
        ELSE 'Rare'
    END as activity_level,
    CASE
        WHEN ra.next_scheduled IS NOT NULL THEN 'Scheduled'
        WHEN bs.blocked_days_next_30 > 15 THEN 'Mostly Unavailable'
        WHEN bs.blocked_days_next_30 > 0 THEN 'Partially Available'
        ELSE 'Available'
    END as current_status
FROM volunteer_base vb
LEFT JOIN recent_activity ra ON vb.person_id = ra.person_id
LEFT JOIN blockout_summary bs ON vb.person_id = bs.person_id
ORDER BY ra.services_last_90_days DESC NULLS LAST, vb.full_name;

Team Roster Report

-- Detailed team roster with positions and availability
SELECT
    t.name as team_name,
    t.schedule_to as scheduling_type,
    tp.name as position,
    p.full_name,
    p.photo_thumbnail_url,
    CASE
        WHEN p.passed_background_check = true THEN 'Yes'
        WHEN t.secure_team = true THEN 'Required'
        ELSE 'N/A'
    END as background_check,
    COUNT(DISTINCT pp.plan_id) FILTER (
        WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
    ) as recent_schedules,
    STRING_AGG(
        DISTINCT pl.short_dates || ' (' || pp.status || ')',
        ', ' ORDER BY pl.sort_date DESC
    ) FILTER (
        WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '1 month'
    ) as recent_services,
    MAX(bd.date) FILTER (
        WHERE bd.date >= CURRENT_DATE
    ) as next_blocked_date,
    CASE
        WHEN COUNT(bd.date) FILTER (WHERE bd.date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '14 days') > 7
            THEN 'Mostly Unavailable'
        WHEN COUNT(bd.date) FILTER (WHERE bd.date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '14 days') > 0
            THEN 'Partially Available'
        ELSE 'Available'
    END as two_week_availability
FROM planning_center.services_teams t
JOIN planning_center.services_team_positions tp ON t.team_id = tp.team_id
JOIN planning_center.services_person_team_position_assignments pa
    ON tp.team_position_id = pa.team_position_id
JOIN planning_center.services_people p ON pa.person_id = p.person_id
LEFT JOIN planning_center.services_plan_people pp ON p.person_id = pp.person_id
    AND pp.team_id = t.team_id
LEFT JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
LEFT JOIN planning_center.services_blockout_dates bd ON p.person_id = bd.person_id
WHERE t.archived_at IS NULL
    AND p.archived = false
GROUP BY t.name, t.schedule_to, t.secure_team, tp.name, p.person_id,
         p.full_name, p.photo_thumbnail_url, p.passed_background_check
ORDER BY t.name, tp.name, p.full_name;

Worship Planning Analytics

Song Usage Report

-- Complete song analytics for worship planning
WITH song_metrics AS (
    SELECT
        s.song_id,
        s.title,
        s.author,
        s.copyright,
        s.ccli_number,
        s.themes,
        s.hidden,
        COUNT(DISTINCT i.plan_id) as total_uses,
        COUNT(DISTINCT i.plan_id) FILTER (
            WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
        ) as uses_last_3_months,
        COUNT(DISTINCT i.plan_id) FILTER (
            WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '1 year'
        ) as uses_last_year,
        MIN(pl.sort_date) as first_used,
        MAX(pl.sort_date) as last_used,
        STRING_AGG(DISTINCT a.chord_chart_key, ', ') as keys_used,
        AVG(i.length)::INTEGER as avg_length_seconds,
        COUNT(DISTINCT st.service_type_id) as service_types_used_in
    FROM planning_center.services_songs s
    LEFT JOIN planning_center.services_items i ON s.song_id = i.song_id
    LEFT JOIN planning_center.services_plans pl ON i.plan_id = pl.plan_id
    LEFT JOIN planning_center.services_arrangements a ON i.arrangement_id = a.arrangement_id
    LEFT JOIN planning_center.services_service_types st ON pl.service_type_id = st.service_type_id
    WHERE i.item_type = 'song' OR i.item_type IS NULL
    GROUP BY s.song_id, s.title, s.author, s.copyright, s.ccli_number, s.themes, s.hidden
),
arrangement_counts AS (
    SELECT
        song_id,
        COUNT(*) as arrangement_count,
        STRING_AGG(name || ' (' || chord_chart_key || ')', ', ') as arrangements
    FROM planning_center.services_arrangements
    WHERE archived_at IS NULL
    GROUP BY song_id
)
SELECT
    sm.title,
    sm.author,
    sm.copyright,
    sm.ccli_number,
    sm.themes,
    CASE WHEN sm.hidden = true THEN 'Hidden' ELSE 'Active' END as status,
    COALESCE(sm.total_uses, 0) as total_uses,
    COALESCE(sm.uses_last_3_months, 0) as recent_uses,
    COALESCE(sm.uses_last_year, 0) as yearly_uses,
    sm.first_used,
    sm.last_used,
    CURRENT_DATE - sm.last_used as days_since_used,
    sm.keys_used,
    ac.arrangement_count,
    ac.arrangements,
    sm.avg_length_seconds / 60.0 as avg_length_minutes,
    sm.service_types_used_in,
    CASE
        WHEN sm.last_used IS NULL THEN 'Never Used'
        WHEN sm.last_used < CURRENT_DATE - INTERVAL '1 year' THEN 'Not Recently Used'
        WHEN sm.uses_last_3_months >= 10 THEN 'High Rotation'
        WHEN sm.uses_last_3_months >= 5 THEN 'Regular Rotation'
        WHEN sm.uses_last_3_months >= 2 THEN 'Occasional'
        ELSE 'Rarely Used'
    END as usage_category,
    CASE
        WHEN sm.last_used < CURRENT_DATE - INTERVAL '2 months'
            AND sm.uses_last_year >= 6 THEN 'Consider Scheduling'
        WHEN sm.uses_last_3_months >= 8 THEN 'Recently Overused'
        ELSE 'Normal'
    END as recommendation
FROM song_metrics sm
LEFT JOIN arrangement_counts ac ON sm.song_id = ac.song_id
ORDER BY sm.uses_last_3_months DESC, sm.title;

Service Flow Analysis

-- Analyze service structure and timing patterns
WITH service_details AS (
    SELECT
        st.name as service_type,
        pl.plan_id,
        pl.title,
        pl.series_title,
        pl.sort_date,
        pl.total_length / 60.0 as total_minutes,
        pl.plan_people_count as volunteers,
        COUNT(i.item_id) as item_count,
        COUNT(CASE WHEN i.item_type = 'song' THEN 1 END) as song_count,
        COUNT(CASE WHEN i.item_type = 'header' THEN 1 END) as header_count,
        COUNT(CASE WHEN i.item_type = 'media' THEN 1 END) as media_count,
        SUM(CASE WHEN i.item_type = 'song' THEN i.length ELSE 0 END) / 60.0 as music_minutes,
        SUM(CASE WHEN i.item_type != 'song' THEN i.length ELSE 0 END) / 60.0 as non_music_minutes,
        STRING_AGG(
            CASE WHEN i.item_type = 'header' THEN i.title END,
            ' > ' ORDER BY i.sequence
        ) as section_flow
    FROM planning_center.services_plans pl
    JOIN planning_center.services_service_types st ON pl.service_type_id = st.service_type_id
    LEFT JOIN planning_center.services_items i ON pl.plan_id = i.plan_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY st.name, pl.plan_id, pl.title, pl.series_title, pl.sort_date,
             pl.total_length, pl.plan_people_count
)
SELECT
    service_type,
    title,
    series_title,
    sort_date,
    total_minutes,
    volunteers,
    item_count,
    song_count,
    header_count,
    media_count,
    ROUND(music_minutes, 1) as worship_minutes,
    ROUND(non_music_minutes, 1) as other_minutes,
    ROUND(music_minutes * 100 / NULLIF(total_minutes, 0), 1) as worship_percentage,
    ROUND(volunteers::numeric / NULLIF(item_count, 0), 1) as volunteers_per_item,
    section_flow,
    CASE
        WHEN total_minutes < 45 THEN 'Short Service'
        WHEN total_minutes BETWEEN 45 AND 75 THEN 'Standard Service'
        WHEN total_minutes BETWEEN 75 AND 90 THEN 'Extended Service'
        ELSE 'Long Service'
    END as service_length_category,
    CASE
        WHEN song_count = 0 THEN 'No Music'
        WHEN song_count <= 3 THEN 'Light Music'
        WHEN song_count <= 5 THEN 'Standard Music'
        ELSE 'Music Heavy'
    END as music_emphasis
FROM service_details
ORDER BY sort_date DESC;

Ministry Effectiveness Metrics

Volunteer Engagement Score

-- Calculate comprehensive engagement scores for volunteers
WITH engagement_metrics AS (
    SELECT
        p.person_id,
        p.full_name,
        -- Participation metrics
        COUNT(DISTINCT pp.plan_id) as total_scheduled,
        COUNT(DISTINCT pp.plan_id) FILTER (WHERE pp.status = 'C') as confirmed_services,
        COUNT(DISTINCT pp.plan_id) FILTER (WHERE pp.status = 'D') as declined_services,
        COUNT(DISTINCT t.team_id) as teams_serving,
        COUNT(DISTINCT DATE_TRUNC('month', pl.sort_date)) as months_active,
        -- Timing metrics
        AVG(EXTRACT(DAYS FROM pl.sort_date - pp.notification_sent_at)) as avg_notice_days,
        AVG(EXTRACT(DAYS FROM pl.sort_date - pp.status_updated_at)) as avg_response_days,
        -- Reliability metrics
        COUNT(DISTINCT pp.plan_id) FILTER (
            WHERE pp.status = 'C' AND pp.status_updated_at > pl.sort_date - INTERVAL '7 days'
        ) as early_confirmations,
        -- Recent activity
        MAX(pl.sort_date) as last_served,
        MIN(pl.sort_date) FILTER (WHERE pl.sort_date >= CURRENT_DATE) as next_scheduled
    FROM planning_center.services_people p
    JOIN planning_center.services_plan_people pp ON p.person_id = pp.person_id
    JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
    JOIN planning_center.services_teams t ON pp.team_id = t.team_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '6 months'
        AND p.archived = false
    GROUP BY p.person_id, p.full_name
    HAVING COUNT(DISTINCT pp.plan_id) >= 3  -- Minimum activity threshold
)
SELECT
    full_name,
    total_scheduled,
    confirmed_services,
    declined_services,
    ROUND(confirmed_services::numeric * 100 / NULLIF(total_scheduled, 0), 1) as confirmation_rate,
    teams_serving,
    months_active,
    ROUND(total_scheduled::numeric / NULLIF(months_active, 0), 1) as services_per_month,
    ROUND(avg_notice_days, 1) as avg_notice_days,
    ROUND(avg_response_days, 1) as avg_response_days,
    ROUND(early_confirmations::numeric * 100 / NULLIF(confirmed_services, 0), 1) as early_confirm_rate,
    last_served,
    next_scheduled,
    -- Calculate engagement score (0-100)
    ROUND(
        (
            -- Confirmation rate (40% weight)
            (confirmed_services::numeric / NULLIF(total_scheduled, 0) * 40) +
            -- Activity consistency (30% weight)
            (LEAST(months_active / 6.0, 1.0) * 30) +
            -- Response time (20% weight)
            (CASE
                WHEN avg_response_days <= 2 THEN 20
                WHEN avg_response_days <= 5 THEN 15
                WHEN avg_response_days <= 7 THEN 10
                ELSE 5
            END) +
            -- Team diversity (10% weight)
            (LEAST(teams_serving / 3.0, 1.0) * 10)
        ), 1
    ) as engagement_score,
    CASE
        WHEN confirmed_services::numeric / NULLIF(total_scheduled, 0) >= 0.9
            AND months_active >= 5 THEN 'Champion'
        WHEN confirmed_services::numeric / NULLIF(total_scheduled, 0) >= 0.75
            AND months_active >= 3 THEN 'Reliable'
        WHEN confirmed_services::numeric / NULLIF(total_scheduled, 0) >= 0.6 THEN 'Developing'
        ELSE 'Needs Support'
    END as engagement_category
FROM engagement_metrics
ORDER BY engagement_score DESC;

Team Health Dashboard

-- Comprehensive team health metrics
WITH team_stats AS (
    SELECT
        t.team_id,
        t.name,
        t.rehearsal_team,
        t.secure_team,
        COUNT(DISTINCT pp.person_id) as active_members,
        COUNT(DISTINCT pp.plan_id) as total_schedules,
        COUNT(DISTINCT pp.plan_id) FILTER (WHERE pp.status = 'C') as confirmed_schedules,
        COUNT(DISTINCT pp.plan_id) FILTER (WHERE pp.status = 'D') as declined_schedules,
        COUNT(DISTINCT np.plan_id) as plans_with_needs,
        SUM(np.quantity) as total_positions_needed
    FROM planning_center.services_teams t
    LEFT JOIN planning_center.services_plan_people pp ON t.team_id = pp.team_id
    LEFT JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
    LEFT JOIN planning_center.services_needed_positions np ON t.team_id = np.team_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
        AND t.archived_at IS NULL
    GROUP BY t.team_id, t.name, t.rehearsal_team, t.secure_team
),
position_coverage AS (
    SELECT
        t.team_id,
        COUNT(DISTINCT tp.team_position_id) as position_count,
        COUNT(DISTINCT pa.person_id) as qualified_people,
        STRING_AGG(DISTINCT tp.name, ', ') as positions
    FROM planning_center.services_teams t
    LEFT JOIN planning_center.services_team_positions tp ON t.team_id = tp.team_id
    LEFT JOIN planning_center.services_person_team_position_assignments pa
        ON tp.team_position_id = pa.team_position_id
    GROUP BY t.team_id
),
recent_trends AS (
    SELECT
        pp.team_id,
        COUNT(DISTINCT pp.plan_id) FILTER (
            WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '1 month'
        ) as schedules_last_month,
        COUNT(DISTINCT pp.person_id) FILTER (
            WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '1 month'
        ) as people_last_month
    FROM planning_center.services_plan_people pp
    JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
    GROUP BY pp.team_id
)
SELECT
    ts.name as team_name,
    CASE WHEN ts.rehearsal_team THEN 'Yes' ELSE 'No' END as rehearsal_required,
    CASE WHEN ts.secure_team THEN 'Yes' ELSE 'No' END as background_check_required,
    ts.active_members,
    pc.position_count,
    pc.qualified_people,
    ROUND(pc.qualified_people::numeric / NULLIF(pc.position_count, 0), 1) as people_per_position,
    ts.total_schedules as schedules_3_month,
    rt.schedules_last_month,
    rt.people_last_month,
    ts.confirmed_schedules,
    ROUND(ts.confirmed_schedules::numeric * 100 / NULLIF(ts.total_schedules, 0), 1) as confirmation_rate,
    ts.declined_schedules,
    ts.plans_with_needs,
    ts.total_positions_needed,
    ROUND(ts.total_positions_needed::numeric / NULLIF(ts.plans_with_needs, 0), 1) as avg_needs_per_plan,
    pc.positions,
    -- Health score calculation
    CASE
        WHEN ts.confirmed_schedules::numeric / NULLIF(ts.total_schedules, 0) >= 0.9
            AND pc.qualified_people >= pc.position_count * 2
            AND ts.plans_with_needs = 0 THEN 'Excellent'
        WHEN ts.confirmed_schedules::numeric / NULLIF(ts.total_schedules, 0) >= 0.8
            AND pc.qualified_people >= pc.position_count * 1.5 THEN 'Good'
        WHEN ts.confirmed_schedules::numeric / NULLIF(ts.total_schedules, 0) >= 0.7
            AND pc.qualified_people >= pc.position_count THEN 'Fair'
        ELSE 'Needs Attention'
    END as health_status,
    CASE
        WHEN ts.plans_with_needs > ts.total_schedules * 0.2 THEN 'Understaffed - Recruit More'
        WHEN pc.qualified_people < pc.position_count * 1.5 THEN 'Low Depth - Train Backups'
        WHEN ts.confirmed_schedules::numeric / NULLIF(ts.total_schedules, 0) < 0.7 THEN 'Low Engagement - Connect with Team'
        ELSE 'Healthy'
    END as primary_recommendation
FROM team_stats ts
LEFT JOIN position_coverage pc ON ts.team_id = pc.team_id
LEFT JOIN recent_trends rt ON ts.team_id = rt.team_id
ORDER BY ts.active_members DESC;

Executive Summary Report

Ministry Overview Dashboard

-- High-level metrics for leadership
WITH summary_metrics AS (
    SELECT
        -- People metrics
        (SELECT COUNT(*) FROM planning_center.services_people WHERE archived = false) as total_volunteers,
        (SELECT COUNT(DISTINCT person_id)
         FROM planning_center.services_plan_people pp
         JOIN planning_center.services_plans p ON pp.plan_id = p.plan_id
         WHERE p.sort_date >= CURRENT_DATE - INTERVAL '3 months') as active_volunteers,
        -- Team metrics
        (SELECT COUNT(*) FROM planning_center.services_teams WHERE archived_at IS NULL) as total_teams,
        -- Service metrics
        (SELECT COUNT(*)
         FROM planning_center.services_plans
         WHERE sort_date >= CURRENT_DATE - INTERVAL '3 months') as services_last_quarter,
        (SELECT COUNT(*)
         FROM planning_center.services_plans
         WHERE sort_date >= CURRENT_DATE
           AND sort_date <= CURRENT_DATE + INTERVAL '1 month') as services_next_month,
        -- Song metrics
        (SELECT COUNT(*) FROM planning_center.services_songs WHERE hidden = false) as active_songs,
        (SELECT COUNT(DISTINCT song_id)
         FROM planning_center.services_items i
         JOIN planning_center.services_plans p ON i.plan_id = p.plan_id
         WHERE p.sort_date >= CURRENT_DATE - INTERVAL '3 months'
           AND i.item_type = 'song') as songs_used_recently,
        -- Scheduling metrics
        (SELECT AVG(plan_people_count)
         FROM planning_center.services_plans
         WHERE sort_date >= CURRENT_DATE - INTERVAL '3 months') as avg_volunteers_per_service,
        (SELECT SUM(quantity)
         FROM planning_center.services_needed_positions np
         JOIN planning_center.services_plans p ON np.plan_id = p.plan_id
         WHERE p.sort_date >= CURRENT_DATE
           AND p.sort_date <= CURRENT_DATE + INTERVAL '2 weeks') as open_positions_two_weeks
)
SELECT
    total_volunteers,
    active_volunteers,
    ROUND(active_volunteers::numeric * 100 / NULLIF(total_volunteers, 0), 1) as volunteer_engagement_rate,
    total_teams,
    services_last_quarter,
    services_next_month,
    active_songs,
    songs_used_recently,
    ROUND(songs_used_recently::numeric * 100 / NULLIF(active_songs, 0), 1) as song_utilization_rate,
    ROUND(avg_volunteers_per_service, 1) as avg_volunteers_per_service,
    COALESCE(open_positions_two_weeks, 0) as urgent_scheduling_needs,
    CASE
        WHEN open_positions_two_weeks > 10 THEN 'Critical - Many Open Positions'
        WHEN open_positions_two_weeks > 5 THEN 'Warning - Some Gaps'
        ELSE 'Healthy - Well Staffed'
    END as scheduling_status
FROM summary_metrics;

Export-Ready Formats

CSV Export for Volunteer Contact

-- Volunteer contact list for mail merge or communication
SELECT
    p.full_name,
    p.first_name,
    p.last_name,
    STRING_AGG(DISTINCT t.name, '; ') as teams,
    COUNT(DISTINCT pp.plan_id) FILTER (
        WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
    ) as services_last_3_months,
    MAX(pl.sort_date) FILTER (
        WHERE pl.sort_date < CURRENT_DATE
    ) as last_served,
    MIN(pl.sort_date) FILTER (
        WHERE pl.sort_date >= CURRENT_DATE
    ) as next_scheduled,
    CASE
        WHEN p.archived = true THEN 'Inactive'
        WHEN COUNT(DISTINCT pp.plan_id) FILTER (
            WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
        ) = 0 THEN 'No Recent Activity'
        ELSE 'Active'
    END as status
FROM planning_center.services_people p
LEFT JOIN planning_center.services_plan_people pp ON p.person_id = pp.person_id
LEFT JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
LEFT JOIN planning_center.services_teams t ON pp.team_id = t.team_id
WHERE p.archived = false
GROUP BY p.person_id, p.full_name, p.first_name, p.last_name, p.archived
ORDER BY p.last_name, p.first_name;

Tips for Report Building

Performance: For large datasets, consider creating materialized views for complex calculations that don’t need real-time updates.

Visualization: These queries return data optimized for charts. Use the categorical fields for grouping and numerical fields for metrics.

Privacy: Always respect privacy when sharing reports. Consider removing personally identifiable information for broad distribution.

Next Steps

Import these queries into your BI tool of choice
Set up automated refresh schedules for dashboards
Create drill-down capabilities from summary to detail views
Combine with other Planning Center modules for comprehensive insights
Export key metrics for leadership meetings and planning sessions

​Services Reporting Examples

Ready for Production Use

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Volunteer Management Dashboard

​Complete Volunteer Overview

​Team Roster Report

​Worship Planning Analytics

​Song Usage Report

​Service Flow Analysis

​Ministry Effectiveness Metrics

​Volunteer Engagement Score

​Team Health Dashboard

​Executive Summary Report

​Ministry Overview Dashboard

​Export-Ready Formats

​CSV Export for Volunteer Contact

​Tips for Report Building

​Next Steps

Services Reporting Examples

Query Requirements

Schema Prefix

Row Level Security (RLS)

Volunteer Management Dashboard

Complete Volunteer Overview

Team Roster Report

Worship Planning Analytics

Song Usage Report

Service Flow Analysis

Ministry Effectiveness Metrics

Volunteer Engagement Score

Team Health Dashboard

Executive Summary Report

Ministry Overview Dashboard

Export-Ready Formats

CSV Export for Volunteer Contact

Tips for Report Building

Next Steps