Technical Articles
SAP UI5 Smart filter approach for passing date filter to a parameterized CDS view
Hi SAP Fiori and SAP UI5 development enthusiasts,
It’s been a little while since I wrote my last blog and I came across an issue while developing a custom SAP UI5 application that was quite challenging and required a lot of painful research. I finally managed to crack the code and come up with the solution. A little background about the application – I was developing a stock inventory application for 3PL (Third Party logistics) partners to maintain their stock inventories in S/4 HANA and also be able to compare their stock to the SAP stock quantities in both material base unit and alternate units – e.g. KG, LB, TON, etc. for the same material. Maybe the material base unit is KG, but the 3PL is maintaining this in TONs.
So there is an S/4 HANA CDS view called I_MatlStkAtKeyDateInAltUoM which takes in a date parameter as in input and has Material, Plant, Storage Location, and Batch as keys with some other fields.
define view I_MatlStkAtKeyDateInAltUoM
with parameters
P_KeyDate : vdm_v_key_date
as select from P_MatlStkAtKeyDateInAltUoM( P_KeyDate : $parameters.P_KeyDate )
...
{
// Stock Identifier
@ObjectModel.foreignKey.association: '_Product'
key Material as Product,
@ObjectModel.foreignKey.association: '_Plant'
key Plant,
@ObjectModel.foreignKey.association: '_StorageLocation'
key StorageLocation,
key Batch,
@ObjectModel.foreignKey.association: '_Supplier'
// Units
@Semantics.unitOfMeasure
key MaterialBaseUnit,
@Semantics.unitOfMeasure
key AlternativeUnit,
// Quantities in Base Unit of Measure
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
@DefaultAggregation : #SUM
MatlWrhsStkQtyInMatlBaseUnit,
// Quantities in Alternative Unit of Measure
@Semantics.quantity.unitOfMeasure: 'AlternativeUnit'
@DefaultAggregation : #SUM
cast( fltp_to_dec( MatlWrhsStkQtyInAltUoM as abap.dec(31,14) ) as nsdm_stock_qty_auom ) as MatlWrhsStkQtyInAltUoM,
....
If you view data for this CDS, Eclipse pops up an input box for entering the date since it is a mandatory parameter to the CDS view:
P_KEYDATE parameter
Quantities in material base unit and alternate unit are returned:
I_MATSTKATKEYDATEINALTUOM results
So I developed a custom Fiori SAP UI5 application that looks like a Fiori elements List report object page – but has a way more flexibility – hence the custom SAP UI5 approach.
Custom Smart Filter and smart table like Fiori Elements List Report
The results look like this and allows the users to enter the ThreePLQuantity and Unit of measure:
Smart table with results from query
The smart filter call to the backend OData service looks like this:
Call to Backend S/4 HANA OData service
So here is the HTTP get call in url-encoded format:
GET MaterialStock(P_KeyDate=datetime%272022-09-27T12%3a00%3a00%27)/Set?sap-client=100&$skip=0&$top=100&$filter=Plant%20eq%20%27USA1%27%20and%20StorageLocation%20eq%20%271000%27&$select=Plant%2cStorageLocation%2cProduct%2cBatch%2cKeyDate%2cQuantityInBaseUoM%2cMaterialBaseUnit%2cQuantityInAltUoM%2cAlternativeUnit%2cThreePLQuantity%2cThreePLUnitOfMeasure%2cDifferenceQuantity%2cDifferenceUnitOfMeasure&$inlinecount=allpages HTTP/1.1
Here is the call in url-decoded format – much easier to read….
GETMaterialStock(P_KeyDate=datetime'2022-09-27T12:00:00')/Set?sap-client=100&$skip=0&$top=100&$filter=Plant eq 'USA1' and StorageLocation eq '1000'&$select=Plant,StorageLocation,Product,Batch,KeyDate,QuantityInBaseUoM,MaterialBaseUnit,QuantityInAltUoM,AlternativeUnit,ThreePLQuantity,ThreePLUnitOfMeasure,DifferenceQuantity,DifferenceUnitOfMeasure&$inlinecount=allpagesHTTP/1.1
So now here is where the subject matter for this blog comes in… and if you’ve read this far, maybe you have been searching for it. It was really tricky to create the date filter and pass this to the backend from the date smart filter.
We need to get the date filter field value and call the OData service as GETMaterialStock(P_KeyDate=datetime’2022-09-27T12:00:00′)/Set
A little strange that there is a /Set after the parameter in parenthesis….
I read all the blogs and SAP help articles on CDS with parameters that I could Google search along with smart filters for dates but the articles did not give enough insight into this topic. I finally figured it out and wanted to share this knowledge with my fellow SAP Fiori and SAP UI5 developers.
So here is the smart filter and table – the SAP UI5 components are from the same SAP UI library as the Fiori elements – and this keeps the UI consistent with the SAP Fiori look and feel. Consistency is a good thing……. from SAP UI5 coding to playing a tennis match…. be consistent 🙂
Here is a snippet of the XML view – note that there is no date filter defined – but it is automatically shown on the filter bar:
<mvc:View
xmlns:mvc="sap.ui.core.mvc"
xmlns="sap.m"
xmlns:smartFilterBar="sap.ui.comp.smartfilterbar"
xmlns:smartTable="sap.ui.comp.smarttable"
xmlns:customData="http://schemas.sap.com/sapui5/extension/sap.ui.core.CustomData/1"
xmlns:core="sap.ui.core"
controllerName="com.********.ztplstockcustom.controller.Stock"
displayBlock="true"
>
<Page id="page" title="{i18n>title}">
<!-- use this to make the table occupy the available screen height -->
<VBox id="_IDGenVBox1" fitContainer="true">
<!-- smartFilterBar -->
<smartFilterBar:SmartFilterBar id="smartFilterBar" entitySet="MaterialStockSet" persistencyKey="SmartFilter_Explored" enableBasicSearch="false">
<smartFilterBar:controlConfiguration>
<!--<smartFilterBar:ControlConfiguration id="_IDGenControlConfiguration1" mandatory="true" key="P_KeyDate" visibleInAdvancedArea="true" preventInitialDataFetchInValueHelpDialog="false"/>-->
<smartFilterBar:ControlConfiguration id="_IDGenControlConfiguration2" key="Plant" visibleInAdvancedArea="true" preventInitialDataFetchInValueHelpDialog="false" />
<smartFilterBar:ControlConfiguration id="_IDGenControlConfiguration3" key="StorageLocation" visibleInAdvancedArea="true" preventInitialDataFetchInValueHelpDialog="false" />
<smartFilterBar:ControlConfiguration id="_IDGenControlConfiguration4" key="Product" visibleInAdvancedArea="true" preventInitialDataFetchInValueHelpDialog="false" />
<smartFilterBar:ControlConfiguration id="_IDGenControlConfiguration5" key="Batch" visibleInAdvancedArea="true" preventInitialDataFetchInValueHelpDialog="false" />
</smartFilterBar:controlConfiguration>
<!-- layout data used to make the table growing but the filter bar fixed -->
<smartFilterBar:layoutData>
<FlexItemData id="_IDGenFlexItemData1" shrinkFactor="0" />
</smartFilterBar:layoutData>
</smartFilterBar:SmartFilterBar>
<!-- smartTable -->
<smartTable:SmartTable
id="smartTable"
width="100%"
direction="Column"
fitContainer="true"
tableType="ResponsiveTable"
header="Stock Inventory"
enableAutoBinding="true"
beforeRebindTable="onBeforeRebindTable"
useTablePersonalisation="true"
smartFilterId="smartFilterBar"
entitySet="MaterialStockSet"
fieldChange="onFieldChange"
editable="false"
editTogglable="false"
customData:useSmartToggle="true"
requestAtLeastFields="Product,Plant,StorageLocation,Batch,KeyDate,QuantityInBaseUoM,QuantityInAltUoM,AlternativeUnit"
initiallyVisibleFields="Product,Plant,StorageLocation,Batch,KeyDate,QuantityInBaseUoM,QuantityInAltUoM,AlternativeUnit"
>
<smartTable:customToolbar>
<OverflowToolbar id="_IDGenOverflowToolbar1" design="Transparent">
<ToolbarSpacer id="_IDGenToolbarSpacer1" />
<Button id="_IDGenButton1" text="Upload Excel" type="Transparent" icon="sap-icon://upload" press="UploadExcel" activeIcon="sap-icon://upload" />
</OverflowToolbar>
</smartTable:customToolbar>
<Table id="_IDGenTable1" alternateRowColors="true" >
<columns>
<Column id="_IDGenColumn1">
<customData>
<core:CustomData key="p13nData" value="\{"columnKey": "ThreePLQuantity", "leadingProperty": "ThreePLQuantity", "columnIndex":"9"}" />
</customData>
<Text id="_IDGenText1" text="ThreePLQuantity" />
</Column>
<Column id="_IDGenColumn2">
<customData>
<core:CustomData key="p13nData" value="\{"columnKey": "ThreePLUnitOfMeasure", "leadingProperty": "ThreePLUnitOfMeasure", "columnIndex":"10"}" />
</customData>
<Text id="_IDGenText2" text="ThreePLUnitOfMeasure" />
</Column>
</columns>
<items>
<ColumnListItem id="_IDGenColumnListItem1">
<cells>
<Input id="_IDGenInput1" value="{ThreePLQuantity}" type="Number" editable="true" submit=".processInput" />
<ComboBox xmlns="sap.m" id="box0" change=".onChange" items="{ path: 'to_MaterialUnitOfMeasure'}" selectedKey="{DifferenceUnitOfMeasure}">
<items>
<core:Item xmlns:core="sap.ui.core" id="coreitem" key="{AlternativeUnit}" text="{AlternativeUnit}" />
</items>
</ComboBox>
</cells>
</ColumnListItem>
<ColumnListItem id="_IDGenColumnListItem2">
<cells>
<Input id="_IDGenInput2" value="Test" type="Text" editable="true" />
</cells>
</ColumnListItem>
</items>
</Table>
</smartTable:SmartTable>
</VBox>
</Page>
</mvc:View>
So how do we pass the query to the backend – how do we pass the date parameter to the smart table binding in the controller? Drum roll please….. in our onBeforeRebindTable method….
onBeforeRebindTable: function (oEvent) {
var oSmartTable = oEvent.getSource();
var oSmartFilterBar = this.byId(oSmartTable.getSmartFilterId());
var vCategory;
var mBindingParams = oEvent.getParameter("bindingParams");
var that = this;
if (oSmartFilterBar instanceof sap.ui.comp.smartfilterbar.SmartFilterBar) {
//Custom filter
var oCustomControl = oSmartFilterBar.getControlByKey("P_KeyDate");
var oCustomControlPlant = oSmartFilterBar.getControlByKey("Plant");
var oCustomControlStorageLocation = oSmartFilterBar.getControlByKey("StorageLocation");
var oCustomControlProduct = oSmartFilterBar.getControlByKey("Product");
var oCustomControlBatch = oSmartFilterBar.getControlByKey("Batch");
var sPlant = oSmartFilterBar.getFilterData()['Plant'];
var sStorageLocation = oSmartFilterBar.getFilterData()['StorageLocation'];
var sProduct = oSmartFilterBar.getFilterData()['Product'];
var sBatch = oSmartFilterBar.getFilterData()['Batch'];
//Here is how to get the date from the smart filter and then pass it in the binding for the table
var sDate = oSmartFilterBar.getFilterData()['$Parameter.P_KeyDate'];
var oDate = new Date(sDate);
var dateFormat = sap.ui.core.format.DateFormat.getDateInstance({ pattern: "yyyy-MM-ddThh:mm:ss" });
var dateStr = dateFormat.format(oDate);
var sFilter = "/MaterialStock(P_KeyDate=datetime%27" + dateStr + "%27)/Set";
sFilter = sFilter.replaceAll(":", "%3a");
oSmartTable.setTableBindingPath(sFilter);
////////// .....
}
So the main part here which may help you if you are looking for how to pass the date from the smart filter to the backend CDS view as a parameter, it boils down to this:
var sDate = oSmartFilterBar.getFilterData()['$Parameter.P_KeyDate'];
var oDate = new Date(sDate);
var dateFormat = sap.ui.core.format.DateFormat.getDateInstance({ pattern: "yyyy-MM-ddThh:mm:ss" });
var dateStr = dateFormat.format(oDate);
//&27 is the URL encoded value for ' and %3a is for :
var sFilter = "/MaterialStock(P_KeyDate=datetime%27" + dateStr + "%27)/Set";
sFilter = sFilter.replaceAll(":", "%3a");
oSmartTable.setTableBindingPath(sFilter);
So to wrap up, if you have a Fiori SAP UI5 application where you need to pass a date parameter to the underlying CDS view, this blog has shown how to define the XML view with the smart table and filter and the main part is how to pass the query to the backend in the controller. There is a few tricky steps that took me some time to figure out, but hopefully you came across this blog and it addresses those questions you may have right away.
Please do comment and share your feedback. I have quite a few blogs that were posted from my older S-User account that was sunset. The blogs are still there but I can no longer make changes or edits as the author:
Publishing Events from S/4 HANA to Event Mesh for Cloud integration for external systems
Loading SAP S/4 HANA Exchange Rates from OANDA Foreign Exchange Rate API through CPI integration
SAP S/4 HANA Custom Fiori app for Project & WBS using CDS View Table Function, SmartFilter & Tree table
SAP S/4 HANA custom Fiori Elements App for Stock Transfer Orders, Deliveries, Shipments – Tips and tricks
SAP Fiori/UI5 custom app – Outsmarting the Smart filter dynamically for overriding the default behavior
SAP HANA Express Edition on AWS – Tips to get XSA build and run working
How to access an SAP HANA XSA Container schema from external applications such as NodeJS
SAP HANA Express Edition on AWS for XSA development space issues
https://blogs.sap.com/2018/01/28/sap-hana-express-edition-on-aws-for-xsa-development-space-issues/
Integrating SAP HANA XSA with Microsoft Office 365 Sharepoint Excel using Microsoft Graph API and NodeJS
How to Post an Image from SAPUI5 and Store in HANA DB as BLOB using XSJS
Thanks,
Jay
Hi Jay,
Really good post/information and can understand the pain you went through to get to this point (I've done this slightly differently and much prefer your approach - my version isn't blog postable!).
One question I would have if implementing your design is whether you need to be careful about the time for Timezone aspects if working in countries like Australia. I've found I need to do the following when dealing with datetimes that convert to Date with v2 OData so that in Australia, you don't go back a day or something (UI5 and CDS seem to not handle things automatically very well):
new Date(Date.UTC(oDate.getFullYear(), oDate.getMonth(), oDate.getDate(), 0, 0, 0));
Keep posting your findings - It's amazing how many easy things are ridiculously hard until you know how.
Cheers,
Matt
Hi Matt - you bring up a valid point. I've noticed some strange behavior and I still I need to account for the timezones. The data is in a custom table that has the date and time - but not the timezone. Will need to check and see.
Thanks,
Jay
BTW - Liking your music (and you have a nice PRS I'm jealous of!) - Now just need an international TechEd to come back or Sapphire with a bit more development content, and get the Jam Band happening again!
Jocelyn Dart - Hi Jocelyn - Was hoping you can share this with the other Fiori apps - https://blogs.sap.com/tags/73555000100800000131/ . It's been a while since I blogged but wanted to add this useful tips around smart filters/tables and CDS with date parameters - it was pretty tricky to solve but it should help anyone trying to figure this out.
Thanks,
Jay