Advanced Reporting Techniques
Complex Calculated Fields
Statistical Calculations
- Average Deal Size: SUM([Revenue]) / COUNT([Opportunity ID])
- Win Rate: COUNT(IF [Status] = 'Won') / COUNT([Opportunity ID]) × 100
- Sales Cycle: AVG([Close Date] - [Created Date])
Conditional Logic
Deal Category =
IF [Revenue] > 100000 THEN "Enterprise"
ELSE IF [Revenue] > 50000 THEN "Mid-Market"
ELSE IF [Revenue] > 10000 THEN "SMB"
ELSE "Small"
Date Calculations
- Days Overdue: IF [Close Date] < TODAY() THEN TODAY() - [Close Date] ELSE 0
- Quarter: "Q" + QUARTER([Close Date]) + " " + YEAR([Close Date]) → "Q1 2024"
- Age Buckets:
- 0-30 days: New
- 31-90 days: Warm
- 91+ days: Stale
Multi-Table Joins
Join Types
- Inner Join: Show only opportunities WITH products (opportunities that have product line items)
- Left Outer Join: Show ALL opportunities, even if no products (displays NULL for product fields)
- Example: Opportunity (parent) LEFT JOIN Product (child) ON Opty.Id = Product.OpportunityId
Use Case: Opportunity + Contact Report
Requirement: Show opportunities with decision-maker contact info
- Join: Opportunity BC → Contact BC (via Account)
- Fields: Opty Name, Revenue, Contact Name, Contact Email, Contact Title
- Filter: Contact Role = "Decision Maker"
- Result: Each opportunity shows primary contact for follow-up
Sub-Queries & Aggregations
Sub-Query Example
Question: Show accounts with opportunities totaling > $500K
Main Query: Account BC
Sub-Query: SUM(Opportunity.Revenue) WHERE Opportunity.AccountId = Account.Id
Filter: Sub-Query Result > 500000
Result: List of high-value accounts
Nested Aggregations
- Average of Averages: AVG(Average Deal Size Per Rep)
- Top Performers: Reps where Personal Revenue > Team Average Revenue
Cross-Tab (Matrix) Reports
Display data in rows and columns with aggregated intersections.
Example: Revenue by Product Line × Territory
| Product Line | Northeast | Southeast | West | Total |
|---|---|---|---|---|
| 5G Plans | $2.1M | $1.8M | $3.5M | $7.4M |
| Devices | $1.5M | $1.2M | $2.3M | $5.0M |
| Enterprise Solutions | $3.2M | $2.7M | $4.1M | $10.0M |
| Total | $6.8M | $5.7M | $9.9M | $22.4M |
Dynamic Parameters
- Cascading Prompts: User selects Territory → Product dropdown shows only products sold in that territory
- Default Values: Date Range defaults to "Current Quarter" but user can override
- Optional Prompts: User can leave "Rep" blank to see all reps, or select specific rep to filter
Performance Tuning
Optimization Techniques:
- Index Usage: Ensure filters use indexed columns (Close Date, Owner, Status)
- Limit Rows: Add "TOP 1000" to avoid loading 1M records
- Summary First: Show summary data by default, drill-down loads details on demand
- Scheduled Pre-Run: Complex reports run overnight, users view cached results
- Avoid Wildcards: "Name LIKE 'Smith%'" (fast) vs. "Name LIKE '%Smith%'" (slow, full table scan)
Example: Cohort Analysis Report
Requirement: Track customer retention by signup month
- Cohort: Group customers by Account Created Month (Jan 2024, Feb 2024, etc.)
- Metrics: % still active after 3 months, 6 months, 12 months
- Calculation:
- Jan 2024 Cohort: 100 customers signed up
- After 3 months (Apr 2024): 85 still active (85% retention)
- After 6 months (Jul 2024): 72 still active (72% retention)
- Visualization: Line chart showing retention curves per cohort