Skip to Content
Technical Articles
Author's profile photo Uladzislau Pralat

ABAP CDS Consumption View Features Ultimate Test: Part 1

ABAP CDS Consumption Views are exposed as BW Queries and can be consumed by S/4 HANA Query Browser and BI Clients like Analysis for Office and Design Studio. With my BW background I was curious to put ABAP CDS Consumption Views to test and fulfill some of the typical BW Query requirements like:

  • Currency Translation
  • Display of Currencies and Units of Measure
  • Use of Attributes
  • Use of Texts
  • Use of Hierarchies
  • Use of Variables
  • Use of Calculated Key Figures
  • Use of Restricted Key Figures
  • Use of functions like NDIV0 and NODIM
  • Use of Time Dependent Dimensions and Hierarchies
  • Use of language dependent texts
  • Percentage Share calculation
  • Exception aggregation using Formula and Count
  • Filtering Key Figures Structure
  • Setting Key Figure attributes like number of decimals
  • Setting Description (Variables, Characteristics, Key Figures, etc)
  • Setting Variable value dynamically
  • Defining Layout (rows, columns and free characteristics) and switching on hierarchy

So for my ultimate test I built my Flight Y2Y comparison ABAP CDS Consumption View from ground up starting from BASIC views to prepare data, then created TEXT, HIERARCHY, DIMENSION, FACT and CUBE view then finally built by CONSUMPTION view. I will not go much into details of implementation because all that you can explore on your own. What is more important is to understand ABAP CDS Consumption Views capabilities.

This is what I have got:

As you can see from screen shot above it is possible to create hierarchy (Region) and characteristic variables. Also parameters for currency conversion (Display Currency) and restricted key figures restrictions (Year 1 and Year 2).

As you  can see from screen shot above it is possible to do:

  • Convert currencies (dc e.g. display currency columns)
  • Create restricted columns (Year 1 and Year 2)
  • Create calculated columns (Seats Occupied %)
  • Activate hierarchy (region characteristic)
  • Display unit of measures (currency, quantity and % for formula)
  • Set number of decimal places (1 for Seats Occupied % columns)
  • Characteristics and Key Figures are given proper descriptive names

Lets see hierarchy node selection variable  in action

As you can see from above screen shot node variable filtered hierarchy to Europe and North America data

Let me swap Region with Airline and display some more capabilities

From Airline characteristic properties presentation can be changed as well as attribute display activated

As you  can see from screen shot above it is possible to:

  • Activate display of characteristic attribute (URL)
  • Change characteristic presentation (key and text)

Finally lets test characteristic variable

As you can from above screen shot Airline characteristic variable is working limiting data to LH and UA.

You can create demonstrated ABAP CDS Consumption View and all other underlying view using source code below.

 

BASIC Views:

ZSAPBC_CARR


@AbapCatalog.sqlViewName: ‘ZCARR’

@AccessControl.authorizationCheck:#NOT_REQUIRED

@EndUserText.label: ‘Airline’

defineview zsapbc_carr asselectfrom scarr

{

carrid,

url,

currcode,

cast(case

when carrid =‘LH’or carrid =‘AB’then‘Germany’

when carrid =‘AA’or carrid =‘CO’or carrid =‘DL’or

     carrid =‘NW’or carrid =‘WA’then‘US’

when carrid =‘AC’then‘Canada’ 

when carrid =‘AF’then‘France’ 

when carrid =‘AZ’then‘Italy’

when carrid =‘BA’then‘UK’

when carrid =‘FJ’then‘Fiji’

when carrid =‘NG’then‘Austria’

when carrid =‘JL’then‘Japan’

when carrid =‘QF’then‘Australia’

when carrid =‘SA’then‘South Africa’

when carrid =‘SQ’then‘Singapure’

when carrid =‘SR’then‘Swirzerland’ 

when carrid =‘UA’then‘US’

else‘Other’

endas abap.char(13))  as region   

}


 

ZSAPBC_REGION


@AbapCatalog.sqlViewName: ‘ZREGION’

@AccessControl.authorizationCheck:#NOT_REQUIRED

@EndUserText.label: ‘Flight – Region’

define view zsapbc_region as select distinct from zsapbc_carr {

key region,

case

when region = ‘Germany’ or region = ‘France’ or region = ‘Italy’ or

       region = ‘UK’ or region = ‘Austria’ or region = Swirzerland then ‘Europe’

when region = ‘US’ or region = ‘Canada’ then ‘North America’

when region = ‘South Africa’ then ‘Africa’

when region = ‘Fiji’ or region = ‘Japan’ or region = Singapure then ‘Asia’

else ‘Other’

end as main_region 

}

where region <> ‘Australia’

 

union

 

select distinct from scarr {

key ‘Europe’ as region,

    ‘World’ as main_region

}

 

union

 

select distinct from scarr {

key ‘North America’ as region,

    ‘World’ as main_region

}

 

union

 

select distinct from scarr {

key ‘Asia’ as region,

    ‘World’ as main_region

}

 

union

 

select distinct from scarr {

key ‘Australia’ as region,

    ‘World’ as main_region

}

 

union

 

select distinct from scarr {

key ‘Africa’ as region,

    ‘World’ as main_region

}

 

union

 

select distinct from scarr {

key ‘World’ as region,

    as main_region

}


 

TEXT, HIERARCHY, DIMENSION Views:

ZSAPBC_CARR_TEXT


@AbapCatalog.sqlViewName: ‘ZCARR_TEXT’

@Analytics: {dataCategory:  #TEXT, dataExtraction.enabled: true}

@AccessControl.authorizationCheck: #NOT_ALLOWED

@EndUserText.label: ‘Flight – Carrier Text’

define view zsapbc_carr_text as select from scarr {

key carrid,

@Semantics.text: true

carrname as CarrierName   

}


 

ZSAPBC_CARR_DIMENSION


@AbapCatalog.sqlViewName: ‘ZCARR_DIM’

@Analytics: {dataCategory: #DIMENSION, dataExtraction.enabled: true}

@ObjectModel.representativeKey: ‘CARRID’

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: ‘Flight – Carrier Dimension’

define view zsapbc_carr_dimension as select from zsapbc_carr as scarr

 association [1..1] to zsapbc_carr_text as _carr on $projection.carrid = scarr.carrid

{

@ObjectModel.text.association: ‘_carr

key carrid,

url,

currcode,

@EndUserText.label: ‘Region’

region,

_carr

}


 

ZSAPBC_REGION_HIERARCHY


@AbapCatalog.sqlViewName: ‘ZREGION_HIER’

@Analytics: { dataCategory: #HIERARCHY, dataExtraction.enabled: true }

@ObjectModel.representativeKey: ‘REGION’

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: ‘Flight – Region Dimension’

@Hierarchy.parentChild.name: ‘REGION_GEO’

@Hierarchy.parentChild.label: ‘Region Geography’

@Hierarchy.parentChild{ recurse:          {
                         parent: ParentNode‘,   child: HierarchyNode   } }

define view zsapbc_region_hierarchy as select distinct from zsapbc_region

 association[0..1] to zsapbc_region_dimension as _region_dim on $projection.HierarchyNode = _region_dim.region

{

@ObjectModel.foreignKey.association: ‘_region_dim

key region as HierarchyNode,

main_region as ParentNode,

_region_dim

}


 

ZSAPBC_REGION_DIMENSION


@AbapCatalog.sqlViewName: ‘ZREGION_DIM’

@Analytics: { dataCategory: #DIMENSION, dataExtraction.enabled: true }

@ObjectModel.representativeKey: ‘REGION’

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: ‘Flight – Region Dimension’

define view zsapbc_region_dimension as select from zsapbc_region

association[0..*] to zsapbc_region_hierarchy as _region_hier on
$projection.region = _region_hier.HierarchyNode

{

@ObjectModel.Hierarchy.association: ‘_region_hier 

key region,

@EndUserText.label: ‘Main Region’

main_region,

_region_hier

}


 

FACT View:

ZSAPBC_FLIGHT_FACT


@AbapCatalog.sqlViewName: zsflight_fact

@AbapCatalog.compiler.compareFilter: true

@Analytics.dataCategory: #FACT

@AccessControl.authorizationCheck:#NOT_REQUIRED

@EndUserText.label: ‘Flight Fact’

define view zsapbc_flight_fact with parameters

    @Consumption.defaultValue: ‘CAD’

    p_display_currency : s_currcode

    as select from sflight inner join zsapbc_carr as scarr                                                      on sflight.carrid = scarr.carrid {

key sflight.carrid,

key sflight.connid,

key sflight.fldate,

scarr.region, 

cast(substring(sflight.fldate,1,6) as abap.numc( 6 )) as flmonth,

cast(substring(sflight.fldate,1,4) as abap.numc( 4 )) as flyear, 

sflight.currency,

sflight.paymentsum as payment,

‘CAD’ as disp_curr,

currency_conversion(

    amount             => sflight.paymentsum,

    source_currency    => sflight.currency,

    target_currency    => $parameters.p_display_currency,

    exchange_rate_date => sflight.fldate,

    exchange_rate_type => ‘M’,

    error_handling     => ‘SET_TO_NULL’            

) as payment_disp_curr,

cast(‘EA’ as abap.unit(3) ) as unit,

sflight.seatsmax,

sflight.seatsmax_b,

sflight.seatsmax_f,

seatsmax + seatsmax_b + seatsmax_f as seatsmax_total,

sflight.seatsocc,

sflight.seatsocc_b,

sflight.seatsocc_f,

seatsocc + seatsocc_b + seatsocc_f as seatsocc_total   

}


 

CUBE View:

ZSAPBC_FLIGHT_CUBE


@AbapCatalog.sqlViewName: ‘ZSFLIGHT_CUBE’

@AbapCatalog.compiler.compareFilter: true

@Analytics: { dataCategory: #CUBE, dataExtraction.enabled: true }

@AccessControl.authorizationCheck:#CHECK

@EndUserText.label: ‘Flight Cube’

define view zsapbc_flight_cube with parameters

p_display_currency : s_currcode,

p_year_1: rscalyear,

p_year_2: rscalyear    

as select from zsapbc_flight_fact( p_display_currency:
$parameters.p_display_currency ) as flight

    association [1..1] to zsapbc_carr_dimension as _carr on  $projection.carrid  = flight.carrid

    association [1..1] to zsapbc_region_dimension as _region on  $projection.region  = flight.region

      {

@ObjectModel.foreignKey.association: ‘_carr

key flight.carrid,

key flight.connid,

@EndUserText.label: ‘Date’ 

key flight.fldate,

@EndUserText.label: ‘Region’

@ObjectModel.foreignKey.association: ‘_region’

@ObjectModel.Hierarchy.association: ‘_region’ 

flight.region,

_carr,

_region,

@Semantics.calendar.yearMonth: true

@EndUserText.label: ‘Month’ 

flight.flmonth,

@Semantics.calendar.year: true

@EndUserText.label: ‘Year’

flight.flyear,

@Semantics.currencyCode: true

@EndUserText.label: ‘Booking Currency’    

flight.currency,

@Semantics.amount.currencyCode: ‘Currency’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Booking (bc)’

flight.payment,

@Semantics.quantity.unitOfMeasure: ‘Currency’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Booking (bc) Year 1′

case when flyear = :p_year_1 then payment

else 0

end as payment_year_1,

@Semantics.quantity.unitOfMeasure: ‘Currency’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Booking (bc) Year 2′

case when flyear = :p_year_2 then payment

else 0

end as payment_year_2, 

@EndUserText.label: ‘Display Currency’ 

cast(‘CAD’ as abap.cuky( 5 )) as disp_curr,

@Semantics.amount.currencyCode: disp_curr

@EndUserText.label: ‘Booking (dc)’

@DefaultAggregation: #SUM

flight.payment_disp_curr,

@Semantics.quantity.unitOfMeasure: disp_curr

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Booking (dc) Year 1’

case when flyear = :p_year_1 then payment_disp_curr

else 0

end as payment_disp_curr_year_1,

@Semantics.quantity.unitOfMeasure: disp_curr

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Booking (dc) ear 2’

case when flyear = :p_year_2 then payment_disp_curr

else 0

end as payment_disp_curr_year_2,

@Semantics.unitOfMeasure: true 

@EndUserText.label: ‘UOM’ 

flight.unit,

@Semantics.unitOfMeasure: true

@EndUserText.label: ‘%’ 

cast( ‘%’ as abap.unit( 3 ) ) as unit_percent,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Seats Max Econ.’ 

flight.seatsmax,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Max Bus.’ 

flight.seatsmax_b,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Seats Max 1st’

flight.seatsmax_f,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Max Total’

seatsmax_total,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Max Year 1’

case when flyear = :p_year_1 then seatsmax_total

else 0

end as seatsmax_total_year_1,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Max Year 2’

case when flyear = :p_year_2 then seatsmax_total

else 0

end as seatsmax_total_year_2,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Occ Econ.’

flight.seatsocc,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Occ Bus.’ 

flight.seatsocc_b,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Seats Occ 1st’ 

flight.seatsocc_f,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Occ Total’

seatsocc_total,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Occ Year 1′

case when flyear = :p_year_1 then seatsocc_total

else 0

end as seatsocc_total_year_1,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Occ Year 2′

case when flyear = :p_year_2 then seatsocc_total

else 0

end as seatsocc_total_year_2,

@Semantics.quantity.unitOfMeasure: unit_percent

@DefaultAggregation: #MAX

@EndUserText.label: ‘100%’

100 as hundred 

 }


 

CONSUMPTION View:

ZSAPBC_FLIGH_QUERY


@AbapCatalog.sqlViewName: ‘ZFLIGHT_QUERY’

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck:#CHECK

@VDM.viewType: #CONSUMPTION

@Analytics.query: true

@EndUserText.label: ‘Flight Query’

define view zsapbc_flight_query with parameters

     @EndUserText.label: ‘Display urrency’    

     @Consumption.defaultValue: ‘CAD’

     p_display_currency  s_currcode,

     @EndUserText.label: ‘Year 1’    

     @Consumption.defaultValue: ‘2016’    

     p_year_1: rscalyear,

     @EndUserText.label: ‘Year 2’    

     @Consumption.defaultValue: ‘2017’    

     p_year_2: rscalyear    

     as select from zsapbc_flight_cube( p_display_currency:                                                            $parameters.p_display_currency,

                                       p_year_1: $parameters.p_year_1,

                                       p_year_2: $parameters.p_year_2 )

{

@AnalyticsDetails.query.axis: #ROWS

@AnalyticsDetails.query.displayHierarchy: #ON

@Consumption.filter: { selectionType: #HIERARCHY_NODE,

                      multipleSelections: true,                                                                                                                                  mandatory: false }  

@AnalyticsDetails.query.hierarchyInitialLevel: 3

region,

@AnalyticsDetails.query.axis: #COLUMNS

seatsocc_total_year_1,   

@AnalyticsDetails.query.axis: #COLUMNS

seatsmax_total_year_1,

@AnalyticsDetails.query.axis: #COLUMNS

@AnalyticsDetails.query.decimals: 1

@EndUserText.label: ‘Seats Occ (%) Year 1′

@AnalyticsDetails.query.formula: ‘NDIV0( NODIM( seatsocc_total_year_1 ) /

                                   NODIM( seatsmax_total_year_1 ) ) *

                                   hundred’

1 as seats_occ_prc_year_1,   

@AnalyticsDetails.query.axis: #COLUMNS

seatsocc_total_year_2,   

@AnalyticsDetails.query.axis: #COLUMNS

seatsmax_total_year_2,       

@AnalyticsDetails.query.axis: #COLUMNS

@AnalyticsDetails.query.decimals: 1

@EndUserText.label: ‘Seats Occ (%) Year 2′

@AnalyticsDetails.query.formula: ‘NDIV0( NODIM( seatsocc_total_year_2 ) /

                                   NODIM( seatsmax_total_year_2 ) ) *                                            hundred’

1 as seats_occ_prc_year_2,

@AnalyticsDetails.query.axis: #COLUMNS

zsapbc_flight_cube.payment_year_1,   

@AnalyticsDetails.query.axis: #COLUMNS

zsapbc_flight_cube.payment_year_2,   

@AnalyticsDetails.query.axis: #COLUMNS

zsapbc_flight_cube.payment_disp_curr_year_1,       

@AnalyticsDetails.query.axis: #COLUMNS

zsapbc_flight_cube.payment_disp_curr_year_2,

@AnalyticsDetails.query.axis: #FREE   

connid,         

@AnalyticsDetails.query.axis: #FREE

@Consumption.filter: {selectionType: #SINGLE,

                      multipleSelections: true,

                      mandatory: false }

carrid,

@AnalyticsDetails.query.axis: #FREE

fldate,

@AnalyticsDetails.query.axis: #FREE

flmonth,

@AnalyticsDetails.query.axis: #FREE

flyear,

@AnalyticsDetails.query.axis: #FREE

currency,

@AnalyticsDetails.query.axis: #FREE

disp_curr,

@AnalyticsDetails.query.axis: #FREE

unit

}


 

CDS Views can be downloaded from here:

zsapbc_carr

zsapbc_region

zsapbc_carr_dimension

zsapbc_carr_text

zsapbc_region_dimension

zsapbc_region_hierarchy

zsapbc_flight_fact

zsapbc_flight_cube

zsapbc_flight_query

 

In second part of my blog I will challenge ABAP CDS Consumption View even more. This time  setting variable value dynamically (similar to BW OLAP Variable user-exit functionality)

Assigned tags

      40 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hello Uladzislau,

      Thanks for sharing this example. Do you know how to asign a measure to the FREE axis?

      I know this is possible to do with dimensions using @AnalyticsDetails.query.axis: #FREE (as shown in your example), so that the data does not appear in the initial view but it can be shown just by clicking on Free Characteristics.

      I have tried using the same annotation with measures but it has not worked so far.

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Fernando,

      first of all, measures (key figures in BW terms) can not be in FREE axis (free characteristics).

      The same requirement in BW I would approach by filtering Key Figure structure to only key figures I want to see at, but I do not know how to do the same in ABAP CDS yet.

      Regards, Uladzislau

       

      Author's profile photo Former Member
      Former Member

      Thank you Uladzislau, I'll keep on trying!

       

      Author's profile photo Stefan Unnebrink
      Stefan Unnebrink

      If you want to hide a key figure then you can use

      @AnalyticsDetails.query.hidden

      With this the keyfigure is hidden at the beginning, but can be switched on by a user at runtime

       

      With @Consumption.hidden

      a key figure can be switched off at all and can't be switched on by a user. This could be usefull, if the key figure should be used in formulas but the key figure itself is of no business reelvance

      Regards, Stefan

      Author's profile photo Dmitry Kuznetsov
      Dmitry Kuznetsov

      A good one, Uladzislau! I especially like the use of annotation @AnalyticsDetails.query.formula:

      In GIThub your hierarchy file contains dimension code, though 😉

      Keep on going!

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Dmitry, thank you for bringing it to my attention. Hierarchy file content fixed.

      Author's profile photo Iwan Santoso
      Iwan Santoso

      Hi Uladzislau,

      Thank you for very informative blog. I'm wondering if you have find a way to have a dynamic column name in CDS view. I came to conclusion that this is not supported (yet). Maybe you know any work around i can use. For example, the query below has dynamic column name based on the period entered in the parameter.

       

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Iwan,

      it is possible in BW because this functionality was implemented prior to Business Objects acquisition. Dynamic headers is a requirement for front-end tool such Businesses Objects whereas ABAP CDS is a back-end. Separation of duties though.

      Regards, Uladzislau

       

      Author's profile photo Former Member
      Former Member

      Hi

      Uladzislau Pralat .

      Very nice blog. I am one of the lead user of CDS view since we are on S/4 1605. I still see that there are few things missing in CDS  query (aka consumption view) functionality, like variables, value help/search help, cell level calculations etc. I want to get your view on one of the below issues that I am facing right now.

      I am trying to create use navigational attribute from a dimension view  (in your example, use currency code  from sapbc_carr_dimension  as a navigational attribute in the consumption view and can I have consumption filter in the consumption view on this navigational attribute . (I know in your case this maybe not make sense for currency code, but I am having a real life example of customer dimension having customer group as an attribute and in my fact table have customer and made the association to customer dimension in composite view . In my consumption view I want report by customer group but also have consumption filter by customer group.  (I am able to get consumption filter (value help) for customer since I made the association to customer in the composite view , but when I use the navigational attribute of this customer and want a consumption filter on such nav attribute , I am having trouble.

      Help appreciated.

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Raju,

      your only option would be to define custom CDS view the way you want.

      Regards, Uladzislau

       

      Author's profile photo Former Member
      Former Member

       

      Interesting blog, I am not very familiar with BW but still learned interesting things about CDS views and annotations.

      I am curious about the reason that you hard coded the country names in ZSAPBC_SCARR. Is this normal in BW instead of getting it from a table like SPFLI, or is this just to demo something?

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi James,

      from what I know country names are not available in Flight data model. Just extending existing data  model with additional information to demo some CDS features.

      Regards, Uladzislau

       

      Author's profile photo Former Member
      Former Member

      Uladzislau,

       

      Great blog. Very useful. Is there a way to mimic user Bex user exit using CDS views?

       

      Thanks

      Ankit

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Ankit,

      the is no exact match, but check derivation annotation.

      Regards, Uladzislau

       

      Author's profile photo santosh MAHALAHA
      santosh MAHALAHA

      could you help in guiding how to achieve below logic

      1. Go to KNC1 table, and provide the Company Code(KNC1-BUKRS), Customer(KNC1-KUNNR) and Fiscal(KNC1-GJAHR) and get the sum of all positive Balances carried forward(KNC1-UMSAV)
      2. Now repeat the same for last fiscal (Fiscal-1) and get the sum of Balances carried forward (KNC1-UMSAV)
      3. Average_Receivable = (c+d)/2
        1. From Table BSID, get sum of Amount in Local Currency (BSID-DMBTR) for the Company Code, Customers, Fiscal-1, Accounting document type(from selection screen if applicable), BSID-UMSKS=Blank, BSID-UMSKZ=Blank and BSID-SHKZD= ‘S’ (Debit).
        2. From Table BSID, get sum of Amount in Local Currency (BSID-DMBTR) for the Company Code, Customers, Fiscal-1, Accounting document type(from selection screen if applicable), BSID-UMSKS=Blank, BSID-UMSKZ=Blank and BSID-SHKZD= ‘H’ (Credit).
        • Subtract ii) from i) to get the net customer receivables
        1. Follow the similar steps with table BSAD to get the Sales_BSAD (Sales for which invoices are cleared)
        2. Annual Sales= Sales_BSID + Sales_BSAD
      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Santosh,

      I will be glad to help and answer specific questions, but unfortunately I do not have time to work on your requirements from start to end.

      Regards, Uladzislau

      Author's profile photo santosh MAHALAHA
      santosh MAHALAHA

      Thanks, As per your experience in CDS view, Could you suggest if ACDOCA table can be used for this requirement..

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Santosh,

      it is a functional question, not CDS views question. Sorry.

      Regards, Uladzislau

       

      Author's profile photo Alfonso Gonzalez Sagarra
      Alfonso Gonzalez Sagarra

       

      Dear Pralat,

       

      Thanks a lot for the Blog; it is really helpfull.

      However, I am wondering if we can't use the Bex Query designer on top of a #Cube CDS view, in order to make all these Bex artifacts, since I see that the development effort is multiplied when done with CDS, while in a Bex query it will be something quite stright forward.

      Do you know if there is any documentation about this around SAP?

      We are now starting our S4HANA migration and Embeded Analystics is a hot topic we need to define as soon as possible.

       

      Thanks a lot and best regards,

      Alfonso.

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Alfonso,

      creating Queries using CDS views might look like a complicated and time consuming task, but it is only necessary for complex queries that users can not create. All simple queries users should create themselves in Query Designer App.

      If reporting requirements are too complex for CDS Query view (for example, you need to use customer exit variables) then you can always generate DataSource from CDS Cube view and bring data to BW (virtualize with the help of Open ODS or materialize loading data in delta mode with the help of streaming Process Chain).

      There is no one fit all solution. You should know all you options and choose one that is the best fit.

      Regards, Uladzislau

       

       

      Author's profile photo Stefan Unnebrink
      Stefan Unnebrink

      Hi Uladzislau, Hi Alfonso,

      complex queries which can't be defined in CDS can be defined in the Bex-Querydesigner/BW-Modelling tools directly on top of a CDS view of datacategory CUBE or DIMENSION. The name of the infoprovider is "2C" followed by the SQL-name specified in the CUBE-view.

      But you should keep in mind that the life-cyle of BW-queries is different from the life-cycle of CDS views (e.g. transport, SAP-content, where-used, ...)

      Regards, Stefan

       

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Stefan,

      I have not tried, but I am not sure that Queries created in BW Modeling tools will be visible in S/4 HANA Query Browser.

      Regards,

      Author's profile photo Mohammed Sami
      Mohammed Sami

      Hi  Uladzislau Pralat ,

      How does Aggregation takes place in ABAP CDS views?

      I am trying to understand the following annotation @DefaultAggregation: #SUM. Just by specifying this annotation does aggregation of measure takes place against any dimension/attribute.

      Or, it is like we have to use SUM() aggregation function and group by clause in order to aggregate data for a measure against any dimension/attribute.

      Need your inputs here, please.

       

      Regards,

      Mohammed Sami

       

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Mohammed,

      @DefaultAggregation annotation defines default aggregation behavior for measures. Measures are aggregate by dimensions in drill down.

      In my query I put Region in drill down (annotated by @AnalyticsDetails.query.axis: #ROWS) it means that all measures are grouped by Region. There are other dimension that are not in drill down (annotated by @AnalyticsDetails.query.axis: #FREE) but can be put in drill down. Users might change drill down then group by will change.

      Regards, Uladzislau

      Author's profile photo Former Member
      Former Member

      Hello Uladzislau

       

      Thank you for sharing this great article.

      I do have a question though around BEX selection. Would it be possible to create a 'characteristic' selection through CDS view?

      For example, I have a "GL_ACCOUNT" characteristic which has many values (Sales Income, External Sales, Labour, Overheads, Rent etc).

       

      I'd like to create two selections (structure) in ROWS based on the GL_ACCOUNT that shows "Sales Income" and "Rent" only and I'd like to use the two selections above into a formula (I understand how to create the formula), but I just having problem with creating a structure characteristic selection through CDS view. If you could shed some light, that would be very appreciated

      Thanks in advance

       

      Kind regards

      Galvin

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Galvin,

      there no equivalent for BEX structure in CDS Views, but you still can fulfill your reporting requirements by creating 3 restricted measures as described in my blog (first for Sales Income, second Rent and third for Sales Income and Rent).

      Regards, Uladzislau

       

      Author's profile photo Former Member
      Former Member

      Hi Uladzislau

      Thanks for your reply.

      On a separate note, We are using the standard pre-delivered "embedded analytics" CDS views for one of our project. We obviously need to do few enhancements to it (adding filters, fields, etc).

      What do you think is the best practice in terms of modifying standard CDS views (do you create your own views which extends the standard delivered views? or modify directly the standard pre-delivered views?)

      There are concerns that any potential upgrade might impact the standard SAP pre-delivered views (at the same time we tried to always utilize standard delivered CDS as much as possible). I thought I'd get your input around this if you may

      Thanks again

       

      Kind Regards

      Galvin

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Galvin,

      the best practice is it to avoid modifications and make use of CDS views enhancements to preserve changes during upgrade.

      Watch following video to learn more How to enhance ABAP CDS Views in a modification-free Manner

      Regards, Uladzislau

      Author's profile photo Former Member
      Former Member

      Thank you Uladzislau

      Author's profile photo Former Member
      Former Member

       

      Hi Uladzislau

      It's me again

      I'd like to ask you about please with regard to your consumption view,

      In the below statement , how is it possible to set the "default value" of the parameter dynamically? (like a customer exit in BEX)

       

      For example, I need to set the default value of my "year' parameter into "current fiscal year" or "current fiscal period"?

       

      @Consumption.defaultValue: ‘2016’

      p_year_1: rscalyear,

      Thanks in advance again

       

      Kind regards

      Galvin

       

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Galvin,

       

      the solution is Parameter Derivation. See following blog: Trick with parameter derivation for using analytical report variants in ABAP CDS views

      Regards, Uladzislau

       

      Author's profile photo Kiran Subramaniam
      Kiran Subramaniam

      Hi Uladzislau,

      Migrating from ECC on Oracle to ECC Suite on HANA (7.5 SPS11) and need your suggestion on creating such ABAB CDS Views and consuming them via BI Clients

      PS – I was able to CDS Views but cannot see them in Analysis for Office tool . What might be missing ?

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Kiran,

      should be visible. Make sure that query name is right.

      Regards, Uladzislau

      Author's profile photo Nikhil M S
      Nikhil M S

      Hi Uladzislau Pralat ,

      I tried to create the CDS Views using the link for files from Git Hub which you provided.

      But I'm getting the Error at the ZSAPBC_FLIGHT_CUBE .

       

      All the other CDS Views got activated, the Dimension and Hierarchy Views also activated successfully. What is the reason, for the errors? I'm using the same script mentioned in the blog.

      Could you please help here?

      Thanks,

      Nikhil

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Nikhil,

      sequence of activation might be an issue.

      Regards, Uladzislau

      Author's profile photo Christian Winheller
      Christian Winheller

      Hi,

      very extensive and therefore helpful example. Thanks a lot for sharing this!

      Still trying to get the base principle so I am far from answering my following question:
      I wonder, are there ways to have hierarchies represent multiple dimensions?
      Region is based on the same, lowest level information "country".
      Would it be possible to provide a collapse/expand in RSRT on the following?

      Parent Child
        Airline
      Airline Country

      Thank you for any hint.

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Christian,

      yes, you can use multiple dimensions in CDS view hierarchy. Please refer to my blog Add Value to Your S/4 HANA Embedded Analytics Content with Material Hierarchy

      Regards, Uladzislau

       

      Author's profile photo VIPUL FEGADE
      VIPUL FEGADE

      Hi ,

      I want to show the Amount field in selection screen to filter the record for one of requirement  in Analysis for office . I am trying with below annotations but it not shows in selection screen , can you tell me whats wrong with this or any limitation on DMBTR field ,

      @Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false}
      @AnalyticsDetails.query.axis: #FREE

      AmountInCompanyCodeCurrency,

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Vipul,

      not sure that it is possible with measures.

      Regards, Uladzislau

      Author's profile photo VIPUL FEGADE
      VIPUL FEGADE

      Thank You Uladzislau for update.