none
Data connection ODC file and Excel Reports Templates RRS feed

  • Question

  • Hi,

    I created a new Data connection file (ODC) derivated from "Project And Task Data" : I just added an Enterprise Project custom filed in the SQL query.

    How can I use this new Data connection to create a new Excel Report template?

    Thanks

     

    Saturday, April 9, 2011 11:27 AM

Answers

  • Hi,

    I don't have this issue. I open ODC from Server (read-only), paste my new query in there, get the same first message as you and confirmed. After that, data is refreshed without any issue. I have also used "Project And Task Data". After seeing refreshed data, I went back to Properties dialog and exported this new connection as ODC (saved locally). Afterwards, I uploaded this ODC to Data Connection page. Then I opened template "Projects and Tasks" (too lazy to create a report from scratch, sorry). Then I clicked on Data - Connection - Properties - Definition. I clicked on Browse to get my new ODC, clicked OK and saved this report with a different name. I have no issues when doing so.
    So sorry, I have never seen your error message?

    Regards
    Barbara

    Sunday, April 10, 2011 1:04 PM
    Moderator

All replies

  • Hi,

    in your new report: Data - Connections - Existing Connections. Navigate to URL like http://server/PWA/ProjectBICenter/Data%20Connections%20for%20PerformancePoint. Navigate to specific location and select your odc file.

    Does that help?
    Barbara

    Saturday, April 9, 2011 11:45 AM
    Moderator
  • Hi Barbara,

    Is it in the new Excel Report that I have to find the sequence : Data - Connections - Existing Connections?

    Do we have to type the URL like: http://server/PWA/ProjectBICenter/Data%20Connections%20for%20PerformancePoint

    Saturday, April 9, 2011 12:53 PM
  • Hi,

    uuups, mistake. Sorry, switching between environents made me forget. I have to apologize. What version are of Excel are you running? In Excel 2010, it is on ribbon Data, Existing Connections button. Then click on Browse for more and enter URL in path field of Select Data Source dialog. Use your your URL there, referring to your data connection library. I assume you have saved it at default location?

    Regards
    Barbara

    Saturday, April 9, 2011 1:00 PM
    Moderator
  • Thanks Barbara,

    Yes, I'm using Excel 2010 and I saved the ODC file in the default folder, besides the other ODC files, but I'm not able to navigate to this folder. I edited the SQL query directly in the "Connection Properties" dialog ((Command Text zone) : it worked (I got my Enterprise project custom field in the report) but it's not a user friendly interface!

    I'll try again...

    Thanks

    Saturday, April 9, 2011 1:22 PM
  • Hi,

    I had also some issues to enter the url of my language ODC folder. So I used the URL of Data Connections in first field. When this location was open, I could navigate to language subfolders.

    When editing SQL query, I copy it either to notepad (or even to SQL to see my results) for a better overview.

    Regards
    Barbara

    Saturday, April 9, 2011 1:35 PM
    Moderator
  • Barbara,

    << (or even to SQL to see my results) for a better overview>>

    I did that also, but when I copy in a connection ODC file, a query which was working fine in SQL Server, I get the errors:

    ------------------------- 

    The connection in this workbook will no longer be identical to the connection defined in the external file located at:

    'C:\Users\Administrator\Documents\My Data Sources\ProjectServer_Reporting Project plus CF.odc'

    The link to the external file will also be removed. Do you want to proceed with changes to the connection in the workbook?

    -------------------------------

     

    The query did not run, or the database table could not be opened. Check the database server...

    -------------------------------

    The following data range failed to refresh:

    ProjectServer_Reporting Project plus CF

    Continue to refresh all?

    ------------------------------

    Is there a specific syntax for these SQL queries to be understood in an ODC file?

    Thanks for your help


    Sunday, April 10, 2011 12:02 PM
  • Hi,

    I don't have this issue. I open ODC from Server (read-only), paste my new query in there, get the same first message as you and confirmed. After that, data is refreshed without any issue. I have also used "Project And Task Data". After seeing refreshed data, I went back to Properties dialog and exported this new connection as ODC (saved locally). Afterwards, I uploaded this ODC to Data Connection page. Then I opened template "Projects and Tasks" (too lazy to create a report from scratch, sorry). Then I clicked on Data - Connection - Properties - Definition. I clicked on Browse to get my new ODC, clicked OK and saved this report with a different name. I have no issues when doing so.
    So sorry, I have never seen your error message?

    Regards
    Barbara

    Sunday, April 10, 2011 1:04 PM
    Moderator
  • Thanks a lot Barbara,

    I'll try again with the light of your experience!

    Sunday, April 10, 2011 1:24 PM