How can I get both values from a multi-dimensional array in a multi-select ssrs report parameter?

Question

I have the following SQL query in an SSRS dataset:

SELECT c.Id
, c.LastName + ', ' + c.FirstName AS CustomerName
, r.PurchaseDate
FROM     tblCustomer c
JOIN Receipt r ON r.CustomerId = c.Id
WHERE  StoreId = @storeId

I also have three parameters for the report: start date, end date, and customer. The workflow is: select a start date, select an end date, then the above dataset is filtered to only show the customer names in the multi-select dropdown parameter that have a receipt date within the start and end dates. The problem is, when customer has multiple receipts within the date range, the customer shows up more than once in the dropdown parameter. I copied the VB code that filters out the duplicates:

Public Shared Function RemoveDuplicates(parameter As Parameter) As String()

Dim items As Object() = parameter.Value
System.Array.Sort(items)
Dim k As Integer = 0

For i As Integer = 0 To items.Length - 1
If i > 0 AndAlso items(i).Equals(items(i - 1)) Then
Continue For
End If

items(k) = items(i)
k += 1
Next
Dim unique As [String]() = New [String](k - 1) {}
System.Array.Copy(items, 0, unique, 0, k)

Return unique
End Function

which works great, except that it only shows the Customer ID in the dropdown.

How do I get the multi-select dropdown to have the CustomerName as the label and the Customer Id as the value?

This is not VBA. I am tempted to edit but held off as you have gold medal. It is VB.NET. Please amend so we can remove from VBA queue. — S Meaden
Short reply: stackoverflow.com/questions/27943204/… Longer reply: the code came from a source that said it was VB for applications. I took them at their word, but I'm going to assume you know what you're talking about. — bynary

Harry Harry · Accepted Answer · 2018-05-15T03:05:47

You can get around this by doing a cascading parameters.

I don't see where the start_date and end_date parameters are being used..

You can create another dataset, lets call it customers.

Your customer dateset query will be:

SELECT DISTINCT
 c.Id
, c.LastName + ', ' + c.FirstName AS CustomerName

FROM     tblCustomer c
JOIN Receipt r ON r.CustomerId = c.Id
WHERE  StoreId = @storeId

--  and Receipt.somedate between @start_date and @end_date

Set your customer parameter to source it's data from this query. You will only ever have customers from the above select..

Go to the customer parameter.. available value -> customer data set Set the values in there - value field will be ID and Label field will be name

Of course, your main dataset need to have @customerID filter along with @start_date and @end_date

How can I get both values from a multi-dimensional array in a multi-select ssrs report parameter?

1 Answers