Trying to get Reporting Services to understand my code

Question

I'm trying to encapsulate some expressions that I need quite frequently in my reports into report functions, so that I could write the code once and use it everywhere in my report. But I'm really a Pascal / C# programmer and struggle with the VB(A) syntax.....

But even this tiny date formatting function somehow doesn't work...

Public Function DateFmt (ByVal dt As DateTime) As String
    Return IIF(IsNothing(dt), "", FormatDateTime(dt, 2))
End Function

Basically, if a DateTime field is NULL in the database, I want to show an empty string (empty cell), otherwise use the "short date" formatting option.

On a cell, I'm trying to add this as the cell expression, but when I start typing the expression like this:

=Code.

the intellisense kicks in, but my function isn't shown in that list..... hmm.... what am I missing?

Update: I noticed that while intellisense doesn't work, I can still put the expression =Code.DateFmt(.....) into the cell - but alas, it doesn't work.

While adding the expression directly nicely suppresses NULL dates in display, using this shared function causes NULL dates to show up as 01.01.0001 again in my report. WHY?

Also: I need to have a function that returns a value depending on a list of choices - a SWITCH statement basically. Is this code here correct?

Public Function GetGrp(ByVal grp As String)
    Select Case grp
      Case "Region"
          Return fields!RegionId.Value
      Case "Somedate"
          Return YEAR(Fields!Somedate.Value)
    End Select
End Function

This function actually causes compilation to fail, with this error message:

Error in line 7 of user-defined code: [BC30469] Reference to a non-public member requires an object instance

~~?!?!?!?!? What exactly is SSRS trying to tell me here?~~

Update #2:
OK, figured this one out - the Fields collection isn't available in custom code (no clear why not - anyway....).

So I needed to change this function to :

Public Function GetGrp(ByVal grp As String, ByVal fields As Fields)

and now that part works

about the null dates showing up as 01.01.0001: it could be that IsNothing() is not the right check. Try using IsNull() instead. — Cristian Lupascu
@w0lf: but why does it work when I type in this expression directly - but not when I encapsulate this into a function? — marc_s

Cristian Lupascu Cristian Lupascu · Accepted Answer · 2013-02-01T08:22:37

The VBA way of returning a value from a function is to assign the value to that function name; I don't think there's a return keyword.

That's possibly also the reason why the functions don't show up in the intellisense list.

Try to define the functions like this:

Public Function DateFmt (ByVal dt As DateTime) As String
    DateFmt = IIF(IsNothing(dt), "", FormatDateTime(dt, 2))
End Function

Public Function GetGrp(ByVal grp As String)
    Select Case grp
      Case "Region"
          GetGrp = fields!RegionId.Value
      Case "Somedate"
          GetGrp = YEAR(Fields!Somedate.Value)
    End Select
End Function

Update:

Also, the VBA code in SSRS is more like a collection of static functions, meaning that they cannot access report state (such as parameters/fields).

I would try passing the two fields!* values as byval parameters to the GetGrp function.

Note: I don't have SSRS or Office to test this code, so please let me know if something fails.

Trying to get Reporting Services to understand my code

2 Answers

Update: