Hello,
I am in the process of creating a number of dashboards based on MS Project Server data.
First, I tried to build dashboards by having them connect directly to Project Server through the ODATA interface. This approach worked very well and proved easy to set up. I uploaded the reports at our on premises Report Server and users were able to access them. The only problem is that the data are embedded in the report(s), so to update the data one needs to open the actual report(s). This means that the update cannot be scheduled to happen automatically at offline hours which is particularly useful when a lot of data are involved.
So I thought I should separate the data from the report(s) by taking advantage of the data source and dataset features of Report Server. I defined a data source of type XML pointing to our Project Server (connection string: "data source=https://xxx.xxx.com/sites/pwa/_api/ProjectData/Projects"). Pressing "Test Connection" reported back "ok" (although subsequent tests proved that almost any value entered there causes the same resonse). I then opened Report Builder and set as dataset the one I just created.
Presumably because I specified an XML data source, I was presented with the text-based version of Report Builder where the user is expected to enter in the Query Designer window the actual query to be used.
Does anybody know how to enter a valid query to read the XML data produced specifically by Project Server's ODATA interface? I assume it is some kind XML Query but I failed to find any examples or a simple tutorial. Furthermore, is this a good way to go about in order to create Project Server-based reports on an on premises Report Server (i.e. auto scheduled updates are very important) or should I employ some other approach?
Thank you very much in advance.
