SSRS and Reporting on Multiple SharePoint Lists

1
votes

I am looking for some advice for reporting on data from 2 separate SharePoint 2010 lists with SSRS (SQL Server Reporting Services). As many of you know, SharePoint list data is content-based and not intrinsically relational. Therefore, I need to choose a tool to create the relationship and join the 2 lists on a unique identifier, and tap into this single dataset through SSRS to build a report. While SSRS has the ability to connect to SharePoint lists, it lacks the ability to join them into a single dataset, which is what is needed for report development.

Does anyone have any opinions on how to best develop and host this solution? I need the data to maintain a real-time connection, so building an ETL import workflow into a SQL table isn't an option. I am leaning towards using MSAccess, creating an ODBC connection, and then connecting to it via SSRS. However, I am concerned about potential limitations around the passing of credentials multiple times (double, maybe even triple hop scenarios). This will eventually need to be a completely server-side solution.

Any thoughts or opinions would be greatly appreciated!

2
reporting-servicesms-access-2013ssrs-2008-r2datamodelsharepoint-list
Have you looked at the suggestions from this link? I find the 3rd answer particularly interesting, since you can easily use the OPENROWSET result in a join. – Dan
Dan makes a good point. You need something in the middle that looks like a database to query and join the data and this is one way that it might be possible (not to mention more reliable than using MS Access). You might be able to write a UDF or SP to tie this all together. – Nick.McDermaid
Thank you guys. I will try this and report back. I am guessing that the SP server needs to be set up as a linked server to the SQL Server for this to work. – user2620038
Thanks everyone for the suggestions. From your answers, I think it becomes apparent that there is no straight-forward approach for merging separate SP lists, without having SQL Server playing some sort of middleware role. We eventually decided to use a relatively new third party tool that creates a semantic "data virtualization" layer. It kind of serves as a self-service ETL and modeling tool that can pull data from disparate sources (multiple SP lists in this example), and create a server-side relational structure that can be tapped into via ODBC connection. Seems to work for now! – user2620038

2 Answers

1
votes

This is probably a road to misery so I hesitate to continue ...

You could try using the Lookup function - assuming your data has a logical one-to-one or one-to-many structure.

Be aware that:

  1. You will need a complex expression in every texbox that needs data from the lookup list - report dev becomes a nightmare

  2. Lookups are prone to fail without any errors - they just dont return anything. This is effectively impossible to debug - you are mostly reduced to guesswork.

Good luck!

Actually I have resolved this scenario a few times in the real world with a "near real time" ETL solution, i.e. an SSIS package that runs every minute and updates SQL tables.

1
votes

I've used a different approach, it is quite tedious for large tables but if you have access to the sharepoint content database, you can find your list contents stored in the table AllUserData you just have to know your tp_ListID, that can be found in your [AllLists] table in the same DB or by using this method https://nickgrattan.wordpress.com/2008/04/29/finding-the-id-guid-for-a-sharepoint-list/

If you have lookup values in your table, then one of the int columns in table with the lookup column corresponds to the [tp_ID] column in the other list. Like I said this approach is quite tedious for tables with many columns but for a few columns this is fairly easy to do, and you are free in the SQL query to join the two lists

as an example

SELECT Books.[Title], Books.[Year], Publisher.Name as [Publisher]
FROM
(SELECT tp_ID as [ID], nvarchar1 as [Title], int1 as [Year], int2 as publisher_ID
 FROM [AllUserData]
  where tp_ListId = 'AFDC45EB-7BB5-42A2-9712-A432F3937671') as Books
  JOIN
(SELECT tp_ID as [ID], nvarchar1 as [Name]
FROM [AllUserData] 
where tp_ListId = 'AFDC45EB-45A2-42A2-9712-C15D00FAF91B') as Publisher
ON Publisher.ID=Books.publisher_ID