I have a scenario where based on my selection on grid(which is record type) I fetch data based on selected grid Ids and provide excel with that data.
Data to be shown on excel is from various table selected grid Id would help me get those data from DB.
My thought process for solution here is :
To create Stored Proc taking selected grid Id as input and getting all required data and use export data to excel and get that to excel. Is that performance efficient and good to proceed with if I am not looking for creating a customized plugin for same.
Will view be same or different?
Any other idea to proceed in this scenario.
Discussion posts and replies are publicly visible
Making a stored procedure would not be performant always and will include putting more development effort as well
I would suggest to use a!exportDataStoreEntityToExcel() function or Export Data Store Entity to Excel smart service to export your data as excel.
These out of the box functions are optimised automatically to scale up even if your data gets large.
You can read about them here: https://docs.appian.com/suite/help/21.4/Export_To_Excel_Smart_Service.html#supported-excel-formatting
Since your data to be exported has to be fetched from different tables, you can first create a view and create a respective data store entity of this view.
And, later use this entity to export your excel. The id's selected for export on the grid can be passed as a query filter in the above function / smart service.
You can view an example here for implementing this function
I have 250+ columns of data to be fetched from different tables(which I am planning to get in batches) is there a way we can get more than 50 columns in a call?
Additionally I need to get data in transposed way, I don't see any parameter for that, custom cell position and value won't work if I use same node in iterative way , I will have to use 6 calls for same smart service to get this done.
Is there a better way for this?
Well, as per Appian documentation, the selection accepts up to 50 columns. So, I don't think its possible to extract more than 50 columns in one call.
Off course, as you mentioned you can call, like 6 times to query up to 300 columns iteratively.
These smart services are for dumping excel exports mostly and for transposing, you should somehow make that within your database view.
Also, to get it right, before the data is transposed, i.e. currently how your data is stored in the database, does it contain 250+ columns ?
Or, once you transpose, you are expecting ~250 columns?
No I will be fetching from multiple tables to create the excel so 250+ columns
Seems like an uncommon use case to export such a large data set.
But, you can use the smart service iteratively to achieve this
Indeed it is!
© 2022 Appian. All rights reserved.