Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table and set of pivot dimensions in some list boxes (See attached) .
I have given the condition:
GetSelectedCount (C_dimension) > 0 OR GetSelectedCount(S_dimension) > 0 OR GetSelectedCount(M_dimension) > 0 OR GetSelectedCount(P_dimension) > 0
for the table in calculation condition field in the 'General' tab of table properties. And with a condition like
=SubStringCount('|' & Concat(distinct C_dimension, '|') & '|', '|Customer|')
for every dimension in the 'enable conditional' option.
All the columns are getting displayed in the table when I did not select any pivot dimension from the list box. (Attached scenario displays all the dimensions from 1st ,2nd 4th list boxes even when they are not selected. For this scenario I want to display 'Material' and corresponding expressions 'Total Sales E' , 'Total Sales Y' only. It should not display un-selected dimensions from other list boxes ).
I read the dimensions from the following script.
CustomerDimensions:
LOAD * INLINE [C_dimension
Customer
Business Area
Country
Profit Center
Region
];
SalesDocDimensions:
LOAD * INLINE [S_dimension
Sales Office
Sales Order
Currency
Sales Order Item
];
MaterialDimensions:
LOAD * INLINE [M_dimension
Material
MaterialDescription
MUG Header
Material Category
MUG
NKE
];
PeriodicalDimensions:
LOAD * INLINE [P_dimension
Week
Year
Month
];
I want the table not to display columns which i don't select. Should show some message if i don't select any dimension at all.
TIA,
James
Your calculation condition for the chart can be this
GetSelectedCount (C_dimension) > 0 OR GetSelectedCount(S_dimension) > 0 OR GetSelectedCount(M_dimension) > 0 OR GetSelectedCount(P_dimension) > 0
and then for individual dimensions, you can try one of the two options as proposed above
1) =SubStringCount('|' & Concat(distinct C_dimension, '|') & '|', '|Customer|') and GetSelectedCount (C_dimension) > 0
or
2) =SubStringCount('|' & GetFieldSelections(C_dimension, '|') & '|', '|Customer|')
Use GetFieldSelections rather than Concat:
=Index('|' & GetFieldSelections(C_dimension, '|', 40) & '|', ',|Customer|,')
Concat() returns all the values if nothing is selected, but GetFieldSelections() has the required behaviour.
Hi Jonathan,
GetFieldSelections returns none even after selecting one dimension from each list box. It displays expressions alone.
Please help.
-James
Hi James
I do exactly the same thing in my app and this expression works fine for me in the Enable Condition of one of the dimensions (changing the 'TAC' part accordingly for each dimension)
=SubStringCount(Concat(_DimensionID, '|'), 'TAC')
so yours would be
=SubStringCount(Concat(C_dimension, '|'), 'Customer')
Does this work for you?
as Jonathan answered this will get you all values of the Dimension if nothing is selected
enhance your part by GetSelectedCount (C_dimension) > 0 and it works even if None is selected
=(GetSelectedCount (C_dimension) > 0) and (SubStringCount(Concat(C_dimension, '|'), 'Customer')>0)
Unlucky !
I already tried it Tracy !!
I guess there is a gap between my 'calculation condition' and 'Enable conditional' , isn't it ??
-James
I am not able to get your answer Rudolf.
You mean to enhance my 'calculation condition' from GetSelectedCount (C_dimension) > 0 to (GetSelectedCount (C_dimension) > 0)
and 'Enable Conditional' from SubStringCount('|' & Concat(distinct C_dimension, '|') & '|', '|Customer|')
to (SubStringCount(Concat(C_dimension, '|'), 'Customer')>0)
Am i right ?
-James
no you Need to add the complet Expression for each column in enable condition
=(GetSelectedCount (C_dimension) > 0) and (SubStringCount(Concat(C_dimension, '|'), 'Customer')>0)
calculation condition will allow you to Show or not to show the Chart, while enable condition allows you to control each single column one by one
as I understood you want to control the behaviour in a table. you select a value from your dimensions and this column should be displayed and not others which are not selected
Your calculation condition for the chart can be this
GetSelectedCount (C_dimension) > 0 OR GetSelectedCount(S_dimension) > 0 OR GetSelectedCount(M_dimension) > 0 OR GetSelectedCount(P_dimension) > 0
and then for individual dimensions, you can try one of the two options as proposed above
1) =SubStringCount('|' & Concat(distinct C_dimension, '|') & '|', '|Customer|') and GetSelectedCount (C_dimension) > 0
or
2) =SubStringCount('|' & GetFieldSelections(C_dimension, '|') & '|', '|Customer|')
Perfect !
Thank you so much Sunny ! The MAN !! (Y)
Following combo worked.
calculation condition:
GetSelectedCount (C_dimension) > 0 OR GetSelectedCount(S_dimension) > 0 OR GetSelectedCount(M_dimension) > 0 OR GetSelectedCount(P_dimension) > 0
Individual dimensions:
=SubStringCount('|' & Concat(distinct C_dimension, '|') & '|', '|Customer|') and GetSelectedCount (C_dimension) > 0
-James