connecting sql reporting services to a share point list

Connecting SQL Reporting Services to a SharePoint List Jennifer Lewis

Connecting SQL Reporting Services to a SharePoint List

Written on: 29 December 2008

Overview Sometimes people such as executives or managers would like to see reports of the data that appears in SharePoint without having to look in SharePoint and creating the information “from scratch”. Here are a few examples of the types of reports that interest managers or executives:

• A report containing summary averages of all the surveys that have been taken by the users

• A report containing a list of overdue outstanding issues • A project status report

Assumptions Although the document contains a small demonstration on using the SQL Reporting Extensions designer, the reader should have general working knowledge on how to create reports using the SQL Reporting Extensions (SQL Reporting Services). A good tutorial that can get you started on creating reports using Reporting Services can be found at Emad Yazdanpanah’s blog: http://www.csharpcourses.com/2008/05/create-report-with-reporting-services.html. Requirements In order to create reports:

• Visual Studio 2005 or later • SQL Reporting Extensions, which are installed by default if you installed the SQL Server

2000 or SQL Server 2005 Client tools • A SharePoint list exposed Anonymously or via Windows Integrated Authentication

Directions The demonstration is using Visual Studio 2005.

1. Open Visual Studio 2. Start a new project by selecting File-New Project-Business Intelligence Projects-

Report Server Project. In this illustration, the project will be named SampleSPReport .



3. In the Solutions Explorer, right-click on Shared Data Sources and select Add New Data Source

4. Add the following information:

Under the General Tab a. In the Name field, name the data source. In this example, the name of the data

source will be SharePointSite. b. In the Type drop-down field, select XML c. In the Connection string, enter

http://<server>/<path>/_vti_bin/lists.asmx, where <server> is your server name, and <path> is the path where to get the list. For example, if your server is called Bogus, and the path is Site1, enter http://Bogus/Site1/_vti_bin/lists.asmx.



Under the Credentials Tab

a. Select Use Windows Authentication (Integrated Security) or No Credentials. In this example, we will Use Windows Authentication (Integrated Security)

The other authentication types are not supported by the designer. If you need any of the other authentication types, you can change the authentication type when you publish the data source on the SQL Report Server. You will need the correct access on the Report Server to do this.



5. Click OK 6. Right click on the Reports folder and select Add – New Item



7. When the Add New Item dialog box appears, select the Report template. You may leave the default name for the report, or you may rename it. For this illustration, I will rename it FavoritePythons.

8. Click Add The report designer should open by default.



9. If it is not currently selected, select the Data tab.

10. From the Dataset drop-down field, select <New Dataset>



This illustration will demonstrate creating a simple report without any filtering or manipulation. 11. In the dialog box enter the following information: From the Query Tab

• In the Name field, enter the name of the dataset. In this illustration, I will call it Pythons.

• In the Data source drop-down, make sure that the data source that you created earlier is selected

• In the Command type drop-down, select text. • In the Query string box, enter the following:

<Query> <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems" /> <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction> </Query>

• You may leave the value in the Timeout field blank.



In the Parameters Tab

• Enter the following parameters: listName The GUID for your list rowLimit 9999 (or any number you like) viewName The GUID for the view on your list

You can get the List GUID and the View GUID using the Stramit CAML Viewer (http://www.codeplex.com/SPCamlViewer) or the GUID Picker (http://blogs.msdn.com/ronalus/archive/2007/09/08/a-little-guid-picker.aspx)



12. Click OK You should now see your data set information appear in the left hand side of your workspace. The field names start with the ows_ prefix.



Troubleshooting Tips • If you initially get an error message when you click OK, close the error dialog

and click the refresh button in the toolbar. If you are still getting an error, check your parameters.

• If you don’t see any fields with the prefix of ows_, it means that your list has no entries.

If you are already familiar with working with the SQL Reporting Extensions designer, you should be able to create a report using the SharePoint data. However, I will include an example instruction on designing a simple report using the Table report items and my data.

1. Select the Layout tab

2. From the Toolbox, select the Table object.



3. Click and drag the object to the work area. 4. Set the following properties on the table:

a. In the DataSetName, select the data set that you created. In this example, the data set is Pythons.

b. You can also set other properties such as the font format and page breaks. For this illustration, only the DataSetName property will be set.



5. From the Datasets section, click on the fields that you would like to appear on the report

and drag the fields the Detail row in the table.



6. To test your report, click the Preview Tab

You should see an example of your report:

Drag the fields from this section to the “Detail” row on the table.



Again, if you are familiar with creating reports using the SQL Reporting Extensions and SQL Reporting Services, you can “jazz up” the report by adding formatting such as:

• Alternating colors for the rows • Different titles and footers • Different fonts and colors • Headers and footers on the report. • Any custom code on the report.

Once you create the report, you are ready to publish to a SQL Report Server.

1. In the Solution Explorer, right click on the project name and select Properties.



2. Enter the following information in the fields:

a. In the TargetDataSourceFolder, enter the folder name exactly as it appears on the SQL Report Server where your data source will be stored. For example, if the folder is called Bogus Reports, enter Bogus Reports.

b. In the TargetReportFolder, enter the folder name exactly as it appears on the SQL Report Server where your reports will be stored. For example, if the folder is called Bogus Reports, enter Bogus Reports.

c. In the TargetServerURL, enter the URL where the SQL Report Server is located. In your configuration, you may have SQL Report Server running on the same box as your URL, or may be running SQL Report Server on a separate machine.



3. In Solution Explorer, right click on the object you want to publish and click Deploy



Sources David Wise’s Blog: Connecting SQL Reporting Services to a SharePoint List. http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx. Retrieved 8/27/2008. TechNet. How to: Publish a Report to a SharePoint Library from Report Designer. Retrieved 9/11/2008.

connecting sql reporting services to a share point list

Documents