Hello,

I am new to Outsystems and currently working on Outsystems 9.1.

I am now currently developing Report Generation tool for a system. And It needs build custom Advanced SQL based on user definition. I got that part done but I am having an issue with presenting the result.


Outsystems seems to only put recordset in predefined structures or entities. 

Is there anyway to achieve something dynamic.


Thank you in advance to anyone responding.

For Advance SQL you must use structure for output. It may be just a dummy structure. Outsystems need some output even if statement is insert/delete.


Thank you for your immediate response.

But the problem I'm having is that I have a huge query that generates over 400 columns. And users are able to select which columns they want to export to excel.

It is near impossible to check if every columns is selected or not. As for generating the query, it is done and works with dummy structure with same amount of columns.


Now I just have to generate excel file with only selected columns. How should I approach it?


Kataphroneo wrote:

But the problem I'm having is that I have a huge query that generates over 400 columns. And users are able to select which columns they want to export to excel.

This huge query comes from joining multi tables? Not recommend this since will slow down performance a lot.

Now I just have to generate excel file with only selected columns. How should I approach it?

This should be better when user already know which columns they want and you retrieve it as required. Yes you can create screen where users will choose the column they require, then with these selected columns you "generate" the query and store it into table. So the query is a text inside a table now.

Next, you create stored procedure in database with something like "Execute Immediate" to run your string query. Outsystems just need to call this procedure. Normally you should use extension to integrate with it or expose it to REST service then Outsystems just consume it.

It what comes to me maybe others people on this forum has different ideas.


So that means It is not advisable doing it in Outsystems rather I should make another more manageable application and expose it as REST service and let Outsystems consume it. 


It feels like a workaround but I guess that is the only way to work with Outsystems. 


Thank you for taking time to respond to me @Eric Halim.

No it's not another manageable app but it's a database stored procedure just to make it easier handling dynamic sql part.

Even If I use stored procedure. I still can't get the RecordSet as a Dynamic List right?

So what's the need to make stored procedure. 

Not sure about database part but maybe you can return it as an object (which is dynamic as query changes) then within Outsystems you can just parse it accordingly.

OK. I'll see if something changes.

I think this should be better when user already know which columns they want and you retrieve it as required....

The users will choose which columns to add from predefined pool of columns. But those predefined columns would be selected 1 by 1 and in any order they want.