MDX -> Pasting query into powerpivot produces the following error but works in CubeBrowser -> "unable to cast object of type 'system.dbnull' to type 'system.string'"

Question

Here is my query.

 

SELECT NON EMPTY { [Measures].[Revenue],  [Measures].[PrevDayRevenue], [Measures].[RANKRevenuePrevOrder], [Measures].[RANKRevenueCurrOrder] } ON COLUMNS,

NON EMPTY { ( [RevRankCurrOrder] ) } ON ROWS 

FROM [DW] 

WHERE {[Date Link].[PK Date].&[2012-01-10T00:00:00]}

I am just wondering if anyone has seen this issue before.  I know that issues occur when pasting an MDX query into Excel and SSRS, but need to know if there is any way to fix this, etc.  

Any help direction will be greatly appreciated.

Thanks.

***Update (01/27/2012). :  I have went through and updated all of the measures and sets that I am using adding in IIF(IsEmpty( to eliminate null situations that could occur.  I am still experiencing the same error.  

Answer 1

Hi kSmith,

As i can't reproduce the issue in my powerpivot environment, i can't explain the root cause for you. However, could you please use the Design mode to automatically generate the MDX query instead of copying it to the MDX Statement box. To do it, when you are on "Specify a MDX query" page, click "Design" button. In the cube query window, drag and drop dimensions and measures required to the result and filter fields to get the data returned as the original MDX query.

Regards,
Jerry

Answer 2

Hi Jerry,

Thank you for your response.  I am unable to actually get any query to work whatsoever in powerpivot with this specific cube (using designer or man made mdx).  I have found that if I remove my calculated sets and members from the cube then reprocess that I am then able to run any simple query not involving sets and members (via powerpivot, every type of query [designer/manmade] works in CubeBrowser).

I should have specified that I am using RC0 2012 (powerpivot/ssas/sql server) all in conjunction so there could be some bugs present.

From my standpoint currently, I think it may have to do specifically with using the rank function in powerpivot but it looks to be something bigger than that.

I actually posted another question regarding whether the RANK function would cause such a problem, no one has responded regarding this as of yet. :

http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/7669ca50-3f04-457c-b64d-a2be230b2f9a/#7669ca50-3f04-457c-b64d-a2be230b2f9a

All this being said there are still a lot of reasons as to why this could be occurring.  If you have any insight regarding this.  I would greatly appreciate hearing it.  It could obviously be an issue with my sets and members, but that does not explain why it works in the CubeBrowser but completely blows up anything and everything when attempting to use with powerpivot.....

Thanks.

Answer 3

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.