SAP UI5 Smart filter approach for passing date filter to a parameterized CDS view

[jmalla]

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="\{&quot;columnKey&quot;: &quot;ThreePLQuantity&quot;, &quot;leadingProperty&quot;: &quot;ThreePLQuantity&quot;, &quot;columnIndex&quot;:&quot;9&quot;}" />

                            </customData>

                            <Text id="_IDGenText1" text="ThreePLQuantity" />

                        </Column>



                        <Column id="_IDGenColumn2">

                            <customData>

                                <core:CustomData key="p13nData" value="\{&quot;columnKey&quot;: &quot;ThreePLUnitOfMeasure&quot;, &quot;leadingProperty&quot;: &quot;ThreePLUnitOfMeasure&quot;, &quot;columnIndex&quot;:&quot;10&quot;}" />

                            </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

https://blogs.sap.com/2021/10/28/publishing-events-from-s-4-hana-to-event-mesh-for-cloud-integration...

 

Loading SAP S/4 HANA Exchange Rates from OANDA Foreign Exchange Rate API through CPI integration

https://blogs.sap.com/2021/10/29/loading-sap-s-4-hana-exchange-rates-from-oanda-foreign-exchange-rat...

 

SAP S/4 HANA Custom Fiori app for Project & WBS using CDS View Table Function, SmartFilter & Tree table

https://blogs.sap.com/2021/03/19/sap-s-4-hana-custom-fiori-app-for-project-wbs-using-cds-view-table-...

 

SAP S/4 HANA custom Fiori Elements App for Stock Transfer Orders, Deliveries, Shipments – Tips and tricks

https://blogs.sap.com/2021/03/19/sap-s-4-hana-custom-fiori-app-for-stock-transfer-orders-deliveries-...

 

SAP Fiori/UI5 custom app – Outsmarting the Smart filter dynamically for overriding the default behavior

https://blogs.sap.com/2021/03/19/sap-fiori-ui5-custom-app-outsmarting-the-smart-filter-dynamically-f...

 

 

SAP HANA Express Edition on AWS – Tips to get XSA build and run working

https://blogs.sap.com/2022/05/12/sap-hana-express-edition-on-aws-tips-to-get-xsa-build-and-run-worki...

 

How to access an SAP HANA XSA Container schema from external applications such as NodeJS

https://blogs.sap.com/2019/07/11/how-to-access-an-sap-hana-xsa-container-schema-from-external-applic...

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

https://blogs.sap.com/2019/01/23/integrating-sap-hana-xsa-with-microsoft-office-365-sharepoint-excel...

 

How to Post an Image from SAPUI5 and Store in HANA DB as BLOB using XSJS

https://blogs.sap.com/2017/03/14/how-to-post-an-image-from-sapui5-and-store-in-hana-db-as-blob-using...

 

Thanks,

Jay