Consumption of CDS views in Analysis for Office

May 12, 2017 | 5,689 Views |

Priyanka Sadana

more by this author

SAP Analysis for Microsoft Office

analysis for office cds cdsviews searchhelp

Follow RSS

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.

To report this post you need to login first.

19 Comments

You must be Logged on to comment or reply to a post.

Kenneth Moore May 12, 2017 at 3:26 pm

Hello Priyanka,

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?

like (0)
1. Priyanka Sadana Post authorMay 17, 2017 at 5:13 am
  
  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.
  
  like (0)
  1. Kenneth Moore May 31, 2017 at 2:01 pm
    
    I see. We are not on HANA. Thanks.
    
    like (0)
Karan Sehjpal June 6, 2017 at 6:58 am

good work priyanka.

yet so simple to understand. thanks

like (1)
Daniel Klein June 22, 2017 at 12:35 pm

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

like (0)
1. Priyanka Sadana Post authorAugust 8, 2017 at 6:46 am
  
  Hi Daniel,
  
  Version of Analysis for office is 2.3.1.59737.
  
  Regards,
  
  Priyanka
  
  like (0)
  1. Former Member March 4, 2018 at 11:22 pm
    
    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
    
    like (0)
Former Member August 3, 2017 at 12:38 pm

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

like (0)
1. Priyanka Sadana Post authorAugust 3, 2017 at 1:14 pm
  
  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
  
  like (0)
2. Former Member August 3, 2017 at 8:04 pm
  
  I have the same issue. Something is missing when we follow mentioned steps for connecting from Analysis Office.
  
  like (0)
  1. Priyanka Sadana Post authorAugust 8, 2017 at 6:35 am
    
    Hi Abhishek,
    
    What is the issue? Please also mention the version of analysis for office that you are using.
    
    Regards,
    
    Priyanka
    
    like (0)
Muruga MuthuKrishnan August 18, 2017 at 4:26 pm

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,

like (0)
1. Priyanka Sadana Post authorSeptember 12, 2017 at 12:36 pm
  
  Try using 2C* or 2C concatenated with your view name.
  
  Thanks,
  
  Priyanka
  
  like (0)
  1. Muruga MuthuKrishnan November 11, 2017 at 11:46 am
    
    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,
    
    like (0)
  2. Former Member December 26, 2017 at 7:15 pm
    
    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
    
    like (0)
Maksim Alyapyshev August 22, 2017 at 8:57 am

Hi!

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

Br, Maksim

like (0)
1. Muruga MuthuKrishnan November 11, 2017 at 11:47 am
  
  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,
  
  like (0)
  1. Priyanka Sadana Post authorNovember 13, 2017 at 5:28 am
    
    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?
    
    like (0)
Former Member November 21, 2017 at 5:05 pm
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 ..
1. 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).
2. 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
like (0)