SSRS Error in custom code: expression expected

Question

I am using a couple of custom functions to perform some stats in SSRS. The code was built and tested in Visual Studio 2010, and works just fine when I try and preview the report. However, when I try and deploy the report to SSRS 2008R2 I get an error:

There is an error on line 40 of custom code: [BC30201] Expression expected.

I searched high and low for a solution to this problem, but I have yet to find one. Here's the problematic code:

Public Function Avrg(c1 As Double, c2 As Double, c3 As Double, c4 As Double, c5 As Double, c6 As Double, c7 As Double, c8 As Double, NumQuarters As Integer) As Double
    Dim AV_Data As Double() =
    IIf(NumQuarters = 1, {c1, c2, c3, c4, c5},
    IIf(NumQuarters = 2, {c1, c2, c3, c4, c5, c6},
    IIf(NumQuarters = 3, {c1, c2, c3, c4, c5, c6, c7}, {c1, c2, c3, c4, c5, c6, c7, c8})))

    Dim stats = STDEV_Ave(AV_Data)

    Avrg = stats(2)
End Function

EDIT: Line 40 is apparently the function declaration (the first line presented)...though I can't find anything wrong with that.

The function takes 8 parameters from the report and based on the number of non-archived quarters of data that the report is reading (which can vary from 1-4), the array declaration is different. That array feeds into a statistical function that performs the actual stats and returns the desired number. In this example, it's a simple average.

I changed the code once, originally it was an if-elseif-else clause, but I read several places that I needed to replace that with a cascading IIF because SSRS's VB compiler is gimped.

Any help would be greatly appreciated!

Answer 1

Ok, so I figured it out, I just thought I'd post the answer for anyone who was wondering how to solve this problem.

First of all, here's my reference for this: http://www.sqlservercentral.com/blogs/dknight/2012/01/26/ssrs-custom-code-with-external-assemblies/

SSRS's ability to compile VB, particularly in 2008R2 and earlier, is incompatible with the compiler in VS 2010. (EDIT, SEE: Integrate SSRS with Visual Studio 2010) In order to get around this I created a class library of shared functions via Visual Studio and put my functions in it. Follow the instructions above, and use my notes as a supplement for the parts which may be glossed over.

Public Class ClassName
....<Other functions>
    Public Shared Function StdDevP(c1 As Double, c2 As Double, c3 As Double, c4 As Double, c5 As Double, c6 As Double, c7 As Double, c8 As Double, NumQuarters As Integer) As Double
    Dim ST_data As Double() = IIf(NumQuarters = 1, {c1, c2, c3, c4, c5}, IIf(NumQuarters = 2, {c1, c2, c3, c4, c5, c6}, IIf(NumQuarters = 3, {c1, c2, c3, c4, c5, c6, c7}, {c1, c2, c3, c4, c5, c6, c7, c8})))
    Dim stats = STDEV_Ave(ST_data)

    StdDevP = stats(1)
End Function
End Class

For SSRS 2008R2 in particular this is where we deviate slightly from the referenced instructions: Change your target framework to 3.5 in the solution properties to avoid compatibility issues. Also make sure the DLL is signed, as per instructions above!

Build the class library and grab the dll from your project's debug/bin folder (this is the default). Once you've got it, there are two things you're gonna need to do on your reporting server.

First: Copy the dll to this folder:...\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin This will be what your report references.

Second: use gacutil to add the dll to your C:\Windows\Assembly folder. If you have the full version VS installed on the server, you can use the Visual Studio Command Prompt for this.

gacutil /i "ClassName.dll"

If you don't have VS installed (or just have BIDS), you'll either need to use an elevated command prompt and CD to the folder where gacutil.exe is, OR if you don't have gacutil at all, you'll need to download the Microsoft SDK for your operating system, which will include gacutil. I have Windows Server 2008 R2 so I'm going to use the one for .NET framework 4 and Windows 7. Use whichever version is appropriate for your server. You'll find gacutil here after you install (v number changes based on what SDK you have):

C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin

In your SSRS report, add the assembly (the one in your ReportServer folder) to your Report Properties in BIDS on your server.

Now, in order to reference your code in an expression, you would use the Class Library and Class name to get to your functions. For example:

=ClassLibrary.ClassName.StdDevP(Fields!Column_1.Value,...)

Your report should now deploy and function just the same as if you had put your functions in custom code instead!

Answer 2

One thing to note: when I've had this happen, I usually can fix it with whitespace changes. Like if you have a Switch statement broken out into multiple lines for clarity, that works fine in preview but blows up when deployed. Putting the entire thing on one line will usually fix it.

This makes it more of a pain to modify later, but is a bit less drastic than having a custom assembly if you don't have too much code involved.