BizTalk Mapper – Keyed Cumulative Sum

From time to time I get questions about mapping puzzles, and I love it! Send me more of those! (I realize might regret that statement later). This time the puzzle was how best to do a conditional cumulative sum, or keyed conditional sum, also possibly known as grouped conditional sum.

The scenario is this (I’ve removed some namespaces etc for readability):

<Compensation>
  <rows>
    <row>
      <Compensation_Code>1</Compensation_Code>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>1</Compensation_Level>
      <Compensation_Id>1113</Compensation_Id>
      <Compensation_Days>41856.50</Compensation_Days>
      <Compensation_Amount>25288084</Compensation_Amount>
      <Compensation_Tax>6690289</Compensation_Tax>
    </row>
  <row>
      <Compensation_Code>1</Compensation_Code>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>1</Compensation_Level>
      <Compensation_Id>1113</Compensation_Id>
      <Compensation_Days>41856.50</Compensation_Days>
      <Compensation_Amount>29627</Compensation_Amount>
      <Compensation_Tax>6690289</Compensation_Tax>
    </row>
    <row>
      <Compensation_Code>1</Compensation_Code>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>2</Compensation_Level>
      <Compensation_Id>1113</Compensation_Id>
      <Compensation_Days>41856.50</Compensation_Days>
      <Compensation_Amount>234348</Compensation_Amount>
      <Compensation_Tax>6690289</Compensation_Tax>
    </row>
  </rows>
</Compensation>

How do you best produce a map that as step one summarize all Compensation_Amount for each variation of Compensation_SubCode, and returns this (let’s call this scenario 1):

<Compensation>
  <rows>
    <row>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Amount>25552059</Compensation_Amount>
    </row>
  </rows>
</Compensation>

and as step two, summarize all Compensation_Amount for each combination of Compensation_SubCode and Compensation_Level, and return this (let’s call this scenario 2):

<Compensation>
  <rows>
    <row>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>1</Compensation_Level>
      <Compensation_Amount>25317711</Compensation_Amount>
    </row>
    <row>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>2</Compensation_Level>
      <Compensation_Amount>234348</Compensation_Amount>
    </row>
  </rows>
</Compensation>

You might start thinking custom xslt and xsl:sort or xsl:keys or muenchian methods and things like that. That’s one way to do it. I actually prefer using the mapper for most maps until performance forces me to go another way.

My solution for scenario 1:

My Solution for scenario 2:

I am not presenting a complete solution for scenario 1, since it’s really just a subset of the functionality required for scenario 2, but you should get the point from the explanation of scenario 2.

The magic of the scenario 2 solution happens in the three scripting functoids. The first one, connected to the destination root node contains the following snippet (again namespace deprived for readability):

List<string> keyList= new List<string>();
void initglobals() {}

It’s simply responsible for making sure that the variable keyList is set up. The empty method that return void ensures that I can connect this to an output node to make sure it triggers, yet it will produce no output.

The next functoid then makes use of that list, and adds all unique keys to it (which is unique combinations of the two keys separated by a secure character, in this case a colon):

public string AddKey(string key)
{
  if (keyList.Contains(key))
    return string.Empty;
  keyList.Add(key);
  return key;
}

The Not Equals functoid then makes sure that we will only see output when a new unique combination is found, by checking to make sure that the string outputted from the scripting functoids <> and empty string.

The third functoid then is responsible for doing the summation (I’ve inserted a linebreak and some spaces in the xpath statement for readability, so don’t just copy paste this):

<xsl:template name="OutputSum">
  <xsl:param name="param1" />
  <xsl:param name="param2" />
  <xsl:element name="Compensation_Amount">
  <xsl:value-of select="sum(//row[Compensation_SubCode=$param1 
              and Compensation_Level=$param2]/Compensation_Amount)" />
  </xsl:element>
</xsl:template>

This simple xslt call template takes the two key values in, and does a summation of all rows that has these key values set to these specific values.

Now someone might object and say that this would mean an unnecessary amount of summation gets done. But that’s actually not the case, the mapper is clever enough to only do this under the condition of the Not Equals functoid returning true, as the following xslt snippet shows (this is just a portion of the xslt generated):

<xsl:variable name="var:v3" select="userCSharp:LogicalNe(string($var:v2) , &quot;&quot;)" />
<xsl:if test="$var:v3">
  <row>
    ...
    <xsl:call-template name="OutputSum">
    <xsl:with-param name="param1" select="string(Compensation_SubCode/text())" />
    <xsl:with-param name="param2" select="string(Compensation_Level/text())" />
    </xsl:call-template>
  </row>
</xsl:if>

’So a short summation (pun intended) of the work involved:

1. Create a global variable, a list to hold keys

2. Add to that list once you discover a new key

3. Add logic to that discovery to only output your destination node when you hit a new key

4. Implement a xslt call template and use xpath to sum based on your keys