Unable to cast object of type 'System.DBNull' to type 'System.String'. RRS feed

  • Question

  • Trying to pull data into PowerPivot from analysis services via the MDX Query Design tool. I can build out the exact query I'm looking for, but keep encountering this "Unable to cast object of type 'System.DBNull' to type 'System.String'." error whenever I try to finish the import. It's a valid query, and I'm not sure what's going on and can't find any help via searching online aside from a single vague forum post 2009 (which doesn't help my cause).

    I need help figuring out what's wrong and how to fix it. Any ideas? Would love a link to a similar problem/solution online if anyone's better at using Bing than me.

    Here's the MDX generated by the Design tool:
     SELECT NON EMPTY { [Measures].[Time1], [Measures].[Time2] } ON COLUMNS, NON EMPTY { ([Date].[Date YMD].[Date].ALLMEMBERS * [Person].[Person Name].[Person Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Person].[Boss].&[Jane Doe], [Person].[Boss].&[John Smith] } ) ON COLUMNS FROM [PersonGroup]) WHERE ( [Person].[Boss].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Friday, May 31, 2013 7:44 PM


  • Hi Gunday,

    What's the version of your SQL Server PowerPivot for Excel? In Excel 2010 PowerPivot, we will encounter the error when trying to import data from an Analysis Services cube that contains calculated measures.

    Cause: Outdated version of PowerPivot: 11.0.2100.60

    Please refer to the following steps to solve this issue:

    1. 1. First,  UNINSTALL the PowerPivot you currently have on your machine via the Control Panel/Add Remove Programs.
    2. 2. Go to this URL: http://www.microsoft.com/en-us/download/details.aspx?id=29074
    3. Review the “System Requirements” for any additional information on what may be needed

    Click the DOWNLOAD button appropriate for your version of Excel (32-bit or 64-bit) and follow the prompts to download the new version of PowerPivot for Excel.

    Best Regard,

    Elvis Long
    TechNet Community Support

    Friday, June 7, 2013 2:22 AM