Dynamic Aging Buckets in SSRS

I was given a task of converting some aging reports to SSRS. Now as you know, you can't really do grouping in SQL based on a range. So something like that needs to be implemented in SSRS in the report writter instead. I have figured out that code for you. :)

First we need to copy this code into the report. From the report properties the first box will be for adhoc vb functions to add to the report. In general these functions should be added to a class and linked, either way this is the function you need.

Public Function GetBucket(parameter As parameter, days As Integer) As String
On Error GoTo ProcError

 Dim s As String
 Dim i As Integer
 s = ""
 
 If parameter.IsMultiValue Then
  For i = 0 To parameter.Count - 2
   If (parameter.Value(i) < days and parameter.Value(i+1) > days) Then
        GetBucket = Str(parameter.Value(i)) + " -" + Str(parameter.Value(i+1))
        Exit Function
    End If
  Next
 End If

 GetBucket = parameter.Value(parameter.Count - 1)  + "+"
 Exit Function

ProcError:
GetBucket = Err.Description
End Function

After that, we will define a calculate field in our dataset.

=Code.GetBucket(Parameters!Bucket,DateDiff("d",Fields!DateToAgeOn.Value,Today()))

Then set up a parameter, type integer, and allow mulitple values. Then we can just set some default values for our ranges.