One of the requirements at my job was to create report based on a custom SharePoint list. At first I had to struggle since most of the information relating to Reporting Server and Sharepoint is very scattered on the internet, and being in a Program Management role I am not a hardcore programmer. Below is my compilation on how I actually used standalone microsoft reporting server with SharePoint, i.e. without using the Reporting Server & SharePoint Integration mode, I hope this helps any one getting started with reporting based on a SharePoint List.
As first step you need to get following software installed and ready;
1. Microsoft SQL Server (I used 2005, SP3)
2. Microsoft SharePoint Services (I am using 3.0)
3. Microsoft Reporting Services, standalone mode.
4. Visual Studio or Business Intelligence Studio.
Step for Creating Data Source:
1. Launch Visual Studio.
2. Select New project, if this is your first time with this project I recommend using Report Server project Wizard as your first project.
Click Next.
3. In wizard window add data source name e.g. myfirstdatasource.
4. Select Type 'XML', this is for querying data
5. In connection String use this format: http://<site>/_vti_bin/lists.asmx replace string if you want to connect to a sub site e.g.
http://sps_Server/mysite/mysubsite/_vti_bin/lists.asmx
(you have option to select shared data source, in case you are using multiple reports and wants to share data source between them)
Click Next.
6. In next dialog, use query in following format:
- <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>{77C1775D-1B84-4547-BDFA-E49AB2FA2574}</DefaultValue>
- </Parameter>
- <Parameter Name="viewName">
- <DefaultValue>{5542DD96-BF96-49E1-8052-CGADDE77026F}</DefaultValue>
- </Parameter>
- </Parameters>
- </Method>
- <ElementPath IgnoreNamespaces="True">*</ElementPath>
- </Query>
Line 2: Uses the webservices of site and query ‘a’ specific list.
Line 5-7: Pass list name as parameter, we will be using GUID.
Line 8-10: By Default, if you don’t specify viewName as defined in line 8-10, the server will query the default view. My requirement was to query a specific view, so I am defining a view’s GUID.
There is an excellent utility to get GUID of lists and views, called Stramit Caml Viewer 2007:
7. In Next steps choose how you want your reports to look.
8. In last dialog box, (shown below) select the reporting server you want to deploy to, if you are not familiar with reporting server there are two URLs that you will be working with, one will be similar to http://<Server>/ReportServer, you publish your reports from visual studios at this location. The second is similar to http://<server>/Reports, this is where you access your reports.
NOTE: These URLs are based on default installation of reporting server, it might be different for your installation, please contact your system administrator for correct URLs.
9. Click next, this screen will show you summary. Verify if all information is correct and click finish.
10. The system has created a default report for you. You can always customize this as per your requirement. I plan to write about this as soon as I have some more time to spare… :)
11. Once you are done with your changes you can always deploy by right clicking the project in solution explorer and selecting deploy.
Check out all your reports at the server/reports URL.
