How to get distinct values of a SharePoint list column using SQL Server Reporting Services

In SQL Server Reporting Services (SSRS), to include data from an XML data source in your report, you must have a dataset that is based on a report data source of type XML. This built-in data source type is based on the XML data extension. Use this data source type to connect to and retrieve data from XML documents, Web services, or XML that is embedded in the query.

We can connect to a Windows SharePoint Services Lists Web Services, and use GetListItems method retrieve data from a SharePoint library by using the XML data source type in SSRS.

However, the GetListItems returns records for a SharePoint list column may include duplicate records. In some scenarios, you may want to remove the duplicate records. For example, use the records in a parameter.

Since, there is no direct way to remove the duplicate records using the Windows SharePoint Services Lists Web Services, you can use Custom Code in SSRS to remove the duplicate records. Below are the detailed steps for your reference:

Create a Report Server Project in Business Intelligence Development Studio.

Open Business Intelligence Development Studio (BIDS) and new a Report Server Project.

Add a report to the project:

Create a XML data source

1.      From the View menu, select Report Data.

2.      In the Report Data panel, right-click Data Sources, and then click Add Data Sources….

3.      In the Data Source Properties dialog, configure the connection

a.      Type a name for the data source in the Name textbox

b.      Check Embedded connection

c.       Select type XML

d.      Type the connection string in the Connection string textbox

The connection string is the URL of the Windows SharePoint Services Lists Web Services.
http://sharepointasia/sites/IRSIS/_vti_bin/lists.asmx

4.      Go to the Credentials tab, check the item Use Windows Authentication(integrated security)

Create a Dataset based on the XML data source type

1.      In the Report Data panel, right-click Datasets, and then click Add Dataset….

2.      In the Dataset Properties dialog, type the name for the Dataset.

3.      Select the data source we created in the last step in the Data source dropdown list.

4.      Select Text for Query type.

5.      In the query textbox, type the following statement:

   http://schemas.microsoft.com/sharepoint/soap/GetListItems

  

     

        

            {5D7A343B-CFE2-4B53-8C51-EEEB87591A12}

        

     

  

   *

Note: You need to replace the value for listName parameter with actual value in your environment.

Add a dummy parameter to the report

1.      In the Report Data panel, right-click Parameters, and then click Add Parameters….

1.      In the Report Parameter Properties dialog, type a name for the parameter.

2.      Check the item Hidden and Allow multiple values

3.      Go to Available Values tab, set the parameter to get values from the Dataset we set before.

4.      Go the Default Values tab, set the parameter to get values from the Dataset we set before.

Embed code to the report

The code is used to remove duplicate records from the dummy parameter, and then return a unique array.

1.      Click the report body.

2.      Click the Report menu, and select Report Properties…

3.      Go to Code tab.

4.      Embed the following code:

Public Shared Function RemoveDuplicates(parameter As Parameter) As String()

            Dim items As Object() = parameter.Value

            System.Array.Sort(items)

            Dim k As Integer = 0

            For i As Integer = 0 To items.Length - 1

                        If i > 0 AndAlso items(i).Equals(items(i - 1)) Then

                                    Continue For

                        End If

                        items(k) = items(i)

                        k += 1

            Next

            Dim unique As [String]() = New [String](k - 1) {}

            System.Array.Copy(items, 0, unique, 0, k)

            Return unique

End Function

Create another parameter

This parameter is used for user interaction. This parameter get the distinct value from the custom code function RemoveDuplicates.

1.      In the Report Data panel, right-click Parameters, and then click Add Parameters….

2.      In the Report Parameter Properties dialog, type a name for the parameter.

3.      Go to Available Values tab, check item Specify values

4.      Click Add to add a value, type the following expression in both Label and Value textbox.

=Code.RemoveDuplicates(Parameters!DummyParameter)

Note: the DummyParameter is name of the dummy parameter.

5.      Go to Default Values tab, check item Specify values

6.      Click Add to add a value, type the following expression in the Value textbox.

If Allow multiple values is checked, please use the following expression

=Code.RemoveDuplicates(Parameters!DummyParameter)

Otherwise, you can use:
=Code.RemoveDuplicates(Parameters!DummyParameter)(0)

Note: the DummyParameter is name of the dummy parameter.

Additionally, if you use cascading parameter, and the child parameter includes duplicate records, please use the custom code function to remove the duplicate records.

A full sample can be downloaded from:

http://cid-3c7e963ff6ccd974.office.live.com/browse.aspx/.Public?uc=1

Reference

Custom Code and Assembly References in Expressions in Report Designer (SSRS): http://msdn.microsoft.com/en-us/library/ms159238.aspx

XML Connection Type (SSRS): http://msdn.microsoft.com/en-us/library/dd220468.aspx

Lists.GetListItems Method: http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx

Created By: Jin Chen

Create Date: 2010-11-10