CODEWITHSUNDEEP
reporting-servicesms-access-2013ssrs-2008-r2datamodelsharepoint-list
user2620038
user2620038

Reputation:

SSRS and Reporting on Multiple SharePoint Lists

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!

Upvotes: 1

Views: 2884

Answers (2)

Hedinn
Hedinn

Reputation: 864

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

Upvotes: 1

Mike Honey
Mike Honey

Reputation: 15017

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.

Upvotes: 1

Related Questions