Sorry if I'm missing something obvious but I'm a newbie to SSRS (SQL Server 2012 R2) and SharePoint 2010.

My boss wants some basic reports from our internal SP site (list data) and I'm doing some research on SP + SSRS. Fields from more than one list are needed so I used the LookUp expression on Report Builder/BIDS to generate a sample report.

Here's the details. I've got two separate lists:

  1. PROJECT (containing columns "ProjectID" and "TasksID") and
  2. TASK (containing "TaskID", "Description" and "Hours")

I want to generate a basic report displaying the total Hours (mini tasks) spend on different Tasks grouped by Project, on which they are belonging to.

Here's what I got so far: http://imgur.com/b44mFRj (bypass the Greek nonsense, its Projects - Tasks - Description - Hours)

For the last 2 columns I used the expressions:

=Join(LookUpSet(Fields!TaskID.Value, Fields!TaskID.Value, Fields!Description.Value, "Dataset2"), " " + vbcrlf)


=Join(LookUpSet(Fields!TaskID.Value, Fields!TaskID.Value,Fields!Hours.Value, "Dataset2"),"" + vbcrlf)

The result is an array of strings so as a first step I joined them with Join and separated them by newline. This messed things up cause I cant get the totals I need (total Hours spend on Task, total Hours spend on Project, total Hours for all Projects etc).

Are there any ways to get the results on different cells so I can work with them?

Thanks - Sorry for the big intro.

  • I think I'll have to code.. These expressions are too limiting. Please guide me here: link
    – lephleg
    Mar 29 '13 at 13:17

Here is a post that explains how to do this via code in the report: "Sum Result of SSRS LookupSet Function" http://salvoz.com/blog/2013/05/27/sum-result-of-ssrs-lookupset-function/

  • 1
    Please don’t add just a link as answer to a question. If the link breaks, the answer you provided will not be useful for future visitors. Provide an answer of your own and use the link as reference. If needed, you can block quote the content from your link. More information is found in our help center, especially How do I write a good answer
    – Benny Skogberg
    Mar 20 '14 at 6:59

Please note - I have not tested the code in the link as suggested by user24507.
However, if the link to http://salvoz.com/blog/2013/05/27/sum-result-of-ssrs-lookupset-function/ has broken & you need it - here it is.

Copy and paste this into the custom code section of your report:

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 

To call the function in your expression use this:

=Code.SumLookup(LookupSet(Fields!Parent_Number,Fields!Parent_Number.Value,Fields!StoreCount.Value, "StoreCount"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.