Hide a row in Excel OLAP pivot table

0
votes

I have an Excel sheet which connects to a cube. The information is presented in a pivot table. The problem is that I need to hide one member of the dimension on the rows axis.

That is I have the following table.

a value1

b value2

c value3

total

I want to hide the row with value b. I cannot solve this with filters in the pivot table since the member must always be hidden and if the filter is used then a user can select it.

What I have tried so far:

  • Use a named set with an MDX calculation. This does not work since a named set cannot be used in filters.

  • Use a calculated measure IIF(currentmember = b, null, value). This does not work since the grand total still includes the value for b.

Any suggestions? I prefer not to create a scoped member in the cube specifically for this report.

2
sql-serverexcelssaspivot-tableolap
Maybe permissions may help here? You could disallow access to member b to a role in which all users using pivot table access are members. – FrankPl
Thanks for the suggestion but unfortunately the row need to show when the users are browsing the cubes themselves. I only need to hide it for this specific report. – user2053579

2 Answers

0
votes

In case anyone is still interested I found a solution to the problem.

  1. Created a new measure in the cube with a null value.
  2. Created a scope calculation for the measure in the cube, IIF(currentmember = b, null, value).
  3. Created a new perspective in the cube list where the new measure is not visible.

Lots of work but I could not find any other options in this case.

0
votes

I've searched high and low for this answer. I had a similar issue. I was trying to calculate a measure from other 3 measures then filter and aggregate the filtered result. I came up with this:

  1. Calculate the column in the source view table ( a - b + c = x)
  2. Add the unfiltered calculated column (x) to the dsv
  3. Create a named calculation in the dsv that uses a case statement to filter the original calc measure
  4. Add the named calculation as measure

I choose to do it this way to capture the measure unfiltered first then, if another filter needs to be added or one needs to be taken off I can do so without messing with the views again. I just add the new dim to filter by to my named calculation case statement.