I've been working on some Microsoft Team Foundation Server (tm) (TFS) reports for a team at Microsoft. A report publishes the results of an MDX query. The latest report I have been working on is complex enough to need some VB code. There are lots of examples of how to pass the individual fields from the query to the VB code, but I had to search and interpolate to come up with how to pass the entire Fields set (not technically a collection) to the VB Code.

As it turns out, it is very simple. For this example we will say the name of the VB function that you want to send the set to is "GetValue". That would make the expression in the report look like this.

Code.GetValue(Fields, FieldName) 

This will cause the report to show whatever GetValue returns.

To enter the VB code you open the Properties dialog for the report and select the Code tab. In your VB code you will have to extract the exact fields that you want from Fields, but that is pretty simple too.

Public Function GetValue(ByVal ReportFields As _
   Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields, _
   ByVal FieldName As String) As String
Dim myField As String
Dim myFields As Fields

myFields = ReportFields
myField = FieldName 

Return myFields(myField).Value 

End Function 

Normally in the report itself your expression would be something like this.

=Fields!FieldName.Value 

To use custom code to do this would be a bit silly. However consider a scenario where you want to compare multiple values or do some calculation on the field values. Suppose your dataset has fields used to track the progress in your project based on Story Points and you would like to display a Per Cent Complete value on your report or chart this value over time.

After adding the GetValue function to your code you might add a function like this.

Public Function GetPerCentComplete(ByVal ReportFields As _
   Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields, _
   ByVal StoryPointEstimate As String, ByVal StoryPointRemaining c) As String 

Dim PerCentComplete As Double
Dim myFields As Fields
Dim Estimate As String
Dim Remaining As String 

myFields = ReportFields
Estimate = StoryPointEstimate
Remaining = StoryPointRemaining 

PerCentComplete = Convert.ToString_
   (Convert.ToDouble(GetValue(myFields, Estimate)) / Convert.ToDouble(GetValue(myFields, Remaining) ) 

Return PerCentComplete 

End Function 

Then your report could have a call like this:

=Code.GetPerCentComplete(Fields, "CumulativeStoryPointEstimate", CumulativeStoryPointRemaining") 

This is much easier to read and deal with in your report.

Technorati Tags: VSTS

tag with del.icio.us