Custom code in Reporting Services report

Question

In Reporting Services I would like to add a parameter that contains data from a custom code block. Ideally, I would be able to run the following code (this is a simple testing example):

Function GetPeriods() As String()
 Dim values As System.Collections.ArrayList = 
    New System.Collections.ArrayList()
 For i as integer = 1 to 24
    values.Add(i)
 Next
 Return values.ToArray()
End Function

and put the following in the "Text Field" of the parameter:

=Code.GetPeriods()

However, when I run the report, the parameter I apply this to is disabled and empty. Is there a different technique that should be used? Or am I doing something wrong?

Answer 1

If you're using SQL 2008 Reporting Services then you can have a look at this page which introduces the concept of using custom assemblies.

If you're using SQL 2005 Reporting Services then this link is the one you want.

It's a mostly trivial thing, simply compile your code into a class library and follow the instructions provided to allow your report to reference it.

Answer 2

You are returning an array item (an array of strings) into a text field. Instead, try returning a plain string. That should work. If you would still like to return an array list, you must basically bind it to a list control in your RDL. You can definitely do that with dataset extensions. However, I am not sure if there is any other easy way. Check the proprties of the list control and see if it allows you to directly bind to an array list.

Answer 3

You can create the same stored procedure on SQL Server and load parameter values from that procedure.

Answer 4

To access your members/functions implemented in custom code of SSRS report you should set the access modifier to "Public":

Public Function GetPeriods() As String
...

see article Writing Custom Code in SQL Server Reporting Services