SSRS distinct lookupset函数

Question

我正在使用Join(Lookupset)函数来查找唯一的组值，该函数返回一个序列号。以下是我的函数：

Join(LookupSet(Fields!itemId.Value & Fields!UseByDate.Value & Fields!rackId.Value
    , Fields!itemId.Value & Fields!UseByDate.Value & Fields!rackId.Value
    , Fields!CustomerSeqNo.Value
    , "PickingList"), ",")

问题在于某些项目上存在多个交易。我想要去除重复项。

我找到了一个博客http://blogs.msdn.com/b/bobmeyers/archive/2012/06/18/creating-short-lists-using-the-lookupset-function.aspx，但无法让 SSRS 报告生成器引用 Linq 组件。我的问题是 如何只显示唯一的值？

- jhowe

Answer 1

你不需要使用Linq，但仍然需要自定义代码（在BIDS中转到报告 -> 报告属性 -> 代码）

您可以在此处放置一个RemoveDuplicates函数，类似于以下内容:

Public Shared Function RemoveDuplicates(m_Array As Object()) As String()

    System.Array.Sort(m_Array)
    Dim k As Integer = 0
    For i As Integer = 0 To m_Array.Length - 1
        If i > 0 AndAlso m_Array(i).Equals(m_Array(i - 1)) Then
            Continue For
        End If
        m_Array(k) = m_Array(i)
        k += 1
    Next

    Dim unique As [String]() = New [String](k - 1) {}

    System.Array.Copy(m_Array, 0, unique, 0, k)

    Return unique

End Function

要在您的Join中使用它：

Join(Code.RemoveDuplicates(LookupSet(...)),",")

- Paul The Muzz

---

谢谢@user3697615，它在我的情况下起作用并解决了一个大问题。 - Builder

Answer 2

我同意@user3697615的看法，Report Code是最好的选择。不过，我更喜欢直接将其构建为一个字符串： "最初的回答"

public shared function JoinDistinct(
  dups as object(),
  delimiter as string
) as string

  dim result as string = ""
  system.array.sort(dups)

  for i as integer = 0 to dups.length - 1
    if i <> 0 then result += delimiter
    if i = 0 orElse dups(i) <> dups(i-1) then result += dups(i)
  next i

  return result

end function

这样，我们就可以减少一个嵌套的函数调用：

=Code.JoinDistinct(LookupSet(...), ",")

- pwilcox

Answer 3

如果你和我一样，你也希望元素按照频率排序（降序）。

我创建了下面的VisualBasic代码来实现这个目标。

Public Shared Function RemoveDuplicates(dataset As Object()) As String()
    Dim unique As New System.Collections.Generic.List(Of String)
    Dim frequency As New System.Collections.Generic.List(Of Integer)
    For i As Integer = 0 To dataset.Length - 1
        Dim index As Integer = -1
        For j As Integer = 0 To unique.Count - 1
            If dataset(i).Equals(unique(j)) Then
                index = j
                Exit For
            End If
        Next
        If index < 0 Then
          unique.Add(dataset(i))
          frequency.Add(1)
        Else
            frequency(index) += 1
        End If
    Next
    Dim uniqueArray As [String]() = unique.ToArray()
    Array.Sort(frequency.ToArray(), uniqueArray)
    Array.Reverse(uniqueArray)
    return uniqueArray
End Function

这是基于他人答案的，其中SSRS表达式如下：

Join(Code.RemoveDuplicates(LookupSet(...)),",")

注意：我在大约一个小时内学会了VisualBasic来解决这个问题，所以我的算法可能不是最有效的。

- nothotscott

Answer 4

我喜欢 pwilcox 的想法，于是我写了这个过滤掉空值和空白值的代码。

Public Function JoinDistinct(arr As Object(), delimiter As String) As String

    System.Array.Sort(arr)
    Dim result As String = String.Empty
    Dim lastvalue As String = String.Empty
    For i As Integer = 0 To arr.Length - 1
        If Not arr(i) Is Nothing And arr(i) <> lastvalue And arr(i) <> String.Empty Then
            If result = String.Empty Then
                result = arr(i)
            Else
                result = result + delimiter + arr(i)
            End If
        End If
        lastvalue = arr(i)
    Next
    Return result
End Function

使用方法：

=Code.JoinDistinct(LookupSet(...), ",")

- Jordan Ryder