08. Sales Report: Per Family/Model (Analytical)

Owned by Georgios Vasilakos (Unlicensed)
Apr 28, 2023
3 min read

Report Identity:

Code

ODPRODREPPERFMINFO

Name

Per Family Model

Filename

OneDealer_PerFamilyModel_EN_v1.4

Description

Sales Report: Per Family/Model (Analytical)

Business Case

30065

Resource Code

OD0001871

Parameters

Description

Field (U_IDMS_ParamName)

Provided by UI

Values

Default Values

Date from

PDateFrom

YES



Date to

PDateTo

YES



Make Code

PMakeCode

YES



Opportunity Type

POpportunityTypeCode

YES

  • All
  • Customers from Service
  • Fleet
  • Lease
  • Retail sale
  • Small Fleet


Group Type

PGroupType

YES

FAMILY, FAMILYMODEL


Branches

PListOfRangeBranches

YES



Show Model/Family Percentage

PShowModelFamilyPerc

YES



Show Family Totals

PShowFamilyTotals

YES




PShownUpCalcRule

NO

ACTUAL, 1PERLEADFIRST

ACTUAL


PTDCalcRule

NO

ACTUAL, 1PERLEADVOIDAY

ACTUAL

View Type

PRptViewType

NO

A, S

A

The above parameters should be found at the @IDSM_CR_PARAMETER table (filtered by U_IDMS_ReportCode).

  • PShownUpCalcRule (it defines the way used for counting “Sales Opp”):
    • “ACTUAL” - This takes the total number of existing “Sales Opp”
    • “1PERLEADFIRST” - This counts the first “Sale Opp” per Lead in the period and we assign it to the Family/Model of the Main VOI
  • PTDCalcRule (it defines the way used for counting “Test Drives”):
    • “ACTUAL” - This takes the total number of existing “Test Drives”
    • “1PERLEADVOIDAY”: This counts the first “Test Drive” per Lead and per day for the same VOI in the period
  • PRptViewType (it defines the way data presented at the Crystal Report)
    • “A” – Analytical (per Branch)
    • “S” – Summary (all Family)


Report Layout 

Report Calculation Logic 

Header (green section)

FieldDescriptionComments
Report NameSales Report: Per Family / Model (Analytical)Name of the report
MakeSelected from the parameters
Opportunity Type

Selected "Opportunity Type" from the parameters


Dealership (Range)Selected "Sales Area" from the parameters
PeriodDate from - Date to selected from the parameters
SourceSelected "Source" from the parameters or dash "-" if nothing is selected
ChannelSelected "Channel" from the parameters or dash "-" if nothing is selected

Body (red section)

Report Column

U_IDMS_EventTypeCode (values)

Workflow Transition/State

Description

Sales Opp

CurrentWeekSpontaneousCustomers 
(and/or CurrentWeekShownUpByPhone, CurrentWeekShownUpByEmail, CurrentWeekCustomersFromService)

** State IN → Initial Contact
% (Sales Opp)

(Sales Opp per Family / Sales Opp for the Branch) * 100

Offers

CurrentWeekOffers

* Transition IN → Offer
% (Offer/Sales Opp)

(Offers per Family / Sales Opp of the Branch ) * 100

Test Drives

CurrentWeekTestDrive

* Transition IN → Execute Test Drive & Receive Vehicle
% (Test Drive/Sales Opp)

(Test Drives per Family / Sales Opp of the Branch) * 100

Sales

CurrentWeekNewCarSales

* Transition IN → Sign Order

Registrations

CurrentWeekClassifications

* Transition IN → Delivery Completed
% (Registrations)

(Registrations per Family / Sales for the Branch) * 100

Cancelations

CurrentWeekCancelations

* Transition IN → Order Cancelled

Pend. Order

CurrentWeekPendingOrders

* Transition IN → Order

Effectiveness



(Sales per Family / Sales Opp per Family ) * 100

* Transition IN: the record that will be considered by the event, is created when the user selects the relevant transition and saves. 

** State IN: the record that will be considered by the event, is created when closing the activity of the STATE, during the selection of the next activity in the STATE (transition)

Description

The report Sales Report: Per Family/Model (Analytical) filters Leads/Opportunities “per Family” or per “Family/Model” according to the initial parameter provided:

  • UI parameters
    • Period (based on the Lead/Opportunity “Update Date”)
    • Make Code (according to user dimensions permissions)
    • Opportunity Type
    • Group Type (Family, Family/Model)
    • Branch (according to user dimensions permissions)
    • Show Model/ Family Percentage (Yes, No)
    • Show Family Totals (Yes, No)
  • Non-UI parameters
    • PShownUpCalcRule - ACTUAL
    • PTDCalcRule - ACTUAL
    • PRptViewType - A

and displays the summary of the “even types” as per below:

  • “Sales Opp” – it counts “Sales Opp” based on the parameter “PShownUpCalcRule” value
  • “Offers”
  • “Test Drives” – it counts “Test Drives” based on the parameter “PTDCalcRule” value
  • “Sales”
  • “Registrations”
  • “Cancelations”
  • “Pending Orders”
  • “Effectiveness” - it is considered by the ratio of “Sales” towards the “Sales Opp”

Fields considered from table "@IDMS_EVNTRESULT"

  • Dates: "U_IDMS_EventDate" field
  • EntityType: "U_IDMS_EntityType" field with the “IDMS_Lead” value set
  • Entity: "U_IDMS_EntityCode" field
  • MakeCode: "U_IDMS_Prop1Code" field
  • BranchCode: "U_IDMS_Prop2Code" field
  • CompanyCode: "U_IDMS_Prop3Code" field
  • Location: "U_IDMS_Prop5Code" field
  • EventType: "U_IDMS_EventTypeCode" field
  • VOI: "U_IDMS_VOICode"