NA Business Team
APAC SD Team
Yellowfin Support Team
NA Business Team
NA Business Team
Category
Report Builder
Product Version
7.3
Aggregate a text column
Answered
I have a text column that currently displays a unique row for each value. I would like to aggregate these, essentially with a concatenation, so that all of the text values are in one field.
Currently:
- 1 | A
- 1 | C
- 2 | B
Desired:
- 1 | A, C
- 2 | B
Is there a way to do this in Yellowfin?
The same question
Yes, though slightly difficult, this is possible in Yellowfin!
To accomplish this we will create a custom function using our DB's equivalent of MySQL's group_concat or SQL Server's & PostgreSQL's string_agg. A complete guide on implementing custom functions within Yellowfin can be found here: http://wiki.yellowfin.com.au/display/USER73/Custom+Functions
In this case we will want to add a new function to the custom-functions.xml file found in the <your_yellowfin>/appserver/webapps/ROOT/WEB-INF folder:
This custom function accepts a string parameter:
String - this is the text field that will be grouped and concatenated within the GROUP_CONCAT() function
Before:
After:
By default this expression will concatenate with a comma separating each value. To change this, you will need to research how your DB's specific function handles this and include the result in the <sql> section of the custom function.
Yes, though slightly difficult, this is possible in Yellowfin!
To accomplish this we will create a custom function using our DB's equivalent of MySQL's group_concat or SQL Server's & PostgreSQL's string_agg. A complete guide on implementing custom functions within Yellowfin can be found here: http://wiki.yellowfin.com.au/display/USER73/Custom+Functions
In this case we will want to add a new function to the custom-functions.xml file found in the <your_yellowfin>/appserver/webapps/ROOT/WEB-INF folder:
This custom function accepts a string parameter:
String - this is the text field that will be grouped and concatenated within the GROUP_CONCAT() function
Before:
After:
By default this expression will concatenate with a comma separating each value. To change this, you will need to research how your DB's specific function handles this and include the result in the <sql> section of the custom function.
Yes, though slightly difficult, this is possible in Yellowfin!
To accomplish this we will create a custom function using our DB's equivalent of MySQL's group_concat or SQL Server's & PostgreSQL's string_agg. A complete guide on implementing custom functions within Yellowfin can be found here: http://wiki.yellowfin.com.au/display/USER73/Custom+Functions
In this case we will want to add a new function to the custom-functions.xml file found in the <your_yellowfin>/appserver/webapps/ROOT/WEB-INF folder:
This custom function accepts a string parameter:
String - this is the text field that will be grouped and concatenated within the GROUP_CONCAT() function
Before:
After:
By default this expression will concatenate with a comma separating each value. To change this, you will need to research how your DB's specific function handles this and include the result in the <sql> section of the custom function.
Yes, though slightly difficult, this is possible in Yellowfin!
To accomplish this we will create a custom function using our DB's equivalent of MySQL's group_concat or SQL Server's & PostgreSQL's string_agg. A complete guide on implementing custom functions within Yellowfin can be found here: http://wiki.yellowfin.com.au/display/USER73/Custom+Functions
In this case we will want to add a new function to the custom-functions.xml file found in the <your_yellowfin>/appserver/webapps/ROOT/WEB-INF folder:
This custom function accepts a string parameter:
String - this is the text field that will be grouped and concatenated within the GROUP_CONCAT() function
Before:
After:
By default this expression will concatenate with a comma separating each value. To change this, you will need to research how your DB's specific function handles this and include the result in the <sql> section of the custom function.
Are there any plans on simplifying this in the future? I'm using a third party license with no access to the above. I'm using FOR XML in my view to accomplish this.
Are there any plans on simplifying this in the future? I'm using a third party license with no access to the above. I'm using FOR XML in my view to accomplish this.
I'd like to experiment with this. For the DB's relevant line or DB name in the function, should we refer to the DB hosting our YF app? or the target DB of our view? If it's the latter I assume we'll need separate custom functions for both MySQL and SQL sources?
I'd like to experiment with this. For the DB's relevant line or DB name in the function, should we refer to the DB hosting our YF app? or the target DB of our view? If it's the latter I assume we'll need separate custom functions for both MySQL and SQL sources?
...
...
Hi Diogo,
Just wanted to check to see if there was something you needed here?
Thanks,
Eric
Hi Diogo,
Just wanted to check to see if there was something you needed here?
Thanks,
Eric
Hello Eric,
All OK. I realised I had in the past created this custom function and had not added it after the latest update of YF.
Ticket can be closed.
Thanks,
Diogo
Hello Eric,
All OK. I realised I had in the past created this custom function and had not added it after the latest update of YF.
Ticket can be closed.
Thanks,
Diogo
Replies have been locked on this page!