How to apply filter in export CDY to excel?

Question

Hi Everyone, 
 I have the requirement data is displayed to UI through grid and provided the facility to user to export data in excel using export CDT to excel smart service. But when user applies sorting on UI and click on export the downloaded excel is not in the same sorting. can anyone please suggest me how to achieve this? 
 Thanks, 
 Shubham

Harshit Bumb (Appyzie) · Accepted Answer

I am not sure how strictly you need this sorting but a workaround can be that you create a temporary table with an extra column "process id" using which you will be able to identify the PM that triggered the export. Next, you can dump the data into that staging table in whatever order you like (based on what used selected on the grid) and then use this smart service on that staging table.

Chris · Answer

Gotcha, so you are not exporting a CDT to Excel, you are exporting from your Data Store Entity to Excel - this service does not have any filtering options as you noticed. 
 As Harshit mentions, this data will come have to come directly from the DB without filtering applied at runtime - so if you need it sorted, you will have to manipulate it on the DB side - such as pulling the set from your main table (unsorted), then sorting it in your local CDT via script task and adding it back to a copy of your main table "dbo.YOUR_TABLE_EXPORT" (with a column for process instance ID / pp!id, to avoid a race condition), then if you export from there (filtering for process instance ID) it will be sorted.

Deepak gupta · Answer

It is not in the sorting order because you sort it by clicking on the column of the grid not in the query entity. To get the same sort of data, you have to sort the data from the query entity.

Chris · Answer

Hi Shubham, I'm assuming the grid display is a data subset of your CDT, essentially a sorted version of the CDT is being displayed but the actual CDT used in the export does not have this sorting applied. If this is not the case, please elaborate on your configuration. 
 todatasubset().data is a common way to sort a CDT - you would apply the same a!pagingInfo() used in your grid(except for batch size typically), to achieve the same sorting. 
 a!localVariables(
 local!gridPaging: a!pagingInfo(1,10,a!sortInfo("id",true)),
 local!data: {
 a!map(id: 3, data: 7),
 a!map(id: 1, data: 5),
 a!map(id: 2, data: 6)
 },
 local!dataSorted: todatasubset(
 local!data,
 a!pagingInfo(
 local!gridPaging.startIndex,
 -1,
 local!gridPaging.sort
 )
 ),
 
 local!dataSorted.data
) 
 I have to note that the Export CDT to Excel service is deprecated, however, I do continue to use it in production as we do not have a suitable replacement for some situations yet. Otherwise, a!exportDataStoreEntityToExcel() works great when you can export from your data store. Except, it does not have sorting

Mike Schmitt · Answer

Chris said: Except, it does not have sorting 
 I have no idea why this hasn't been fixed yet 
 As far as the OP's use case - I think we need a bit more info about how they're capturing their Data CDT to be passed into the CDT to Excel smart service. I assume they might be freshly querying it in the process model, in which case the real trick (IMHO) is to simply pass the current filtering/sorting data from the form, out into the process model, and re-use it upon running the query that pulls back the (presumably whole) data set into the process CDT.

How to apply filter in export CDY to excel?

Top Replies