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