Dynamic dimensional scope

[Eman]

Hi all,

I'm trying to create a report composer where the users can create pivot tables by their own choosing which dimensions and expressions show in the table. 
Everything seems to work fine until I need to show the row relative values (%).

Absolute:

dim1	dim2	exp1	exp2	total
x	a	10	90	100
	b	30	30	60

Relative:

dim1	dim2	exp1	exp2	total
x	a	10%	90%	100%
	b	50%	50%	100%

= Count(DISTINCT my_value) 
/
Count( TOTAL <dim1, dim2> DISTINCT my_value)

In this case i can easily define which are my dimensional scopes (dim1 and dim2) to obtain the correct relative value. But what about if user needs to add dim3? This change my formula to:

= Count(DISTINCT my_value) 
/
Count( TOTAL <dim1, dim2, dim3> DISTINCT my_value)

Is it possible to do it dynamically? 

I tried something like:

= Count(DISTINCT my_value) 
/
Count( TOTAL <GetFieldSelections(dim_selected)> DISTINCT my_value)

or using variables to get the selected fields or, even, converting total to set analysis but without any success.

Can somebody help me please?

Thanks in advance,
Emanuele

[rubenmarin · Accepted solution]

Hi Emanuel, I think you attempt was close, can you try it using a $-expansion syntax?:

= Count(DISTINCT my_value) 
/
Count( TOTAL <$(=GetFieldSelections(dim_selected))> DISTINCT my_value)

Or using concat:

= Count(DISTINCT my_value) 
/
Count( TOTAL <$(=Concat(dim_selected,','))> DISTINCT my_value)

[Eman]

Hi Ruben,

first solution works properly! Thank you very much!