Search code examples
sql-serverexcelssaspivot-tableolap-cube

Pivottable on SSAS cube: hide rows without result on measure

I'm having trouble hiding rows that have no data for certain dimension members for the selected measure, however there are rows for that member in the measuregroup.

Consider the following datasource for the measuregroup:

+---------+----------+----------+----------+--------+
| invoice | customer | subtotal | shipping | total  |
+---------+----------+----------+----------+--------+
| 1       | a        | 12.95    | 2.50     | 15.45  |
| 2       | a        | 7.50     |          | 7.50   |
| 3       | b        | 125.00   |          | 125.00 |
+---------+----------+----------+----------+--------+

When trying to create a pivottable based on a measuregroup in a SSAS-cube, this might result in the following:

enter image description here

However, I would like to hide the row for Customer b, since there are no results in the given pivottable. I have tried using Pivottable Options -> Display -> Show items with no data on rows but that only works for showing/hiding a Customer that's not at all referenced in the given measuregroup, say Customer c.

Is there any way to hide rows without results for the given measure, without creating a seperate measuregroup WHERE shipping IS NOT NULL?

I'm using SQL-server 2008 and Excel 2013

Edit:

For clarification, I'm using Excel to connect to the SSAS cube. The resulting pivottable (in Excel) looks like the given image.

Solution

  • In the DSV find the table with the shipping column and add a calculated column with expression:

    Case when shipping <> 0 then shipping end

    Please go to the properties window for the Shipping measure in the cube designer in BIDS and change the NullHandling property to Preserve. Change the source column to the new calculated column. Then redeploy and I am hopeful that row in your pivot will disappear.

    And ensure Pivottable Options -> Display -> Show items with no data on rows is unchecked.

    If that still doesn't do it connect the Object Explorer window of Management Studio to SSAS and tell us what version number it shows in the server node. Could be you aren't on the latest service pack and this is a bug.