Compartilhar via

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

  • Artigo

You can connect to a Windows SharePoint Services Lists Web Services, and use GetListItems method retrieve data from a SharePoint list by using the XML data source type in SSRS.However, the GetListItems returns records for a column of a SharePoint list may include duplicate records.

Unfortunately there is no direct way to remove the duplicate records using the Windows SharePoint Services Lists Web Services.This article will show you how to retrieve a column of a Sharepoint list and how to use Custom Code in SSRS to remove the duplicate records.

Create a Report Server Project in Business Intelligence Development Studio

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

Add a report to the project

1. Right click the Report folder, select Add, click New Item....

2. Select Report template, input a report name "Remove Duplicate Records.rdl" … .

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. For example:
https://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:

<Query>

   <SoapAction>https://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>

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

      <Parameters>

         <Parameter Name="listName">

            <DefaultValue>{5D7A343B-CFE2-4B53-8C51-EEEB87591A12}</DefaultValue>

         </Parameter>

     </Parameters>

   </Method>

   <ElementPath IgnoreNamespaces="True">*</ElementPath>

</Query>

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.

Reference

Custom Code and Assembly References in Expressions in Report Designer (SSRS)

https://msdn.microsoft.com/en-us/library/ms159238.aspx

XML Connection Type (SSRS)

https://msdn.microsoft.com/en-us/library/dd220468.aspx

Lists.GetListItems Method

https://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx

[download sample]

Comments

  • Anonymous
    June 20, 2013
    Interesting post. thanks!

  • Anonymous
    January 31, 2014
    Works fine in BIDS but not working when report uploaded to Report server site. Am getting the error "The report parameter ‘parameter1’ has expression-based ValidValues. The sizes of the value and the label (multi-value) arrays have to be identical. (rsInvalidValidValueList)" Do you have any solution for this? Thanks in advance.....

  • Anonymous
    April 10, 2014
    Thank you so much!! It really helped me out with my report!

  • Anonymous
    November 03, 2014
    This guide is really stupid. All you need to do is create new datasets with distinct queries for the items you want, for instance SELECT DISTINCT COLOR FROM TABLE; and then specify that dataset and field for the filter!

  • Anonymous
    November 03, 2014
    Thank you so much ... this worked like a charm.

  • Anonymous
    November 05, 2014
    @boogerface - so what if you have a connection to a SharePoint 2010 List and instead of a SQL DB as your Datasource?  I only get XML instead of SQL, so using DISTINCT won't work for my situation.  I'd appreciate the help.

  • Anonymous
    November 12, 2014
    I am getting the error : The expression that references the parameter 'dummy' does not exist in the parameterscollection. Letters in the names of parameters must use the correct case. My dummy parameters is called dummy and the expression it is referring to is =Code.RemoveDuplicates(Parameters!dummy). This is with SSRS 2008.

  • Anonymous
    June 21, 2015
    It is not working and it is not throwing any error also. the second parameter is simply greyed out.