Hi I want to show the records as given below


In the pic above For name Carly there is two records but I want to show the Name only in first row and Year Total (calculated column) is the addition of 5 + 10 i.e 15.

I want to show like this for all the name in the table. Please, Provide the optimized solution how to do this.   

Hi,

If you run on SQL Server then have a look at the OVER clause

https://www.outsystems.com/forums/discussion/51548/aggregate-or-advance-sql/

As for to not show a value in  a cell you can check if value is same in currentrow - 1 using an if() in your cell expression.

Regards,

Daniel


Daniël Kuhlmann wrote:

Hi,

If you run on SQL Server then have a look at the OVER clause

https://www.outsystems.com/forums/discussion/51548/aggregate-or-advance-sql/

As for to not show a value in  a cell you can check if value is same in currentrow - 1 using an if() in your cell expression.

Regards,

Daniel


Yes, But how to calculate Yeat total for perticular usename? Thanks for replaying.


hi,

# you can use query as shown in the link

# or create a function that do the total year, given input parameter Name

# in the column year total, put an expression to call that function 

Rohan Hanumante wrote:

Daniël Kuhlmann wrote:

Hi,

If you run on SQL Server then have a look at the OVER clause

https://www.outsystems.com/forums/discussion/51548/aggregate-or-advance-sql/

As for to not show a value in  a cell you can check if value is same in currentrow - 1 using an if() in your cell expression.

Regards,

Daniel


Yes, But how to calculate Yeat total for perticular usename? Thanks for replaying.


Did you look at the SQL OVER clause, and try to implement it in an advanced SQL? Did you not get required result?

If so could you share your an OML with data bootstrap?


http://www.sqlservertutorial.net/sql-server-window-functions/sql-server-lag-function/

The best 'Low code' way is:

  • Use a Aggregate to get to whole list
  • Create a function GetYearTotalByName;
    • input the list, Name and year
    • ListFilter the List on Name
    • Loop the the filtered list and sum the YearTotal

Or:

  • Create a Screen with a Aggregate Group by Name with Sum(working Days)
  • Create a webblock with Name as parameter (better would by the Id of the User) and use this parameter in a Aggregate of this webblock, this will get all the detail lines of Name.
  • Use this Webblock in every Row in the 1st screen