Using Visual Studio 2008 with Report Builder 3.0 - How to add a custom VB function to calculate a date 90 days before the date in another field

Question

This is my first question on Stack though I have had many answered here 8-)

My employer is using SQL Server Business Intelligence Development Studio with Visual Studio 2008 and Report Builder 3.0 to generate reports from SharePoint Lists.

They want me build a Function in VB that I can call from a Report Builder Expression that will look at a date (NeedDate) which is also displayed in the Tablix in the report, and display a date in another cell that is 90 days before that date. It must be a VB function that is called in the Expression builder - that is the requirement from my boss.

I was using this in the expression builder in Report Builder, and it was working: =iif(ReportItems!NeedDate.Value = Nothing, Nothing, DateAdd(DateInterval.Day,-90, ReportItems!NeedDate.Value)) but my boss wants me to use the function and insert it after the =iif(ReportItems!NeedDate.Value = Nothing, Nothing as "code. what needs to go here..."

I don't have a lot of experience doing this sort of thing. I don't understand how to make a VB function written in Visual Studio designer work in Report Builder by inserting the "code." and I do not know the syntax for taking the date from one report item, subtracting 90 days from that, and displaying it in another cell in a report. Thus I am reaching out to you all for advice/assistance, and any provided is most gratefully appreciated!

Answer 1

So, the answer was this:

I used this expression in the field:

=Code.SubtractDays(90, ReportItems!ActionNeedDate.Value)

And this was the function:

Function SubtractDays(ByVal days As Integer, ByVal dateField As String) As String
    If Not IsDate(dateField) Then
        Return ""
    Else
        Dim nDayCal As Date = DateAdd(DateInterval.Day, -(days), CDate(dateField))
        Return Format((nDayCal), "dd-MMM-yy")
    End If
End Function