Analytical Queries based on CDS are a powerful way to model analytical data sources that can be used in a variety of user interfaces (SAP Analytics Cloud, Analysis for Office, Smart Business KPIs, Web Dynpro Grid, …). Just by reading the technical documentation it is not always easy to transform the business use case into actual coding. Read this blog post in case you are interested to get a rather use case driven access to Analytical Query Design.
(all coding samples are based on CDS Analytical Projection Views - but they should work as well for CDS View (Entities) with slight adoptions )
Contents:
- Use Case Description
- Used Syntax Elements
- Sample Query
- Runtime Preview
Use Case Description
For many business-related reports, it can be advantageous to tailor a report to the context of the user who executes the report. With derivation functions, you can derive context-specific values to restrict data in CDS queries. Here are some examples of common derivation functions:
- Time-related functions, such as Today or Current Fiscal Period
- Responsibility-related functions, such as My Cost Centers
- Business semantic-related functions, such as Leading Ledger
To make it concrete - lets define a query that selects the following Finance Data:
- Only data of the leading ledger
- Data of the rolling year
- All revenue postings based on Semantic Tags
Used Syntax Elements
- Annotation: "@AnalyticsDetails.variable:"
In general, a CDS parameter represents a single value which has to be determined at runtime either by user input or derivation. But in Analytics, this concept is too strict. There is the need for multiple values, for intervals, for hierarchy-nodes. In some cases the input should be optional. This can be achieved with the AnalyticsDetails.variable annotations. If these annotations are used, the ODATA.publish: true is not supported.
- Annotation: "@Consumption.derivation:"
This annotation can be used to select values from another CDS view. This can either be a single value, interval, list or a hierarchy. The values can then be used inside the query for a global selection or a measure. In combination with the annotation "@Consumption.filter.hidden: true", this leads to a dynamic filter at runtime without user interaction.
- CASE WHEN THEN END AS
Standard way to define restricted measures.
Sample Query
@EndUserText.label: 'Simple Derivation Query'
@AccessControl.authorizationCheck: #NOT_ALLOWED
define transient view entity ZJF_C_Derivation_Q01
provider contract analytical_query
with parameters
@Consumption.hidden :true
@AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'PostingDate', mandatory: true, selectionType: #INTERVAL, multipleSelections: false }
@Consumption.derivation: {
lookupEntity: 'F_FsclDteFuncRngeVal',
resultElement: 'DateFunctionStartDate',
resultElementHigh: 'DateFunctionEndDate',
binding : [ { targetParameter : 'P_DateFunction', type: #CONSTANT, value: 'ROLLINGFISCALYEARCLOSED' },
{ targetParameter : 'P_FiscalYearVariant', type: #CONSTANT, value: 'K4' } ] }
P_RollingYear : fis_budat,
@AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'GLAccount', mandatory: true, selectionType: #SINGLE, multipleSelections: true }
@Consumption.hidden :true
@Consumption.derivation: {
lookupEntity: 'F_GLACCOUNTBYSEMANTICTAG',
resultElement: 'GLAccount',
binding : [ { targetParameter : 'P_GLAccountHierarchy', type: #CONSTANT, value: 'FPA1' },
{ targetParameter : 'P_SemanticTag', type: #CONSTANT, value: 'RECO_REV' } ] }
P_Revenue : fis_racct,
@AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'Ledger', mandatory: true, selectionType: #SINGLE, multipleSelections: false }
@Consumption.hidden :true
@Consumption.derivation: {
lookupEntity: 'F_LeadingLedger',
resultElement: 'Ledger'}
P_Ledger : fins_ledger
as projection on I_GLAccountLineItem
{
@AnalyticsDetails.query.axis: #ROWS
@UI.textArrangement: #TEXT_LAST
CompanyCode,
@AnalyticsDetails.query.axis: #FREE
GlobalCurrency,
@AnalyticsDetails.query.axis: #FREE
FiscalYearPeriod,
@AnalyticsDetails.query.axis: #FREE
FiscalYearVariant,
@AnalyticsDetails.query.axis: #FREE
GLAccount,
@AnalyticsDetails.query.axis: #COLUMNS
@Semantics.amount.currencyCode: 'GlobalCurrency'
@Aggregation.default: #SUM
@EndUserText.label: 'Revenue'
case
when ( PostingDate = $parameters.P_RollingYear )
and ( GLAccount = $parameters.P_Revenue ) then curr_to_decfloat_amount( AmountInGlobalCurrency )
end as RevenueRollingYear
}
where Ledger = $parameters.P_Ledger
and FiscalYearVariant = 'K4'
Runtime Preview
This preview is done with the Web Dynpro Data Grid that I started via the backend transaction RSRT with the technical query name "2CZJF_C_Derivation_Q01".
Data Analysis
Here you can see the initial drill-down of the sample query with Company Code in the rows.
Data Analysis
Query Information
In the query information you can see which values have been derived for the defined variables.
Query Information
Background regarding (Fiscal) Date Functions
Date Functions and Fiscal Date Functions are a strong concept to filter dates based on predefined Date Function Configurations. As the names indicate two different calendars are supported: Gregorian and Fiscal Calendar. Whereas the Fiscal Date Functions depend on the Fiscal Year Variant in addition. Based on the current date and the configuration any kind of single date or date interval can be calculated (e.g.:
Last Day of Previous Month,
Current Fiscal Quarter). With the application Manage Date Functions it is easily possible to define own date functions.
- For the Gregorian Calendar the CDS View "C_SGLGREGORIANCALDATEFUNCTION" can be used in a "@Consumption.Derivation".
- For the Fiscal Calendar you find the relevant CDS Views under the link "Derivation Functions for Finance"
Links
The following sources provide further information: