Hide irrelevant cells on Multi Column reports

Introduction

In some cases it is useful to have certain meaningless cells hidden.

For example, if your company does not want to show to the YTD values for certain accounts.

Hide Columns

Type	CXO-Cockpit Tips and Tricks
Audience	What is the role of the CXO-Cockpit Captain
Difficulty	Medium
Applies to	Multi-Column Template

Solution

The solution is to introduce a calculated member of the measure dimension. This measure checks if a certain dimension value is used and then returns null (empty).

Read more about creating calculated members in the /wiki/spaces/CXO62/pages/105733568 Article

Example MDX statement

In this example, we would use the MEA (measure) dimension to create a custom calculation to apply the logic for hiding the row values that combine with a YTD column:

CREATE MEMBER CURRENTCUBE.[MEA].[MEA].Null_WhenYTD
AS CASE
WHEN [VIW].[VIW].CurrentMember.Name = 'YTD'
THEN NULL
ELSE [MEA].[MEA].[ [None]] ]
END,
VISIBLE=1 ;