Leverage the power of ODATA with Excel

Hello,

In this Blog post, I will describe how to pull customized data from Cloud For Customer into Excel. To get started, you need to install the PowerQuery Excel AddOn, which can be downloaded from Microsoft: https://www.microsoft.com/en-us/download/details.aspx?id=39379

Use case:
Reports and OWL downloads are great to select data fast and display the latest information available. However, Reports are limited to the fields that are available in data sources. ODATA works on the business object mode which makes the access to the fields much slower, but it gives you access to all fields including extension fields, notes and attachments.

For Cloud Application Studio Developers:
You can create a custom ODATA service in the ODATA Explorer Admin Workcenter. To this custom ODATA service, you can add standard and also custom business objects, which are not part of the standard C4C ODATA service.

Your first report with ODATA, PowerQuery and Excel

Once you have installed the PowerQuery AddOn, you will notice a new Toolbar labeled “POWER QUERY” in Excel.

To connect C4C to PowerQuery, click “From Other Sources”. Make sure you are on the tab “POWER QUERY”, because the same button exists on the tab “DATA”.

Choose “From ODATA Feed” in the dropdown:

This will open a popup dialog asking for a URL. Enter your C4C tenant URL here and append “/sap/byd/odata/v1/c4codata/”. The / at the end is important. You can use the same URL in the browser and an XML should be shown.

If this is the first time you connect PowerQuery to this tenant, it will ask for authorizations:

Make sure you select “Basic” authorization on the left side and enter your username and password you usually use to log on to C4C. Click connect to continue.

The PowerQuery AddIn will now connect to the C4C system and provide you a list with available collections. This is a long list as there is pretty much a Collection for every node in every business object.

Interesting Collections for you are the short ones named: AccountCollection, OpportunityCollection etc.

You can filter the list with the search bar on top. Once you clicked on a collection a short preview will be built on the right side. In the example, we use the OpportunityCollection.

Once you have chosen your start-Collection, press “Edit” to continue.

The button “Load” would instantly start to download the collection into the currently opened excel spreadsheet. In most cases this is not what you want. Chose edit to fine tune the query and add, remove and filter data.

Now a new window opens:

This is the Query Designer. It shows you some example data from your system to give you an idea what it stored in the fields. This is not the actual report. We’re designing it here.

The main area:
Here you see sample data. The data just there to give you a preview of how the final report might look. Work on the header row to remove, expand, filter data.

There are mainly three types of column types.

1. Flat field (shown as plain text)
2. Structure (shown as “Record”)
3. Table (shown as “Table”)

On the flat field, you can pre-filter data. Data filtered in this stage is not fetched from C4C. This mainly useful to restrict the payload.

On a structure, you can decide if you want to expand it. Usually you either want to expand structures, or delete them entirely. All fields selected will be added into the main list.

On a table, you can select the fields you want to expand into the main list. Note that if you have for example an Opportunity with multiple Sales Activity entries and you expand the Sales Activity entry, the Opportunity will be duplicated for each Sales Activity.

Note: If you drill down multiple levels, you will already feel that the refresh will significantly slow down. To keep the performance on an acceptable level, you should only select the fields you really need and remove the ones you don’t need. Also the sequence plays a role. Remove all unnecessary header first.

On the right side, you can see the applied steps. You can go back any time and change a previeous step. At runtime, the query is run with exactly the steps shown here.

In the screenshot above, I have done quiet some work. I expanded some names, some values and I also expanded the Product and the Sales Team list.

Before importing it into excel, you might want to give the query a better name. You can do so on the right side in the property name.

The last step in the Query Designer is to close and load the data into excel. Push the Close & Load button on the left side in the ribbon bar.

Excel will automatically create the columns and will immediately start to download the data from C4C. How long the download takes depends on the complexity of the query and the amount of data that must be retrieved.

You can see the Workbook Querie pane on the right side. This pain will show you the status of the query.

Once the data is retrieved, you can work with it in Excel. You can save the document and open it later again. It will contain the same set of data. If you want to update the data, click the refresh button on the query.

A connection to C4C is only required while you build a query and while the retrieve / download is running.

If you want to edit the same query again, you can do so by clicking right on the query -> edit. This will open the query designer again.

I hope this blog post encourages you to play with the C4C ODATA interface and the Power Query AddIn.

Best Regards,
Stefan