While working on an SSAS project today, I was scripting out some MDX calculations in the cube (year to date, quarter to date, month to date, previous year’s month to date, and previous year to date). I wrote the calculations and they looked fine to me, but when I tried to browse the cube in BIDS after deploying, I received this very perplexing error:
I noticed that when I traversed the date hierarchy it seemed to be isolated to the QTD functions, so of course I focused on those. I tried a few variations of the MDX (QTD, PeriodsToDate) all looked good. After some additional stumbling around and doing some research, I eventually discovered that if I clicked the “Show Empty Cells” icon (seen below),
my calculations appeared to be working correctly. Apparently this is some kind of bug in the cube browser in SSMS and BIDS. You’ll notice that if you connect to your cube from Excel and browse the cube using a Pivot Table, you are able to successfully browse the cube without any strange errors. I hope this saves someone else the 45 minutes I spent trying to figure out what in the world was going on.