| Profit/Margin |
(([Extended_Amount] – [Discount_Amount]) – ([Extended_Product_Cost] – [Rebate_Amount]))
|
| Extended Net Amount |
[Extended_Amount] – [Discount_Amount]
|
| Net Sales |
[oeel_invoicedt: Extended Net Amount]
|
|
[ExtAmtByDiscPct]=[OEEL.price] * [OEEL.qtyship]*(100-[OEEL.discpct])* [ICSS.csunperstk]*([OEEH.salesexratee](If any))
|
| Fill Rate Order |
IF([oeeh_invoicedt: # orderno] is null or [oeeh_invoicedt: # orderno] = 0 or [oeeh_invoicedt: # OEEH_shipcom_flag] is null,0, (([oeeh_invoicedt: # OEEH_shipcom_flag] where [OEEH.OEEH_shipcom_flag] ='Yes') / ([oeeh_invoicedt: # orderno])) * 100)
|
| Fill Rate Line |
IF([oeel_invoicedt: # lineno] is null or [oeel_invoicedt: # lineno] = 0 or [oeel_invoicedt: Sum: oeel_shipcomplete_Boolean] is null,0,([oeel_invoicedt: Sum: oeel_shipcomplete_Boolean]/[oeel_invoicedt: # lineno])*100)
|
| Fill Rate Unit |
IF([oeel_invoicedt: Sum: oeel_qtyship] is null or [oeel_invoicedt: Sum: oeel_qtyship] =0 or [oeel_invoicedt: Sum: oeel_qtyord] = 0 or [oeel_invoicedt: Sum: oeel_qtyord] is null,0,([oeel_invoicedt: Sum: oeel_qtyship]/[oeel_invoicedt: Sum: oeel_qtyord])*100)
|
| On Time |
IF([OEEH.oeeh_shipdt] is null or [OEEL.oeel_origpromisedt] is null,1,IIF( DATEDIFF(DAY,[OEEH.oeeh_shipdt],[OEEL.oeel_origpromisedt]) = 0,1,0))
|
| On Time 2 Days |
IF([OEEH.oeeh_shipdt] is null or [OEEL.oeel_origpromisedt] is null,1,IIF( DATEDIFF(DAY,[OEEH.oeeh_shipdt],[OEEL.oeel_origpromisedt]) > 2 or DATEDIFF(DAY,[OEEH.oeeh_shipdt],[OEEL.oeel_origpromisedt]) < -2,0,1))
|
| On Time 5 Days |
IF((IFNULL([OEEL.oeel_qtyord],0.00)-IFNULL([OEEL.oeel_qtyship],0.00)) > 0,0,1 )
|
| On Time Orders Perc |
IF([oeeh_invoicedt: Sum: oeeh_ontime] is Null or [oeeh_invoicedt: # orderno] is null or [oeeh_invoicedt: # orderno] =0 ,0,([oeeh_invoicedt: Sum: oeeh_ontime] / [oeeh_invoicedt: # orderno])*100 )
|
| On Time Orders 2 Days Perc |
IF([oeeh_invoicedt: Sum: oeeh_ontime_2days] is Null or [oeeh_invoicedt: # orderno] is null or [oeeh_invoicedt: # orderno] =0 ,0,([oeeh_invoicedt: Sum: oeeh_ontime_2days] / [oeeh_invoicedt: # orderno])*100 )
|
| OE On Time Orders 5 Days Perc |
IF([oeeh_invoicedt: Sum: oeeh_ontime_5days] is Null or [oeeh_invoicedt: # orderno] is null or [oeeh_invoicedt: # orderno] =0 ,0,([oeeh_invoicedt: Sum: oeeh_ontime_5days] / [oeeh_invoicedt: # orderno])*100 )
|
| On Time Lines Perc |
IF([oeel_invoicedt: Sum: oeel_ontime] is Null or [oeel_invoicedt: # lineno] is null or [oeel_invoicedt: # lineno] =0 ,0,([oeel_invoicedt: Sum: oeel_ontime] / [oeel_invoicedt: # lineno])*100 )
|
| On Time Lines 5 Days Perc |
IF([oeel_invoicedt: Sum: oeel_ontime_5days] is Null or [oeel_invoicedt: # lineno] is null or [oeel_invoicedt: # lineno] =0 ,0,([oeel_invoicedt: Sum: oeel_ontime_5days] / [oeel_invoicedt: # lineno])*100 )
|
| On Time Lines 2 Days Perc |
IF([oeel_invoicedt: Sum: oeel_ontime_2days] is Null or [oeel_invoicedt: # lineno] is null or [oeel_invoicedt: # lineno] =0 ,0,([oeel_invoicedt: Sum: oeel_ontime_2days] / [oeel_invoicedt: # lineno])*100 )([aret_invdt: Sum: aret_Open Amount] where ([ARET.aret_transcode_description] = 'Scheduled Pay' or [ARET.aret_transcode_description] = 'Debit Memo' or [ARET.aret_transcode_description] = 'Credit Memo' or
|
| OE Line Commission Profit |
([aret_invdt: Sum: aret_Open Amount(([Extended_Amount] – [Discount_Amount]) – ([Extended_Commission_Cost] – [Rebate_Amount]))
|
| Margin % |
IF([oeel_invoicedt: Extended Net Amount] IS NULL OR [oeel_invoicedt: Extended Net Amount] = 0,0,(((IFNULL([oeel_invoicedt: Sum: Extended_Amount],0.00)-IFNULL([oeel_invoicedt: Sum: Discount_Amount],0.00)) - (IFNULL([oeel_invoicedt: Sum: Extended_Product_Cost],0.00) - IFNULL([oeel_invoicedt: Sum: Rebate_Amount],0.00))) / [oeel_invoicedt: Extended Net Amount])*100)
|
| Rebate Amount |
IIF([PDER.pder_seqno] <> 0 AND [PDER.pder_rebatecd] <> 's',0,IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_ speccostty] is null,((IFNULL([OEEL.oeel_qtyship] ,0.00) * IFNULL([PDER.pder_rebateamt],0.00)) * -1), IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] is null,(IFNULL([OEEL.oeel_qtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0,((IFNULL( [OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder _rebateamt],0.00))* ([ICSS.icss_csunperstk] / 100)) ,
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,((IFNULL( [OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder _rebateamt],0.00))* ( 1 / 100)) ,
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0, (((IFNULL( [OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER .pder_rebateamt],0.00))* ([ICSS.icss_csunperstk] / 100)) * -1),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00)) *(1/100))*-1),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00))* ([ICSS.icss_csunperstk] / 1000)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00))* ( 1 / 1000)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0, (((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00))* ([ICSS.icss_csunperstk] / 1000)) * -1),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00)) *(1/1000))*-1),
|
|
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00))* ([ICSS.icss_csunperstk])),
|
|
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,(IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00))*([ICSS.icss_csunperstk])) *-1) ,
|
|
IIF(([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00) *IFNULL([PDER.pder_rebateamt],0.00))*-1), IIF([ICSS.icss_csunperstk] IS NULL, 0, 0))))))))))))))))
|
| Extended Product Cost |
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_ csunperstk] = 0, ((IFNULL([OEEL.oeel_stkqtyship],0.00) *IFNULL([OEEL.oeel_prodcost],0.00))*-1),
|
|
IIF(([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS. icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL. oeel_stkqtyship],0.00)* IFNULL([OEEL.oeel_prodcost],0.00))*([ICSS. icss_csunperstk]*-1)),
|
|
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,(IFNULL([OEEL.oeel_stkqtyship] ,0.00)*IFNULL([OEEL.oeel_prodcost],0.00)),
|
|
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00)) * ([ICSS.icss_csunperstk])),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] is null,((IFNULL( [OEEL.oeel_qtyship],0.00)*IFNULL([OEEL. oeel_prodcost],0.00))*-1),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] is null,(IFNULL([OEEL.oeel_qtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0,((IFNULL( [OEEL.oeel_stkqtyship], 0.00)*IFNULL([OEEL.oeel_prodcost],0.00))/( [ICSS.icss_csunperstk]/100)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,((IFNULL( [OEEL.oeel_stkqtyship],0.00)*IFNULL([ OEEL.oeel_prodcost],0.00))*(1/100)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))*[ICSS.icss_csunperstk])/100) * -1,
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))*(1/100))*-1),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL. oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost] ,0.00))*[ICSS.icss_csunperstk])/1000),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))*(1/1000)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL. oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))* [ICSS.icss_csunperstk])/1000) * -1,
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))*(1/1000))*-1),
|
|
IIF([ICSS.icss_csunperstk] IS NULL, 0,0)))))))))))))))
|
| Extended Commission Cost |
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*-1) ,IIF(([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'Y' and [ICSS .icss_csunperstk] > 0,((IFNULL([OEEL.oeel_ stkqtyship],0.00)*IFNULL([OEEL.oeel_commco st],0.00))*([ICSS.icss_csunperstk]*-1)),
|
|
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,(IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00)),
|
|
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00)) * ([ICSS.icss_csunperstk])),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] is null,((IFNULL([OEEL.oeel_qtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*-1),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] is null,(IFNULL([OEEL.oeel_qtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)* IFNULL([OEEL.oeel_commcost],0.00))/([ICSS. icss_csunperstk]/100)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*(1/100)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*[ICSS.icss_csunperstk])/100) * -1,
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*(1/100))*-1),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*[ICSS.icss_csunperstk])/1000),
|
|
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)* IFNULL([OEEL.oeel_commcost],0.00))*(1/1000)),
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*[ICSS.icss_csunperstk])/1000) * -1,
|
|
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00) *IFNULL([OEEL.oeel_commcost],0.00))*(1/1000)) *-1),
|
|
IIF([ICSS.icss_csunperstk] IS NULL, 0,0)))))))))))))))
|
| OE Line Commission Margin |
((([oeel_invoicedt: Sum: Extended_Amount]-[oeel_invoicedt: Sum: Discount_Amount]) - ([oeel_invoicedt: Sum: Extended_Commission_Cost] - [oeel_invoicedt: Sum: Rebate_Amount])) / [oeel_invoicedt: Sum: Extended_Amount])
|
| Commission Margin % |
((([oeel_invoicedt: Sum: Extended_Amount]-[oeel_invoicedt: Sum: Discount_Amount]) - ([oeel_invoicedt: Sum: Extended_Commission_Cost] - [oeel_invoicedt: Sum: Rebate_Amount])) / [oeel_invoicedt: Sum: Extended_Amount])*100
|
| Commission Amount |
[oeel_commamtout]
|
| Commission Cost |
[Rebate Commission Cost]
|
| Discount Amount |
If ([OEEL.oeel_returnfl] = 'yes', ([OEEL.oeel_wodiscamt] + [OEEL.oeel_discamtoth]) * -1,([OEEL.oeel_wodiscamt] + [OEEL.oeel_discamtoth]))
|
| Extended Amount |
|
| Rebate Cost |
[Extended_Product_Cost] - [Rebate_Amount]If ([OEEL.oeel_returnfl]='yes', IFNULL([OEEL.oeel_netamt],0.00) * -1,IFNULL([OEEL.oeel_netamt],0.00))
|
| Rebate Commission Cost |
[Extended_Commission_Cost] - [Rebate_Amount]
|
| Number of Orders |
Count of Total orders (#orderno)
|
| Total Sales |
SUM (Oeeh_totinvamt) By Oeeh_invoicedt
|
| Total Profit |
(IFNULL([oeel_invoicedt: Sum: Extended_Amount],0.0) - IFNULL(SavedExpression('Extended product cost'),0.0))/IFNULL([oeel_invoicedt: Sum: Extended_Amount],1.0)
|
| Gross Margin |
(([Extended_Amount] – [Discount_Amount]) – ([Extended_Product_Cost] – [Rebate_Amount]))
|
| Gross Margin % |
((([oeel_invoicedt: Sum: Extended_Amount] – [oeel_invoicedt: Sum: Discount_Amount]) – ([oeel_invoicedt: Sum: Extended_Product_Cost] – [oeel_invoicedt: Sum: Rebate_Amount])) / [oeel_invoicedt: Sum: Extended_Amount])*100
|
| Cost |
[Rebate Cost]
|
| Line Extended Margin by Discount Percentage |
[oeel_line_extended_margin]=([ExtAmtByDiscPct]-[DiscAmt])-([PRODCOST]-[RebAmt])
|
| Extended Sales by Discount Percentage |
[oeel_extended_sales_by_discpct]=([ExtAmtByDiscPct]-[DiscAmt])
|