24/9/2023
F2595A Manage Date Functions (Version 2) is mentioned to filter Fiscal Year Date.
By using Data Function, it is possible to filter date dynamically in Fiori Smart Business Apps and Analytic Query created with Custom Analytical QUeries. 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.
Firstly, In my observation, there are no good documents or sites on Date Function for Fiori Smart Business app and Analytic Query created with Custom Analytical QUeries.
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.
SAP Fiori apps F2595A Manage Date Functions (Version 2) maintains fiscal and calendar date ranges used with other analytical reports, e.g. current fiscal period.
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.
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).
Standard Date Function
Create Custom Date Function
Use the Date Funtion in custom Analytic Query and Generic Drill-Dwon app
Further possibilities for custom Date Function.
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”.
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.
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.
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.
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.
Custom Analytical Queries:
In newer version of S/4HANA, Date Function can be used to filter date dimension.
In the following Query, Date Function YEARTODATE is used on Posting Date.
Select "Year to date".
Year to date is selected.
After publishing the query, run the query.
in the prompt, default start date and end date are derived using Date Function Year to date.
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.
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 newrer version of S/4HANA, I_FiscalCalDateFunction is provided with which it is possible to derive dates based on Fiscal Year.
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!
F2595A Manage Date Functions (Version 2) is mentioned to filter Fiscal Year Date.
Date Function for Fiori apps
By using Data Function, it is possible to filter date dynamically in Fiori Smart Business Apps and Analytic Query created with Custom Analytical QUeries. 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 and Analytic Query created with Custom Analytical QUeries.
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.
SAP Fiori apps F2595A Manage Date Functions (Version 2) maintains fiscal and calendar date ranges used with other analytical reports, e.g. current fiscal period.
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)”.
- it is also possible to filter on the date in Custom Analytical Queries.
- 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. In newer version of S/4HANA, I_FiscalCalDateFunction could help to get the date about Fiscal Year and Period.
Example of Date Function used in the applications
Filtered in the KPI tile Generic drill-down app:
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).
Filtered in the custom Analytic Query:
Movie
Standard Date Function
Create Custom Date Function
Use the Date Funtion in custom Analytic Query and Generic Drill-Dwon app
Further possibilities for custom Date Function.
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.
Custom Analytical Queries:
In newer version of S/4HANA, Date Function can be used to filter date dimension.
In the following Query, Date Function YEARTODATE is used on Posting Date.
Select "Year to date".
Year to date is selected.
After publishing the query, run the query.
in the prompt, default start date and end date are derived using Date Function Year to date.
Use Date Function View for any apps (not only for KPI Tile)
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).
Dynamic filtering without Date Function
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) |
In newrer version of S/4HANA, I_FiscalCalDateFunction is provided with which it is possible to derive dates based on Fiscal Year.
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!
- SAP Managed Tags:
4 Comments
