Hello,
We are using the Export Data Store Entity to Excel smart-service to generate an excel sheet. The requirement is to have the data in the excel sheet sorted by a date column in the descending order. I have defined the ORDER BY clause in the view to handle the data sorting. For some reason, the Export Data Store Entity to Excel smart-service overrides this and generates the excel sheet sorted by the primary key in ascending order. Is there a way to export data into an excel sheet where the data is sorted by a column that is not a primary key ?
Discussion posts and replies are publicly visible
After checking, I can see there are a few previous discussions around this topic here on Community, but so far there are no solid answers I can find. I was mistakenly thinking that the Export DSE to Excel node had a way to accept sorting parameters, but after checking into it, I was wrong. I think the only viable current way of handling this is to create a special view to handle your export, where the primary key is the column you want it to be sorted by. If this is insufficient for you, I suggest opening a support case with Appian to lay out your use case and get them to hopefully fix the Export DSE to Excel node soon.
Does "SELECT ROWNUM ID" work as a primary key in a view? We are directly specifying the view as data store entity in Export DSE to Excel node. So I am guessing it will sort the data as per the ROWNUM right?
Yes, you can define a view's primary key as a row number column. We do this in MSSQL with:
SELECT ROW_NUMBER() OVER(ORDER BY YOUR_COLUMN) as 'id' from YOUR_TABLE
The only catch I've found is on occasion, the XSD prefers the column type definition as BIGINT rather than INT.
Feel free to post a new thread (this one is older) if you have any issues with the setup.