Skip to Content
avatar image
Former Member

How to fetch all records from ODATA service without using TOP and SKIP

Hi Everyone,

I have requirement, When I search for accounts in UI based on search criteria it should display results in List.I have to download all the results to Excel when I click on Button.To display results in List I am writing below code which is working fine.

<List id="idList" items="{/AccountCollection}" growing="true"
			growingThreshold="20" growingScrollToLoad="true" select="handleListSelect" mode="SingleSelectMaster">

But Issue here , As I am using property growingThreshold="20" in List Control.From back end at a time I am getting only 20 records. But in count I can see around 3000 records. When I scroll down after every 20 records it's calling oDATA service and display 20 next 20 records by using SKIP and TOP properties.

sap/opu/odata/sap/ZCRM_BUPA_ODATA_SRV/AccountCollection?$skip=0&$top=20

sap/opu/odata/sap/ZCRM_BUPA_ODATA_SRV/AccountCollection?$skip=20&$top=20

sap/opu/odata/sap/ZCRM_BUPA_ODATA_SRV/AccountCollection?$skip=40&$top=20

So when I click on Download to Excel I am able to download only 20 at a time. If I scroll as it is calling ODATA service for next 20 records then I am able to download next 20(I get only next 20 I won't get previous 20 ). In the back end they are saving data based on ODATA call in table and they are exposing as Excel to Frontend.

If I am increasing growingThreshold="2000" I am able to download 2k records at a time but it's having performance issue(6 mins to fetch and display results).

Can anyone suggest irrespective of growingThreshold property can we store all records in a table in back end without using skip and top? or any better solution.

Thanks,

Best Regards,

Viswanath

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Dec 07, 2016 at 02:16 PM

    what's ur code to download the data as excel?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 07, 2016 at 02:37 PM

    Hi Jun Wu

    In the back end we are following this blog to expose data as Excel https://blogs.sap.com/2014/04/24/download-sapui5-table-as-excel-generated-in-abap-backend-system/.

    In UI I am using sap.m.URLHelper.redirect

    Kindly let me know if you need any further details

    Regards,

    Viswanath

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Jun Wu

      When I press on search button, I am capturing all the search parameters in filter.

      Next I am binding same to the List as shown above by using items="{/AccountCollection}".

      In controller I am using below code for filters.

      	onSearch : function (oEvt) {
      		// add filter for search
      		var aFilters = [];
      		var sQuery = oEvt.getSource().getValue();
      		if (sQuery && sQuery.length > 0) {
      			var filter = new sap.ui.model.Filter("name1", sap.ui.model.FilterOperator.EQ, sQuery);
      			aFilters.push(filter);
      		}
      		if (this.sValue1 && this.sValue1.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zDistLevel", sap.ui.model.FilterOperator.EQ, this.sValue1));
      		}
      		if (this.sValueOwning && this.sValueOwning.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zOwningAff", sap.ui.model.FilterOperator.EQ, this.sValueOwning));
      		}
      		if (this.sValuebuy && this.sValuebuy.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zBuyAff", sap.ui.model.FilterOperator.EQ, this.sValuebuy));
      		}
      		if (this.sValueAssign && this.sValueAssign.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zEmployID", sap.ui.model.FilterOperator.EQ, this.sValueAssign));
      		}
      		if (this.sValueStatus && this.sValueStatus.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zStatus", sap.ui.model.FilterOperator.EQ, this.sValueStatus));
      		}
      		if (this.sValueSAP && this.sValueSAP.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("accountID", sap.ui.model.FilterOperator.EQ, this.sValueSAP));
      		}
      		if (this.sValuenabs && this.sValuenabs.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zNABSID", sap.ui.model.FilterOperator.EQ, this.sValuenabs));
      		}
      		if (this.sValueregion && this.sValueregion.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zOwingRegion", sap.ui.model.FilterOperator.EQ, this.sValueregion));
      		}
      		if (this.sValueAddress && this.sValueAddress.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("ZLocation", sap.ui.model.FilterOperator.EQ, this.sValueAddress));
      		}
      		// Address check box changes for MAilPO type 
      		if (this.sValueAddressChkBox) {
      			aFilters.push(new sap.ui.model.Filter("zMailPO", sap.ui.model.FilterOperator.EQ, "X"));
      		}			
      		if (this.sValueCity && this.sValueCity.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zCity", sap.ui.model.FilterOperator.EQ, this.sValueCity));
      		}
      		if (this.sValueState && this.sValueState.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zStateKey", sap.ui.model.FilterOperator.EQ, this.sValueState));
      		}
      		if (this.sValuezip && this.sValuezip.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zPostal", sap.ui.model.FilterOperator.EQ, this.sValuezip));
      		}
      		if (this.sValuecountry && this.sValuecountry.length > 0) {
      			aFilters.push(new sap.ui.model.Filter("zCountryKey", sap.ui.model.FilterOperator.EQ, this.sValuecountry));
      		}
      		aFilters.push(new sap.ui.model.Filter("category", sap.ui.model.FilterOperator.EQ, "2"));
      		// update list binding
      		var list = this.getView().byId("idList");
      		var binding = list.getBinding("items");
      		binding.filter(aFilters, "Application");
      
      	},
      <br>

      But in backend they are able to store only 20 in table and send same to UI through another service as URL which I consume on click of but at footer of List which displays Search results.

      handleDownload:function(oEvent){
      		
      		var oModel = new sap.ui.model.odata.ODataModel("/sap/opu/odata/sap/ZCRM_DOWNLOAD_EXCEL_SRV",true);
      		
      		var sRead = "/DownloadSet" ;
      		
      		oModel.read( sRead, null, null, true, function(oData, oResponse){
      			  
      		      var fileURL =   oData.results[0].url;
      		      var encodeUrl = encodeURI(fileURL);
      		      sap.m.URLHelper.redirect(encodeUrl, true);
      		         },function(){
      		             alert("Read failed");
      		             });     
      	
      		
      	},
      <br>
  • avatar image
    Former Member
    Dec 07, 2016 at 11:54 PM

    You can download all data from backend with odata format option

    http://<domain>:<port>/sap/opu/odata/sap/ZCRM_BUPA_ODATA_SRV/AccountCollection?$format=xslx

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi,

      Thanks for your response.

      If we used as you suggested we will get excel sheet of all fields in entity set , but still we are able to get only 20 at a time in ODATA service we are unable to download all for search result.

      We want only few fields to be shown in excel not all the fields in entity set so we used separate entity set.

      Regards,

      Viswanath

  • Dec 08, 2016 at 01:22 PM

    this is happening because in your backend code there must have implemented $skip an $top logic, and returning only 20 records.

    I think you can have two separate backend logic one for list and second for excel, and in excel backend logic ignore $skip and $top values.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Akhilesh,

      Thanks for your response. Skip and top values in Backend are taking from growingThreshold Property from UI. Could you please let me know how can we save search values in backend in custom table by ignoring skip and top?

      Regards,

      Viswanath Golakoti.

  • Dec 13, 2016 at 03:11 PM

    Hi Viswanath,

    I think u need to remove skip and top while calling the service.

    ap/opu/odata/sap/ZCRM_BUPA_ODATA_SRV/AccountCollection?$skip=0&$top=20

    Instead of like this u can simply call like below.

    sap/opu/odata/sap/ZCRM_BUPA_ODATA_SRV/AccountCollection

    Here the threshold value is that u are not allowing the list in the view to display all the records.

    U are stopping the list to display all records at a time by using threshold value.

    Try this and let me know if u have any doubt.

    Thanks,

    Deepak

    Add comment
    10|10000 characters needed characters exceeded