Grouped and Banded Reports

Export your Salesforce, SAP, or Oracle banded reports directly - Scoop automatically detects grouping levels, validates subtotals, and separates summary rows from detail data. No manual cleanup required.

What Are Grouped/Banded Reports?

Grouped reports (also called "banded reports") organize data into hierarchical groups with subtotals. They're common in enterprise reporting:

Example: Sales by Region and Product

Region: West
  Product: Widgets     $10,000
  Product: Gadgets     $15,000
  Subtotal West:       $25,000

Region: East
  Product: Widgets     $12,000
  Product: Gadgets     $18,000
  Subtotal East:       $30,000

Grand Total:           $55,000

The challenge: You want to analyze the detail rows (individual products), but the subtotals and grand totals get in the way.

How Scoop Handles Grouped Reports

When you upload a grouped report, Scoop automatically:

1. Detects Grouping Columns - Identifies which columns define groups (e.g., Region)
2. Finds Subtotal Rows - Recognizes rows containing "Subtotal", "Total", "Sub-Total"
3. Validates Subtotals - Checks that subtotals actually sum correctly
4. Identifies Aggregation Rules - Detects SUM, AVG, COUNT, MIN, MAX per column
5. Separates Data - Gives you clean detail rows for analysis
6. Preserves Grouping Metadata - Enables drill-down by group

Supported Report Types

Quick Start

Step 1: Export Your Report

Export your grouped report as CSV, Excel, or HTML. Include all rows (details + subtotals).

Salesforce Example:

1. Run your Summary or Matrix report
2. Click Export > Details Only or Formatted Report
3. Choose CSV or Excel format

Step 2: Upload to Scoop

1. Navigate to Sources > New Dataset
2. Upload your file
3. Scoop automatically detects grouping structure

Step 3: Review Detection Results

Scoop shows you:

• Which columns are grouping columns
• How many grouping levels were detected
• Which rows were identified as subtotals
• Aggregation rules per column

Step 4: Analyze Clean Data

Your dataset now contains:

• Detail rows only - subtotals filtered out
• Grouping metadata - for drill-down analysis
• Correct totals - aggregations work properly

What Gets Detected

Grouping Levels

Scoop identifies multi-level groupings automatically:

Subtotal Patterns

Scoop recognizes rows matching these patterns:

• "Subtotal", "Sub-Total", "Sub Total"
• "Total", "Grand Total"
• Rows following group boundary changes
• Rows with aggregated values matching detail sums

Aggregation Rules

For each numeric column, Scoop detects:

• SUM - Column totals add up
• AVG - Column shows average
• COUNT - Column shows row count
• MIN/MAX - Column shows extremes
• Non-additive - Column doesn't aggregate (marked separately)

Advanced: How Detection Works

Scoop uses a sophisticated algorithm (BandedReportValidator):

1. Scan for Group Boundaries

   • Find columns where values change when subtotals appear
   • Track the "grouping level" (0 = innermost, higher = outer)

2. Identify Subtotal Rows

   • Pattern match: rows containing subtotal keywords
   • Position analysis: rows following group changes
   • Value validation: numeric columns that sum correctly

3. Validate Aggregations

   • Compare subtotal values to detail row sums
   • Detect aggregation type per column
   • Flag non-additive columns

4. Handle Grand Totals

   • Detect at report start or end
   • Validate: grand total = sum of subtotals
   • Handle non-additive grand totals gracefully

Best Practices

For Reliable Detection

1. Include All Rows - Don't pre-filter subtotals before upload
2. Use Consistent Labels - "Subtotal" works better than "ST" or custom labels
3. Maintain Structure - Keep the original report layout
4. Include Headers - Column names help Scoop understand the data

When Detection Fails

If Scoop doesn't detect grouping correctly:

1. Check subtotal labels - Use standard terms like "Subtotal" or "Total"
2. Verify numeric consistency - Subtotals should actually sum to correct values
3. Simplify grouping - Very complex multi-level reports may need simplification
4. Export as "Details Only" - Sometimes easier to skip subtotals at export

Examples

Salesforce Opportunity Report

Before (raw export):

Stage        Owner    Amount
Prospecting  Alice    $10,000
Prospecting  Bob      $15,000
Subtotal Prospecting: $25,000
Negotiation  Alice    $50,000
Subtotal Negotiation: $50,000
Grand Total:          $75,000

After (Scoop processed):

Stage        Owner    Amount
Prospecting  Alice    $10,000
Prospecting  Bob      $15,000
Negotiation  Alice    $50,000

Grouping metadata preserved: You can still "group by Stage" in your analysis.

Excel Pivot Table Export

Scoop handles Excel pivot tables with subtotals the same way:

• Detects row and column groupings
• Removes subtotal rows
• Preserves the underlying data structure

Troubleshooting

Subtotals Not Detected

Problem: Scoop didn't identify subtotal rows.

Causes:

• Non-standard subtotal labels
• Subtotals that don't sum correctly
• Very sparse data with few subtotals

Solutions:

• Rename subtotal labels to standard terms
• Verify calculations in source report
• Use "Export Details Only" if available

Wrong Rows Marked as Subtotals

Problem: Detail rows are being filtered out.

Causes:

• Row content contains "total" or "subtotal" text
• Unusual data patterns match subtotal heuristics

Solutions:

• Review the detection results in Scoop
• Adjust grouping settings if available
• Pre-clean data that contains misleading text

Missing Grouping Levels

Problem: Scoop detected fewer levels than expected.

Causes:

• Outer grouping columns have inconsistent values
• Very few rows per group

Solutions:

• Ensure consistent values in grouping columns
• Include more data if possible

Related Resources

• Importing HTML Reports - For BI tool exports
• Email Automated Imports - Automate report ingestion
• Best Practices for Source Reports