SharePoint and SQL Server Reporting Services

So, lots of developers out there that work in the same areas as I do (C#, ASP.NET, SharePoint/MOSS) have authored articles about how to extract data from a SharePoint list via the SharePoint list web services (http://sitename/_vti_bin/Lists.asmx) and use that in a SQL Server Reporting Services report. My favorite posting on this is my Avanade co-worker Jason Huh (http://www.jyhuh.com) who posted a blog entry (http://www.jyhuh.com/blog/archive/2007/10/01/using_sharepoint_web_service_from_ssrs.aspx) filled with great images and directions.
I appreciate the detail he went to as it allowed me to follow those directions to produce 6 or so SSRS reports on a recent project I was working on. The data in those lists on my project were actually promoted lists from submitted InfoPath forms but their source does not honestly matter for this posting as we are only interested in getting the data out of those lists and not into them. What made those lists a problem is they would have sometimes 15 or 20 columns in the default view. Looking at them in the browser, provided interesting, while not bad, user experience. In my mind, what would have been better, is if we could have a view just for the reporting items without cluttering up the default view for the users looking at the submitted forms. This blog post is the steps on how to do that without custom C# code or anything like that using only the SharePoint web services and SSRS. I used WSS 3.0 installed on SQL Server 2008 with SSRS 2008 but the basics should apply and I will confirm they work on SQL/SSRS 2005 later.
 
So, to show my my solution, which I could not find any place else out there on the interwebs when I needed it a few months ago, lets take a list in SharePoint:
This list shows an ID field, and a concatenated Title with that holds state names, their abbreviation and the region they belong in all in the default view "All Items":
 
 
 
Here is another view of the list, called reporting view, that shows 4 different fields that are in that list. It is the same data but in different fields as
well as an additional Boolean field called active.
 
States-ReportingView
 
So, now that we have our list defined, lets create an empty project using the SQL Business Intelligence Studio.
 
We are going to need to add two shared data sources of type XML (obviously change the reference to your own server)

image

 

image

Now add a blank report and create one data set, using the shared data source views with the following query:
 

<Query>
    <SoapAction>
http://schemas.microsoft.com/sharepoint/soap/GetViewCollection</SoapAction>
    <Method Namespace="
http://schemas.microsoft.com/sharepoint/soap/" Name="GetViewCollection">
        <Parameters>
            <Parameter Name="listName">
                <DefaultValue>States</DefaultValue>
            </Parameter>
        </Parameters>
    </Method>
    <ElementPath IgnoreNamespaces="true">
GetViewCollectionResponse{}/
GetViewCollectionResult{}/
Views{}/
View{@Name,@DisplayName}</ElementPath>
</Query>

Here is the result:
(Notice the element path statement used to get just the fields I want back)
You will need to the GUID for the view you are going to report off but you can’t copy it to your clipboard :(.
 
image

Now we are going to need to create another data set called Lists using the shared data source Lists with the following query:
Substitute the GUID of the view you want as well as fields you need to report on.
You will have have to use the format for the fields @ows_FieldName or replace everything in the ElementPath element with a star “*” to get them all back.

<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>States</DefaultValue>
            </Parameter>
        <Parameter Name="viewName">
                <DefaultValue>{7D9158D1-793A-469C-A8A4-60864F586024}</DefaultValue>
            </Parameter>
        </Parameters>
    </Method>
    <ElementPath IgnoreNamespaces="true">
GetListItemsResponse{}/GetListItemsResult{}/listitems{}/data{}/row{@ows_Title,@ows_StateName,@ows_StateAbbreviation,@ows_Region,@ows_Active}
</ElementPath>
</Query>

The result looks something like this:

image

Once you have that all created and working well (it does take some refinement) we are going to create a report parameter called view name. My preferred way is to right click in the report data tool box in the Report Parameters folder and chose Add Parameter.

I called it viewName bit obviously it could be anything.

On the General tab, The parameter should be a Data Type of “Text”, allow for blank values, and for now the parameter visibility should be Visible (we will be changing that near the end).

image

Additionally, the Available Values tab should be configured to come from our DataSet Views, with the Value Field called Name and the Label Field called DisplayName:

image

Finally, the Default Values tab should be configured similarlly to come from our DataSet views with the value field being Name:

image

Once that is all built, design the report how you need to but for this example, I added a Tablix (Table in SSRS 2005) with each of the fields I needed in my report.

image

Once the report is working, go back into the lists query and replace the GUID with the parameter name, (@viewName)

<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>States</DefaultValue>
            </Parameter>
        <Parameter Name="viewName">
                <DefaultValue>[@viewName]</DefaultValue>
            </Parameter>
        </Parameters>
    </Method>
    <ElementPath IgnoreNamespaces="true">
GetListItemsResponse{}/GetListItemsResult{}/listitems{}/data{}/row{@ows_Title,@ows_StateName,@ows_StateAbbreviation,@ows_Region,@ows_Active}
</ElementPath>
</Query>

When you preview the report, you should now get a drop showing the views you have on that list and an opportunity to get the fields you need on the report. The final steps are to filter the dataset to only return the view want by right clicking on the data set and then the filter tab and setting the expression to the name of the view you need:

image

Test this out on your preview to make sure you only get one view back in the drop down.

The final step is to change the parameter to hidden so it will always use this view without prompting the end user by right clicking on the parameter and changing the Visibility setting to hidden:

image

The end result will be a functioning report, using a view that is not the default view but rather one just for reporting. Eventually, I am sure this could be extended to a hidden view but I have not tried that yet.

 

If you have problems or questions, I can do my best to answer them if you leave a comment on this blog!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s