How to Create SSRS Report on SharePoint List

There is fairly an easy way to create a SSRS report on SharePoint List. By using an XML data source, we will get access to the SharePoint List. Then, create your own CAML query to manipulate SharePoint List Data. Here is the step by step example of creating SQL Reporting Services report on SharePoint List.

 

1. Create a Report Server Project in Visual Studio

 

2. Add a Data Source with following info types:

 

- Name: Should be the Data Source Name

 

- Type: XML

 

- Connection String: Should be the SharePoint Web Service for SharePoint List

 

  [Example: http://myintranet/ts/FazlulsWorld/_vti_bin/lists.asmx]

 

- Credentials: Windows Authentication or choose appropriate radio button

 

3. Add a New Report:

 

- Select Shared Data Source that you just created

 

- Paste the CAML Query into the Query String. You may have to find out the GUID for the SharePoint List and View on which you are reporting on.

 

To find these out:

 

- Go to your SharePoint List

 

- Click on Settings-> List Settings

 

- Scroll to the bottom of the page where you can see the views of that list

 

- Select the view on which you want to report on

 

- You will notice the following type of URL in the Browser address bar:

 

 http://myintranet/ts/FazlulsWorld/_layouts/ViewEdit.aspx?List=%7BC2189786%2D9403%2D4A02%2D904F%2DE8E02A7A208A%7D&View=%7BD5114EBE%2DC217%2D4BBD%2D9951%2D6F33332D8269%7D&Source=%252Fts%252FFazlulsWorld%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D%25257BC2189786%25252D9403%25252D4A02%25252D904F%25252DE8E02A7A208A%25257D

 

- Copy the strings for List and View [Red Tagged]

 

- Paste the string into the Encoded: Text Box of URLEncode/URLDecode site    

 

  [http://www.albionresearch.com/misc/urlencode.php] 

 

- Click URL Decode button. It will decode the List Name GUID into Plain: Text Box

 

- Do it for the View Item too and place the List and View GUID into the listName and viewName area of the CAML 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>{C2189786-9403-4A02-904F-E8E02A7A208A}</DefaultValue>
         </Parameter>
         <Parameter Name="viewName">
            <DefaultValue>{D5114EBE-C217-4BBD-9951-6F33332D8269}</DefaultValue>
         </Parameter>
      </Parameters>
   </Method>
</Query>

 

- Select appropriate fields for reporting