SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. Join them; it only takes a minute:

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: 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)

and

=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" http://salvoz.com/blog/2013/05/27/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
    
While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Gaurravs Jul 25 at 4:07

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
Next
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

 
discard

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.