Export to excel for large data set which is to be retrieved from 2 data entities

HI Mohit,

Using the SP(Stored procedure ) or view is one of the approach.

Please refer this link.

community.appian.com/.../database-performance-best-practices

Thanks,
Sudha.P

Create a view by combining the two tables and can use Export Datastoreentity to excel / Export CDT to excel smartservice to export the data

Hi Mohit,

As other suggested, You can use view for that. If you have huge data then you can have show the confirmation UI to the user saying that "Data loading into excel is in progress. You will get an email or task assigned once it complete. So that you can trigger the Export to Excel process asynchronously. Once data is ready then you can either email to user or assign fresh task to user with the document.

Create a view having proper mapping of both tables. Please have a look at JOINS in SQL and make proper view. You can map CDT with already existing View (we can do this with table as well).

Once you map CDT with view you are ready to fetch data. Before exporting, if needed you can write query entity to get data filtered.

Use process model and call smart service EXPORT CDT TO EXCEL.

Hi Mohit,
As you mentioned dataset is extremely large. So, just creating a view will not solve your purpose here. I would suggest to use 'Store Procedure ' here to avoid time-out or performance issue.
Or, try to split data-set with some filter and put into different sheet in the excel file, if your business requirement allow to do so. Let us know you thought on this.Thanks.

FYI,
Query entity cannot fetch more than 2000 records. So, this won't help. Already tried this approach.

Hi Chandhinir,

I tried this approach as this is Appian recommended for large datasets. The problem i'm facing is that i am unable to use more than one filter for the filter input node in this Export Datastoreentity to excel / Export CDT to excel smartservice.

Let me know if you can help.
Thanks

Export DSE to Excel claims to allow either an array of a!queryFilter() items, or a single a!queryLogicalExpression() (which can contain multiple filters and subsequent logicalExpression() calls nested, perhaps, indefinitely). If neither of these methods are working for you, you might need to open a support case as it would indicate an issue with the OOB functionality.

Would you be able to post the code you're passing into the node for the Filters parameter here?

How big of a dataset are we talking about?
What is the exported data used for? There may be better formats/solutions than Excel.

As others have said, combining the two queries into a view could be the simplest way to retrieve all the data from the 2 entities (may be able to suggest more if knew what the structure/data to be retrieved from the entities were).

As far as using the Export to Excel functionality, you may be able to get over the limitation of query entities by pulling the dataset in batches (e.g. pull 500 records at a time), assign the records to a process variable and increase the increment each time until the dataset is complete. This approach may run into issues depending how big or efficient the view you'd pull from would be, but you would have access to all the data from both entities if you can accomplish that.