Skip to Content

Date Function for Fiori Smart Business app

By using Data Function, it is possible to filter date dynamically in Fiori Smart Business Apps. It is important especially for Smart Business KPI tiles as they don’t   have value prompt screens associated with them. The purpose of this blog is to explain how Date Functions work and how they can be created.

 

My motivation to write this blog

Firstly, In my observation, there are no good documents or sites on Date Function for Fiori Smart Business app.

Second, dynamic filtering on date attribute is typical requirement in Analytical apps, and this Date Function can be used not only for Smart Business app, but all CDS Views, and which should help value and productivity.

Finally, I know the typical request to filter on Fiscal Year/Period but it is not possible to filter with Date Function on S/4HANA1709, so I think it is necessary to provide the workaround. In addition, it also provide the way to filter dynamically based on the parameter value, which should also be the typical requirement especially as FLP encourage to use Default User Value.

 

Summary

  • It is possible to filter a date dynamically with Date Function. For example, In Fiori predefined app Purchase Order Value , the data is filtered with “Previous year to date” dynamically by default, so, when it is executed on 20 Sep 2018, the date is filtered with the interval from 2017-01-01 to 2018-09-20 dynamically in this KPI tile.
  • In Many KPI Tiles, data is filtered dynamically with a Date Function value. The Date Function value can be changed in the Evaluation of the KPI tile or when launching the tile. For example, YEARTODATE (from 2018-01-01 to 2018-09-20 when running on 20 Sep 2018) can be changed to MONTHTODATE (From 2018-09-01 to 2018-09-20). The Date Function value can be set as the parameter value, so the CDS Views used in those KPI Tiles have the parameter for Date Function (P_DateFunction), e.g. C_SUPPLIEREVALUATION in Operational Supplier Evaluation, C_TIMEVARIANCE_CDS in Supplier Evaluation by Time. In other word, dynamic date filtering with Date Function is possible only for the CDS View which has the parameter P_DateFunction and parameters for “Start Date”(P_StartDate) and Date To(P_EndDate)”.
  • In addition, it is possible to create custom a Date Function value using the Fiori tool “Date Function”. For example, it is possible to create the Data Function Value “Last 180 days” (From “today-180” to “today”).
  • Only for On Premise: If dynamic filtering is needed for a Custom CDS View, or the logic to derive filtering values is too complex (e.g. derive start date of the Fiscal Year), custom consumption derivation logic will be required to be coded in the CDS View. In the case, the Lookup Entry C_SglGregorianCalDateFunction used in Date Function might be available, and I_FiscalCalendarDate might help as well. This blog provides the example.
    • the sample source shows how to get the value of User Default Value using the predefined VDM “I_UserParameter_F2200” (although it is not the main topic of this blog).
  • Only for On Premise: The CDS View C_SglGregorianCalDateFunction, used in Date Function, can be used to derive start and end date value for any CDS Views, not only for the one for KPI Tile.

 

Example of Date Function used in the predefined KPI Tile

Filtered in the KPI tile:

Run the Fiori app “Purchase Order Value” (KPI Tile app).

When opening the filtered parameters/dimensions, “Rel Date Functions” (=P_DateFunction) is used and “PREVIOUSYEARTODATE” is set as the parameter value.

The date from 2017-01 to 2018-08 is displayed (filtered as expected).

When setting “YEARTODATE” in the parameter “Rel Date Function”, the date from 2018-01 to 2018-09 is displayed (filtered as expected).

 

Setting:

Definition of the KPI tile

Launch “KPI Workspace” in KPI Design Group (the role “SAP_BR_ANALYTICS_SPECIALIST” should be assigned to the user), and search with “Purchase Order Value”. Then, open the Evaluation. You can find “PREVIOUSYEARTODATE“ is set as the parameter value for “Rel Date Function”.

 

 

CDS View:

Predefined VDM “C_PURORDVALUEWITHPLND” is used. Open the CDS View with ADT. In the source of the CDS View, the parameter P_DateFunction, P_StartDate and P_EndDate are defined, and the values of P_StartDate and P_EndDate are derived from Lookup Entry C_SglGregorianCalDateFunction using the value of P_DateFunction as Target Parameter. P_StartDate and P_EndDate are used as the parameter values after FROM clause.

 

define view C_PurOrdValueWithPlnd

with parameters

P_DisplayCurrency           : displaycurrency,

 

@Consumption.hidden : true

@Environment.systemField : #SYSTEM_LANGUAGE

P_Language                  : sylangu,

 

P_DateFunction : datefunctionid,

   

    @Consumption.derivation: { lookupEntity: ‘C_SglGregorianCalDateFunction’,

    resultElement: ‘DateFunctionStartDate’, binding: [ {

      targetParameter: ‘P_DateFunction’, type: #PARAMETER, value : ‘P_DateFunction’ },

       { targetParameter : ‘P_Language’ , type : #SYSTEM_FIELD, value : ‘#SYSTEM_LANGUAGE’  } ]

    }

    @Consumption.hidden: true

    P_StartDate: /srmsmc/puc_spend_valid_from ,

   

    @Consumption.derivation: { lookupEntity: ‘C_SglGregorianCalDateFunction’,

    resultElement: ‘DateFunctionEndDate’, binding: [ {

      targetParameter: ‘P_DateFunction’, type: #PARAMETER, value : ‘P_DateFunction’ },

       { targetParameter : ‘P_Language’ , type : #SYSTEM_FIELD, value : ‘#SYSTEM_LANGUAGE’  } ]

    }

    @Consumption.hidden: true

    P_EndDate: /srmsmc/puc_spend_valid_to

 

as select from    P_PurgSpendActlFutr(  P_DisplayCurrency:$parameters.P_DisplayCurrency,

P_StartDate : $parameters.P_StartDate,

P_EndDate : $parameters.P_EndDate  )  as ActualSpend

……

 

 

Below is the result of the C_GregorianCalDateFunction, the source of the Lookup Entry C_SglGregorianCalDateFunction. “Start Date” and “End Date” are set for each value of “DateFunction” in this view. The Date Functions values not starting with Z, YZ, YY are predefined ones, e.g. YEARTODATE, YESTERDAY.

 

Create Date Function value with “Date Function” app

Open “Date Function” tile. (Role “SAP_BR_ANALYTICS_SPECIALIST” should be assigned to the user)

Predefined/Created Date Function values are displayed.

Create new Date Function Value.

Click “+”.

In the following case, the new Date Function value to filter last 60 days is created.

Parameter Value
Date Function ID ZZLAST60DAYS
Date Function Name Last 60 days
Date Function Type Range
Relative Date Type Relative to Base Date
Base Date Today
Relative Date 60 Day(s) Ago

 

 

After setting parameter values, It is possible to run Simulation by pushing “Simulation” button.

 

Push “Save” in the bottom right of the screen.

 

“ZZLAST60DAYS” is created.

 

Data Preview of the CDS View “C_GregorianCalDateFunction” in ADT.

“ZZLAST60DAYS” is added.

 

Once created as per above, tt can now be used in a KPI tile.

In the Fiori app “Purchase Order Value” (KPI Tile app), set “ZZLAST60DAYS” as the parameter value of “Rel Date Function”.

 

The chart is changed.

In the tabular view, only the months of the selected period are included in the result (as expected).

 

(Custom Date Function Values)

The following settings are possible to create custom Date Function Values.

Single:

 

Range:

When “Relevant to Base Date” is selected, the range can be set based on the Base Date and the period before/after the base date. In the following setting, the base date is ”Last day of previous month in current year” (End Date) and 4 weeks ago from base date is set to Start Date.

 

When “Configured” is selected in “Relative Date Type”, Start Date and End Date are set.

2nd field: Options for relation to the date.

3rd field: Options for time dimension.

4th field: Options for year.

 

Dynamic filtering without Date Function

Only for On Premise: For Custom CDS View which don’t t have P_DateFunction, Date Function cannot be used, and there might be  cases that the logic to derive start and end date is so complex that Date Function cannot be used. Even in such cases, dynamic date filtering is possible by using Lookup Entry in the Custom CDS View.

Filtering date in custom CDS View, “C_GregorianCalDateFunction” could be used as the Lookup Entry as in the sample code of “Consumption View: ZC_Sales” in the blog.

@Consumption: {

filter:     {selectionType:     #INTERVAL},

derivation: {

lookupEntity: ‘C_SglGregorianCalDateFunction’,

resultElement: ‘DateFunctionStartDate’, binding: [

{ targetParameter: ‘P_DateFunction’, type: #CONSTANT, value : ‘YEARTODATE’ },

{ targetParameter : ‘P_Language’ , type : #SYSTEM_FIELD, value : ‘#SYSTEM_LANGUAGE’  } ],

resultElementHigh: ‘DateFunctionEndDate’ }

}

SalesOrderDate,

 

  • Please note that the status of C_SglGregorianCalDateFunction is “Not Released.” In S/4HANA1709 (See “Not released” predefined VDM” in the blog).

 

Below is the example to filter the date with the interval from Start date of the current Fiscal Year and the day to run the query (System date). In this case, the custom CDS View “ZC_PurOrdValueWithPlnd” is created by copying the Predefined VDM “C_PurOrdValueWithPlnd”), and start date and end date are read from “I_FiscalCalendarDate”. Then Fiscal year variant has to be used as the key. For getting Fiscal Year Variant, at first, User Default Value of Controlling Area set in FLP is read from the predefined VDM “I_UserParameter_F2200” in which USR05 is used as the source, and Fiscal Year Variant is read from the predefined VDM “I_ControllingArea” using the Controlling Area value gotten in the previous step.

User Default Value can be set in FLP. Go to “Settings” in Me Area.

Select Default Value, and set the default value of CO Area.

In Parameters tab in the Transaction SU01 (User Maintenance), the value set in FLP is saved. It is stored in the table USR05, and the VDM “I_UserParameter_F2200” uses it as the source.

  • In this way, any parameter values set as Default Value can be used as the parameter value in CDS View.

@AbapCatalog.sqlViewName: ‘ZCPOVALPLNDVSACT’

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@AccessControl.personalData.blocking: #NOT_REQUIRED

@EndUserText.label: ‘Purchase Order Value with Planned Spend’

@VDM.viewType: #CONSUMPTION

@OData.publish: true

@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel.usageType.dataClass: #MIXED

@ObjectModel.usageType.serviceQuality: #D

@ObjectModel.usageType.sizeCategory:  #L

 

define view ZC_PurOrdValueWithPlnd

with parameters

P_DisplayCurrency           : displaycurrency,

 

@Consumption.hidden : true

@Environment.systemField : #SYSTEM_LANGUAGE

P_Language                  : sylangu,

 

@Consumption.hidden: true

    @Consumption.derivation: { lookupEntity: ‘I_UserParameter_F2200’,

    resultElement: ‘UserParameterValue’, binding: [

    { targetElement : ‘UserParameter’ , type : #CONSTANT, value : ‘CAC’ } ]

    }

    P_COArea: kokrs ,

 

    @Consumption.hidden: true

    @Consumption.derivation: { lookupEntity: ‘I_ControllingArea’,

    resultElement: ‘FiscalYearVariant’, binding: [

    { targetElement : ‘ControllingArea’ , type : #PARAMETER, value : ‘P_COArea’ } ]

    }

    P_FYVariant: periv ,

 

    @Consumption.hidden: true

    @Consumption.derivation: { lookupEntity: ‘I_FiscalCalendarDate’,

    resultElement: ‘FiscalYearStartDate’, binding: [

    { targetElement : ‘CalendarDate’ , type : #SYSTEM_FIELD, value : ‘#SYSTEM_DATE’ }

    ,    { targetElement : ‘FiscalYearVariant’ , type : #PARAMETER, value : ‘P_FYVariant’ }]

    }

    P_StartDate: /srmsmc/puc_spend_valid_from ,

   

    @Consumption.hidden : true

    @Environment.systemField : #SYSTEM_DATE

    P_EndDate: /srmsmc/puc_spend_valid_to   

as select from    P_PurgSpendActlFutr(  P_DisplayCurrency:$parameters.P_DisplayCurrency,

P_StartDate : $parameters.P_StartDate,

P_EndDate : $parameters.P_EndDate  )  as ActualSpend

 

left outer join I_PurchasingCategoryMatlGroup   as PurchasingCategoryMatlGroup on ActualSpend.MaterialGroup = PurchasingCategoryMatlGroup.MaterialGroup

….

(the rest is omitted)

 

Closing Comments:

Supposed scenario to use Date Function is to change the filtering period in the predefined Fiori KPI Tiles. But in my personal opinion, it is more cases that custom CDS View is created to create their custom KPI Tile. In the case, I don’t like to use the parameter P_DateFunction but want to have P_StartDate and P_EndDate and set the default value using Lookup Entry in the CDS View, as it should be more user friendly to enter start date or end date than to enter e.g. “YEARTODATE” or “YESTERDAY”.

But even in the case, it should save effort to use “C_SglGregorianCalDateFunction” as the Lookup Entry to derive start date and end date, and so, the “Date Function” tool should be helpful to create custom Date Function Values. In addition, this scenario should help for any CDS Views for any apps, e.g. Analytic Query of CDS View for Multidimensional Reporting. I personally think it is more important value of “Date Function”.

 

Enjoy Analytics!

 

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