SSRS. List distinct values from a SharePoint list

If you create reports using Report Builder or BIDS it's available to use a SharePoint list or document library as a DataSource. To retrieve data in this case CAML query is used, thus there is no direct way to get only distinct values from DataSet.

And such is the need at least to fill parameter values:

Preface

So we have a report using the SharePoint list as a DataSource, DataSet, selecting values from one column of the list and Parameter wich displays (has to display) distinct values from the DataSet.

And if you run the report as it is, you will see duplicate values in the parameter drop-down list (see picture above). That is exactly what the DataSet returns:

System.Linq

To process data before transferring it to the parameter we need to pass through its a function (VB.NET is already some closer), wich sorts and removes duplicate values from the array transferred to it.

For such cases the report allows us to use custom code. It can be either reference to an assembly or just plain text. In the case of the plain text you have to use VB.NET because of support of C# is not implemented.

Installation of Report Builder requires .Net Framework 3.5, which allows us safely use Linq. First of all add reference to System.Core assembly:

Now in Code tab point out our static function, that accepts a report parameter and returns unique values for it:

Public Shared Function GetUniqueValues(parameter As Parameter) As String()
    Dim items As Object() = parameter.Label
    System.Array.Sort(items)
    Dim res As [String]() = System.Linq.Enumerable.ToArray(System.Linq.Enumerable.Cast(Of String)(System.Linq.Enumerable.Distinct(items)))
    Return res
End Function

You can use Label property of parameter instead of Value property. It may look something like this:

Reference to values from the data set by using Expression in the parameters is impossible. As a result, we have two parameters: a hidden parameter that retrieves data from the data set and the second one that filters the data and displays it to the user:

Performance on large lists, in this case, would be a bottleneck, but I couldn't find out the other way. I can only add that the long waiting time of generation can be replaced with a subscription. It allows us to obtain the report, such as e-mail in PDF format every day at 8:30 AM.

Users will not have to "chase" for information. The information itself will come to them at his convenience. In addition, corporate email is almost always available, which is not the corporate portal.

Demo report is here.