Static Values
It is often helpful to the business user if the prompts that appear before the query is executed are already filled with useful, default values. This is especially true if the business user never changes these values.
It is possible to define default values for variables in the analytical query definition. The default values for variables can be static, or they can be derived dynamically at query run-time.
Let's first review the code used to implement a static value for a variable. In our case we are providing two fixed values to fill an interval selection type:
In our example, the variable will not appear to the business user as a prompt, so they are not able to adjust the default values. This is because we have included the annotation @Consumption.hidden: true. However, the important thing to remember is that the interval variable is filled with the default values.
Here is what the result using our code:
Note
You might wonder why you would create a static-value variable that is hidden instead of using hard-coded filters for the dimension values. A use case might be that you want to fill a parameter once with a static value and reuse the parameter in multiple places throughout the code. Using a parameter means you would only need to change the static value once instead of having to change all filter values. This is not seen by the business user, but it helps to maintain efficient code.If you want to show the prompt so that the business user is able to see the variable values, or perhaps they might want to change the values, then do not include the annotation: @Consumption.hidden: true or use the value false for this annotation.
Deriving the Values of Variables
Instead of implementing static variable values, we can also automatically derive the default variable values and present these values to the business user, or hide them. This approach is often used to derive dates using the built-in date functions.
Let's take a look at the code used to implement a date-based derivation for a variable:
In this example, we want to return all flights between two provided dates.
First, the business user is prompted to enter a to date, and to fill the parameter P_FlightDate. Notice that we have provided a static default value of 20240909 for this parameter. We could also have derived the default value, for example to fill the parameter with the current date.
The date the business user enters is then used to derive the value of the second parameter P_FirstDayOfMonth which provides the from date.
To understand the derivation, notice the annotation @Consumption.derivation that reads the standard SAP-provided CDS view I_CalendarDate to provide a column FirstDayOfMonthDate. The CDS view I_CalendarDate requires a binding of a date which is used to select the required record. You can see this in the binding annotation binding: that uses the first parameter P_FlightDate to select a record from the CDS view using the column CalendarDate. The result column FirstDayOfMonthDate is then returned and fills the parameter P_FirstDayOfMonth.
Notice the from parameter in our code is set to hidden. If you prefer to show this variable to the business user, then do not use this setting.
In the example above, the user has overridden the default value of 09/09/2024 and entered 10/02/2024 as the to date. The from date is then derived as 10/01/2024 (first day of month). The variable values are then used in the where clause so that the analytical query displays only the flights between these dates.
Be aware, derivations for non-hidden variables are always processed before the variables are prompted. However, the user can overwrite the derived default values. Derivations for hidden variables are calculated after all the dependent variables (in our case P_FlightDate) have had their values entered.
In our case, the second variable P_FirstDayOfMonth is calculated after the first variable P_FlightDate has been entered (or the default value is kept). Each time we change the variable P_FlightDate the derivation of P_FirstDayOfMonth is re-calculated.
However, if we made the second variable P_FirstDayOfMonth also visible, the business user would then see two variable prompts. Then, the derivation of P_FirstDayOfMonth would be calculated only once and this would be before the variable prompts appear. The default value of P_FlightDate would be used. Both variable values can be changed by the user afterward but the derivation for P_FirstDayOfMonth is not performed again. This means that we provide to the business user the option to stay with the default derived value, or they can overwrite the derived value. If the derived value should not be changed then you should hide the variable P_FirstDayOfMonth.
In our case, if we chose to show the variable P_FirstDayOfMonth it would provide the flexibility for the business user to either use the default derived value, or change it.