Following the example in the figure, Enablement of Hierarchies, the business user can preselect a hierarchy - for example, for a dimension field in the rows like G/L Account. This is accomplished in the Display tab. No filter selection is entered for G/L Account in the Filters tab.
When the Analytical Query is executed, the hierarchy for this field is automatically shown and expanded to the Level entered in the Display tab.
The user can set a different hierarchy of their choice at any time of execution by just selecting the G/L Account field in the Query layout header and choosing the hierarchy button as shown in the previous figure.
Note
Even without hierarchy enablement, it is possible to select a hierarchy when the Analytical Query is executed.
The parent for a measure defines the parent-child relationship in a hierarchy. The collapsed field defines whether the measure is initially collapsed or expanded.
When you execute the query, you can expand your hierarchy for measures.
Derivation functions derive context-specific values that can be used to restrict result data in Analytical Queries.
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.
For example, depending on which derivation function you use, the context can be the current date, the logged-on user, or even the language currently selected. 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 get a list of all derivation functions and their definitions, you can use the View Browser.
In the app, use Alternative Search and select the Annotation option. For the Name field, enter OBJECTMODEL.MODELINGPATTERN, and for the Value field, enter #DERIVATION_FUNCTION.
The previous figure shows the F_MYCOSTCENTERS derivation function, which derives the cost center(s) of the responsible user who executes the Analytical Query.
In the previous figure, the user S4F07-20 is assigned to cost center, TEST_DERIV, as the User Responsible. Postings on this cost center exist.
In the Filter tab of the Analytical Query configuration, the F_MYCOSTCENTERS derivation function is assigned.
When the S4F07-20 user executes the Analytical Query:
- They will find the Cost Center field as a mandatory variable field in the Filter section.
- Cost center TEST_DERIV for which user S4F07-20 is the User Responsible, will automatically be selected as the only cost center for which S4F07-20 is responsible.
When this user chooses Go, the Query layout is filled with data posted to this cost center.
If you use the drill-down functionality for the Cost Center, you can select the User Responsible to verify that the log-on user S4F07-20 really is the User Responsible for this cost center.
For a calculated measure, you can define exception aggregation.
The previous figure, Display Tab - Calculated Measure - Exception Aggregation, shows which functions for exception aggregation are available. Choose Exception Aggregation in scenarios when you do not want to show the result as a simple aggregation of all the values. Choose a field and apply the appropriate aggregate function.
You can create parameters of one of these three types - Numeric, Date, or Date function.
The previous figure, Creation of a User Input Filter, gives an example of how to set up a User Input Filter of type Numeric and where to maintain the Filter value for it.
In the previous figure, Use of a User Input Filter in a Calculated Measure, a calculated measure is set up referring in its formula to the User Input Filter created.
According to the formula, the Calculated Measure value results in a multiplication of the Actuals in EUR times the User Input Filter value.
When the query is executed, you will find the (default) User Input Filter value of 0.1 in the Filter section and could change this variable value. If you choose Go, the calculation is done at runtime and the result is shown in the Calculated Measure column.
Using the Converted Measure feature, you can run a currency translation at runtime.
Note
Accounting in SAP S/4HANA on Premise already stores data in parallel in ten currencies, so the question is whether a further currency translation at runtime is needed.If you have the Accounting Document Number Field in your custom CDS view as well as in your Analytical Query, but you do not execute a drill-down to the Accounting Document Number, data is aggregated and the currency translation works with aggregated data.
If you have the Accounting Document Number Field in your custom CDS view as well as in your Analytical Query and you execute a drill-down to the Accounting Document Number, the currency translation is executed for each single set of data.
The performance of currency translation depends on whether you aggregate data or not:
- If every single data set has to be translated, the currency translation costs performance.
- If you work with aggregated data, the currency translation is not performance relevant.
You can use Add Counter to retrieve a distinct count of the combination of dimensions. The list of dimensions can vary based on the data source from which it is derived.
The previous figure, Add Counter 2/2, shows the posted combinations of Customer-Division-Material.
When you define two structures (one in the rows, one in the columns), which both contain formulas, it is unclear to the system how to calculate the formulas at the point where both formulas intersect.
In this example, there are two rows and two columns with simple values, the third row is a simple summation formula and the third column is a simple multiplication.
In the cell in which the row and column formulas meet, it is not clear which calculation should be made.
If you calculate according to the column formula in this cell, the cell contains (A + C) * (B + D).
If you calculate according to the rows formula in this cell, the cell contains (A * B) + (C * D).
The result gives a different value.
If a formula collision occurs, as described in the example, you can determine the formula that has to be used in the calculation by creating a structure using the Characteristic Structure Element functionality.
There are two types of structure elements:
- Restricted Structure Elements
- Calculated Structure Elements
The previous figure, Formula Collision and Characteristic Structure Elements 3/6, shows a Characteristic Structure positioned in the rows with four structure elements. Two of them are restricted structure elements, the other two are calculated structure elements.
When you set up a restricted structure element like Actuals in EUR, you will find the Collision Handling section.
When you set up a calculated structure element like Deviation Absolute in EUR, you will also find the Collision Handling section.
In the Collision Handling section of the characteristic structure element, you can set three collision specific fields:
- Formula Collision
- Decimal Collision
- Scaling Collision
For example, when there is a collision between two structures that contain formulas, you can specify the formula that is going to be used in the calculation.
You can set either of these three values:
- Default: The formula that was first created takes priority by default
- This: The result of this formula has priority in a collision
- Concurrent: The result of the other formula takes priority in a collision
The previous figure, Dynamic Labels 1/2, describes how you can create dynamic labels - for example, when you work with restricted measures.
Within the restricted measure, you first have to create a User Input Filter (variable) for the restricting dimension.
In our scenario, the CATEGORY_VARIABLE was created for the restricting dimension Planning Category.
When you choose Create Dynamic Label within your restricted measure, the Dynamic Label Editor opens and you can enter a label text and add the User Input Filter (CATEGORY_VARIABLE), which can be selected using the User Input Filter button.
On the Filters tab of the query designer, you can select a default variable value for your User Input Filter (CATEGORY_VARIABLE).
When you open the Query Preview, you will find the User Input Filter:
- In the Filter section, you can keep or change the default value
- In the Column, insert Plan Data in EUR - Category …. as the Header Label text of this column
With the help of the Runtime Extensibility for an Analytical Query, the business user is enabled to add all additional dimensions and all measures from the underlying CDS View data source to the Analytical query in the Web Dynpro app that had not been selected in the Analytical Query.
This offers the following advantages:
- The queries don't increase in size, which improves system performance
- There is no need for transport changes because they are done directly in the production system
- Business users can enhance queries very flexibly in the production system
Note
If needed, you can exclude a dimension or measure from the Runtime Extensibility. This is done in the CDS View definition, that is, in the Custom CDS Views app in the Element Properties tab.