Project Server SSRS Reports – Common Conditional Formatting Expressions

When building SSRS Reports for Project Server 2010, I typically want to get data from the Project Plan, or the Project Site. Getting this data out is all well and good – but I thought some examples of cell formatting would be a good item to blog about.

Below are some typical and common examples of what expressions I use to tidy the values up. NOTE: If copying and pasting these from this blog, you may need to adjust the ‘ or ” values as they may not be the correct type for SSRS!

Remove Characters from Front of a Value (E.G Turn ‘(1) Active’ into ‘Active’

=RIGHT(Fields!Priority.Value, Fields!Priority.Value.ToString().Length – 3).Trim()

Cleanup SharePoint Fields (Removes all the Odd Characters Returned with SharePoint Choice or Calculated Fields)

Code for Report – Use Once in the Report:

Public Function CleanSPSInput(strIn As String) As String
Dim RegEx as System.Text.RegularExpressions.Regex
‘ Replace invalid characters with empty strings
if strIn = “” OR IsDBNull(strIn) Return “”
Else
Return Regex.Replace(strIn, “.+;#”, “”)
End If
End Function

Code for Expression – Can be Used Against Several Fields:

=code.CleanSPSInput(Fields!Name_of_Person_Responsible.Value)

Format Currency (E.G Turn ‘4.0000000000’ into ‘4’)

=FormatCurrency(Fields!Current_Budget.Value,0)

Format Percentage (E.G Turn ‘4.0000000000’ into ‘4%’)

=FormatPercent(Fields!Current_Prob_Impact.Value,0)

Convert Boolean (True/False) into a String (E.G You cannot use Booleans as report filters, so may need to convert the field to a string)

=CStr(Fields!Goodwill_Support_Ticket.Value)

Adding Numbers Together (Add Two Values Together – This Will Account for Decimal Places as well)

Cdbl(Fields!FirstNumber.Value )+Cdbl(Fields!SecondNumber.Value)

Formatting a Number and Appending Some Text on the End (E.G Turn’ 4.0000000000′ into ‘4 hrs’)

=FormatNumber(Fields!Variance_from_Baseline__Hours_.Value,1) & ” hrs”

Show Text if No Value, Otherwise Show Currency Value

=IIf(Fields!Baseline_Budget.Value Is Nothing,”No Baseline”, FormatCurrency(Fields!Baseline_Budget.Value,0))

Show Text if No Value, Otherwise Show Formatted Date Value

=IIf(Fields!Baseline_End_Date.Value Is Nothing,”No Baseline”, FormatDateTime(Fields!Baseline_End_Date.Value,DateFormat.ShortDate))

Colour Cells Based on a RAG Indicator Value (E.G RAG Lookup Table)

=Switch(Fields!Commercial_RAG.Value Is Nothing, “White”, Fields!Commercial_RAG.Value = “Red”, “Red”, Fields!Commercial_RAG.Value = “Amber”, “Orange”, Fields!Commercial_RAG.Value = “Green”, “Green”)

Grand Total of a Dataset Value (E.G You may experience #Error if you use just a plain SUM expression)

=Sum(CDbl(Fields!Cost.Value))

Remove HTML from Returned Dataset Value

Code for Report – Use Once in the Report:

Public FUNCTION RemoveHtml(ByVal Text As String) AS String
IF Text IsNot Nothing Then
Dim objRegExp AS NEW System.Text.RegularExpressions.Regex(“<(.|\n)+?>”)
RETURN objRegExp.Replace(Text, ” “)
END IF
END Function

Code for Expression – Can be Used Against Several Fields:

=code.RemoveHTML(Fields!Description.value)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s