How to use Aggregates to substitute Advance SQL?

Without using Advance SQL (sticking to Aggregates and List), how can I achieve the final output (as shown in the screenshot)? Please refer to the excel for the data sample.

Excel Data Sample:

Screenshot Example:

(For some reason, whatever image files (JPG, JPEG, PNG) i tried to upload, it throws file extension error)

What have you tried, and what were your difficulties?

I tried to use 2 web blocks to get the HolidayCount and WorkCount but find it cumbersome. If the end-user requires more than two columns, it will be odd to keep adding web blocks.

Using traditional approach, i would just use Crystal Report to generate Cross tab report.

I also tried to use Group and Join but it did not give the result i wanted.

So my difficulty lies in how to generate "cross tab" using just aggregates in Outsystems? The end-user prefers me not to use advance sql where possible.

Just like ListAppendAll takes over 'Union' in Advanced SQL, is there anyway to "ListJoin" to combine two aggregates together just like a 'Join' with 2 entities?

Ok, I'm assuming you know what a Join is. If you don't, then it seems that it's a similar concept of the "cross tab report" you have mentioned.

Your final output has data coming from two entities: Country and PersonLocation. So the first thing you'll need to do is to join those two tables together. You will find that they are not directly related - you will need to add a third entity into the aggregate to be able to relate Country and PersonLocation.

Then you will need to group by Country.Name. This will reduce your output to only those two countries. But won't give you the counts.

Your counts are not simple counts, but conditional counts. The HolidayCount is the number of PersonLocation on that country that has Remark="Holiday". The WorkCount is similar, but for Remark="Work". You will need to add two calculated attributes if you want to solve this using aggregates.

The first calculated attribute will be 1 if Remark="Holiday", but 0 otherwise. The formula that can be used for that effect is: If(PersonLocation.Remark = "Holiday", 1, 0). After you have added this calculated attribute, your HolidayCount will simply be the Sum of this attribute.

You should be able to do a similar thing for the WorkCount.