How to display Microsoft Project Task’s Notes field in a report

June 4, 2008 @ Christophe Fiessinger's Blog from chrisfie

Vote This Post DownVote This Post Up (No Ratings Yet)
Loading ... Loading ...


In MS Project you have the ability to add notes to each tasks in a project plan. This data is stored as an Image Data Type in the SQL database called TASK_RTF_NOTES (not that the field TASK_NOTES is a nvarchar of size 255, thus you’ll only get the first 255 characters displayed). So how can you render the entire note field using SQL Server Reporting Services? Before giving you the answer a special thank you to Sam Brooks from Microsoft for passing along this solution:

image

(SQL Reporting Services code sample attached at the bottom of this post)

T-SQL Query

SELECT ProjectName,TaskName,TRTF.TASK_RTF_NOTES
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P
ON P.ProjectUID=T.ProjectUID
INNER JOIN PWA_Published.dbo.MSP_TASKS AS TRTF
ON TRTF.TASK_UID=T.TaskUID
WHERE TaskIsProjectSummary=0
AND P.ProjectName='Task RTF' --- For debugging purposes REMOVE!!!!
ORDER BY P.ProjectName, T.TaskName

Note that the TASK_RTF_NOTES is not in the Project Server Reporting database, thus we need to get it from the Published database.

Report Code

  • Add a reference to System.Windows.Forms (Report –> Report Properties –> References)

image

  • Add the following function in the report’s code (Report –> Report Properties –> Code):
‘Instantiate a rich text box control in memory
Public rtfRTB As new System.Windows.Forms.RichTextBox

‘Instantiate a stringbuilder object
Public s As New System.Text.StringBuilder()
Public Function byteArrayToString(ByVal b() As Byte) As String
Dim i As Integer
dim mystr as string

on error goto errortrap

s.length = 0
For i = 0 To b.Length - 1
Console.WriteLine(chr(b(i)))
If i <> b.Length - 1 Then
s.Append(chr(b(i)))
End If
Next
mystr = left(s.ToString, len(s.ToString)-1)

rtfRTB.rtf = mystr
return rtfRTB.text
rtfRTB.clear

exit function
errortrap:
return "n/a"
rtfRTB.clear
s.length = 0
End Function
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }
  • For the TASK_RTF_NOTES field use the following Value:
=code.byteArrayToString(Fields!TASK_RTF_NOTES.Value)
image 
And voila! 
image 
Another way to do the same thing is to use custom assemblies, check this blog post from Bryant Likes for more information: http://blogs.sqlxml.org/bryantlikes/pages/824.aspx


This article is syndicated from Christophe Fiessinger's Blog . The original article is available here. Read more in Christophe Fiessinger's Blog, Project Management News .

No tag for this post.
Popularity: 2%
Reminder : PMToolbox has ZERO tolerance to copyright violation and agrees to follow strictly PMI's Professional Responsibility. That's why each post on this site includes a link to the original version at its source site.

Related Posts

Comments

Got something to say?






[?]