HI All,
I am working on 19.3 and have a requirement where I need to filter data from from the view based on the condition that are provided by user and then have to export it to excel.
The total no of column are 59 hence single node of Write Datastore Entity to excel is getting failed.
I tried using the same smart node twice where first node have selection of first 50 fieldsand starting cell value as "A1" and 2nd node have selection for remaining fields and starting cell as "AY" i.e 51st column.
When I am debugging the process the document obtained has only last node column.
Is there anyway by which we can get all the 59 in columns in one sheet of the excel by using Write "Datastore Entity To Excel Smart Service"
Discussion posts and replies are publicly visible
Have you tried using the Export SQL to Excel Smart Service. Create a SQL View that populates the same data as the CDT and use this service to create the spreadsheet bypassing the CDT.
HI Dave,
I have tried it but getting issue while creating the filter Dynamically i,e with Write to data store Entity we can write the filter logic which will only be applied when we pass some value to to filter the result set.
With Export SQl to Excel i m unable to use the filter dynamically as we have write the complete SQL query for it
This isn't too hard to get around. For SQL to excel, in the node itself just pass in a unique expression rule which generates your SQL code. The expression rule would also accept any parameters such as options and filters. There inside the expression rule itself, it's pretty easy to use the different passed-in options to generate slight variations of the same SQL code depending on what you need to do. This method is also really easy to test, since it just generates SELECT statements which you can just run directly in SQL to make sure they work.
Hi,
You can set your filters on the view easily enough. Pass the filter values into the node and use them in the SQL... That's way I had to implement it.
Hi Dave/Mike,
I tried here but unable to frame the query.
i have one process variable pv!filter of type : 'type!{urn:com:appian:types}filter'('ab','ac','ad','ae','af')
Table:"ABC" have column ab, ac, ad ae, af which needs to be queried if user passes some values in the filter
i.e if user give some filter i.e ab=12 and ac=23 then only these filters should be applied or if no value is passed then overall data should be queried.
It would be great if you can help me to frame the the SQL with just 2 filter value
DaveMC
If you didn't create a separate expression rule to handle your SQL code generation yet, do that first. If you have, what do you have so far and what are your results?