Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
For your parameter query, can you add a distinct clause? This will filter down to unique values.
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
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
User | Count |
---|---|
9 | |
8 | |
6 | |
2 | |
1 |
User | Count |
---|---|
15 | |
11 | |
3 | |
2 | |
2 |