# Grouped and Banded Reports

Automatically detect and handle reports with subtotals, group breaks, and summary rows

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

| Source | Report Type | Notes |
|--------|-------------|-------|
| **Salesforce** | Summary Reports, Matrix Reports | Explicitly optimized |
| **SAP** | ALV Reports, Crystal Reports | Multi-level grouping |
| **Oracle** | BI Publisher, Oracle Reports | Banded layouts |
| **Excel** | Pivot Tables with subtotals | Auto-detection |
| **Any CSV/Excel** | Reports with subtotal rows | Pattern-based detection |

## 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:

| Level | Example Column | Behavior |
|-------|----------------|----------|
| Level 0 | Product (innermost) | Subtotal after each product group |
| Level 1 | Region | Subtotal after each region |
| Level 2 | Year (outermost) | Grand subtotal after each year |

### 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](importing-html-reports) - For BI tool exports
- [Email Automated Imports](email-automated-imports) - Automate report ingestion
- [Best Practices for Source Reports](best-practices-in-selecting-creating-source-reports)