I’m just getting started with this site and blog. I plan to make log of the things I discover as I go about my daily process of encouraging people to be more agile.
Here is my biggie for the week. This one saved me a ton of time and simplified my code.
I’ve been working on some 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
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.
Next, I will show how to make this testable outside SQL Reporting Services.
Have fun!
