Concatenating Values of a column

[Former Member]

***** 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])).

Regards,

Rogerio

Hi,

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])))

and

[final] to :

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

Cheers,

Rogerio

the stores name for each state