The Vehicle Sales Detail calculated insight aggregates the direct revenue, dealer revenue, parts revenue, and service revenue for each customer and vehicle combination. This calculated insight aggregates information from the Vehicle Revenue from Dealer Sales, Vehicle Revenue from Direct Sales, Vehicle Sales from Parts Sales, and Vehicle Sales from Repair Order insights. The result is grouped by customer and vehicle.
Required Editions
| Available in: Enterprise, Unlimited, and Developer Editions |
Her
| Expression | Description |
|---|---|
SELECT
ssot__Vehicle__dlm.ssot__VehicleIdentificationNumber__c as VIN__c ,
ssot__Vehicle__dlm.ssot__CurrentOwnerId__c as CustomerId__c
|
Extracts the Vehicle Identification Number from the Vehicle object and assigns it
an alias VIN__c. Extracts the Current Owner ID
from the Vehicle object and assigns it an alias CustomerId__c. |
SUM( CASE when
isnull(Vehicle_Revenue_From_Dealer_Sales__cio.DealerAmount__c) then 0 else
Vehicle_Revenue_From_Dealer_Sales__cio.DealerAmount__c end ) as
DealerSalesAmount__c
|
Calculates the total dealer sales amount by summing up the DealerAmount__c value from the Vehicle Revenue From Dealer Sales calculated insight and assigns the value zero if the result is null. |
SUM( CASE when
isnull(Vehicle_Revenue_From_Direct_Sales__cio.DirectAmount__c) then 0 else
Vehicle_Revenue_From_Direct_Sales__cio.DirectAmount__c end ) as
DirectSalesAmount__c
|
Calculates the total direct sales amount by summing up the DirectAmount__c value from the Vehicle Revenue From Direct Sales calculated insight and assigns the value zero if the result is null. |
SUM( CASE when isnull(
Vehicle_Revenue_From_Parts_Sales__cio.PartsAmt__c) then 0 else
Vehicle_Revenue_From_Parts_Sales__cio.PartsAmt__c end ) as
PartsTxnAmount__c
|
Calculates the total parts sales amount by summing up the PartsAmount__c value from the Vehicle Revenue From Parts Sales calculated insight and assigns the value zero if the result is null. |
SUM( CASE when
isnull(Vehicle_Revenue_from_Repair_Orders__cio.RepairAmount__c) then 0 else
Vehicle_Revenue_from_Repair_Orders__cio.RepairAmount__c end ) as
RepairOrderAmount__c
|
Calculates the total dealer sales amount by summing up the RepairAmount__c value from the Vehicle Revenue From Repair Orders calculated insight and assigns the value zero if the result is null. |
FROM ssot__Vehicle__dlm LEFT JOIN
Vehicle_Revenue_From_Dealer_Sales__cio ON
(ssot__Vehicle__dlm.ssot__VehicleIdentificationNumber__c =
Vehicle_Revenue_From_Dealer_Sales__cio.VIN__c) LEFT JOIN
Vehicle_Revenue_From_Direct_Sales__cio ON
(ssot__Vehicle__dlm.ssot__VehicleIdentificationNumber__c =
Vehicle_Revenue_From_Direct_Sales__cio.VIN__c) LEFT JOIN
Vehicle_Revenue_From_Parts_Sales__cio ON
(ssot__Vehicle__dlm.ssot__VehicleIdentificationNumber__c =
Vehicle_Revenue_From_Parts_Sales__cio.VIN__c) LEFT JOIN
Vehicle_Revenue_from_Repair_Orders__cio ON
(ssot__Vehicle__dlm.ssot__VehicleIdentificationNumber__c =
Vehicle_Revenue_from_Repair_Orders__cio.VIN__c)
|
Joins the Vehicle object to the child calculated insights by matching the vehicle identification number. |
GROUP BY VIN__c, CustomerId__c
|
Groups the results by VIN__c, and CustomerId__c aggregating the total sales by each
unique vehicle and customer. |