In my last post, I decided to take a look at how to use External Content Types to surface Project Server Reporting database data on Risks and Issues. In this post, I plan to talk about some quick and easy steps that are required to provide a useful dataset.
Now, before we get too far into this post, I should probably mention that working directly in the database is always a sensitive issue, but as far as I can tell, adding a custom view to the Reporting database will not cause supportability issues going forward. Just make sure that everything is documented and backed up. Now back to the narrative…
The challenge that I found when I first started playing with the risk tables in the Reporting database was that none of the risk tables seemed to include the Project name. A couple include the Project ID field, but that won’t help when it’s surfaced in an External List.
…so I decided to create a custom SQL view. To do that, I fired up SQL Management Studio and navigated to the Reporting database. Once I found that, I right clicked on the Views option to create a new View.
From there, it’s a relatively simple matter to select the WSSRisk_Olapview and EPMProject_Userview tables to be included in my view. I check the fields to be included, and then add a join at the ProjectUID field. (If you’re not familiar with this interface, it works much like Access, you just grab the ProjectUID field from the table on the left and drag it to the one on the right to generate the join.)
Save the Custom View, and you can now use it for reporting purposes, whether that report is based on an External Content Type, an Office Data Connection file, or a direct connection back into SQL.