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.

Kataphroneo hi,

Did you manage to find a solution for the dynamic structure?

I have the same problem before me. I generate the SQL statement based on user selected tables and columns they want to see and I also generate a list of datatypes for the selected columns. That SQL statement I execute through an advanced query, with a structure that has 40 text columns as the output structure. That result set I run through RecordListToExcel. The binary from that I process with the Excel_Package. Per column, for each cell value i write the Cell_WriteByIndex with a cell type derived from the data type for the column.

Performance goes way down if the excel is large to get number columns as formatted number cells in excel in a dynamic way within OutSystems in stead of text values, which client does not accept. I do the same for dates and times to get them in the right format.

Please let me know.

Wilko


Hello Wilko,

After a whole year of wacky workaround shenanigans.

I came to the conclussion that it is impossible with the tools at our disposal in Outsystems.

---------------------------------------------

So with full of dread, I tried using a real programming language and it pretty much went smoothly.

I created NodeJS application that accesses the Oracle database directly.

It was easy to build (Including mapping to the physical tables and columns), Fast(Using async processes) and most of all it was damn reliable.

-------------------------------------------

By the way. What you tried is almost exactly the same as mine.

Outsystems is naturally slow at calling Extension methods.  So rather than using Cell_WriteByIndex, Create new extension method which receives all of the rows and columns at the same time.

And within that apply styles by range.

For example:

A2:A9999 as Number, B2:B9999 as Text and so on.


But my final advice is:

Just don't. Even if you manage it. It is going to be ugly.

Programming is not that hard. Just write a program that does the heavylifting.


Best Regards And Best of luck!

Kataphroneo

If you are doing this to return a table then there is actualy a fairly simple although :hacky" way of doing this. You can structure your SQL so that it returns a single formatted HTML table string. ie instead of name,date,time it returns "<tr><td>name</td><td>Date</td><td>time</td></tr>" then just render the entire string.

Not nice, doesn't give abilit to process the fields but lets you output in any format you like. You can also add CSS in there for any formatting etc.

You could probably even use the SQL built in XML functions to create the tables formatting for you. Haven't tried it myself but look into something like


https://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml

John hi,

Challenge is that client wants all output in excel, where dates are formatted like dates, decimals like decimals etc and not as text. I have two sets of sql i need to process to excel.

One is dynamic sql based on OS entity and entity_attribute tables, so there i know the data type and i can direct the output using Cell_WriteByIndex etc.

Second is where i receive the sql, but do not have the data types, so the output of the advanced sql is basically a structure of text columns, which goes through record list to excel, where i loop through the column, read the content of the second row, determine if it looks like decimal, integer or date/time and then write back to the excel in the formatted way.

That way, if you have outputted 20.000 rows with 8 columns to change the formatting, it takes about 10 seconds. So for larger data sets we gave them the unformatted export to excel also in which all is text, so at least they can get to the data.

For Kataphroneo, thanks for the advice also, if you would have an example of "Create new extension method which receives all of the rows and columns at the same time.", that would be appreciated, as i am not good in creating my own extensions.

Thanks,

Wilko