Summarizing Excel Spreadsheet Columns

Situation:

I have a map that uses an Excel schema as the target.

Here are the column headers:

Retailer Store ID               Invoice Date       UPC       QTY        Unit Cost             Extended Cost      Retail Unit Price                Extended Retail                Gross Margin %                Book/Magazine

I need to add an extra record at the end that summarizes the highlighted columns above, so I created a clone of the target record  and used the sum function for each of those columns:

sum($Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Quantity_Lowest_Consumer_Units_Moved )

sum($Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Net_Cost )

sum($Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Extended_Cost_Amount )

sum($Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Retail_Price )

Those are all working properly, but I am stuck on how to sum the Extended Retail column because I am multiplying 2 input fields to get that value for each row:

$Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Retail_Price * $Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Quantity_Lowest_Consumer_Units_Moved

Solution:

You can use local variables within the Data Mapper to achieve your requirement. Please use the below steps:

1) On the root level of the target schema create a local variable like "varInitiateSum" and save local variable value as "set-context('Sum',0)". 

2) Create the below Local variables on record level of the target schema:

2.1. Create First local variable like "varqty" and save local variable value as "$Input_Case_2182_input/Root/Record/Quantity_Lowest_Consumer_Units_Moved".

2.2. Create Second local variable like "varretailprice" and save local variable value as "$Input_Case_2182_input/Root/Record/Retail_Price".

2.3. Create Third local variable like "varMul" and save local variable value as
"$varqty * $varretailprice"

2.4. Create Fourth local variable like "varGetSum" and save local variable value as
"get-context('Sum' , 0)".

2.5. Create Fifth local variable like "varSum" and save local variable value as
"set-context('Sum' , $varMul + $varGetSum )".

3) On the column "ExtendedRetail" of the Target Schema apply the below textual rule: "$Input_Case_2182_input/Root/Record/Quantity_Lowest_Consumer_Units_Moved * $Input_Case_2182_input/Root/Record/Retail_Price".

4) At last on the column "Extended Retail" in the clone of the Target Record Schema apply the textual rule: "get-context('Sum' , 0)" .