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. 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”. @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, 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. @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”.