Happy Friday all. Has anyone been able to export data to an excel template for the purpose of charting the data? In my use case, I would like to have an excel template that contains a few charts, and export data to say sheet 2, where sheet 1's charts reference the data in sheet 2 (preferably hidden), to update the charts via the output. Where I am at so far:
Export to Excel - This service will only export the data in text format, excel pie charts require number format. With the Excel fields formatted as number, the graphs are still empty after export due the type discrepancy as the triangle errors appear in the fields, via the text output from this service. I have also tried using separate fields that contain the expression =VALUE(A1) that reference the exported data, which are then used in the charts, but those expressions do not run after export and the values/charts are empty until I highlight the field and click enter. Annoying. I've considered macros that run on Workbook_Open to force value updates, but I cannot force all users to enable macros.
Export DSE to Excel - This service seems to completely remove all formatting in the workbook. Even with data exported to sheet 2 only, the charts on sheet 1 are removed from the template, text header fields with font/color are reverted to plain text as are the VALUE expressions.
Export Process Report - This will not work as our practice is to archive processes from memory after 30 days due to the environment size, and the data set needs to span more than the previous 30 days.
..Export to Excel is so close, but the =VALUE() expressions not loading and text-only output are a show stopper..
Any tips from the community? Thanks!
Discussion posts and replies are publicly visible
As an update to this, the only way I was able to get this working was utilizing the deprecated Export CDT to Excel service, but it does work! Since this is used in a COVID related app, I'm banking on the pandemic ending prior to the node removal...
Will attach a screen shot of the excel export with sample data here, the exported data is sent to a hidden sheet, where the main sheet references =VALUE() from the hidden sheet to resolve any potential type mismatch errors. The output is designed to be sent to executives weekly.
+1 more vote for incorporating this deprecated Export CDT to Excel functionality in the base product!
I know that saving your CDT to DSE and then using DSE to excel is ugly, however there are likely some speed benefits when going from DB->Excel (as well as large rowsets not being stored in Appian memory).
If you are doing lots of calculations in your CDT before export, can you create a DB view that does that calculation for you and then create a CDT that pulls from the view? I imagine that the DB is going to be a better and more efficient calculation tool than expression (if your SQL is good).
Yes, we use Export DSE to Excel as our primary export mechanism for reports, most based on views. However in this case we wanted to apply nice visuals in the export for executives. This export is also based on a view, however, utilizing DSE to Excel completely removes all formatting in the entire notebook template, even if the export is slated for sheet 2, sheet 1 is also neutralized. Charts are removed, text formatting is removed, etc. So, not a possibility for this use case unfortunately.