none
Project Online and SSRS RRS feed

  • Question

  • Hi Everyone,

    I have a customer who wants to migrate to Project Online from PS2013. They have a bunch of SSRS reports that they have spent considerable time in building. The business like these reports too and they contribute to a bunch of executive dashboards. 

    They are keen to move to project online however they want to still continue to use their existing reports. 

    Now whilst Power BI and rest services in proj online is perhaps in my opinion a better solution(iv done this with other customers) they still want to use their ssrs reports. I have suggested spinning up SQL in Azure and then using the rest services in proj online datasources to send data out to azure, then embed the report viewer webparts in a project on line page to utilise as they do today.

    Keen to get others thoughts on this and correct me if you think this is impossible???

    (Note the ssrs reports displays data from sharepoint proj sites plus pwa server settings)

    thanks

    :)


    • Edited by Nikki Scott Tuesday, November 17, 2015 11:57 PM
    Tuesday, November 17, 2015 11:56 PM

Answers

  • Hello,

    Correct, you don't have access to the SQL database directly in Project Online so you need to extract the data out in an SQL database either on-prem or Azure. The data can be extracted out using any of the client side API's, typically CSOM or OData. To get  the data in real time you will need to develop remote event receivers that extract the data on the project and SharePoint events (publish , save etc.).

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    • Marked as answer by Nikki Scott Thursday, November 19, 2015 10:00 PM
    Thursday, November 19, 2015 3:29 PM
    Moderator

All replies

  • Hello,

    That certainly is possible - you only have access to Odata or CSOM APIs to extract data out to SQL etc. Assuming the data used in the SSRS Reports is available in these APIs then it will work. How are you getting the Project Site data? Is this just default Issues and Risks data from the Project Web App reporting schema? What PWA settings do you mean? How do you get this data today for PS2013 in a supported way, PSI?

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, November 18, 2015 10:09 AM
    Moderator
  • thanks for your response paul, today its all in sql that is accessible. A dev used ssis to build a mini app to merge and link SharePoint proj site data with the project server data ( all ent fields in server settings etc) and yes this includes custom lists in proj site and proj custom fields that are on pdps. So with proj online we need to do the same..I pressume we then need to do same with ssis in azure right? So my question then is is all this in client side object model in guessing it is..but ssis needs to make it real time as well..thoughts?

    Thursday, November 19, 2015 8:31 AM
  • Hello,

    Correct, you don't have access to the SQL database directly in Project Online so you need to extract the data out in an SQL database either on-prem or Azure. The data can be extracted out using any of the client side API's, typically CSOM or OData. To get  the data in real time you will need to develop remote event receivers that extract the data on the project and SharePoint events (publish , save etc.).

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    • Marked as answer by Nikki Scott Thursday, November 19, 2015 10:00 PM
    Thursday, November 19, 2015 3:29 PM
    Moderator
  • Hi Paul

    I just wanted to let you know that now with the new power query available in power BI - I have successfully written a bunch of queries that merge the project odata with the sharepoint site custom list data - hoorah! finally no need to even have to use ssrs ..only thing is if you want realtime refresh available you need to purchase power bi pro (else the reports refresh daily)

    you also need to figure out how to get to the sharepoint data..i wont give away all my secrets :)

    Nikki

    Friday, December 25, 2015 9:25 PM
  • Yes this is possible using a Power Query function to call the SharePoint list REST / ODATA APIs on each Project Site for the lists data you want in the report. :)

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, December 30, 2015 5:50 PM
    Moderator
  • For anyone else who is interested in getting this data from custom SharePoint list data from the Project Sites into one report, see the example below using Power BI Desktop or Power Query in Excel:

    https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Tuesday, January 5, 2016 3:18 PM
    Moderator
  • Hello Nikki,

    Were you able to make an exact replica of the SSRS reports to Power BI or you had to use the visualisations comes with Power BI?

    We have currently deployed SSRS report in order to match the proposed design (failed to achieve the same with Power BI and Excel Services). Currently we are struggling to show the same in Report Viewer. Can you share some of the secrets if not all --- lol.

    Regards

    Tanzim


    Tanzim Akhtar

    Tuesday, April 19, 2016 6:26 AM