Now the above result shows the issue you are facing. Notice that the null value for the first row(Bib-shorts) is now repeated for all values of the second dimension, Since cube has no way to determine which value to display. Now lets add another dimension to the query. The below sample query is based on AdventureWorks, the first sample shows that the result has some nulls, if i un-comment the 'non-empty' all null values will vanish, go ahead try it. Now with Excel you cannot solve the issue, however it might just be possible to write an MDX query that works. So if we have 3 values in Dimension2, attribute 1 then 'Value 1' will be repeated three times.

When you add another dimension's attribute lets say Dimension2.Attribute1, since 'Value 1' had nothing in Fact, therefore Cube will not understand which value of of Dimension2.Attribute1 is to be displayed in front of Dimension1.Attribute1, therefore it will display all its values. Lets suppose that for Dimension1 ,attribute1, and the attribute 'Value 1', you had nothing in Fact, so this is removed from the result, now you forced Excel to display it by selecting the option. So is there something that I can add the SCOPE above to just work in the program dimension and get ignore/ skip and work as normal if any other dimension added to the pivot table? then I found this solution using SCOPE, below, but just like above I could not find an away to just have the scope ignore the logic if any other dimension added so the data does not get duplicated. The problem here is that using this option is going to affect the other dimensions and the requirement is to only this work for the Student dimension only and the user doesnвЂ™t like to keep changing the option back every time.

Working on a task that required to bring all a dimensionвЂ™s data to the Pivot Table(Excel Sheet) even if they are not related to the fact.ДЇirst I was able to do it by using the option вЂњPivotTable OptionsвЂќ -> вЂњDisplayвЂќ -> вЂњShow items with no data on rowsвЂќ from Excel.

Show items with no data greyed out