This question came up in the newsgroup a little while ago, and intrigued me enough to play around with it. The question was how to add Departments to the Issues and Risks reports in Project Server 2010, so that you can report a rollup of how many issues or risks exist on projects by Department. I played around with it a bit, and the answer turned out to be pretty simple. Basically, we just have to add the field in the appropriate ODC files within the BI Center.
This answer though is indicative of the ease with which reporting can be modified in Project Server 2010. If the right field doesn’t occur in the OLAP Cube, we can simply create a join with a table and borrow the field from that table. In this case, that task is even easier because the sample ODC files included in the default installation already have the right tables joined. All we have to do then is add a string of text to the ODC file to surface the field from the Project table. (And for those of you who are not so proficient at writing SQL queries, Microsoft has provided the Report Wizard Solution Starter to generate the ODC code correctly.)
What also intrigued me is that I instinctively went to solve the problem first the same way I would have in Project Server 2007, i.e. trying to get the right field in the right cube. In this case, that’s not possible using the out of the box interface, and it was actually easier to just bring in multiple tables to Excel and join them there. Here we have an excellent example of how removing the old Data Analysis views actually force us to use a much more robust reporting methodology in 2010. In retrospect, it would seem that Data Analysis views were a crutch that held us back from exploring better reporting options in 2007. And for the record, I see no reason why this solution wouldn’t have worked in 2007 (with the possible exception of WSS not supporting Excel Services).
I suspect that this will be a recurring thread: how to surface specific fields in specific reports…
Verifying the OLAP Cube (Not)
Before talking about how to resolve the issue, let’s talk about how not to resolve the issue. My first step was to verify whether or not we could simply add the Project Departments to the Issue cube. To do so, I selected Server Settings, and clicked on the OLAP Database Management option. I selected the OLAP Database, and Configuration from the toolbar.
I looked for an Issue cube, of which there was none in this screen. The closest thing I suppose to an Issue cube is the Project cube, and as far as I can tell, Departments are added to that cube by default on install. So, I confirmed that Project Departments have been added to the Project Cube, and rebuilt the cube.
At the end of the day, this really didn’t do anything for me…..but that’s exactly how I would have attempted to solve the issue in 2007 to get the data in the Data Analysis views.
Modifying the ODC File
So now, how do we solve this in 2010 (and in retrospect how could we have solved this in 2007)? What we have to do is find a unique identifier in the Issues cube, which turns out to be the Project Unique ID. We then need to find a table within the Reporting database that contains the Departmental data, and join the two using the Unique ID field. That’s relatively easy. It’s made easier by the fact that the Issues ODC file contained in the BI Center on install already is configured to join the Issues cube with the MSP_EpmProject_UserView table. All we need to do is add a string into the ODC to add one more field from the EpmProject_UserView table and our job is done.
So to do that, we first navigate to the BI Center > Data Connections > English folder, and click on the Project Server – Issues Report.
When you open the file, that should trigger the launch of an Excel file with the ODC stored as a data connection. (Feel free to check out this earlier post on modifying the ODC file: http://blogs.catapultsystems.com/epm/archive/2010/01/18/modifying-the-default-odc-files-to-filter-on-specific-projects.aspx)
Click on the Connections button in the Data tab to access the ODC file.
Select the Project Server – Issues Data connection, and click on Properties. Then select the Definition tab. That should result in the following screen:
Add the following string into the Command Text section…
ProjectOwner.[Project Departments] as [Project Departments],
(This pulls the Project Departments field out of the dbo.MSP_EpmProject_UserView table and surfaces it in our Excel file)
Hit OK, and confirm that Project Departments has been added to the Excel workbook (it should be all the way over on the right).
Now export the ODC file from the Data Tab > Connections button > Definition tab:
You may now upload the ODC file over the original one (making sure to keep a copy of that with the “_OLD” suffix) or make a new report based off of the existing Issues sample report.
Coming soon…..how one would accomplish the same thing using the Report Wizard Solution Starter, assuming that I can figure out how to make it work.