Skip to Content
Technical Articles

SAP Cloud Platform Integration – Message Monitoring Automated Report

Introduction:

This blog explains on how to send automated status report for the message processing via SAP CPI. The notification is sent by setting up a timer triggered integration flow (IFlow) on regular intervals.

Here is the how the over flow looks like:

Steps to be followed in creating the Iflow:

Step 1: Create a Package with Name: Cloud Platform Integration Reporting.

Step 2: Create an IFlow with Name: Reporting_IFlow

Step 3: Create an Integration flow with following Components.

  1. Start Timer (Timer)
  2. dateCapture (Content Modifier)
  3. setParam (Groovy Script)
  4. Request-reply
  5. ODataService(Receiver Participant)
  6. OData Adapter(V2)
  7. MessageMapping
  8. XSLT Mapping
  9. RunDate (Write Variable)
  10. Receiver Participant
  11. End message
  12. SFTP Adapter/Mail Adapter.

Step 4: Configure Timer.

Configure the timer to run for every day. I’ve done the configuration such that the interface will run every day twice (once in every 12 hours) and captures the data since the previous flow time.

Step 5: Configure dateCapture (Content Modifier)

In the content modifier, create three properties.

Action Name Type Data Type Value
Create LogEndDate Expression java.lang.String ${date:now:yyyy-MM-dd’T’HH:mm:ss.SSS}
Create LogStartDate Global Variable RunDate
Create ManualStartDate Constant 2020-01-01T00:00:00.000

For the First run of the interface, we need to have a Start date, from the consecutive runs, Global variable will be considered for the start date, so that no message will be left uncaptured.

Step 6: Construct the setParam script based on start and end dates from content modifier.

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
def Message processData(Message message) {
       def body = message.getBody(java.lang.String);
     
       def map = message.getProperties();
       def ManualStartDate = map.get("ManualStartDate")
       def LogStartDate = map.get("LogStartDate")
       def LogEndDate = map.get("LogEndDate")
       def whereQuery
       if (LogStartDate ==''){
           message.setProperty ("StartDate", ManualStartDate)
       }else{
            message.setProperty ("StartDate", LogStartDate)
       }
       message.setProperty("EndDate", LogEndDate);
       return message;
}

 

Step 7: Calling the OData API via OData V2 adapter and configure with below parameters

Operation Details Query(GET)
Resource Path MessageProcessingLogs
Query Options $select=MessageGuid,CorrelationId,ApplicationMessageId,ApplicationMessageType,LogStart,LogEnd,Sender,Receiver,IntegrationFlowName,Status,AlternateWebLink,IntegrationArtifact,LogLevel,CustomStatus,TransactionId,PreviousComponentName&$filter=LogStart ge datetime’${property.StartDate}’ and LogEnd le datetime’${property.EndDate}’
Content Type Atom
Timeout (in min) 1

Step 8: Message Mapping

Do the Message Mapping with following XSD as source:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="MessageProcessingLogs">
        <xs:complexType>
            <xs:sequence>
                <xs:element minOccurs="1" maxOccurs="unbounded" name="MessageProcessingLog">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element type="xs:string" nillable="false" maxOccurs="unbounded" name="MessageGuid" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="CorrelationId" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="ApplicationMessageId" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="ApplicationMessageType" xmlns=""/>
                            <xs:element type="xs:dateTime" nillable="true" minOccurs="0" maxOccurs="unbounded" name="LogStart" xmlns=""/>
                            <xs:element type="xs:dateTime" nillable="true" minOccurs="0" maxOccurs="unbounded" name="LogEnd" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Sender" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Receiver" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="IntegrationFlowName" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Status" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="AlternateWebLink" xmlns=""/>
                            <xs:element nillable="false" maxOccurs="unbounded" name="IntegrationArtifact">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Id" xmlns=""/>
                                        <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Name" xmlns=""/>
                                        <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Type" xmlns=""/>
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="LogLevel" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="CustomStatus" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="TransactionId" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="PreviousComponentName" xmlns=""/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

 

And the Below XSD as target.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="MessageProcessingLogs">
        <xs:complexType>
            <xs:sequence>
                <xs:element minOccurs="0" maxOccurs="unbounded" name="MessageProcessingLog">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element type="xs:string" nillable="false" maxOccurs="unbounded" name="IntegrationFlowName" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Completed" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Failed" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Processing" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Retry" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Escalated" xmlns=""/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

 

Mapping Rules:

  1. Map both the root nodes.
  2. Map the Target MessageProcessingLog(Subnode) with below logic.

Source Field: IntegrationFlowName To Target MessageProcessingLog

3. Map source IntegrationFlowName to  Target IntegrationFlowName with below logic

**Remember — First SplitBy Value must have Context change on Value change and second SplitBy Value must have Context change on Each value

4. Map the Source IntegrationFlow and Status from Source to Target Completed Field with below logic

This is the most important and tricky part.

IntegrationFlowName—removeContext—SplitByValue(ValueChange)—FormatByExample(Input-Status, Pattern-SplitByValue Output)—Equals(String1- FormayByExample, String2-Constant”COMPLETED”)—ifWithoutElse(Condition–Equalsoutput, trueValue–FormatByExample)—Count—Completed

repeat the same logic for Failed, Processing, Retry and Escalated by changing the constants.

 

Step 9: XSLT Mapping

Once done with Message Mapping, we will have the desired output in XML format. To convert the XML to XLS format, we need to do XSLT mapping. Use the below XSLT Code to convert the XML message in XLS format.

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>

<xsl:stylesheet version="1.0" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ns0="urn:test.com" xmlns:html="http://www.w3.org/TR/REC-html40">
	<xsl:template match="/">
		<Workbook>
			<Styles>
				<Style ss:ID="Default" ss:Name="Normal">
					<Alignment ss:Vertical="Bottom"/>
					<Borders/>
					<Font/>
					<Interior/>
					<NumberFormat/>
					<Protection/>
				</Style>
				<Style ss:ID="s21">
					<Font ss:Size="22" ss:Bold="1"/>
				</Style>
				<Style ss:ID="s22">
					<Font ss:Size="14" ss:Bold="1"/>
				</Style>
				<Style ss:ID="s23">
					<Font ss:Size="12" ss:Bold="1"/>
				</Style>
				<Style ss:ID="s24">
					<Font ss:Size="10" ss:Bold="1"/>
				</Style>
			</Styles>
			<Worksheet ss:Name="Sheet1">
				<Table>
					<xsl:call-template name="Excel"/>
				</Table>
			</Worksheet>
		</Workbook>
    </xsl:template>
	<xsl:template name="Excel">
        <Row>
            <Cell>
            <Data ss:Type="String">IntegrationFlowName</Data>               
            </Cell>
            <Cell>
            <Data ss:Type="String">Completed</Data>
            </Cell>
            <Cell>
            <Data ss:Type="String">Failed</Data>                
            </Cell>
            <Cell>
            <Data ss:Type="String">Processing</Data>            
            </Cell>
            <Cell>
            <Data ss:Type="String">Retry</Data>                
            </Cell>
            <Cell>
            <Data ss:Type="String">Escalated</Data> 
            </Cell>
            </Row>
		<xsl:for-each select="//MessageProcessingLog">

            <Row>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="IntegrationFlowName" />
                    </Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Completed" />
                    </Data>
                </Cell>
                                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Failed" />
                    </Data>
                </Cell>
                                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Processing" />
                    </Data>
                </Cell>
                                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Retry" />
                    </Data>
                </Cell>
                                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Escalated" />
                    </Data>
                </Cell>
            </Row>
        </xsl:for-each>
    </xsl:template>
<xsl:template match="MessageProcessingLogs">
</xsl:template>
</xsl:stylesheet>

 

Step 10: Storing the RunDate for next trigger.

We need to store the run date of the flow so that there won’t be any duplicate counts displayed in the consecutive runs.

Step 11: Connect to SFTP/Mail Adapter.

For SFTP adapter Configuration —SFTP Receiver

For Mail adapter Configuration – Mail Receiver Adapter

 

Once the configuration is done, and triggered, the output file generated looks like the below

 

I’ve Masked the IFlow Names.

 

Conclusion:

This is one of the simplest ways of generating report for your Cloud Platform Integration. With the help of SAP API Business HUB, utilizing the new feature, we can further extend the look and feel of the reporting tool.

 

Thanks,

Happy Learning!!

15 Comments
You must be Logged on to comment or reply to a post.
  • For the First run of the interface, we need to have a Start date, from the consecutive runs, Global variable will be considered for the start date, so that no message will be left uncaptured.

     

     

    for this , we can use Default value. If global variable value not available it will take default value and this will avoid groovy scripting.

     

     

    /
    • Hi Ramesh,

      Agreed. we can maintain Default Value. My intention is to keep Start date as externalized parameter so that we can just configure the value without editing the iflow.

      Sidharth VR

       

  • Hi Sidharth,

    I am getting below error message when uploading source and target message in message mapping.

    “Upload of the resource failed. The content of WSDL file is invalid. cvc-elt.1: Cannot find the declaration of element ‘xs:schema’.”

    Can you please assist on this.

    Regards,

    Nikhil

    • Hi Nikhil,

      Its a XSD structure. follow the below steps.

      1. Copy the code to notepad and store it as “source.xsd” in your desktop
      2. In CPI, navigate to resources tab, Add—>Schema—->XSD
      3. choose the source.xsd from your desktop and upload.

      let me know if it doesn’t work.

      Thanks,

      Sidharth

  • Hello Sidharth,

     

    Thanks for this blog. Can you please help us on below error at content modifier.

    ‘java.lang.IllegalArgumentException: Illegal pattern character ‘T’ ‘

     

    We have made config as you have mentioned for CM.

     

     

    Regards,

    Yuvraj Karwar

  • Hi Sidharth,

    Such a good informative blog !

    I have a query at this point –> Step 7: Calling the OData API via OData V2 adapter and configure with below parameters

    What credentials do I need to use & what would be the address in the Odata channel?

    It would be very kind of you to clarify the same.

    Thanks,

    Shreya

  • Hi Sidharth,

    Thanks for the Clarification ! Now I can successfully fetch the details from Odata.
    Currently I am facing issue with the XSLT Mapping part !

    Post this conversion step, the output file generated does not look like the screenshot given here.
    Somehow the data doesn’t get changed to the desired XLS format.

    I tried changing the Output Format of the XSLT mapping to both ‘String’ & ‘Bytes’ but the same result.

    Here I am using the mail adapter to deliver this report to my Outlook Mailbox & I have kept the Mail Body Type in the channel as ‘Text/CSV’

    Am I missing something?
    Kindly let me know if you can spot the anomaly.

    • Hi Shreyashri,

      Maintain Body Mime-Type as Text/Plain.

      after xslt, add a script to add output as attachment.

      //Script

      import com.sap.gateway.ip.core.customdev.util.Message;
      import org.apache.camel.impl.DefaultAttachment;
      import java.util.HashMap;
      import javax.mail.util.ByteArrayDataSource;

      def Message processData(Message message) {
      def body = message.getBody(java.lang.String) as String;

      // 2: Construct a ByteArrayDataSource object with the byte array and the content’s MIME type
      def dataSource = new ByteArrayDataSource(body, ‘text/plain’);

      // 3: Construct a DefaultAttachment object
      def attachment = new DefaultAttachment(dataSource);

      // 4: Add the attachment to the message
      message.addAttachmentObject(‘MessageProcessing_Report.xls’, attachment);

      return message;
      }

       

      Hope this works,

      Thanks,

      Sidharth VR

  • /
    image.png