none
Excel Reports and SQL query in PWA 2010 RRS feed

  • Question

  • Hi,

    It seems that Excel Reports in the Business Intelligence Center use a very sophisticated SQL Query. Same thing for the connection string in the ODC file.

    Is there an interactive way to do such Excel Reports without being an expert developer?

    Thanks

    Tuesday, February 1, 2011 2:02 PM

Answers

  • Hi,

    what kind of data are you missing in default ODCs?

    Andrew described in his blog http://blogs.catapultsystems.com/epm/archive/2010/01/18/modifying-the-default-odc-files-to-filter-on-specific-projects.aspx how to get SQL statement from exsiting ODC. However I replace second half of step 3 and step 4 by doing the following: Since I am not that good in writing SQL statements without any syntax issues, I start SQL Server Management Studio. I open RDB and create a new view. I replace existing text by statement from ODC. So I get a graphical interface, where I can add and delelte any fields. I copy my new statement and follow again what Andrew is describing strating with step 5.

    Perhaps is this way also working for you? However, I am not sure what you mean with interactive.

    Regards
    Barbara

    Andrew, once more thanks for your post!!

    • Marked as answer by WLID1966 Tuesday, February 1, 2011 2:34 PM
    Tuesday, February 1, 2011 2:14 PM
    Moderator

All replies

  • Hi,

    what kind of data are you missing in default ODCs?

    Andrew described in his blog http://blogs.catapultsystems.com/epm/archive/2010/01/18/modifying-the-default-odc-files-to-filter-on-specific-projects.aspx how to get SQL statement from exsiting ODC. However I replace second half of step 3 and step 4 by doing the following: Since I am not that good in writing SQL statements without any syntax issues, I start SQL Server Management Studio. I open RDB and create a new view. I replace existing text by statement from ODC. So I get a graphical interface, where I can add and delelte any fields. I copy my new statement and follow again what Andrew is describing strating with step 5.

    Perhaps is this way also working for you? However, I am not sure what you mean with interactive.

    Regards
    Barbara

    Andrew, once more thanks for your post!!

    • Marked as answer by WLID1966 Tuesday, February 1, 2011 2:34 PM
    Tuesday, February 1, 2011 2:14 PM
    Moderator
  • No problemo Barb - thanks for the compliments.
     
    I'm not a SQL guy, but it's not too hard once you know the field names and
    get a feel for the syntax in the ODC file.  For example, most of the Project
    level fields are in the EPMProject_UserView table.  Take an existing ODC
    and append the names of your custom fields in the same syntax.  I found that
    the easiest way is to just do an Export to Excel of the Custom Fields from
    Server Settings, configure the ODC string as a list of fields in Excel, and
    then paste back into the ODC file to test it out.
     
    .....or you could try one of the Project Server 2010 Solution Starters downloadable
    from CodePlex.  The Report Wizard allows you to pick and choose fields, then
    builds the ODC file for you.  It seems to work reasonably well.
     
     

    Andrew Lavinsky [MVP] Twitter: @alavinsky
    Tuesday, February 1, 2011 2:34 PM
    Moderator
  • Hi Barbara,

    That's what I was looking for, while a little bit more complicate but I'll work on that!
    With 'interactive' I meant 'with mouse click' :)

    Thanks again

    • Marked as answer by WLID1966 Tuesday, February 1, 2011 2:34 PM
    • Unmarked as answer by WLID1966 Tuesday, February 1, 2011 2:34 PM
    Tuesday, February 1, 2011 2:34 PM
  • Hi Andrew,

    in the meantime, I can also write the code. However, when starting, I was happy to have a graphical interface for e.g. linking additional tables. From WLID1966's questions, I see a similar approach in getting familiar with all this stuff. So I thought to give him the hint to used SQL Server Management Studio for his first queries.

    Thanks for the idea with solution starter. I haven't had a look at this for some time, seems there is some more functionality included now.

    Regards
    Barbara

    Tuesday, February 1, 2011 5:26 PM
    Moderator