Filtering Data Dynamically in SAP CPI using XSLT Mapping.

Hello All,

Greetings for the Day!!

You may have encountered various scenarios where you configured more than three filters and created different paths yet performed the same task in all these paths. The first filter has the department equal to HR, while the others correspond to Finance, Sales, and Audit, respectively. Even though the data processing occurs in different paths, the same task is being executed in each path.

Complex Integration

As you can see above, the integration appears complex. The architect responsible for the integration would be able to debug it, but it might be a bit time-consuming for other architects to understand the process of the above integration. Validating all the integration steps, by reviewing each one and either removing or adding filter criteria, would also increase the complexity of the integration.

It will also impact the performance of the integration, as it involves multiple steps that need to be executed. This will subsequently increase the processing time of the integration. The API calls made by the above integration will raise the load on the SuccessFactors system due to multiple API calls being made for performing the same task.

Simplified Integration

Solution:

To reduce the complexity of integration and decrease the processing time, as well as to alleviate the load on the SuccessFactors API, we can utilize the ‘XSLT Mapping’ function within the integration. This function enables us to filter data, map fields, and execute necessary distinct functions.

What is XSLT Mapping?

XSLT (Extensible Stylesheet Language Transformations) mapping in SAP CPI refers to the process of using XSLT to transform and map data between different formats during integration scenarios. XSLT is a language used to transform XML documents into various formats.

In the context of SAP CPI, XSLT mapping allows you to:

1. Transform Data: Convert XML data from the source format to the target format using XSLT templates. This is particularly useful when you need to map data from one XML structure to another.
2. Perform Complex Mapping: XSLT provides a powerful way to perform complex transformations, conditional mapping, and data enrichment during integration.

Overall, XSLT mapping is a powerful tool in SAP CPI that enables you to manage complex data transformations and mappings in integration scenarios, easing seamless communication between different systems with varying data formats.

Methodology:

In this scenario, we are going to filter out cost centers and calculate the sum of course costs for all the users, storing it as Total Cost. Generally, most customers have multiple cost centers within their organization, and many new cost centers are created and removed from the system at regular intervals.

Ideally, it is not feasible to have multiple filters and add them manually when new cost centers are created or deleted post Go-Live.

We are going to use “group by” function which is a mechanism in XSLT mapping that allows you to group and aggregate data based on specific criteria. This is particularly useful when you’re dealing with XML data and need to perform operations on grouped data sets.

Here’s how the “group by” function works in XSLT within the context of SAP CPI:

1. Grouping Data : The “group by” function lets you group XML elements based on a common attribute or element value. This means that elements with the same value for the specified attribute or element are treated as a group.
2. Aggregating Data : Once the data is grouped, you can use aggregate functions (like sum, count, average, etc.) to perform calculations on the elements within each group. This allows you to calculate values for each group separately.
3. Output Transformation : After grouping and aggregating, you can define how the resulting grouped and aggregated data should be transformed into the desired output format.

Here’s a simplified example of how you might use the “group by” function in XSLT within SAP CPI:

<!-- Group employees by Cost Center -->
<xsl:for-each-group select="{Entity Name} /{Entity Name}" group-by="{Enter Field to Filter}">
                <CostCenter ><xsl:value-of select="current-grouping-key()" /></CostCenter>
<!-- Calculate the Total Cost within the Cost Center -->
                <Total_Cost><xsl:value-of select="sum(current-group()/{Enter Field to Sum})"/></Total_Cost>

Suppose you have the below input XML data having Cost Center Details:

Input XML :
<CostDetails>
    <CostDetails>
        <externalCode>1982</externalCode>
        <cust_costcenter>3748</cust_costcenter>
        <course_cost>960</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1984</externalCode>
        <cust_costcenter>3748</cust_costcenter>
        <course_cost>850</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1986</externalCode>
        <cust_costcenter>3746</cust_costcenter>
        <course_cost>740</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1988</externalCode>
        <cust_costcenter>3746</cust_costcenter>
        <course_cost>630</course_cost>
    </CostDetails>
</CostDetails>

If you want to group the cost centers and calculate the cost of the course for each user, you could use the following XSLT code for your requirements.

Code :
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
        <output>
            <xsl:for-each-group select="CostDetails/CostDetails " group-by="cust_costcenter">
                <Center><xsl:value-of select="current-grouping-key()"/></Center>
                <Total_Cost><xsl:value-of select="sum(current-group()/course_cost)"/></Total_Cost>
                <employees>
                    <xsl:for-each select="current-group()">
                        <employee>
                            <userId><xsl:value-of select="externalCode "/></userId>
                            <cost><xsl:value-of select="course_cost"/></cost>
                            <costcenter><xsl:value-of select="cust_costcenter"/></costcenter>
                        </employee>
                    </xsl:for-each>
                </employees>
            </xsl:for-each-group>
        </output>
    </xsl:template>
</xsl:stylesheet>	

The response payload below has filtered 2 cost centers, calculated the cost of the course, and provided us with the required output.

Output XML :
<?xml version="1.0" encoding="UTF-8"?>
<output>
    <Center>3748</Center>
    <Total_Cost>1810</Total_Cost>
    <employees>
        <employee>
            <userId>1982</userId>
            <cost>960</cost>
            <costcenter>3748</costcenter>  
        </employee>
        <employee>
            <userId>1984</userId>
            <cost>850</cost>
            <costcenter>3748</costcenter>   
        </employee>
    </employees>
    <Center>3746</Center>
    <Total_Cost>1370</Total_Cost>
    <employees>
        <employee>
            <userId>1986</userId>
            <cost>740</cost>
            <costcenter>3746</costcenter>   
        </employee>
        <employee>
            <userId>1988</userId>
            <cost>630</cost>
            <costcenter>3746</costcenter>
        </employee>
    </employees>
</output>

Scenario 2:

If you want to apply a filter within another filter, you can perform a ‘group by’ operation on both the cost center and status in XSLT mapping. To achieve this, you can utilize nested ‘<xsl:for-each-group>’ elements for each level of grouping.

<!-- Within each Cost Center, group by Status -->
          <xsl:for-each-group select="current-group()" group-by="{Enter Field to Filter}">
                    <Status><xsl:value-of select="current-grouping-key()" /></Status>

Suppose you have the below input XML data having Cost Center Details and Status:

Input XML :
<CostDetails>
    <CostDetails>
        <externalCode>1982</externalCode>
        <course_status>Active</course_status>
        <cust_costcenter>3748</cust_costcenter>
        <course_cost>960</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1984</externalCode>
        <course_status>Active</course_status>
        <cust_costcenter>3748</cust_costcenter>
        <course_cost>550</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1981</externalCode>
        <course_status>Completed</course_status>
        <cust_costcenter>3748</cust_costcenter>
        <course_cost>950</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1983</externalCode>
        <course_status>Completed</course_status>
        <cust_costcenter>3748</cust_costcenter>
        <course_cost>250</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1986</externalCode>
        <course_status>Completed</course_status>
        <cust_costcenter>3746</cust_costcenter>
        <course_cost>740</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1988</externalCode>
        <course_status>Completed</course_status>
        <cust_costcenter>3746</cust_costcenter>
        <course_cost>630</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1987</externalCode>
        <course_status>Active</course_status>
        <cust_costcenter>3746</cust_costcenter>
        <course_cost>330</course_cost>
    </CostDetails>
    <CostDetails>
        <externalCode>1989</externalCode>
        <course_status>Active</course_status>
        <cust_costcenter>3746</cust_costcenter>
        <course_cost>430</course_cost>
    </CostDetails>
</CostDetails>

The XSLT code will group employees first by cost center and then within each cost center, group them by status. It calculates the total cost for each status within the cost center and outputs a structured XML accordingly.

Code :
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
        <output>
            <xsl:for-each-group select="CostDetails/CostDetails " group-by="cust_costcenter">
                <Center><xsl:value-of select="current-grouping-key()"/></Center>
                <xsl:for-each-group select="current-group()" group-by="course_status">
                    <Status><xsl:value-of select="current-grouping-key()"/></Status>
                    <Total_Cost><xsl:value-of select="sum(current-group()/course_cost)"/></Total_Cost>
                    <employees>
                        <xsl:for-each select="current-group()">
                            <employee>
                                <userId><xsl:value-of select="externalCode "/></userId>
                                <cost><xsl:value-of select="course_cost"/></cost>
                                <costcenter><xsl:value-of select="cust_costcenter"/></costcenter>
                                <status><xsl:value-of select="course_status"/></status>
                            </employee>
                        </xsl:for-each>
                    </employees>
                </xsl:for-each-group>
            </xsl:for-each-group>
        </output>
    </xsl:template>
</xsl:stylesheet>

As you can see below, the XSLT performed the ‘group by’ function for both the Cost Center and Status successfully.

Output XML :
<?xml version="1.0" encoding="UTF-8"?>
<output>
    <Center>3748</Center>
    <Status>Active</Status>
    <Total_Cost>1510</Total_Cost>
    <employees>
        <employee>
            <userId>1982</userId>
            <cost>960</cost>
            <costcenter>3748</costcenter>
            <status>Active</status>
        </employee>
        <employee>
            <userId>1984</userId>
            <cost>550</cost>
            <costcenter>3748</costcenter>
            <status>Active</status>
        </employee>
    </employees>
    <Status>Completed</Status>
    <Total_Cost>1200</Total_Cost>
    <employees>
        <employee>
            <userId>1981</userId>
            <cost>950</cost>
            <costcenter>3748</costcenter>
            <status>Completed</status>
        </employee>
        <employee>
            <userId>1983</userId>
            <cost>250</cost>
            <costcenter>3748</costcenter>
            <status>Completed</status>
        </employee>
    </employees>
    <Center>3746</Center>
    <Status>Completed</Status>
    <Total_Cost>1370</Total_Cost>
    <employees>
        <employee>
            <userId>1986</userId>
            <cost>740</cost>
            <costcenter>3746</costcenter>
            <status>Completed</status>
        </employee>
        <employee>
            <userId>1988</userId>
            <cost>630</cost>
            <costcenter>3746</costcenter>
            <status>Completed</status>
        </employee>
    </employees>
    <Status>Active</Status>
    <Total_Cost>760</Total_Cost>
    <employees>
        <employee>
            <userId>1987</userId>
            <cost>330</cost>
            <costcenter>3746</costcenter>
            <status>Active</status>
        </employee>
        <employee>
            <userId>1989</userId>
            <cost>430</cost>
            <costcenter>3746</costcenter>
            <status>Active</status>
        </employee>
    </employees>
</output>

Thank you for reading the blog. I request your feedback on the above configuration and code. Kindly let me know in case of any limitations that I can try to overcome.

Best Regards,
Rasesh Thakkar.