Skip to Content
0

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

Dec 07, 2016 at 01:59 PM

899

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Jun Wu Dec 07, 2016 at 02:16 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Viswanath Golakoti Dec 07, 2016 at 02:37 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

i am asking what the js code is to trigger the excel export

0

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>
0
Seungchul Yang Dec 07, 2016 at 11:54 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

not ?$format=xslx

It should be ?$format=xlsx

0

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

0
Akhilesh Upadhyay Dec 08, 2016 at 01:22 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Deepak Anumula Dec 13, 2016 at 03:11 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded