Saturday, January 18, 2014

How to add custom code in Report builder 3.0 with Sharepoint lists as Data Sets

As you may have noticed you can not build dataset from multiple sharepoint lists. Any way you can write custom code functions in Report Builder in order to find workaroud to the problem.
Right click on the empty space of the report and choose Report Properties
In the Code group add your Custom code function or funtions. Yes it is VB not C# Sorry Насмевка
This particular function Calculates Sum from the specified object returned by the LookupSet function
Function SumLookup(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
Dim suma As Decimal = New Decimal()
Dim ct as Integer = New Integer()
suma = 0
ct = 0
For Each item As Object In items
suma += Convert.ToDecimal(item)
ct +=1
If (ct = 0) Then return 0 else return suma
End Function
And this is how to call your function in the report expression.
=code.SumLookup(LookupSet(Fields!TerritoryGroupID.Value, Fields!TerritoryID.Value, Fields!StoreName.value, "Stores"))