none
SQL Query in an ODC file (Excel Report 2010) RRS feed

  • Question

  • Hi,

    I just want to add a condition in the standard SQL Query (I tested in SQL Server 2008 : OK) of my Excel Report, but I get the error:

    The connection in the workbook will no longer be identical to the connection in the external file located at  http://srv1-en/pwa/ProjectBICenter/Data connections for...

    The link to that external file will also be removed.

    What did I miss?

    Thanks for your help

    Saturday, July 21, 2012 4:40 PM

Answers

  • What you are seeing is a warning that the cached internal ODC no longer matches the external ODC. In an attempt to be "helpful", Excel is giving you the option to break the link to the external ODC. This is usually not what you intended to do.

    To update an external ODC, make your changes in the SQL query as you've done but click Export Connection File instead of OK on the tab where you updated the query. It will prompt you for a location and name. You can overlay the existing ODC at this point. Once complete, close the Excel file without saving it.

    Reopen the Excel file. Excel does a version check on refresh between the external ODC and the internal cached ODC information. If the external ODC has changed, it will automatically update the internal ODC cache with the external updated ODC information.

    Hopefully, this helps.

    Treb Gatte @tgatte

    • Marked as answer by WLID1966 Saturday, July 28, 2012 10:48 AM
    Saturday, July 21, 2012 6:18 PM
    Moderator

All replies

  • Is your excel report working locally , I mean from desktop?


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Saturday, July 21, 2012 4:42 PM
    Moderator
  • Yes it is.

    Saturday, July 21, 2012 5:11 PM
  • Make sure that ODC file location is updated in excel file and same is added Trusted file location

    Is your sample reports (out of the box reports) working fine?


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Saturday, July 21, 2012 6:04 PM
    Moderator
  • Yes, my sample report (out of the box report) is working fine.


    Saturday, July 21, 2012 6:17 PM
  • What you are seeing is a warning that the cached internal ODC no longer matches the external ODC. In an attempt to be "helpful", Excel is giving you the option to break the link to the external ODC. This is usually not what you intended to do.

    To update an external ODC, make your changes in the SQL query as you've done but click Export Connection File instead of OK on the tab where you updated the query. It will prompt you for a location and name. You can overlay the existing ODC at this point. Once complete, close the Excel file without saving it.

    Reopen the Excel file. Excel does a version check on refresh between the external ODC and the internal cached ODC information. If the external ODC has changed, it will automatically update the internal ODC cache with the external updated ODC information.

    Hopefully, this helps.

    Treb Gatte @tgatte

    • Marked as answer by WLID1966 Saturday, July 28, 2012 10:48 AM
    Saturday, July 21, 2012 6:18 PM
    Moderator