Skip to Content

Hi!

In the post post I would like to consider how to model time-dependent attributes and language dependent texts in ABAP CDS views.

I am going to use Sales Model Example from previous blog and a little bit modify it.

1. Sales Model Tables

1.1. Table of sales transactions. It contains time characteristics, sales manager, customer and amount. Number of records is 133 848.

1.2. Sales managers description table contains language dependent texts. Number of records is 9.

1.3. Master data table with time-dependent bonus attribute. The bonus could be changed according to results demonstrated by sales manager. Bonus value is measured in percentage.

2. ABAP CDS views

2.1. Sales data transactional view:

@AbapCatalog.sqlViewName: 'XV_Sales'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Performance test'
@VDM.viewType: #BASIC
define view XV_I_Sales as select from sales002 as t1 {
  
  key t1.salesrep,
  key t1.customer,
  key t1.calyear,
  key t1.calmonth2,
   @DefaultAggregation: #SUM
   t1.amount
}

2.2. Sales managers texts view:

@AbapCatalog.sqlViewName: 'XV_IT_SALESREP'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Salesrep'
@VDM: { viewType: #BASIC }
@ObjectModel: { dataCategory: #TEXT }
define view XVITSALESREP as select from salesrep {
   @ObjectModel.text: {
       element: [ 'text' ]
   }
   key salesrep, 
   @Semantics: {language: true }
   key spras, 
   @Semantics: { text: true }   
   text
}

2.3. Sales managers dimension view with bonus attribute:

@AbapCatalog.sqlViewName: 'XV_IAT_SALESREP'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Time-Dependent Attributes'
@VDM: { viewType: #BASIC }
@Analytics.dataCategory: #DIMENSION
@ObjectModel.representativeKey: 'salesrep'
define view XvIatSalesrep as select from salesrepat
association[0..*] to XVITSALESREP on salesrepat.salesrep = XVITSALESREP.salesrep
 {  
   @ObjectModel: {
       text: {
          association: 'XVITSALESREP'
       }
   }  
    key salesrep,
    @Semantics.businessDate.to: true 
    key dateto,
    @Semantics.businessDate.from: true
    datefrom,
    bonus,
    XVITSALESREP
}

2.4. Sales cube view:

@AbapCatalog.sqlViewName: 'XV_C_Sales'
@AbapCatalog.compiler.compareFilter: true
@Analytics: {
    dataCategory: #CUBE
}
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Performance test consumption'
@VDM.viewType: #COMPOSITE
define view XVCSales as select from XV_I_Sales 
association[0..*] to XvIatSalesrep on XV_I_Sales.salesrep = XvIatSalesrep.salesrep
 
{
@ObjectModel: { foreignKey: {
    association: 'XvIatSalesrep'
}}
    key salesrep,
    key customer,
    key calyear,
    key calmonth2,
    @DefaultAggregation: #SUM
    amount,
    XvIatSalesrep 
}

2.5. Simple analytical query with sales managers and their bonus in rows and sales values by years in columns:

@AbapCatalog.sqlViewName: 'XV_Q_SalesA'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Performance test QUERY'
@VDM.viewType: #CONSUMPTION
@Analytics.query: true

define view XVQSALESA as select from XVCSales  
 {
  @AnalyticsDetails.query.axis: #ROWS
  @AnalyticsDetails.query.display: #KEY_TEXT
  salesrep,
   
  @AnalyticsDetails.query.axis: #ROWS
  XVCSales.XvIatSalesrep.bonus,
  
  @Consumption: { filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false, hidden: false }}
  @AnalyticsDetails.query.axis: #COLUMNS 
  calyear,
  
  @AnalyticsDetails.query.axis: #COLUMNS
  amount
}

3. Launch query in RSRT

3.1. Enter analytical query

3.2. Execute with the following debug options

3.3. Enter variables. Restrict year

3.4. Get generated SQL statements and find in code automatic restrictions by session language and system date.

/* Statement Preview (might be truncated) */
SELECT
 A1~CALYEAR AS K____256
, A1~SALESREP AS K____281
, COUNT( * ) AS Z____151_SUM
, SUM(  A1~AMOUNT  ) AS Z____259_SUM
FROM
XVCSALES AS A1
WHERE
   ( ( ( (
A1~CALYEAR
BETWEEN '2016'
AND '2017'
 ) ) ) )
GROUP BY
A1~CALYEAR
,A1~SALESREP
ORDER BY
 K____256
, K____281
%_HINTS 'NO_RESULT_CACHE'

Automatic filtration by session language:

SELECT SALESREP AS KEY_0001, TEXT AS TXTLG FROM XV_IT_SALESREP WHERE SPRAS = 'E' GROUP BY SALESREP, TEXT

Automatic filtration by system date:

SELECT SALESREP AS KEY_0001, BONUS AS ATR_0002 FROM XV_IAT_SALESREP WHERE DATEFROM <= '20170925' AND DATETO >= '20170925' GROUP BY SALESREP, BONUS

3.5. Result data set:

4. Manual enter of key date

4.1. To manually select key date we could use the following approach in analytical query:

@AbapCatalog.sqlViewName: 'XV_Q_SalesA'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Performance test QUERY'
@VDM.viewType: #CONSUMPTION
@Analytics.query: true

define view XVQSALESA 
  with parameters
    @Consumption.hidden: false
    @Environment.systemField: #SYSTEM_DATE 
    @EndUserText.label: 'Key Date'
    p_keydate: abap.dats
        
  as select from XVCSales  

 {
  @AnalyticsDetails.query.axis: #ROWS
  salesrep,
   
  @AnalyticsDetails.query.axis: #ROWS
  XVCSales.XvIatSalesrep[1:datefrom >= $parameters.p_keydate and dateto <= $parameters.p_keydate].bonus,
  
  @Consumption: { filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false, hidden: false }}
  @AnalyticsDetails.query.axis: #COLUMNS 
  calyear,
  
  @AnalyticsDetails.query.axis: #COLUMNS
  amount
}

4.2. Launch query in RSRT and enter variables values:

Notice new key date variable. It was filled by default by system date value, but I changed the value to ‘02.09.2016’. At selected date we have different values of bonus attribute.

4.3. Result data set:

Result

In the post we considered time-dependent master data and language-dependent texts in ABAP CDS views.

 

Thank you for attention!

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply