none
Project Server 2013 - Project Sites Custom List RRS feed

  • Question

  • Hi,

    I normally use IPMO's Data Miner for extracting custom list information such 'Change Request Register' etc...and using the info in monthly status reports. Works like a charm, but IPMO's 2013 version is not available in App store yet so can't really use them in cloud based PS2013 instances.

    What would be the best way get custom lists info from Project Sites in my SSRS reports?

    Thanks!

    SJ

    Tuesday, May 28, 2013 1:33 AM

Answers

  • HI,

    first, i just realized that i forgot to copy this another URL for you to follow in order to create report using sharepoint list, here you go : http://www.mssqltips.com/sqlservertip/2068/using-a-sharepoint-list-as-a-data-source-in-sql-server-reporting-services-2008-r2/

    now, this is a tricky part because your data source consist of site name, and its also depends upon your type of report.

    is your report is like a project status report based on project UID ? 

    if above is true, then you can use expression in data source and pass project site name to data source to fetch the data of that project site. you can get project site name using project UID from EPM_project_userview.

    hope this helps.


    Khurram Jamshed - MBA, PMP, MCTS, MCITP ( Blog, Twitter, Linkedin )
    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.

    • Proposed as answer by Khurram Jamshed Wednesday, May 29, 2013 4:56 PM
    • Unproposed as answer by SJ_PPM Tuesday, June 25, 2013 2:42 AM
    • Marked as answer by SJ_PPM Thursday, June 27, 2013 5:33 AM
    Wednesday, May 29, 2013 1:18 PM
  • Hi,

    follow the steps:

    1. pass Project UID to query to fetch desired project workspace URL from epm_project_userview and save it in parameter.

    2. use this workspace URL parameter to define data source connection string. you need to use expression instead of hard coded data connection string so that you will be able to read from project sites based on selected project UID.

    hope this helps.


    Khurram Jamshed - MBA, PMP, MCTS, MCITP ( Blog, Twitter, Linkedin )
    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.

    • Marked as answer by SJ_PPM Thursday, June 27, 2013 5:38 AM
    Tuesday, June 4, 2013 6:07 PM

All replies

  • Hi,

    You can have an option to connect with sharepoint list to create SSRS report by using BI visual studio 2008 R2. once connected you can use xml query to fetch the columns from list and use it the same in your report.

    see this MSDN article : http://msdn.microsoft.com/en-us/library/ee633650(v=sql.105).aspx

    and also see this blog for step-by-step with screen shots steps for the same.

    hope this helps.


    Khurram Jamshed - MBA, PMP, MCTS, MCITP ( Blog, Twitter, Linkedin )
    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.

    Tuesday, May 28, 2013 12:40 PM
  • Thanks Khurram. I will try that.

    Will this method will bring all the custom lists? I.e. I don't need to create separate connections for each Project Site. For example if I want to see Change requests across all projects, I won't need multiple data connections.
    Wednesday, May 29, 2013 12:59 PM
  • HI,

    first, i just realized that i forgot to copy this another URL for you to follow in order to create report using sharepoint list, here you go : http://www.mssqltips.com/sqlservertip/2068/using-a-sharepoint-list-as-a-data-source-in-sql-server-reporting-services-2008-r2/

    now, this is a tricky part because your data source consist of site name, and its also depends upon your type of report.

    is your report is like a project status report based on project UID ? 

    if above is true, then you can use expression in data source and pass project site name to data source to fetch the data of that project site. you can get project site name using project UID from EPM_project_userview.

    hope this helps.


    Khurram Jamshed - MBA, PMP, MCTS, MCITP ( Blog, Twitter, Linkedin )
    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.

    • Proposed as answer by Khurram Jamshed Wednesday, May 29, 2013 4:56 PM
    • Unproposed as answer by SJ_PPM Tuesday, June 25, 2013 2:42 AM
    • Marked as answer by SJ_PPM Thursday, June 27, 2013 5:33 AM
    Wednesday, May 29, 2013 1:18 PM
  • Thanks again.

    Yes, my report is a status report and it is based on ProjectUID.

    I will see what I can do. I will let you know if I get stuck.

    Thank you,

    SJ

    Wednesday, May 29, 2013 2:48 PM
  • Hmm. Having a little of difficulty adding PUID in expression. Any suggestions? Doesn't seem to pick up lists.

    Tuesday, June 4, 2013 4:45 PM
  • Hi,

    follow the steps:

    1. pass Project UID to query to fetch desired project workspace URL from epm_project_userview and save it in parameter.

    2. use this workspace URL parameter to define data source connection string. you need to use expression instead of hard coded data connection string so that you will be able to read from project sites based on selected project UID.

    hope this helps.


    Khurram Jamshed - MBA, PMP, MCTS, MCITP ( Blog, Twitter, Linkedin )
    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.

    • Marked as answer by SJ_PPM Thursday, June 27, 2013 5:38 AM
    Tuesday, June 4, 2013 6:07 PM
  • Apologies for the late response.

    Thank you for your help. That worked.

    Just a bit of calrification in your steps:

    Step 1 - Dataset used by ProjectUID parameter will have Project Workspace URL also in there. Not as a parameter as part of the dataset. When setting the parameter, use ProjectUID as Value and URL as Label.

    Step 2 - Use the URL label in your connection string for your Sharepoint List Datasource.


    • Edited by SJ_PPM Thursday, June 27, 2013 5:38 AM spell check
    Thursday, June 27, 2013 5:37 AM
  • Hi SJ,

    I am really stuck here. i need to fetch the information from custom list. i need to build a report which has the input of project name. can you guide me.?


    manikantan

    Tuesday, March 25, 2014 6:34 AM