SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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: (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.

share|improve this question
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"

share|improve this answer
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

I think you can achive this by using ReportBulider and SUM function

share|improve this answer
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review – Waqas Sarwar MCSE Dec 27 '15 at 18:44

Please note - I have not tested the code in the link as suggested by user24507.
However, if the link to 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"))
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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