Haris Khan Haris Khan - 5 months ago 43
Vb.net Question

SSRS Error in custom code: expression expected

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

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!

.NET Version 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!