SSRS Max date from Lookupset

Question

I've spent a long time looking around for a solution for this and not found quite what I want. Efforts to adapt existing solutions for different problems have also not worked!

I am using LookupSet to return a list of dates, then joining them to return a list:

=Join(LookupSet(Fields!cPatSer.Value,Fields!cPatSer.Value,Fields!DDate.Value,"PatD"))

I want to only show the most recent date from that list. Here is what I have tried so far:

The above function wrapped in a Max function (doesn't work because Join returns a string)
Using a split function to split the resultant string looking for the commas then using the max function
Doing both of the above but converting the output to Date objects using CDate and DateTime.Parse first as follows...

=Join(LookupSet(Fields!cPatSer.Value,Fields!cPatSer.Value,CDate(Fields!DDate.Value),"PatD"))

=Join(LookupSet(Fields!cPatSer.Value,Fields!cPatSer.Value,DateTime.Parse(Fields!DDate.Value),"PatD"))

Can anybody provide any pointers please?

Pete Rennard-Cumming Pete Rennard-Cumming · Accepted Answer · 2016-12-14T16:32:11

I've found a solution using Custom Code. Oz Locke made a function that does various aggregates for integer data (links below), and I've amended this to work for dates instead.

In the Code property of the report, paste in this:

'Amended from Oz Locke's code:
'https://github.com/OzLocke/SSRSAggLookup/blob/master/AggLookup.vb
'Allows users to adjust the aggregation type of lookupsets in a cell
Function AggLookup(ByVal choice As String, ByVal items As Object)

'Ensure passed array is not empty
'Return a zero so you don't have to allow for Nothing
If items Is Nothing Then
    Return 0
End If

'Define names and data types for all variables
Dim current As Date
Dim count As Integer
Dim min As Date
Dim max As Date
Dim err As String

'Define values for variables where required
current = CDate("01/01/1900")
count = 0
err = ""

'Calculate and set variable values
For Each item As Object In items

    'Calculate Count
    count += 1

    'Check value is a number
    If IsDate(item) Then

        'Set current
        current = CDate(item)

        'Calculate Min
        If min = Nothing Then
            min = current
        End If
        If min > current Then
            min = current
        End If

        'Calculate the Max
        If max = Nothing Then
            max = current
        End If
        If max < current Then
            max = current
        End If

        'If value is not a number return "NaN"
    Else
        err = "NaN"
    End If

Next

'Select and set output based on user choice or parameter one
If err = "NaN" Then
    If choice = "count" Then
        Return count
    Else
        Return 0
    End If
Else
    Select Case choice
        Case "count"
            Return count
        Case "min"
            Return min
        Case "max"
            Return max
    End Select
End If
End Function

Then in the cell of your report, use this expression:

=code.AggLookup("max", LookupSet(Fields!cPatSer.Value,Fields!cPatSer.Value,Fields!DDate.Value,"PatD"))

https://itsalocke.com/aggregate-on-a-lookup-in-ssrs/ https://github.com/OzLocke/SSRSAggLookup/blob/master/AggLookup.vb

SSRS Max date from Lookupset

1 Answers