How to apply filter in export CDY to excel?

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

  Discussion posts and replies are publicly visible

Parents
  • 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 No mouth

  • Hi Chris

    Thank you for your suggestions, but since I am using export to excel smart service in PM which has filter and selection option that does not accept paging parameters. Please see below screenshot:

    Thanks,

    Shubham

Reply Children
  • 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. 

  • 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.