locked
How to display reports from multiple lists in SQL Server Reporting Services Report Viewer RRS feed

  • Question

  • Hi All,

      I want to generate report through SSRS from more than one sharepoint lists. To generate report from one list i am using below query:

    <Query>
       <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
       <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
          <Parameters>
             <Parameter Name="listName">
                <DefaultValue>{577B2EB4-8BE3-484B-AF5F-8C6F4BF70352}</DefaultValue>
             </Parameter>
          </Parameters>
       </Method>
       <ElementPath IgnoreNamespaces="True">*</ElementPath>
    </Query>

    Please suggest me a query to get results from more than one list like we use joins for SQL tables.

    Thanks in advance,

    Saurabh Kumar Singh

    Mail: saurabhsinghmca@gmail.com

    Friday, April 1, 2011 11:08 AM

Answers

  • Hi,

    This article may guide you in creating SSRS repoint with Sp Lists in SQL 2005.

    http://amitphule.blogspot.com/search/label/SharePoint%20Reporting

     

    Here is what you can do:

    1. Create multiple datasets. means each list have its own data set.

    2. Create new lists in sharepoint . in this new list create items which matches to the name of columns of each dataset. so for each dataset create a separate list. This is because, we can't UNION sp lists as we do in SQL.

    then create a parameter using the new lists you created.

    then create a filter on each data set which matches the paramer value: example company in [@company]. Here company is column @company is parameter.

    3.Create matrix using these data sets from selected columns. create multiple matrix from all the datasets. means if you want data from 5 lists , create 5 matrix.

    4. then attach all these matrixes sideby side.

    5. Run the report, based on the parameter values, the data will get displayed dynamically from multiple lists.


    Best Regards, Ashok Yadala
    • Marked as answer by Leoyi Sun Friday, April 8, 2011 9:21 AM
    Thursday, April 7, 2011 11:33 AM

All replies

  • hi,

    this is possible if you are usin ssrs 2008 r2 and moss 2007. there is "sharepoint list " connection string with which you can associate each list to a separate datasets and then use parameters to filter data from multiple lists and display dynamic data based on filters.

    Even the same can be achieved through SQL 2008 XML Connection string that you are using. As i said use parameters and filters on each data set and then create matrix multiple times with different datasets.

    I have implemented this with the same technique.

     


    Best Regards, Ashok Yadala
    Friday, April 1, 2011 3:57 PM
  • Hi Ashok,

        If i am using MOSS 2007 and Sql Server 2005, then is there any way to generate SSRS reports with it?

      

    Thanks,

    Saurabh

    Monday, April 4, 2011 5:36 AM
  • Hi,

    This article may guide you in creating SSRS repoint with Sp Lists in SQL 2005.

    http://amitphule.blogspot.com/search/label/SharePoint%20Reporting

     

    Here is what you can do:

    1. Create multiple datasets. means each list have its own data set.

    2. Create new lists in sharepoint . in this new list create items which matches to the name of columns of each dataset. so for each dataset create a separate list. This is because, we can't UNION sp lists as we do in SQL.

    then create a parameter using the new lists you created.

    then create a filter on each data set which matches the paramer value: example company in [@company]. Here company is column @company is parameter.

    3.Create matrix using these data sets from selected columns. create multiple matrix from all the datasets. means if you want data from 5 lists , create 5 matrix.

    4. then attach all these matrixes sideby side.

    5. Run the report, based on the parameter values, the data will get displayed dynamically from multiple lists.


    Best Regards, Ashok Yadala
    • Marked as answer by Leoyi Sun Friday, April 8, 2011 9:21 AM
    Thursday, April 7, 2011 11:33 AM