Skip to Content
Author's profile photo Prabhith Prabhakaran

SAP S/4HANA “ABAP CDS” – “Analysis Office For Microsoft Excel” Integration (Tips and Tricks) – Part 1

Hello Team,

This time I thought of Sharing my experience on S/4HANA ABAP CDS and AO integration.

These are some points that I found useful in some of my projects.

1) Setting Issue:

In one of the Project, we had 2 clients in Development environment (300- Development Client (with no test data) and 100-Testing client (with test data)). Unfortunately, while configuring the AO for the first time, I had provided 300 client details.

Because of this wrong client details, my reports were showing “No Applicable Data Found” and I was not finding an option where we could go back and change the setting.

Solution: It’s a simple setting:

Launch AO à File à Customize Analysis à Options à Advanced Tab

Enable the Below setting

With this, we will get an option to change the client and Language, while the report opens.

2)  Sorting Issue in AO:

Issue: I had a requirement where we had to Sort (in ascending order) a specific column, but every time the sorting goes for a toss.

Solution: This was a standard CDS View for AR Aging.

On investing Further, I could understand that its coming from a standard Domain

key cast( cast(AgingGrid.AgingGridColumnSequence as abap.dec(4,0) ) as farp_aging_grid_col_sequence) as AgingGridColumnSequence

Data type here is DEC

I had added a local Annotation here:

           @DefaultAggregation: #NONE

           AgingGridColumnSequence,

 

This changed the behavior of that column and the Sorting started coming correctly.

NOTE: This Thought Process came from the last 3 comments in this thread:

https://archive.sap.com/discussions/thread/3679847

 

3)   Transpose Rows to Columns:

Issue: We already have a report developed in the following structure/format. There is  a user prompt for this report, where user can enter a range of months for posting dates (Ex: 20180401 – 20180630)

Now Business User has come up with a change in requirement, where we have to dynamically transpose the rows into columns like below:

Solution:  I did a sample scenario with BSEG Table.

Data is stored as below:

The rows and columns were adjusted as below:

This is an important annotation:

 

Final Output:

4)  Flexibility of Prompts with one Line Annotation:

 @Consumption.filter: {selectionType: #SINGLE, multipleSelections: true, mandatory: false}

a) “Selection Type” Available options:

b) “Multiple Section” Available options;

c) “Mandatory” Available option

5)   Prompt Sequence:

      @AnalyticsDetails.query.variableSequence : ‘3’ (Local Annotation)

Output:

 

6) Prompt Label:

 

The label provided here will be displayed in AO

 

7)  Key and Text Visibility:

PS: Both Key and Text are Enabled

PS: Only Key is Enabled

 

NOTE: I will keep adding more content to this blog series in upcoming days.

 

Hope this blog was useful for you. Do let me know your Reviews and Suggestions.

BR,

Prabhith

 

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Kermit Bravo
      Kermit Bravo

      Looking forward to part 2, this was great Prabhith!

      Author's profile photo srikanth koppula
      srikanth koppula

      Hi Prabith,

       

      hope you doing well.

      Could you please send me your phone number or email id .