topic PBI Report Builder - removing duplicate values in parameter dropdown in Report Server

PBI Report Builder - removing duplicate values in parameter dropdown

slashdavid — Tue, 12 Dec 2023 20:06:47 GMT

I'm currently working through building a paginated report with PBI Report Builder, but a small issue came up. One of my parameters is called Spend Year. Currently, for the sake of working with a faster loading file, I filtered this down to only include the year 2023. However, the issue with this is that the parameter dropdown is displaying multiple options for 2023. I believe it's displaying a 2023 for each row in the dataset that contains one.

Having multiple options for the same thing just isn't practical, so I've been looking for a way to have the parameter dropdown display distinct options. I managed to find this article: https://www.c-sharpcorner.com/article/remove-duplicate-filter-values-from-ssrs-parameter-drop-down/. While this matches my situation, the VB code that he writes is for String values. I'm working with integers. I tried my best to adapt his code to work for integers (you can find the code below), but after following through everything, my Spend Year parameter is now greyed out with no selectable values. I was sure to configure the available values as detailed in the article.

Does anyone happen to have any idea how I should go about this? If it's useful to know, my data was pulled via a DAX query.

Thank you in advance!

My version of the code:

Public Shared Function RemoveDuplicates(parameter As Parameter) As Integer()
    Dim items As Integer() = parameter.Value
    Array.Sort(items)
    Dim k As Integer = 0
    For i As Integer = 0 To items.Length - 1
        If i > 0 AndAlso items(i) = items(i - 1) Then
            Continue For
        End If
        items(k) = items(i)
        k += 1
    Next
    Dim unique As Integer() = New Integer(k - 1) {}
    Array.Copy(items, 0, unique, 0, k)
    Return unique
End Function

Re: PBI Report Builder - removing duplicate values in parameter dropdown

bradsy — Wed, 13 Dec 2023 18:14:20 GMT

For your parameter query, can you add a distinct clause? This will filter down to unique values.

Re: PBI Report Builder - removing duplicate values in parameter dropdown

slashdavid — Thu, 14 Dec 2023 16:10:16 GMT

Hey, thanks for your response! For some reason the DISTINCT clause was returning an error. I ended up just pulling separate datasets for each parameter to get a list of all unique values that way

Re: PBI Report Builder - removing duplicate values in parameter dropdown

d_gosbell — Thu, 14 Dec 2023 22:44:06 GMT

It's probably more efficient to create a separate dataset for each parameter. This is what the query builder will do if you add a parameter using that. The database engine can typically get a distinct list of values much faster than using VBA to loop through the data on the report side