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
 
 
- 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

Comments

  1. Hussain M. NaqviSeptember 22, 2009 at 5:01 PM

    How to pass a parameter to get a particular record from list(like all the records for CourseName)?
    How to add CAML query
    Where
    Eq
    FieldRef Name=' CourseName'
    Value Type='Text' CSharp
    /Eq
    /Where

    ReplyDelete
  2. MilesSeptember 2, 2023 at 1:27 AM

    This collaboration streamlines data analysis, visualization, and sharing within SharePoint, Device Streaming Slow enabling users to leverage the power of SSRS for comprehensive reporting solutions within their SharePoint environment.

    ReplyDelete

Post a Comment

Popular posts from this blog

Data Science, Big Data & Microsoft Machine Learning

-
Image
Preface Data is seemingly controlling us and everything around us in many ways. Big data is more prevalent in our daily life more than what we think. Business Intelligence has become integral part for effective decision making. Data Science, Artificial Intelligence (AI) and Big Data Analysis has been playing pivotal role in the digital transformation processes. Current industry trend is to assess data behaviour and build knowledge libraries. Industires are dealing with billions of micro devices that are connected to the control hub and emitting time series data continuously. These devices need to be connected to the central data pipeline and thus predict or prescribe device's health, availability and possible failure points. To connect billions of devices into corporate data pipeline, we need to have Big Data repositories. This is where IoT concept kicks in. Dr. Williams, Director of MIT described a world, where "things" (devices or sensors) are con
Read more

Database Testing With DBUnit

-
Image
DBUnit is an Open Source Database Testing Framework which was created by Manuel Laflamme. DBUnit is a Database Unit Testing Framework to Test Database Driven Classes, Procedures or Functions. NUnit, MVC .NET/Visual Studio testing platforms can be used to build a standard unit test suite. Developer should create a dataset, defined within an XML file, and then extends one of a number of parent classes to inherit pre-defined functionality in order to use the framework. An IDbConnection implementation to connect to the database, then DbUnit.NET can be used to test the data access code. Following Operations can be performed using DBUnit: UPDATE TRUNCATE INSERT REFRESH DELETE CLEAN_INSERT DELETE_ALL NONE Other Operations: Composite Operation Transaction Operation Identity Insert Operation DBUnit Support Databases: OracleDatabase MsSQL MySQL IbmDB2 IbmInformix H2 HypersonicSQL PostgreSQL SybaseSQL InterBase OpenBase8 SapDB/MaxDB Derby FrontBase HOW TO WRITE A DBUnit TEST CASE ? To create a
Read more