Powerpivot error occurs -> Unable to cast object of type 'System.DBNull' to type 'System.String'. -> Only occurs when I have calculated measures present in my cube.

Question

I had posted a question regarding something of this nature earlier this year, and went on kind of forgetting about it.  Here I am many cubes later and once again experimenting with PowerPivot in order to make the life of my end users easier.

What I have come to find out is that anytime any type of calculated member is present in my cube this specific error is present, and in turn, I must click the checkbox, "Import measures as text".

This works for the most part aside from the fact that it removes all the datatypes associated with my measures, and I have to go in and manually assign these in order to use any datatype associated functionality in my pivot tables..........(very annoying)

I am not about to create one cube for calculated members and one cube for end user powerpivot usage, although that is a possible and enormous workaround.  

Maybe this was fixed in the non RC0 version of SQL Server, I do not know as we have not moved to this as of yet.  (need to finish our demo products in order to move to this)

Any thoughts/ideas on this are greatly appreciated.

Thanks.

Answer 1

After further review, I have realized that I have been creating my null checks and zero checks incorrectly this entire time.

If you are having this issue I suggest you read Mosha's page regarding IsEmpty and Zero checks with calculated members if you are having this issue.

http://sqlblog.com/blogs/mosha/archive/2005/06/30/how-to-check-if-cell-is-empty-in-mdx.aspx

If you are still having issues after reading this I suggest you read it again because that is what I should have done.

***Update I have changed all of my precalculated measures to work as Mosha provides in the above blog, and still have to import my measures as text.  Hopefully someone has figured out how to solve this issue.

Answer 2

Further further review, looks like this is an existing issue can't believe it took me this long to find this link.

http://connect.microsoft.com/SQLServer/feedback/details/538102/powerpivot-rc-doesnt-recognize-a-measure-from-ssas-as-numeric#

If you are using 2012, make sure you are supplying your calculated members with formats.  Every single one, most of those formats will carry over as text to powerpivot, but it will stop the dbnull error and you won't have to pull the entirety of your query in as string columns.