Skip to main content
Open

Define a SQL expression for a column aggregation

Related products:Software FactoryUniversal GUI
J
Rookie

My view has a column that contains the space used on a single pallet, which ideally should total 1 (1 full pallet). The idea is to use the aggregation of a column to show both the sum, and the “remaining” space.

For example: if a pallet is half full, it would show 50/50, if it is 90% full, it shows 90/10.

I think it is possible using an SQL expression for the aggregation type, but I don’t know where to define the logic after selecting it in the subject components grid settings:
 

SQL expression setting
​​​​​​

I imagine it could look something like this (for my usecase):

SELECT  CONCAT( ROUND(SUM(value), 2), '/', ROUND(1 - SUM(value), 2) ) AS custom_aggregation FROM t1;

 

Update by moderator: converted question into an idea. 

Did this topic help you find an answer to your question?

5 replies

Jeroen van den Belt
Administrator
Forum|alt.badge.img+9

Hi ​@jkruter,

We investigated this and found that the values "Formula (deprecated)" and "SQL expression" were incorrectly selectable here, as they are not supported as column aggregations.

This issue occurred because in Cubes > tab Values, there is also an Aggregation type column where these elements should be available, and both fields mistakenly shared the same domain.

We've corrected this in the upcoming release, so the invalid options will no longer be selectable.

Unfortunately, there is no workaround to display a formula as a column aggregation.

Sorry for the inconvenience.

With regards,

Jeroen

J
Rookie
Forum|alt.badge.img
  • jkruterRookie
  • Author
  • Rookie
  • 3 replies
  • July 8, 2025

Thanks for the answer Jeroen!

Is it possible to move this thread over to the ideas forum instead then? 😉

I imagine it could look something like this (for my usecase):

SELECT  CONCAT( ROUND(SUM(value), 2), '/', ROUND(1 - SUM(value), 2) ) AS custom_aggregation FROM t1;

 

Jeroen van den Belt
Administrator
Forum|alt.badge.img+9
New→Open
Jeroen van den Belt
Administrator
Forum|alt.badge.img+9

Absolutely, I converted it into an idea. Don't forget to vote for it yourself, this is something I cannot do for you.

J
Rookie
Forum|alt.badge.img
  • jkruterRookie
  • Author
  • Rookie
  • 3 replies
  • July 8, 2025

Thanks! Don’t forget to vote for it yourself either! 

Geurt
1 person likes this
  • Geurt
    Geurt

Reply


Terms & ConditionsCookie settingsAccessibility statement

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings