Show columns in table based on selections - Ad Hoc reporting

[#1]

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 'Posting year' , 'Posting month' , 'Posting week' even when they are not selected in the pivot list box 'Periodical dimensions').

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

[Anonymous]

if no selection had been made all columns will be displayed (by Default)

you need to enhance your condition to

GetSelectedCount (C_dimension) > 0

and

SubStringCount('|' & Concat(distinct C_dimension, '|') & '|', '|Customer|')

? would the first condition not be sufficient

GetSelectedCount (C_dimension) > 0

you will see the column if any of the C_dimension is selected

[#3]

Thanks Linder !

Yes, If i select any item in C_Dimension it will be displayed , but if i don't all the columns are displayed which should not. Any solutions for that ?

-James

[Anonymous]

try this

GetSelectedCount (C_dimension) > 0

you will see the column if any of the C_dimension is selected

[#5]

All I want is :

- Should display the columns those which are selected in any of the list boxes.

- Should not display all the columns if nothing is selected.

NOTE: there are 4 separate list boxes

[sujayrpatil]

Hi James,

Please try the following condition for Posting Year dimension:

(GetSelectedCount (C_dimension) > 0 OR GetSelectedCount(S_dimension) > 0  OR GetSelectedCount(M_dimension) > 0 OR GetSelectedCount(P_dimension) > 0) and

Max(Match(P_dimension,'Year'))

[#7 · Accepted solution]

Thank you all !!

Following piece of code worked for me:

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

[cbaqir]

Do you think this code should be used where columns are showing up with "-" see yellow highlight below:

My dimensions are in this format: =$(=only({$ <_dimensionNo={3}>} _dimension))

Dim label: =SubStringCount(Concat(_dimension, '|'), '$(=only({$ <_dimensionNo={3}>} _dimension))')

Expression =SubStringCount(Concat(_expressionName, '|'), '$(=only({$ <_expressionNo={1}>} _expressionName))')

Expression label: ='$(=only({$ <_expressionNo={1}>} _expressionName))'