Render Crosstab Report Columns Using Value Prompt

Show and Hide Crosstab Report Columns based on Value Prompt

Requirement: Hide and show crosstab columns based on multi select value prompt.

Solution:

Create a crosstab report with multiple columns say A, B, C &D, row as E and a measure X.

Now drag a value prompt and click Finish.

Make it Multi Select and Check Box.

 

Define the static choices for the prompt say A, B, C & D.

 

Now go the report query and open the data item A.

Write the CASE statement.

if(A in (#promptmany('P_PromptName','token','[A]')#)) then ([A]) else null

Similarly edit all the remaining columns.

if(B in (#promptmany('P_PromptName','token','[B]')#)) then ([B]) else null

Now go to the report page select Crosstab and from the properties Pane Click Suppression.

 

Apply suppression for Columns only.