Microsoft didn’t spend too much time on the “Status Reports” module in Project Server 2010, leaving it with many disadvantages and even a somewhat not so intuitive handling. This affects especially the extraction of Responses to Status Reports – which you can only export to a non-editable Word template.

Having not found any better solution on the internet for improving the retrieval of Staus Report responses (except a scheme of the required tables), I decided to make my own SQL-Query to do so.

Before I publish it further below, please be aware that the data is only available in the ProjectServer_Published database and NOT the ProjectServer_Reporting database, making it an unsupported “hack” which requires at least read access to the Published database.

  • Affected Database:
    • ProjectServer_Published
  • Required Tables:
    • MSP_SR_REPORTS
    • MSP_SR_RESPONSES
    • MSP_SR_SECTIONS
    • MSP_RESOURCES

Here’s the example SQL-Query (use it with a Pivot-Table in Excel):

SELECT
                      MSP_RESOURCES.RES_NAME AS [Team Member],
                      MSP_SR_REPORTS.SR_NAME AS [Status Report],
                      MSP_SR_REPORTS.SR_IS_ENABLED AS ReportIsActive,
                      MSP_SR_RESPONSES.SR_RESP_SUBMIT_DATE AS [Last Feedback],
                      MSP_SR_REPORTS.SR_MGR_RES_UID AS [Report Requester],
                      MSP_RESOURCES.RES_GROUP AS [Resource Group],
                      MSP_SR_RESPONSES.SR_RESP_PERIOD_START_DATE AS [Reporting Period Start],
                      MSP_SR_RESPONSES.SR_RESP_PERIOD_FINISH_DATE AS [Reporting Period End],
                      MSP_SR_SECTIONS.SR_SECTION_NAME AS Subject,
                      Comment =
                               REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CASE
                                        WHEN PATINDEX('%class="',CAST(MSP_SR_SECTIONS.SR_SECTION_TEXT AS NVARCHAR(MAX))) > 0 THEN STUFF(CAST(MSP_SR_SECTIONS.SR_SECTION_TEXT AS NVARCHAR(MAX)),1,48,'')
                                        ELSE STUFF(CAST(MSP_SR_SECTIONS.SR_SECTION_TEXT AS NVARCHAR(MAX)),1,46,'')                            
                               END,'',''),' ',' '),'&','&'),'',CHAR(13)) ,'',''),'',CHAR(13)),'','')
                      FROM MSP_SR_REPORTS INNER JOIN MSP_SR_FREQUENCIES ON MSP_SR_REPORTS.SR_UID = MSP_SR_FREQUENCIES.SR_UID INNER JOIN MSP_SR_REQUESTS ON MSP_SR_REPORTS.SR_UID = MSP_SR_REQUESTS.SR_UID INNER JOIN MSP_SR_RESPONSES ON MSP_SR_REPORTS.SR_UID = MSP_SR_RESPONSES.SR_UID INNER JOIN MSP_SR_SECTIONS ON MSP_SR_RESPONSES.SR_RESP_UID = MSP_SR_SECTIONS.SR_RESP_UID INNER JOIN MSP_RESOURCES ON MSP_SR_RESPONSES.RES_UID = MSP_RESOURCES.RES_UID WHERE (MSP_SR_REPORTS.SR_IS_ENABLED = 1) ORDER BY [Team Member], [Last Feedback] ASC

If you wonder about the complicated definition of the “Comment” column, let me explain: it basically transforms the column into a valid VARCHAR column and then striping various information, like “<div>DATE</div>” and o ther HTML-Tags within the text.

In the end this could look like this:

Project Server 2010 - Sample Status Report Response Export
Share:
  • 0