SharePoint – List add distinct Parameter values

Unfortunately the CAML syntax does not support the DISTINCT keyword. I needed this this fill in a parameter for filtering purposes.

When searching the net the only solution was to add some CODE to the report Surprised smile

Luckily that guys had the code snippet posted. So here we go.

You need 2 datasets. 1 main dataset you will display on the report. And a second one that you will use to retrieve the Parameter Field from.


In the Projects dataset are all the NON DISTINCT BU names.

1. So we need to create a normal Parameter to get these values from the list.

General settings, check Allow Multiple Values and make it Hidden


Available values is picked up from the Query.


Don’t forget to fill in the Default Values


2. Next add the piece of code I found in the net. It will REMOVE all DUPLICATES from the list values.


Public Shared Function RemoveDups(ByVal items As String) As String 

Dim noDups As New System.Collections.ArrayList() 
Dim SpStr 

SpStr = Split(items ,",") 
For i As Integer=0 To Ubound(Spstr) 

 If Not noDups.Contains(SpStr(i).Trim()) Then 


 End If 

Dim uniqueItems As String() = New String(noDups.Count-1){} 

    Return String.Join(",", uniqueItems) 

End Function 

3. Next add a second Parameter that will retrieve the DISTINCT Parameter Values.


This will be the Parameter that is Visible in the report.


The Available Values will be a FORMULA EXPRESSION


=Split(Code.RemoveDups(JOIN(Parameters!BU.Value, “,”)), “,”)


For the DEFAULT VALUES you add the same Expression.


4. Add the FILTER to the TABLIX in the report and you are ready to go.



Thanks for the guys who supplied the code snippet.


3 Responses to SharePoint – List add distinct Parameter values

  1. This works great for text. Do you know of anyway to do it with a date/time field?

  2. Hi,
    Thanks for this. it works great for me. How do I enable multiple values for my parameters? I tried to check the box but the report throw an error message.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: