Using SharePoint List data in a Stacked Column Chart in SSRS 2008 R2

Question

I need to create a Stacked Column Chart with data from SharePoint 2010.  What is the best way to get counts from the SP list grouped on one value?

 

I have tried using separate datasets and the LookUpSet function, but I get errors trying to get the count.  I also tried using LookUpSet().length but it is not recognized in the Series Properties expressions.  Is there another way to get the count from the array brought back from the LookUpSet function?

Answer 1

Hi Skoch,

To count the values in an array item returned by the Lookupset function, we can use a custom code. The following steps are for your reference:

1. On the report Design surface, open the Report Properties window and click the Code tab.
2. Copy the following function into the code box:
   
   Function CountItems(varArray as array) as integer
   Dim RetVal as integer
   RetVal = ubound(varArray) + 1
   Return RetVal
   End Function
   
3. Set the target expression like below:
   =code.CountItems(LookupSet(Fields!SalesTerritoryGroup.Value,Fields!SalesTerritoryGroup.Value,Fields!SalesTerritoryCountry.Value,"DataSet2"))

Reference:
Using Custom Code References in Expressions

If you have any questions, please feel free to let me know.

Regards,
Mike Yin

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Answer 2

Hi Mike,

For some reason I did not get any notice that you had responded to this.  I eventually did this by having an immediate if inside a sum for each series property similar to the following:

Sum(iif(Fields!List_Field.Value="Yes",1,0))

Thank you for your reply!

Answer 3

Hi Skoch,

Thanks for your posting and feedback.

I am glad to hear that you have got the issue resolved. Thank you for sharing your solution. Hope this thread will help more people who face with the same issue.

Regards,
Mike Yin