New Topic

Community Topic Question Report Builder

Nathan Schroeder

NA Business Team

Assigned

Category Report Builder

Product Version 7.3

How do I concatenate more than two columns?

Rebecca Lees shared this question ago

Answered

Hi,

I'm currently building a report that requires multiple fields to be concatenated. There is more than 3 fields. Is there a way to concatenate all the fields into one easy to use column?

Thank you,

Rebecca

The same question

Replies (3)

Nathan Schroeder ●

Hi Rebecca,

Thank you for getting in touch. To concatenate more than two columns, you can create a calculated field and add together each of the string columns, separated (or not) by a space as seen below:

db8e608d17df2ea017b93af13180d422

To include the space, simply type a space, without quotes into the input box above the "undo" button.

Regards,

Nathan

Reply URL

Eva Bugaric ●

This doesn't work for an Oracle database as Oracle uses pipes || rather than the plus sign +. Any idea how to do this for Oracle? Thanks.

Reply URL

Nathan Schroeder ●

Hi Eva,

You will need to perform this by creating a freehand SQL calculated field, employing pipes rather than +s. If you have trouble implementing this please let me know and we can schedule a screen-share session.

Regards,

Nathan

Reply URL

Eva Bugaric ●

Hi Nathan,

This worked for me. I have tried to create another one where the columns come from another table but I get errors because since I have not yet added any columns from this table to my report, it does not join on that table and I get identifier errors. Why do we have to add at least one column to the report before we can create calculated fields to concatenate strings?

URL

Nathan Schroeder ●

Hi Eva,

Yellowfin's reports are essentially just compiling an SQL statement to present to you. So when there is only one column in the report, the SQL will simply select from that table alone. When you add a freehand SQL, the FROM clause in the underlying SQL does not change to include the new table join until you provide a column that naturally includes that table. I am not entirely sure why this is the case, but this issue has been raised in the past and the answer has always been: this is expected behavior, inherent in the way that freehand SQL functions work inside of Yellowfin.

Sorry that I can not be more specific and glad to hear you were able to achieve your results.

Regards,

Nathan

URL

Eva Bugaric ●

OK, so basically I would have to add a column to get the join, create my calculated field to concat the strings I needed from that specific table, then hide the newly added column since I don't want to see it. This seems to work out. Thanks for your help.

URL