Custom Code in Reporting Services Report

Question

In Reporting Services, I would like to add a parameter that contains data from a custom block of code. Ideally, I could 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 box" of the parameter:

 =Code.GetPeriods() 

However, when I run the report, the parameter that I apply is disabled and empty. Is there any other method that should be used? Or am I doing something wrong?

Answer 1

If you are using SQL 2008 Reporting Services, you can see this page that introduces the concept of using custom assemblies.

If you are using SQL 2005 Reporting Services, then this link is the one you want.

This is basically a trivial thing, just compile your code into a class library and follow the instructions provided to have your report reference it.

Answer 2

You are returning an array element (an array of strings) to a text field. Instead, try returning a simple string. That should work. If you still want to return a list of arrays, you should basically bind it to the list control in your RDL. You can definitely do this with dataset extensions. However, I'm not sure if there is another easy way. Check the properties of the list control and see if it allows you to directly bind to a list of arrays.

Answer 3

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

Answer 4

I checked your code. The only thing wrong is that your function returns String() . When I changed your method signature to return Array , it worked fine in my report.

Change the signature to Function GetPeriods() As Array

Answer 5

To access your members / functions implemented in the custom SSRS report code, you must set the "Public" access modifier:

 Public Function GetPeriods() As String ... 

see Writing custom code in SQL Server Reporting Services

Answer 6

I am trying to do the same, establish a simple list of parameter values ​​from the report code. None of the links in any of these answers shows how to do this, and after quite a bit of digging, I don't think this is possible. Yes, you can get values ​​from a database query, from a web service, or from a custom assembly, but each of them creates a lot of overhead compared to getting a list from a simple function call, for example = Code.GetValues ​​(), where the function uses a For loop to create values.

msvcyc is right to indicate that the parameter expects a string value, but the function returns an array. I changed the return type to Array, as suggested by prashant sable, but the select list is still inactive, it does not work. And the common cold is right in saying that the access modifier must be publicly available.

In my digging, I found an article by James Kovacs from 2005, which indicated why this is not possible. The Parameters class has a get method, but no method is set. In VS 2008 Object Explorer for SSRS 2008, the name of the object has changed, but it still does not contain the installed method (see Microsoft.ReportingServices.Interfaces.IParameter.Name or .Value).

My current workaround is to simply copy the list of values, but if your list of values ​​needs to be dynamic, then your only options could be database queries, web services, or custom assemblies. I think the easiest workaround for these three is to get the values ​​from the database engine, as suggested by oleksiy.t, if you can write a query to return the list of values ​​you need. Your list of integers or my list of time intervals will be easy queries to write. Otherwise, you will need to use one of two workarounds.

Answer 7

All that I saw requires that the parameters and their corresponding settings be part of the RDL.

At the same time, if you are going to “hard code” the values, you can create a data set only for the report, possibly in XML format, or if it needs to be programmatically controlled, do it in the web service.