Consumption of CDS views in Analysis for Office

priyanka_sadana2 · ‎2017 May 12

What is Analysis for Office?

SAP Business Objects Analysis, edition for Microsoft Office, is a Microsoft Office Add-In that allows multidimensional analysis of OLAP sources. It connects to SAP NetWeaver BW and SAP HANA.
Analysis plug-in includes versions for Microsoft Excel where Analysis for Microsoft Excel is a reporting tool.

CDS View to be consumed by Analysis for Office

Here we will learn through top down approach i.e. understanding the consumption view first and then going down to the interface views consumed in consumption view. After consuming the view, we will learn ways to view the latest data and to change value of input parameters without launching analysis for Office again.

Consumption View

Below is your consumption view Z_C_SFLIGHT_SCARR which is using interface view Z_I_SCARRFLIGHT_DEMO

Interface View(s)

Z_I_SCARRFLIGHT Is Association of two Views Z_I_SCARRDEMO and Z_I_FLIGHTDEMO on carrier ID

Interface view Z_I_FLIGHTDEMO

Interface view Z_I_SCARRDEMO

Steps to consume CDS view in Analysis for Office

Insert a data source (in our case a CDS view) and display the results in Analysis for Office

Skip the Logon to SAP Business Objects BI Platform

Select the system

Search with SQL view name of CDS view

Annotation @Consumption.filter is used to apply filter on the fields of a view. Marking multiple selection as true allows multiple inputs on the field. Also a field can be marked as optional or mandatory using the same annotation.

Refer line number 16 in Fig. 1
Here, @consumption.filter is applied on CarrierId.

Use value help and select Airline AA

Press OK and you will be able to view the data in Analysis for Office

This excel sheet can be saved and provided as a template for the end user. End user can change the input parameter via prompts and view data accordingly.
Also, Refresh All button can be used to view the latest data.

Click on Prompts from Data Source

This simple example demonstrated how easy it is to create user friendly reports using CDS View and its annotations.

kmoore007 · ‎2017 May 12

Hello priyanka.sadana2,

Nice blog! I tried it, but the CDS view does not show up when I search for it in Analysis for Excel. Is this a release specific functionality? Is the system you are working with an R/3 system or a BW system?

priyanka_sadana2 · ‎2017 May 17

Hello Kenneth,

Thanks!

Please check the annotation @VDM.viewtype in your code. Views with view type consumption are visible as source in Analysis for Office.

The system I am working upon is an S/4 HANA system.

kmoore007 · ‎2017 May 31

I see. We are not on HANA. Thanks.

former_member377358 · ‎2017 Jun 06

good work priyanka.

yet so simple to understand. thanks

dkle · ‎2017 Jun 22

Hello Priyanka,

thank you for this blog, but please add some info about which ABAP (Netweaver) release you are working on and which Analysis Office version you used.

It seems you used Netweaver 7.51, as the "@ClientHandling" annotation is only available since this version.

Thanks and Regards

Former Member · ‎2017 Aug 03

Hello Priyanka,

thanks for your blog!

I followed your steps but couldn't find the consumption views in the search.

Are there any more settings requested?

Thanks and regards,

Birgit

priyanka_sadana2 · ‎2017 Aug 03

Hi Birgit,

Please check the annotation

@VDM.viewType: #CONSUMPTION.

It should be set to #Consumption to make it visible in the input source in Analysis for Office.

Thanks,

Priyanka

former_member188157 · ‎2017 Aug 03

I have the same issue. Something is missing when we follow mentioned steps for connecting from Analysis Office.

priyanka_sadana2 · ‎2017 Aug 08

Hi Abhishek,

What is the issue? Please also mention the version of analysis for office that you are using.

Regards,

Priyanka

priyanka_sadana2 · ‎2017 Aug 08

Hi Daniel,

Version of Analysis for office is 2.3.1.59737.

Regards,

Priyanka

former_member239088 · ‎2017 Aug 18

Hi Priyanka,

I am facing the same issue, Consumption view is not showing up in Analysis office version 2.5. Another interesting issue is when I use case statement in my composite view corresponding measures are not coming in the analysis office. Do you face similar issue??

Thanks,

former_member205880 · ‎2017 Aug 22

Hi!

Did you tried 2CCMATSTOCKACT or simply 2C*? it should work in Analysis or rsrt.

Br, Maksim

priyanka_sadana2 · ‎2017 Sep 12

Try using 2C* or 2C concatenated with your view name.

Thanks,

Priyanka

former_member239088 · ‎2017 Nov 11

Hi Priyanka,

Thanks for your input. Facing another problem while integrating consumption view in AFO. Report has selection prompt as system date which should be editable prompt.

Through F4 help if we change the date means then new date value is not updating the prompt whereas the same is working in RSRT.

Please provide your input.

Thanks,

former_member239088 · ‎2017 Nov 11

Yes issue is resolved.Thanks for your input. Facing another problem while integrating consumption view in AFO. Report has selection prompt as system date which should be editable prompt.

Through F4 help if we change the date means then new date value is not updating the prompt whereas the same is working in RSRT.

Please provide your input.

Thanks,

priyanka_sadana2 · ‎2017 Nov 13

Hi Muruga,

Please attach the screen shot of where you are trying to edit the input parameter.

Is it only with DATE as input parameter or did you try it with an input parameter other than date?

Former Member · ‎2017 Nov 21

Hello Priyanka,

Firstly thank you so much for your blog appreciate all your effort you put into CDS View .

I have couple of live issues running in my project ..

Can you help me in how to activate or step involved for F4 help or LOV in the variable screen in S4HANA.I was trying to create prompts ( Variable) in query designer and then publish to AO(as I have no option for optional prompts in CDS).

And also help us out how to publish my CDS view in the folder structure in AO ( in Area Tab). I was able to find my CDS view in search option but not able to find in the folder structure in AO.

AO >> Analysis Office

Thanks,

Sanjeev

Former Member · ‎2017 Dec 26

BO - AfO can consume both transient provider queries (@Analytics.query:true) and SAP BW transient providers (@Analytics.dataCategory:#CUBE).

SAP BW transient providers and SAP BW transient provider queries will have names in the form 2C<CDS_SQL_VIEW>

Regards,

Shyam

former_member469349 · ‎2018 Mar 04

Hi Priyanka,

Thank you for the blog.

We followed the same and was able to get the output we always have the technical name of the infoprovider to the dimensions. How can we have remove the technical name of infoprovider?.

Regards,

Rajesh

former_member205144 · ‎2018 Sep 05

Hi Priyanka,

I am using a CDS View in Analysis for Office .The view contains a few Input parameters and a few fields marked as Filter.

Below is the annotation I am using to bring the fields in the AO Prompt:

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

Now, I want to sequence the filter fields and the input parameters in a particular order.

What is the annotation for sequencing fields in the Prompt?

Also, how do we modify the Labels appearing in the Prompt? It is by default taking the text from Data element. Can we alter this by any Annotations ?

Regards

Lavanya

former_member585567 · ‎2018 Sep 05

Hello Lavanya,

In order to display your filter fields in a particular order use the following annotation where '1' is the sequence number

@AnalyticsDetails.query.variableSequence : 1

In order to modify the labels use

@EndUserText.label: 'xxxxxxxxxxxxxx'

Regards

former_member205144 · ‎2018 Sep 06

Thank you Georgios!

Former Member · ‎2019 Mar 22

Hi Priyanka,

I created a consumption CDS view with annotation "@Analytics.query:true " inside.

And expect to run it from Analysis for Office.

But I can't find it from the AFO search of "Select Data Source" by loging into my sandbox S4H system.

However, I can see consumption views with annotation "@Analytics.query:true " delivered by SAP in AFO.

So I copied a SAP consumption view C_SalesOrderItemQry and activate it under another name, still can't find it from AFO.

Then to make a consumption view available in AFO, any further setting required besides the annotation of @Analytics.query:true ?

I'm using:

AFO 2.3
HANA 2.00.034.00

S4HANA on Premise 1809

Thanks!

Regards,

Matt

cristina_ungureanu · ‎2019 Mar 25

Hi Priyanka,

I created a consumption CDS view, with the parameters:

@VDM.viewType:#CONSUMPTION
@Analytics.query: true
@OData.publish: true

And I am not able to find the CDS in Analysis for Office, I tried to find it with 2C*, also the query is available in Fiori.

Do you have any idea witch can be the problem?

Regards,

Cristina

Former Member · ‎2019 Apr 09

Got reply from SAP:

From S/4HANA 1809, a CDS query (CDS view with annotation @AnalyticsDetails.query:true) is available in Open Query dialog box only when it is set as C1-released (As a developer on customer-site, you can use the Use System-Internally (C1) release contract to make custom objects available for use in key user apps. The other release contracts are only relevant for SAP development.).

Refer to SAP Knowledge Base Article 2733553 for details.

If tab ‘API State’ of the view properties is not available in your Eclipse ABAP Development Tool, you can install/upgrade to the latest version after adding URL https://tools.hana.ondemand.com/photon/ into “Available Software Sites”.

Tested the suggested solution, now my consumption views with @AnalyticsDetails.query:true are visible in AFO.

Another test in S4HANA 1610 shows that there is no such “feature”, which again confirmed that this is something new from 1809 as mentioned above.

priyanka_sadana2 · ‎2019 Apr 21

Hi Matt,

Did you try to search with 2C*? Please try with 2C* and if it still does not work then we can keep a meeting to check.

Thanks and regards,

Priyanka Sadana

cristina_ungureanu · ‎2019 Apr 24

Thank you Matt, I am working on S/4HANA 1809 and I have set it as C1 released and now it's available in AFO.

deodutt_dwivedi · ‎2019 Aug 07

Hi Matt,

Thanks for the info. Is this a transportable change?

Regards,

Deo

Subhrangsu · ‎2019 Sep 26

Stumbled across it more than 2 years after it was published, but still very helpful. Thanks priyanka.sadana2

aleha705 · ‎2019 Oct 22

Thank you a lot!

It helped. There is also explanation about use state for SAP objects

https://help.sap.com/doc/saphelp_nw751abap/7.51.0/de-DE/90/339a4e940e44f2be4e96bb9d3b3d4d/frameset.h...

Former Member · ‎2019 Dec 25

It is a nice Blog ...

Could you please help anyone as I am new to CDS Views and my requirement is as follows;

I want to enhance FSV (GL Hierarchy) in current Consumption CDS View.

Regards,

Rafi

former_member186356 · ‎2020 Jan 23

Hello Priyanka,

Looking for answer similar to question Matt Zhou above. More or less we have same issue. We are unable to see the CDS view folders - Info areas in AO. SAP suggested to use check in RSRT, still the same issue. The folders are not showing up in the RSRT or AO. We followed all the steps Matt Zhou - mentioned ( Suggested by SAP ). Unable to find the FOLDER. Is there any way to transport the application component/ Infoarea. We are looking for infoarea - BI in S/4 system RSRT and in AO . Could you please let us know when you get a chance. All auth are provided.

Regards

Ramakrishnan Ramanathaiah

Masoom · ‎2020 Mar 21

Hi Priyanka,

Thanks for this Great blog!!

I have one scenario; I have to build an AO report with ranges/multi-select of inputs in AO prompts, but the reporting requirement is complex, so I will have to use AMDP basically I will create CDS Table function, now data will not be fetched straight forward from the table based on what user provides input in AO input prompt, but I have to do some calculation and return the summarised data to AO, for this case I need to have what user has given input in AO input screen.

So I can capture the input values(of AO) into CDS table function(or AMDP) so that I can manipulate the data in AMDP.

Please give me some direction.

Thanks

zsc · ‎2020 Apr 28

Very informative blog Priyanka, Thank you...!!!

I am having a date range selection on my CDS view and so am using

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

The date range selection is coming up fine on the selection prompt, however, I want to default the operator to range [] instead of = that Analysis for office prompt shows by default.

This way both From and To input boxes will be enabled by default and users will not have to change the operator selection. Please let me know if you have any thoughts on this. Thanks.

priyanka_sadana2 · ‎2020 May 04

Hi Ramakrishnan,

Try searching for any consumption CDS view with 2C* and then check the path of this CDS. This will give you the exact folder. In case you still face the issue we can keep a call to resolve it.

Regards,

Priyanka

sreekanth1410 · ‎2020 May 28

Hello

I would like to know how do we transport the workbook template created in AO from the CDS view to Quality and Production systems

driss_essika · ‎2020 Jul 23

Hi Priyanka,

Thanks for you input. But i'm facing the same issues

From rsrt i find my cds query but from Analysis For Office i don't find the query...

I process like this :

. I copied the cds view C_ProfitAndLossPlanActQ2903 -> 2CCFIPLPLANACT

my cds view name is :

@AbapCatalog.sqlViewName : 'ZCPROFLO2'
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'ZP&L - Plan/Actual'
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY

@Analytics.settings.maxProcessingEffort: #HIGH
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.buffering.status: #NOT_ALLOWED
@Metadata.ignorePropagatedAnnotations: true

@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #MIXED

S4HANA 1909

rajkumarnarasimman · ‎2020 Oct 09

From AFO, is it possible to call the ABAP Exit Class using VirtualElement annotation?

priyanka_sadana2 · ‎2021 Feb 01

Hi Rajkumar,

I am not aware about this. We can schedule a call to discuss the specific requirement.

priyanka_sadana2 · ‎2021 Feb 01

The template gets saved as the AO file and the CDS are transported via the TR. We just need to select the system while launching the file.

former_member727710 · ‎2021 May 13

Hello Priya,

We are facing the following issue and maybe you can help.

We have created several workbooks in AFO based on ABAP CDS views. However, when we modify the layout of the workbook and save it locally or under a role, the layout is not preserved. When the workbook is re-opened, the default layout is used. The one defined in the consumption view using the annotation .query.axis.

Is there a way to preserve the workbook layout instead of switching to the default one defined by the consumption view, each time the workbook opens?

Thanks alot,

Nikos

former_member489760 · ‎2021 Jun 10

Can we save this 2C* consumption query inside a role > folder in AO? If yes, how to do that?

priyanka_sadana2 · ‎2021 Nov 09

Hi Arghadeep,

I did not try saving it inside a role, we can try it together. Please schedule a meeting if the issue is still not resolved.

former_member647085 · ‎2022 Mar 31

HI Priyanka,

Thanks for the useful content. I created a custom CDS view by copying standard CDS view. Standard CDS view is appearingin ANalysis design but custom CDS view is not appearing.

I tried with 2C*.

MY analysis design version is 2.8 and S4HANA2021.

I am trying in another SAP version S4HANA1809 i am not able to see standard CDS Views also, can you answer both these questions.

former_member647085 · ‎2022 Mar 31

HI Priyanka,

Thanks for the useful content. I created a custom CDS view by copying standard CDS view. Standard CDS view is appearingin ANalysis design but custom CDS view is not appearing.

I tried with 2C*.

MY analysis design version is 2.8 and S4HANA2021.

I am trying in another SAP version S4HANA1809 i am not able to see standard CDS Views also, can you answer both these questions.

Henry_Banks · ‎2022 Mar 31

Hi phani_091980 is probably best if you ask your question in the community, rather than under this older blog. Regards, H

former_member647085 · ‎2022 Mar 31

After i created the example you have shown above it is working.

priyanka_sadana2 · ‎2022 Apr 01

It feels great to read that the blog helped you.

former_member647085 · ‎2022 Apr 01

I am facing few more issues Priynaka, can you help ?

S4HANA1809 Standard CDS views are not appearing in Analysis Design. May i know the reason ?

I created new custom CDS view but getting error Error when i use @Analytics: {dataExtraction.enabled: true, query: true }

Please help me where i am going wrong

Consumption CDS:

@AbapCatalog.sqlViewName: 'ZSCWMWHO'
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #DIMENSION
@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel.usageType.sizeCategory: #S
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #MIXED
@EndUserText.label: 'Demo'
@ObjectModel.representativeKey: 'lgnum'
@AccessControl.authorizationCheck: #CHECK

define view Z_C_CDS_WHO as select from Z_C_CDS_WHO_Data {
key lgnum as lgnum,
who as who,
type as type,
status as status,
created_at as created_at
}

-----------------------------------------------------------

@AbapCatalog.sqlViewName: 'ZSCWMWHO'
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #DIMENSION
@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel.usageType.sizeCategory: #S
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #MIXED
@EndUserText.label: 'Demo'
@ObjectModel.representativeKey: 'lgnum'
@AccessControl.authorizationCheck: #CHECK

define view Z_C_CDS_WHO as select from Z_C_CDS_WHO_Data {
key lgnum as lgnum,
who as who,
type as type,
status as status,
created_at as created_at
}

---------------------------------------------------------------------------

@AbapCatalog.sqlViewName: 'ZCDSDATA'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Data retrieval'
define view Z_C_CDS_WHO_Data as select from /scwm/who {

lgnum as lgnum,
who as who,
type as type,
status as status,
created_at as created_at
}

Error: deriving the analytical model from CDS view I_EWM_WAREHOUSEORDER

2

VijayBhaskaran · ‎2023 Jun 29

Hi Priyanka Sadana,

Are the same steps apply to consuming the CDS view in Design Studio and Webintelligence?

Thanks in Advance.