Skip to Content
Author's profile photo Former Member

Concatenating Values of a column

***** NOTE In 03/01/2016 *********

If your requirement is to get the list of values of a dimension [dim] , use ReportFilter([dim]). This will give you the LOV of [dim] , separeted by “;”.

If you need the list unfiltered use NoFilter(ReportFilter([dim])).




Sometimes we face a requirement of concatenating values from an object., like below screen :


This can be achieved with 3 variables :

[conc] , a dim variable

[conc] = ([State]+”,”+Previous(Self));

[final] a measure variable

[final] =Last ([conc] ForEach ([State])) In Report

[No_ending_comma] a measure variable

[No_Ending_Comma] = Left([Final];Length([Final])-1)

How does it work :

The [conc] variable, will concatenate the value of [State] and the previous value of [conc], since the Previous value of ‘California’ doesn´t exist, it returns null and the the first row of [conc] is justa California. Refer to the above image, column [conc] , to see the values of [conc] as [State] is shown.

The  second variable [final] is where the ‘trick’ works :

It takes the last values of [conc] in the report calculated in the context of [State]. I use the ForEach operator so I can get the last value independent of the context used.

This example shows the values concatenated in descending order. To get it in ascending order, use [conc_asc] = (Previous(Self)+”,”+[State])

[final_asc] = =Last ([conc] ForEach ([State])) In Report

[No_ending_comma_asc  =  Rigth([final];Length([final]-1)

And what about the following cenario :

The requirement is to concatenate the stores name for each state

Here, I queried e-fashion for [State] and [Store Name]


I altered [conc] to :

=(Previous(Self;([State]))  ForEach([State];[Store name]) +”,”+([Store name] ForEach([State];[Store name])))


[final] to :

=Last ([conc]ForEach([State];[Store name])) In ([State])



the stores name for each state

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Very nice!. Thanks for sharing.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      thank you Marek!!

      Author's profile photo Former Member
      Former Member

      Just what I needed. Ta

      Author's profile photo Andrea Molina
      Andrea Molina

      Hi Rogerio Plank . Thanks for your post. It helped a lot.

      Just one question. I need to show just the top 3 data and I'm using ranking and it is not working. I get the 3 mixed. Any idea why?



      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Andrea,

      thanks for your comment.

      Can you give me an example in a Excel file, so I can try working it out ?



      Author's profile photo IDA GHIRARDI


      this is exactly what I need.

      when implementing conc variable as dimension, I get #MULTIVALUE result.

      I'm running DESKI v (an old version indeed)



      Author's profile photo Michael Goddard
      Michael Goddard

      Hi all, this looks to be exactly what i need however the part i am struggling with is where do i create 1 variable or 3?


      I am trying to get a list of customers who have bought each MID


      can someone help explain this to me?


      thanks Mike