Datasphere - Populating Dynamic Default Values in Analytical Model Variables

[Vajrakanth_Siddu]

Populating dynamic default values in report variables without user entry is a powerful feature in reporting tools that allows the default value for a variable to be determined at runtime based on certain conditions or logic, rather than being fixed or manually specified by the report designer.

In SAP BW/4HANA, dynamic default values can be set using customer exit variables. The I_STEP variable helps define which stage of processing the exit is at, such as initialisation, selection, or validation. Typically, when I_STEP = 1, the exit is in the initialisation stage, where you can dynamically set default values based on custom ABAP code, but how do we achieve the same results in SAP Datasphere? 

In this blog, I will walk through the process of populating dynamic default values in SAP Datasphere Analytical Model variables without requiring user entry, using a combination of built-in functionality and SQL Script.

In this example, I will explain the process of setting the previous fiscal year period by default in the FISCPER analytical model standard variable.

Step-by-Step Guide: Assigning the Previous Fiscal Year Period to the FISCPER Variable dynamically.

Step 1: Create a table function with the required logic to define the dynamic variable value. This table function will be triggered in real-time during the execution of the analytical model in Datasphere.

Sample SQL Script:

Step 2:  Create a fiscal year period Variable in Analytical Model and select the Dynamic Default in Value Definition section. Note: I have used standard variable in this example and it is mapped to an input parameter so the default value can be passed to input parameter of downstream models. You can also user filter variables at analytical model level if you don't need input parameter at downstream models.

Step 3 :  Select your table function : Default Value Table in Lookup Entity Field and select FISCPER in Result Column Field.

Step 4:  Deploy the Analytical Model and preview the data.  Here you can see previous fiscal period 2024010 as a default value which is generated by the table function.

Step 5:  Create a SAC Story based on above analytical model, system will populate the FISCPER variable with the default value 2024010 which is generated by the table function.

This functionality can be implemeneted in below variables:

1. Standard variable
2. Filter Variable
3. Restricted Measure Variable 
4. Reference Date Variable.

It's a simple functionality but a powerful feature in Datasphere.

[Lora0121]

Hello, @Vajrakanth_Siddu 

I am trying to create a table that contains the current year to define a dynamic variable for data filtering in the AM Model.

I have used the following features:
 - Data Builder
 - New SQL View
 - SQLScript (Table Function)

I wrote the SQL statement as follows.

DECLARE T_RETURN TABLE (
    FISCPER NVARCHAR(7)
);

However, I am getting the following error.

sql syntax error: incorrect syntax near ",": line : line 2 col col 18 (at pos 60), Code: 257, SQL State: HY000

I didn't use a comma.
Any idea what could be causing this?

Any help would be welcome.

[Vajrakanth_Siddu]

Hello,

Your table function is incomplete and missing the necessary "RETURN SELECT ... FROM Table" statement, which is why you're encountering that error. Please complete the function and retest it. Please look at the below example to get an idea.

[tom345]

Hello @Vajrakanth_Siddu,

I am trying to create a table that contains the current date and the two years ago date to define a dynamic filter (Interval) for data filtering in the AM Model.

I have used the following features:
 - Data Builder
 - New SQL View

The Temporal view contanis two date, the current date and the two years ago date, as two different rows:

SELECT CURRENT_DATE AS date_value FROM "1TL_SD_DUMMYTABLE_001"
UNION ALL
SELECT (ADD_YEARS(CURRENT_DATE, -2)) AS date_value2 FROM "1TL_SD_DUMMYTABLE_001";

And I want to use it in a filter variable on the "Billing Date":

But in the preview of the AM, it looks like this:

So I'm trying to create a dynamic filter, which takes the two dates and use them as an interval.

Any help would be welcome.

[Nitin_Patni]

@tom345 as of now Default value can be derived only for a Single value

[NicolasMartinez]

As @Nitin_Patni wrote, intervals are not yet possible in derived values.

A way to work around this:
In your temporal view, you could have two colums, one for your current date and one for the date two years ago.

In your AM you then have to use two filter variables on the billing date, one for start and one for end.

[jwschwab]

I've followed the recipe of Vajrakanth and defined a SQL Script (Table Function) which returns one row with the last day of last month (required for currency conversion). This delivers one row with the proper date.

The column was defined in the View:

We have an Analytic Model with a standard variable for the "Reference Date". This should be populated with the date above as a default value (which can be overwritten by the user):

When i now open the Analytic Model, the query variable window pops up, but the Reference Field is not poplated properly:

Has this function been deactivated or changed ? It worked pretty fine in the last two months. It seems to work pretty fine for Target Currency and Exchange Rate Type, but failds on the Reference Date.

Side note: the documentation of the SQL Script functionality for Datasphere is incomprehensive, only a subset of the "normal" HANA functionality is supported. Does anybody has a link to a note or documentation for this topic ?
Thanks in advance. 

 

[stuzer]

Same issue as @jwschwab . The date field is not filled.

[Vajrakanth_Siddu]

@jwschwab,

Following the SAP Datasphere upgrade on April 11th, we've also identified that the default value functionality for variables is no longer working as expected. This change is currently impacting our reports as well. We raised a customer message to SAP regarding this issue.

[NicolasMartinez]

This seams to be a bug.

The following note was released, saying this error will be corrected with release 2025.12:

3598444 - The default variable value is not displaying in the Analytic Model's Preview 

It should still work in SAC.