locked
SQL Reporting Services - Combining 2 SharePoint Lists in one table RRS feed

  • Question

  • Hi,

    I have SQL Server 2008 R2 Reporting Services and I have 2 SharePoint lists with identical columns.

    I would like to create 1 table using Report Builder which shows data from both of the SharePoint lists.  Essentially doing the equivalent of a UNION ALL on the 2 SharePoint lists.

    Has anyone achived this?

    Thanks,

    Mark 

    Friday, December 7, 2012 2:02 PM

Answers

  • "By default, we can only get values from one list in one query.

    To get values from two list, I would suggest you creating two dataset in Reporting Services, then geting values from each list in each dataset."

    -Jinchun Chen

    However, to my knowledge, a data region (table) may only reference one dataset, not two, making the combination of two datasets into a single table not possible.

    One other comment I saw in another thread was the use of "SLAM"

    http://slam.codeplex.com/

    "Description
    Most developers who have used SharePoint as an application development framework have run into the realization that SharePoint is NOT a relational database. In fact, the accepted wisdom is if you need relational tables, use ASP.NET/SQL straight-up, not SharePoint.

    Enter SharePoint List Association Manager (SLAM). In short it allows you to define relationships (one to one, one to many, many to many) between SharePoint lists (or Content Types) and then leverage those relationships in webparts or custom field types using familiar and straight forward SQL queries."

    Hope this helps.

    Regards,

    Craig


    • Edited by determinism Friday, December 7, 2012 5:53 PM
    • Proposed as answer by Mike Yin Monday, December 10, 2012 6:08 AM
    • Marked as answer by Mike Yin Sunday, December 16, 2012 1:29 PM
    Friday, December 7, 2012 5:47 PM
  • Hi Mark,

    In Reporting Services, the SharePoint List data source type uses the GetListItems method to retrieves the data for a list, and the method can only return the records for a single list. To work around this issue, we can:

    • Use subreport to render data from multiple SharePoint Lists into one tablix.
    • Implement a new XML web service. In the web service, use the SharePoint API to merge or join two or more list. Then, in SSRS, retrieve data from the XML web service using the XML data source type.

    Reference:
    How do I use Report Builder 3.0 to join data from multiple sharepoint lists

    Regards,


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Shahfaisal Muhammed Monday, December 10, 2012 11:48 AM
    • Marked as answer by Mike Yin Sunday, December 16, 2012 1:29 PM
    Monday, December 10, 2012 6:28 AM

All replies

  • "By default, we can only get values from one list in one query.

    To get values from two list, I would suggest you creating two dataset in Reporting Services, then geting values from each list in each dataset."

    -Jinchun Chen

    However, to my knowledge, a data region (table) may only reference one dataset, not two, making the combination of two datasets into a single table not possible.

    One other comment I saw in another thread was the use of "SLAM"

    http://slam.codeplex.com/

    "Description
    Most developers who have used SharePoint as an application development framework have run into the realization that SharePoint is NOT a relational database. In fact, the accepted wisdom is if you need relational tables, use ASP.NET/SQL straight-up, not SharePoint.

    Enter SharePoint List Association Manager (SLAM). In short it allows you to define relationships (one to one, one to many, many to many) between SharePoint lists (or Content Types) and then leverage those relationships in webparts or custom field types using familiar and straight forward SQL queries."

    Hope this helps.

    Regards,

    Craig


    • Edited by determinism Friday, December 7, 2012 5:53 PM
    • Proposed as answer by Mike Yin Monday, December 10, 2012 6:08 AM
    • Marked as answer by Mike Yin Sunday, December 16, 2012 1:29 PM
    Friday, December 7, 2012 5:47 PM
  • Hi Mark,

    In Reporting Services, the SharePoint List data source type uses the GetListItems method to retrieves the data for a list, and the method can only return the records for a single list. To work around this issue, we can:

    • Use subreport to render data from multiple SharePoint Lists into one tablix.
    • Implement a new XML web service. In the web service, use the SharePoint API to merge or join two or more list. Then, in SSRS, retrieve data from the XML web service using the XML data source type.

    Reference:
    How do I use Report Builder 3.0 to join data from multiple sharepoint lists

    Regards,


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Shahfaisal Muhammed Monday, December 10, 2012 11:48 AM
    • Marked as answer by Mike Yin Sunday, December 16, 2012 1:29 PM
    Monday, December 10, 2012 6:28 AM
  • Thankyou for your reply.

    If I use a subreport to render data from multiple SharePoint Lists into one tablix will I be able to export that tablix as one list as a data feed that I can then import into PowerPivot.

    My ultimate aim is to export multiple SharePoint lists as 1 data feed.

    Thanks,

    Mark

    Monday, December 24, 2012 10:48 AM
  • Hi Mark,

    We use a product (and have been for several years now) in our projects made by : http://www.enesyssoftware.com/.  This allows us to add multiple Sharepoint Lists, Multiple SQL sources and then do joins on the data as required writing normal SQL statements against the Sharepoint list(s).  It works for us and may save you a heap of time.  I can't believe Microsoft don't enable the same functionality but good on Enesys for doing it.

    We initially looked at writing our own extension to SSRS that would do similar but then found Enesys and haven't looked back.  It has saved us (and therefore our clients) a lot of wasted money and time.

    Hope that helps

    Mike Lee


    • Edited by Mike Lee NZ Friday, January 25, 2013 8:45 PM
    Friday, January 25, 2013 8:45 PM
  • Nice one.  Thanks for this.  I will definitely check this out.

    Mark

    Monday, January 28, 2013 9:43 AM