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

[Prabhith]

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