Opportunity Changes via the Odata Interface within MS Excel. Click on the Picture to enlarge.
Export Business Object Change Logs/ History into Microsoft Excel
There are many business scenarios for which you need to be able to see the change history or change logs of a document. Audit purposes is certainly one of the most important. But also for analytical purposes or process/data minings this information is important.
How to access change history in C4C
Within the C4C Solution all Business Objects have a Tab within their UI which is called "Changes" where the complete change history is logged and visible. But recently there were more and more requests to access the change data on a mass level and not one by one in the UI. To get all the change logs across an business object there is the possibility to extract them via Data Workbench. However, there are not all attributes covered. Also via the respective Data Sources within the Business Analytics Workcenter not all attributes and nodes of an object could be fetched.
Since the 2211 Release there is a new option to extract the change history of a Business Object data from C4C based on an OData API. It offers to export all changes covering all attributes and nodes of a business object, e.g. Opportunity, Lead or Ticket. With the new Interface one can really extract all the changes that were done on a document comparable to the “Changes” Tab on the UI but for multiple documents at once. The new Interface is called “Odata Service for Change Documents”.
Now extracting the change data via an OData interface sounds like some initial effort to get startet, however there is an easy and fast way via Microsoft Excel to get the change log via OData out of C4C. This blog describes to extract Opportunity changes via the “Odata Service for Change Documents” into Microsoft Excel.
Details of the new interface
First of all I want to share details of this newly introduced interface. a detailed step by step guide for MS Excel follows below. At the moment this interface is not active by default but needs to be requested via an incident. Please refer to the help.sap.com section for further information.
Once active the path of the odata service is https://<tenantURL>/sap/c4c/odata/v1/changedoclist/ChangeDocumentCollection
The service supports the following filter parameters:
- BusinessObject (this filter is mandatory) (to query Ticket changes use BusinessObject eq 'ServiceRequest'
- ChangeDateTime (optional)
- ObjectUUID (optional)
There is no filter available on changed attribute or modification type. In addition standard OData filter like Top and Skip are supported.
To avoid issues with too high data load the service supports an automatic paging mechanism: In case there are more than 1000 entries fetched the result will be cut on 1000 entries but will provide a next link to fetch the next 1000 records.
Note: Excel concatenates the entries automatically, no to do here for the Excel Extraction.
A sample HTTP request may look as:
https://<tenantId>/sap/c4c/odata/v1/changedoclist/ChangeDocumentCollection?$filter=BusinessObject eq 'Opportunity' and ChangeDateTime ge datetime'2022-10-07T00:00:00'
Will fetch all changes done on Opportunities after 2022/10/07.
Authorization method basic (username/password) is supported.
Before returning the result list, the oData implementation checks that the used user has read access for all business object instances for which changes were found. If this is not the case, the system will return an error message.
Extract all Opportunity Changes into Microsoft Excel
Now to make use of the Interface “Odata Service for Change Documents” it needs to be connected and consumed by other systems, which usually goes along with some implementation effort. I would like to demonstrate how to get the data in an easy and fast way based on Microsoft Excel which can also consume data via OData.
This is how it loos like in the end. It might remind of a Data Workbench extract. For the ease of use I hid some columns.
The Table shows:
- Document Type – here: Opportunity
- The UUID of the Upportunity
- Change Timestamp
- Changed By
- Change Type (ObjectNodeElementName)
- Old Value (ObjectNodeElementOldContent)
- New Value (ObjectNodeElementNewContent)
- Modification Type (ObjectNodeElementModificationTypeCode) Create, Update, Remove
Steps to get started with your own extract via OData in Microsoft Excel
- As described above the first step is to request the Enablement of the Interface via an Incident to SAP Support
- Adjust the Path to your Tenant and adjust the Date https://<tenantId>/sap/c4c/odata/v1/changedoclist/ChangeDocumentCollection?$filter=BusinessObject eq 'Opportunity' and ChangeDateTime ge datetime'2022-10-07T00:00:00'
- In Microsoft Excel: open a new blank Page and navigate to the Tab “Data”.Create a new Connection - Open the Area “Get Data” then open the submenu “From Other Sources” and then select “From Odata Feed”.
- Paste the URL Path and click on OK.
- Excel asks you about the Authentication, select “Basic” and provide a username and password
- Click on “connect”.
- A Preview is shown, click on “Load”.
The result is a table which then looks like this:
Result list of all change data according to your filter.
If the query returns more than 1000 entries, excel automatically fetches the next link as described earlier and concatenates them to one single list. in my example the list hast more than 2500 entries.
Adjust the columns to your need and save your project in a worksheet. You can refresh the query by navigating into the Tab "Data" or "Query" and click. "Refresh".
To edit your query navigate to the tab "Query", click on "Edit"
All done. ou can now repeatedly consume the desired document changes via Odata directly in Excel.
Current Limitations
Please refer to the help.sap.com section for current limitations.
Currently this Odata Service is not available for all documents. This Restriction applies to some master data like Account and Contact.
- SAP Managed Tags:
| User | Count |
|---|---|
| 4 | |
| 4 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
