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
Jupiter Munoz said:We are trying to evaluate adding support for basic Excel templates which are likely to address the requirements you mention above.
That's great news
Jupiter Munoz said:However, support for macros and formulas, which require cell based logic would not be supported.
I can live with this - I haven't personally had a use case that would require macro or formula functionality (mostly, exported reports for customer data).
Jupiter Munoz said:As for your second request, I recommend using the Export Record List functionality
The problem here is that I'm talking about exporting various different custom-created customer data reports, with highly advanced and customized on-form filtering (already in place). These are mostly done through Views at the moment which is fine, but for example it provides no avenue through which to do data formatting that's only visible to the Appian side.
That being said, if (Part 1) can be accomplished, that will satisfy ~90% of what I really need; the data transformation could potentially wait for a graceful solution to become available (and/or a different / more advanced smart service that comes in the future).
Jupiter Munoz said:It'd be great if you could open a use case
Can you give me any further details as to how to do this? Is this different from opening a support case via a Customer organization?
Apologies for the late response. You can log a support case and ask them to log a product use case on your behalf. This helps us keep track of common enhancement requests.
Thank!
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
Floating this back to the top as we have yet another major version release coming up and no sign of any remediation for this issue, as well as yet another community post confused as to why it's not working for them.
I understand there is a need for this functionality and we have it in our radar.
That being said, this has not been prioritized due to other competing priorities. One of our devs. is looking into it on his own time but we currently don't have a timeline for this enhancement.
Thanks for your feedback and will provide an update when appropriate.
Has there been any update on this? Just, curious to know. As we have also encountered this as a blocker for one our requirement.
No update at this time. I will provide an update when appropriate.
Thanks.
Really hope this comes soon to a future release. Our federal clients have asked for features which would benefit from this sort of functionality.
Hi ,
Is there any update on this Plugin ?