The Export Data Store Entity to Excel smart service has many advantages including directly handling paging for large data sets, however it is lacking some major important features that prevent it from being the replacement for Export SQL to Excel or Export CDT to Excel (from the Excel Tools plug-in) that it was intended to be.
1) The biggest of these by far is its inability to accept an Excel file as a base template. So far this lack has made this new feature useless to me, personally, for any of my projects because they all require the features that can only be added via a base template: namely, the ability to format certain things in advance. That includes pre-setting column widths, and setting column headers with filtering already in-place. See screenshot for an example of both of these:
It seems to me as if this might be an easy addition to the current smart service since it wouldn't override or break any existing functionality if users are using it *without* the base template already. Of course, I can't speak for what's going on in the Appian internals that might make this harder than I think.
2) Another thing that's missing from, say, the things we were able to do with Export CDT to Excel, is the ability to do some Appian-side transformation to data prior to it being written to Excel. In many cases, for example, end users don't know or care about usernames, but instead would want to see the user's plaintext name i.e. the result of rule!APN_displayUser( pv!username ), or similar. That is not a transformation that can always be done via the database, not even necessarily in a view since not everyone keeps user plaintext names in the database. Another example would be the ability to reformat dates, for instance from a datetime down to an ISO-compliant date string (YYYY-MM-DD), or whatever other format we might like.
So I'm not sure how feasible this might be or how it might be accomplished using the current configurations of the smart service or any seamless additions, but if someone could figure out a way to allow us to pass in certain column names and an expression that should be applied to the value of each row of that column, that would also be amazing. I would consider EDSEtE feature-complete if both of these were added.
[edited (2020-03-12) to add:]3) A third use issue has occurred to me recently after seeing a post on Community: the DSE export has no functionality by which the results can be sorted. No matter what you do, the results are in whatever order they would default to from the database. To me this would almost never be the desired outcome, and being told to built a custom view for each individual use case (as opposed to just being able to pass in a sortInfo parameter somewhere in the smart service call) seems like a major unnecessary inconvenience.
Everyone please let me know what you think and whether there are any other (feasible, generally-usable) wishlist items I should've included.
Thanks!
Discussion posts and replies are publicly visible
Would just like to chime in and add my support here - this one would be near, if not at, the top of my wish list. I continue to run into scenarios where there is no other option except to continue to implement the deprecated Export CDT to Excel node for all of the reasons listed here.
In all of my use cases, customers need a sorted, formatted excel output that is typically run on a schedule and sent via email. With base template capability and a service that can export DATA ONLY (no formatting) from CDT (or DSE), at specific cells, I would have everything I need. As noted in #2 there are advantages to perform data manipulation in Appian (CDT method), such as the user name example where I do not have to duplicate formatting functions in the DB, and thus have 2 areas to update them when necessary. Additionally, if I want to combine 2 data sets for export, this can be managed in Appian via CDT export vs requiring DB manipulations.
Also agree on #3, every time I am able to use the Export DSE to Excel node, I have received complaints about sorting.