How to create a pivot table / crosstab

How to create a pivot table / crosstab

  
Hi,

I'm looking for some help to create a crosstable. Is this possible with Outsystems?

Thanks in advance
Hi Adriana,

Welcome to the OutSystems community forums.

The OutSystems Agile Platform allows you to develop custom web applications, and as such you can create a web form that does exactly the same thing as a pivot table in Excel would do. The advantage here is that you can tailor it to do exactly what you want to - the downside is that you have to develop it yourself.

Thankfully, thanks to our WYSIWYG web editor in Service Studio, it's very easy for you to create a web page the way you want to, by dragging and dropping the widgets you want. However, since it is not a feature that comes out of the box, I suggest you look into our free online academy, and follow our Developer Course 1 tutorials, to get you up to speed. After that, I'm pretty sure you'll be able to do what you want to do, by yourself - and we're here to help you along the way, whenever any other question comes up!

Hope this helps.

Regards,

Paulo Tavares
Hi Paulo,

Thanks for your advise. I followed the Developer Courses but am still wondering how to make a crosstab query. 
In MS Access it can be something like:

TRANSFORM Sum({TABLE}.[COLUMN1]) As SumColumn1
SELECT {TABLE}.[Column2]
FROM {TABLE}
GROUP BY {TABLE}.[Column2]
PIVOT {RISK}.[Column3]

However the SQL function PIVOT seems not to work in OutSystems. This manner implies variable table attribute names as well.

Is there a solution available?

Thanks for helping anyway!
Hi Adriana,

Thanks for getting back to us. I was suggesting doing all the Pivot logic in application logic, but if you're doing it in SQL, it should work as well, provided you're using SQL Server 2005 or higher, in the correct compatibility mode. Read this article from MSDN for more information about it.

For you to use it in OutSystems, you should use an Advanced Query, and define a proper Output Structure, with the right amount of attributes, for the output of the query. That, however, could be a bit harder to do, if you do not know how many columns you will need before runtime.

Regards,

Paulo Tavares