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
Hi Mike,
We are trying to evaluate adding support for basic Excel templates which are likely to address the requirements you mention above. However, support for macros and formulas, which require cell based logic would not be supported.
As for your second request, I recommend using the Export Record List functionality as that allows you to export the same format you see on the record list itself. I don't expect Appian to work on this functionality for out Export to Excel smart service in at least one year.
It'd be great if you could open a use case and attach the Excel template you are planning to use so we have a better idea of your requirements and consider them when making changes to out Export to Excel smart service.
Best regards,
Jupiter Munoz
Principal Product Manager
Hi Jupiter Munoz,
We also have a requirement to use smart service with Base Template.
Requirement in detail: Environment - 19.4 cloud. Excel generation with two pages. First page data comes from data store entity, where as second page is static information, which is fixed data. Excel will be generated with two pages, for each request submission. As the "Export Data Store Entity to Excel" not having base template, it is hard to achieve the aforesaid requirement. We also installed excel tools plugin (v2.2.6), In that also Many basic smart services which have "Excel Base Template" has been deprecated.
New "Export Data Store Entity to Excel" have immense advantages, But many of our use cases will be easily achieved with Base Template.
As Mike Schmitt suggested, it would be great if we can have smart service with base template in future version, or in excel tools plugin.
excel-generation-with-base-template
Thanks,
Muthulakshmi