Dynamically include or Exclude Dimensions after TOTAL qualifier

[madhubabum]

Hi All,

I am having the below base expression : Sum(Sales)/Sum(TOTAL Sales)

If i select 'Region' Dimension from Adhoc Dimension list : Expression like : Sum(Sales)/Sum(TOTAL <Region>Sales)

If i select one more Dimension 'Area' from Adhoc Dimension list : Expression like : Sum(Sales)/Sum(TOTAL <Region,Area>Sales)

Note : If i selected one more dimension like 'City', we need to exclude form expression after TOTAL qualifier.

             Means always i need to exclude City dimension from expression after TOTAL qualifier.

Any suggestions ?

Thanks in Advance

[marcus_sommer]

I think I would just add another dimension-list which controls the TOTAL and querying it within the expressions, like:

sum($(=if(getselectedcount(TotalList)=0, '', 'TOTAL <' & concat(TotalList, ',') & '>')) Sales)

To keep the expressions more readable you may outsource the adhoc-variable into a normal variable.

- Marcus