How to hide Crosstab Columns based on prompt selection without Using Conditional Styles and Variable:
1. Create a value prompt with all the optional columns (In this example I am using prompt Name as pOptClms).
2. Change the Optional columns Data Items/Query Items expressions as for Quantity Column:
IF ( 'Quantity' in ?pOptClms? ) THEN
( [Sales (query)].[Sales].[Quantity] )
ELSE
( Cast(Null as Integer) )
Note: If you want hide any dimension then you need to change the else part as (Cast(Null as Char))
By change the expression with If Statement that will make the prompt as Mandatory. If you want to have optional prompt then you need to Use macros. Replace the If condition with the below macro:
‘Quantity’ in (#promptmany(‘pOptClms’, ‘token’,’’’XYZ’’’)#)
3. Similarly you need to change the expressions for all the optional columns.
4. Now from the Data menu click on Suppress and then click on Suppression Options.
5. From the Suppression options: Click on Columns only and then Uncheck Zero values, Divide by zero and Overflow values.
Run the report and see the result.
This approach can be very useful when you have to align multiple Crosstabs.
No comments:
Post a Comment