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.

image

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

clip_image002[4]

Available values is picked up from the Query.

clip_image004

Don’t forget to fill in the Default Values

clip_image006

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

clip_image008

 
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 

  noDups.Add(SpStr(i).Trim()) 

 End If 
Next 

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

    Return String.Join(",", uniqueItems) 

End Function 

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

clip_image010

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

clip_image012

The Available Values will be a FORMULA EXPRESSION

clip_image014

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

clip_image016

For the DEFAULT VALUES you add the same Expression.

clip_image018

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

clip_image020

clip_image022

Thanks for the guys who supplied the code snippet.

Advertisements

2 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?

Leave a Reply

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

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

%d bloggers like this: