Using a PSI Data Source in SQL Server Reporting Services

SQL Server Reporting Services 2005 (SSRS) has the ability to query data from XML data sources and thus from Web Services, for more information on this subject refer to Reporting Services: Using XML and Web Service Data Sources.

Since EPM 2007 is architected around Web Services and specifically around the Project Server Interface (PSI), I will demonstrate how to build a SSRS report using a PSI as a data source.

Start Visual Studio 2005
Add a Data Source of type XML and enter the URL of the Project Web Service

Add a report without using the Report Wizard
Go to the Data Tab and enter the following XML query

<Query>
   <Method Namespace="http://schemas.microsoft.com/office/project/server/webservices/Project/" Name="ReadProjectList"/>
<SoapAction>http://schemas.microsoft.com/office/project/server/webservices/Project/ReadProjectList</SoapAction>
 <ElementPath IgnoreNamespaces="true">ReadProjectListResponse/ReadProjectListResult/diffgram/ProjectDataSet/Project</ElementPath> 
</Query>

Add a Table control and pick some fields, for instance Fields!PROJ_UID.Value and Fields!PROJ_NAME.Value, then Preview report
and voila!

This is a basic example to demonstrate how it works, what you would probably end up doing is a PSI extension that queries your Reporting database leveraging the EPM Out Of the Box security model. I will demonstrate more useful sample at the Project Conference next week.

For more information on PSI Extension please refer to the EPM SDK and to past posts on my blog.