How can I concatenate multiple values of a single column in analytics, instead of the value displaying on a separate row?

Answer

The formula below can be used to concatenate multiple values into a single column. The user will need to replace the "folder name"."field name" with the folder name and field name they want to concatenate.

cast(EVALUATE_AGGR('LISTAGG(%1, %2  on overflow truncate) WITHIN GROUP (ORDER BY %1)  ', "folder name"."field name", '| ') as  varchar(4000))


The example below will concatenate the barcode field using "Physical Item Details"."Barcode":

cast(EVALUATE_AGGR('LISTAGG(%1, %2  on overflow truncate) WITHIN GROUP (ORDER BY %1)', "Physical Item Details"."Barcode", '| ') as varchar(4000))


To use the formula to concatenate the barcode field in an analytics report:

  1. Click Edit Formula from the cog icon for the Barcode column
  2. Replace "Physical Item Details"."Barcode" in the formula with:
    cast(EVALUATE_AGGR('LISTAGG(%1, %2  on overflow truncate) WITHIN GROUP (ORDER BY %1)', "Physical Item Details"."Barcode", '| ') as  varchar(4000))
  3. Click the check box next to Custom Headings and name it Barcode
    • If you do not check this, the heading converts to the formula name
  4. Click Ok
  5. Click theSave icon
  • Last Updated Jun 21, 2024
  • Views 484
  • Answered By Margaret McGee

FAQ Actions

Was this helpful? 3 0