business intelligence consulting
 

Using a SharePoint List Connection Type as a data source for Reporting Services

by Hans Esquivel 11. October 2012 11:01

In this demo, we'll show you how to create a SQL Server Reporting Services report, using a SharePoint List connection type as the data source.

 

This Tip assumes you have experience working with SQL Server Reporting Services and some basic knowledge of SharePoint Lists. We'll be using Report Builder 3.0 for the purposes of this demo. You may use Business Intelligence Development Studio (BIDS), however.

 

Steps

  1. Open Report Builder 3.0.
  2. Create a new Data Source.
  3. Attach to SharePoint List connection Type.
  4. Set URL path to SharePoint site.
  5. Test Connection.
  6. Design SharePoint List Query.
  7. Design Report Matrix/Table.
  8. Run and Publish Report.

 

Open Report Builder 3.0

1. Start >> All Programs >> Microsoft SQL Server 2008 R2 Report Builder 3.0 >> Report Builder 3.0

 (you should see the following: )

 

2. Click on the Table and Matrix Wizard template option from the list.

3. From within the Choose a dataset window, make sure Create dataset is selected and click Next.

(you will see the Choose a connection to a data source window)

4. Click New... button. The Data Source Properties window will render.

5. Give the new Data Source a Name.

6. Select Microsoft SharePoint List from the Select connection type dropdown list.

7. Enter the URL path to your SharePoint site. Example: https://sharepoint.domain.com

(your configuration should look similar)


7. Click the Test Connection button to validate.

8. If the Test Connection passes, click OK to close the Data Source Properties and click Next to navigate to the Design a Query window.

9. From withn the Design a query window, you will notice three panes:

  • SharePoint Lists - Displays a list of all SharePoint lists from the site you entered in the Connection String.
  • Seleced Fields -  Displays the fields you have selected from the SharePoint Lists pane.
  • Applied Filters - Allows you to set filter(s) on the SharePoint list fields selected. Ths will run before data is returned to the report.

Expand the desired list from the SharePoint Lists pane and select field(s) you would like to include in the query. Optionally, you can check the root box of the list to select all fields. 

10. Once you have selected your fields, click the Run Query button from the toolbar to execute the query. The Query results will render below the three panes mentioned above.

(you should see something similar)


10. You may now add any filters you would like in the Applied Filters pane.

(We skip this step for the demo.)

11. Once you are comfortable with the results, click Next > to render the Arrange fields window.

12. From within the Arrange Fields window, you will notice four panes:

  • Available Fields - list of fields select in the Query Design step.
  • Row Groups - Include the field(s) you would like included in the table or matrix rows.
  • Column Groups - Include the field(s) you would like included in the table or matrix columns.
  • Values - Include the field(s) you would like included in the matrix details section.

Note: you can change the order of the fields in each section by holding down the left mouse button on a field and dragging it to the desired position.

(you should see something similar)

13. Click Next > if you're happy with your arrangement of fields.

14. The next step is to Choose the layout. We won't discuss the properties in this section. Keep the defaults and click Next >.

15. The final step of the Table and Matrix Wizard is to Choose a style. Keep the default option of Ocean and click Finish>>. You will now be taken to the Report Builder 3.0 Designer.

(you should see something similar)

16. Click the Run button from the top-left corner of the ribbon to view the report.

 

You have completed the general steps in creating an SSRS Report, using a SharePoint List Connection Type.

 

Cheers!

In this demo, we'll show you how to create a SQL Server Reporting Services report, using a SharePoint List connection type as the data source.

SharePoint | Reporting Services

Comments (2) -

Siskin82
Siskin82 U.A.E.
5/1/2013 3:50:31 PM #

I have configured SharePoint 2010 environment for Windows Authentication as well as Form based Authentication.

When I try to create a report (BIDS) with SharePoint list data source by connecting as "Use Windows Authentication (Integrated Mode)", I receive the error "Server was unable to process request. ---> Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (System.Web.Services)" (I am logged in with the System Account)

I have tested the same by disabling the Form base authentication and the connection with the datasource works fine. After creating the datasets I enbaled the Form based authentication and after that I am again getting "Access denied" when trying to connect to the datasource.

This means the issue is with the Form based authentication. How can this be resolved? Is there any configuration required for form based authentication which I am missing and which is giving Access Denied??

Regards,

Siskin82

hesquivel
hesquivel United States
5/1/2013 5:10:52 PM #

Hi Siskin82,

To my knowledge, Reporting Services forms authentication isn't supported for reports servers running in SharePoint Integration mode. The Data Source credentials would have to be stored.  I've listed a few links you may want to review for more details.

Note: If you're planning on using Subscriptions in SSRS, you would have to use stored credentials, so perhaps that would work for you. I hope this information helps.

Register a Service Principal Name (SPN) for a Report Server
technet.microsoft.com/en-us/library/cc281382.aspx

Security Overview for Reporting Services in SharePoint Integrated Mode
technet.microsoft.com/.../bb283324(v=sql.105).aspx
(Take a look at both "Authentication Providers in SharePoint Technologies" and "SharePoint and SSRS Authentication Topologies" for details.)

Configure Custom or Forms Authentication on the Report Server
msdn.microsoft.com/en-us/library/cc281383.aspx

Cheers,

Hans



Pingbacks and trackbacks (1)+

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud