Is it possible to get Distinct Values in SSRS from an RSSharePointList dataset?

0
votes

Let me start with my main problem and work into the question as there might be a better solution to my main problem.

I'm currently trying to create a 'datadump' report in SSRS 2008 that gets all records from two related SharePoint lists and combines them into a Tablix, showing all data as joined records in a Tablix. To do this, I created a dataset on the child list, then used the Lookup expression on another Dataset, which is the parent list, for each field I want in the dump. Worked great to join all my records, but one problem. Since not all Parents have a child record, I'm missing out on some Parent records. In SQL, I simply would have just started from the Parent and did a LEFT JOIN, but since SharePoint Lists don't work the same as SQL tables, they have to have a dataset for each list (at least from all I've seen, there might be a workaround I don't know of).

So, I wanted to create a second tablix with all the Parent records that don't show up. My idea is to simply create another dataset of the Parent records where the Parent's ID does not show up in the Child dataset. While there is no 'NOT IN' filter that is available, I did find this solution: http://blog.datainspirations.com/2011/01/20/working-with-reporting-services-filters-part-4-creating-a-not-in-filter/, where I would use the expression '=Iif(InStr(Join(Parameters!FilterList.Value,”,”), Fields!Category.Value)=0,True,False)'

So my question is, is there a way to just get distinct values out of the childs list to use as my filterlist? I know it probably isn't 100% necessary but I'd like to try optimize the performance.

Also, if anyone has a better solution to getting the data dump from two seperate SharePoint lists, I'm all ears :)

1
sharepointreporting-services

1 Answers

1
votes

To solve your original problem, you can use subreports. Have a main report that works on the parent SharePoint list which embeds a subreport based on the child SharePoint list, passing the parent key to the subreport as a parameter. Now you will list all parents and any children they have under them, or none if that is the case.

If you actually prefer having a separate list of parent records that are missing from the child list, then you can use LOOKUP (or LOOKUPSET if there are multiple child records) to set the Visibility-Hidden property so that only the missing ones are visible.