Reporting, Data Analysis & Business Intelligence

Data Analysis Pivot Grid Engine

The Data Analysis engine in MRSware Desktop is a multi-dimensional pivot grid allowing instant slicing and dicing of agency financial performance across any combination of fields.

The 5 Magical Columns

Every Data Analysis view centers around 5 core financial metrics:

Metric Column	Description
1. Sales Dollars	Total invoiced dollar volume.
2. Cost Dollars	Cost of goods or factory baseline cost.
3. Gross Margin	Net margin dollar spread (Sales - Cost).
4. Commission Dollars	Total gross commission earned by the agency.
5. Commission Rate %	Effective commission yield percentage (Commission / Sales).

Customer Ranking & Ship-To Analysis

Run Top 20 Customer Ranking views by Principal, Product Line, or Territory. Utilize Ship-To analysis to track where products are physically delivered versus billed.

Share of Wallet OLAP Cube & Whitespace Discovery

While traditional sales reports only analyze invoiced revenue, MRSware's dedicated Share of Wallet Analysis OLAP Cube (ShareOfWalletCube.cdl) bridges the gap between sales figures and estimated wallet spend potential.

1. Multi-Dimensional Cube Dimensions

Analyze wallet capture from every perspective in the Data Analysis pivot grid:

Dimension Hierarchy	Field / Attribute	Strategic Analytical Use
Customer Dimension	Customer Contact Name & ID	Identify top accounts by uncaptured dollar spend and overall wallet retention.
Product Category	Catalog Item Type	Pinpoint which product categories have low agency penetration across customer tiers.
Principal / Manufacturer	Manufacturer Contact Name	Compare actual factory billings against estimated addressable factory spend.
Sales Representative	Sales Rep Display Name	Evaluate rep performance based on account penetration percentage rather than raw volume alone.
Competitor Dimension	Incumbent Competitor Name	Map out competitor market share across territories to plan targeted displacement campaigns.
Geographic Breakdown	Business State & City	Discover regional whitespace pockets and underserved geographic territories.
Time Dimension	Invoice Year, Quarter & Month	Track year-over-year share expansion and quarterly penetration velocity.

2. Calculated Measures & Performance Indicators

• Actual Sales (Currency): Sum of invoiced sales revenue for the selected time period and dimension slice.
• Estimated Spend Potential (Currency): Aggregated wallet size defined on customer master profiles for that calendar year.
• Share of Wallet % (Calculated Summary): Evaluated dynamically in real-time as (Actual Sales / Estimated Spend Potential) * 100. Displays 0.0% when no sales exist.
• Uncaptured Spend (Calculated Summary): Computes Estimated Spend Potential - Actual Sales when spend potential exceeds revenue. Instantly prioritizes accounts by actionable dollar upside.

3. Share of Wallet Analysis SSRS Report

For executive committee meetings, manufacturer annual reviews, and rep quota discussions, MRSware provides a standard cross-module SQL Server Reporting Services (SSRS) report: Share of Wallet Analysis (Share of Wallet Analysis.rdl) accessible in Report Explorer:

• Parameter Filtering: Filter simultaneously by Year, Customer, Product Category, Principal, and Sales Rep.
• Executive KPI Cards: High-level KPI summaries for Total Estimated Wallet, Realized Agency Revenue, and Overall Wallet Share %.
• Drill-Down Matrix: Grouped tabular presentation by Customer, Category, and Principal with color-coded variance indicators.
• Universal Export: Export finalized presentations to PDF, Excel, or CSV with preserved formulas and styling.

Customer Retention & Attrition Analysis (OLAP Cube & Churn Analytics)

In wholesale distribution and manufacturers' representative operations, customer accounts rarely send formal cancellation notices. Instead, customer churn manifests quietly as lapsed purchase frequency or zeroed replenishment orders. MRSware Desktop provides automated Customer & Revenue Attrition Analysis designed specifically to monitor account retention, quantify lost sales volume, and flag dormant customers before relationships dissolve.

1. Dual Attrition Metrics: Account vs. Revenue Churn

Losing five low-volume accounts has a vastly different financial impact than losing a single flagship Tier-1 customer. MRSware measures both account counts and dollar volumes simultaneously:

2. Dedicated Data Analysis OLAP Cube ("Customer Retention & Attrition Analysis")

To pinpoint exactly where account attrition is occurring across product lines and sales territories, MRSware includes a dedicated pre-configured OLAP Cube: Customer Retention & Attrition Analysis (registered in DACubeDefinition). Rep agencies can drag and drop fields to slice retention metrics across any organizational hierarchy:

Cube Dimension	Field / Attribute	Analytical Value
Customer Dimension	Customer Contact Name & ID	Isolate specific accounts that generated revenue in the prior year but $0 in the current year.
Principal / Manufacturer	Manufacturer Contact Name	Identify manufacturer product lines experiencing customer drop-off versus expansion.
Sales Rep on Customer	Customer Sales Rep Name	Review account retention rates by assigned territory manager or agency account rep.
Sales Rep on Invoice	Invoice Sales Rep Name	Analyze churn by originating transaction sales rep split crediting.
Geographic Breakdown	Business State, City & Zip Code	Detect regional attrition trends or geographic territories experiencing competitive displacement.
Time Dimension	Invoice Year, Quarter & Month	Analyze retention seasonality and trailing purchase patterns leading up to account dormancy.

3. Calculated Summaries & Churn Indicators

• Prior Year Sales (Currency): Total invoiced baseline sales generated during the baseline comparison period.
• Current Year Sales (Currency): Total invoiced sales generated in the active period.
• Sales Variance ($) (Calculated Summary): Dollar difference (Current Year Sales - Prior Year Sales), clearly highlighting revenue gains in positive values and losses in negative values.
• % Change (Calculated Summary): Relative growth or contraction yield: ((Current Year Sales - Prior Year Sales) / Prior Year Sales) * 100.
• Lost Revenue ($) (Calculated Summary): Captures prior baseline dollars specifically for accounts that dropped to exactly $0 in the current period: (Prior Year Sales > 0 && Current Year Sales == 0) ? Prior Year Sales : 0.
• Churned Flag (1=Yes, 0=No): Integer binary measure (1 if Prior > 0 and Current == 0, else 0) allowing instant pivot aggregation of total lost accounts by sales rep, manufacturer, or state.

Line Profitability Analysis (True Net Margin & LPF Scorecard)

In multi-line sales rep agencies, gross sales volume and headline commission rates often mask whether a product line or customer account is actually profitable to represent. High-maintenance principals may demand excessive field sales time, extensive travel, or specialized technical support that erodes agency commissions. Conversely, lean lines with automated ordering may yield exceptionally high net margins despite lower top-line commission percentages.

MRSware Desktop's Line Profitability Analysis OLAP Cube (LineProfitabilityCube.cdl powered by LineProfitabilityData.edl) unites four operational streams into a unified profitability ledger: Invoiced Sales & Commission Splits, Activity Hours & Timer Logs, Direct Field Expenses, and Qualitative Line Performance Factor (LPF) Scorecards.

1. Core Cube Dimensions & Hierarchies

Cube Axis	Dimension Hierarchy	Dimension Table	Analytical Purpose
Rows	Principal (Manufacturer)	ManufacturerDimensionTable	Evaluate net agency profitability, labor burden, and margin by product line.
Rows	Customer	CustomerDimensionTable	Identify high-maintenance vs. high-yield customer accounts across represented lines.
Pages (Filter)	Sales Rep	SalesRepDimensionTable	Slice agency profitability and time allocation by assigned territory representative.
Columns	Activity Year > Quarter > Month	Calculated Date Hierarchy	Track quarterly profitability trends, seasonal expense spikes, and margin growth.

2. Summary Metrics & Calculated Performance Indicators

The cube computes granular baseline figures and dynamic calculated summaries in real-time:

Summary Measure	Type	Format	Calculation & Strategic Significance
Sales	Currency	$#,##0.00	Total invoiced sales volume generated for the product line or customer account.
Gross Commission	Currency	$#,##0.00	Total gross commission earned before rep splits.
Agency Commission	Currency	$#,##0.00	Retained agency commission after deducting sub-rep commission splits.
Rep Commission	Currency	$#,##0.00	Commission split payable to territory sales reps (optional/toggleable view).
Expenses	Currency	$#,##0.00	Direct travel, entertainment, and field expenses charged to the line or customer.
Hours	Float	#,##0.0	Cumulative labor hours recorded via stopwatch timers and journal activity logs.
Commission / Hr	Calculated Summary	$#,##0.00	Return on invested time: Gross Commission / Hours. Reveals high-return vs time-sink lines.
Net Profit / Hr	Calculated Summary	$#,##0.00	Net agency yield per invested hour: Net Profit / Hours.
Expense / Comm %	Calculated Summary	0.0%	Expense ratio: (Expenses / Gross Commission) * 100. Flags excessive travel or entertainment spend.
Commission Rate %	Calculated Summary	0.00%	Effective commission yield: (Gross Commission / Sales) * 100.
Net LPF Score	Calculated Summary	#,##0	Qualitative scorecard: LPF+ - LPF-. Aggregates factory ease-of-doing-business feedback.

3. The Line Performance Factor (LPF) Qualitative Scorecard

Financial numbers tell only part of the story. A product line may appear profitable on paper while causing significant operational friction through late shipments, pricing errors, poor customer service, or delayed commission disbursements. To balance hard financials with operational reality, MRSware incorporates the Line Performance Factor (LPF) framework:

• LPF+ Events (Positive Performance): Tag CRM Journal entries with categories starting with LPF+ (e.g., "LPF+ Fast Quote Turnaround", "LPF+ On-Time Shipments", "LPF+ Quality Marketing Leads"). Each occurrence increments the LPF+ counter.
• LPF- Events (Negative Friction): Tag CRM Journal entries with categories starting with LPF- (e.g., "LPF- Shipping Delay", "LPF- Commission Discrepancy", "LPF- Backordered Catalog SKU"). Each occurrence increments the LPF- counter.
• Net LPF Score (LPF+ - LPF-): In the pivot grid, the Net LPF Score provides an objective, empirical rating for annual manufacturer reviews, contract renewals, or principal contract negotiations.

Report Explorer & Saved Views

Generate standard financial statements, Class Master Lists, and Rep Commission Statements. Save custom Data Analysis pivot layouts for one-click re-opening.