SSRS – Custom Code with External Assemblies

In a previous post I wrote about how you can use embedded custom code to extend the capabilities of Reporting Services.  This week I will show you another method of using custom code but this time with external assemblies.  Ideally when using custom code you would choose to do so using external assemblies.  External assemblies help developers manage code from outside Reporting Services and share the exact same code across multiple report.  Here’s a few of the pros and cons of using external assemblies for custom code.

Pros

  • Can be any .Net language
  • Updates to code can be managed from outside of SSRS
  • Best way to standardize custom code

Cons

  • Deployment is a bit tedious
  • Assemblies will have restricted access to system resource

In this example I’m going to walk you through beginning to end of an example of how to create an external assembly then use it in Reporting Services.  Our goal is to compartmentalize our code into and assembly so every developer does an age calculation the same way.  Calculating age can easily be done in the expression language the Reporting Services provides but using the assembly assures It’s done the exact same way every time.  These steps will walk you through:

  1. Creating an assembly with VB.Net (could be done with C# as well)
  2. Signing the assembly with a strong name.  This must be done because an assembly is deployed to the Global Assembly Cache (GAC)
  3. Provide the proper permissions to the assembly so Reporting services can use it with the <Assembly: AllowPartiallyTrustedCallers()> declaration
  4. Deploying the assembly to Reporting Services and the GAC
  5. Using the Assembly in Reporting Services.

Creating an External Assembly

  • Open Visual Studio and create a new Visual Basic Class Library project called AgeAssembly. This project can be stored in any location you would like.
  • Ensure the project is set to .NET Framework 2.0 then click OK.  Currently only .NET Framework 2.0 is supported for Reporting Services assemblies.

image

  • Rename the Class1.vb file to Age.VB and use the following code:

Public Class Age

Public Shared Function CalculateAge(ByVal BirthDate As Date) As Integer

Return DateDiff("yyyy", BirthDate, DateTime.Now())

End Function

End Class

image

  • Next setup a strong name to sign or version the assembly.  This must be done before the assembly can be deployed to the GAC.  To setup a strong name right-click on the Project in the Solution Explorer and select Properties.
  • Select the Signing page and check Sign the assembly.
  • Select <New…> from the Choose a strong name key file dropdown box.
  • Type SNsnk for the Key file name and uncheck Protect my key file with a password before hitting OK. You can close the properties after this is complete.

image

  • To allow the Reporting Services engine to call this code, you must apply a new assembly attribute. In the Solution Explorer click the Show All Files button to expose the My Project folder.

image

  • Inside the My Project folder find and open the AssemblyInfo.vb file.
  • Add the namespace to the top of the code called Imports System.Security
  • Add the assembly attribute <Assembly: AllowPartiallyTrustedCallers()>
  • This will allow Reporting Services to have access to the assembly. The last step it so build the project. Right-click on the project in the Solution Explorer and click Build.

image

  • Navigate to the folder that contains the project you just created. Once you find the project open Debug folder (…AgeAssembly\bin\Debug).
  • Copy the AgeAssembly.dll to the “C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin” folder then drag and drop the assembly to the C:\Windows\assembly too.
  • Open BIDS and create a new Reporting Services project.
  • Create a new Shared Data Source that points to AdventureWorksDW2008R2 on your local SQL Server database instance and name the data source AdventureWorksDW2008R2
  • Create a basic new report that uses the DimEmployee table which has birthdate and a hire date in a tablix.  We can use our assembly to calculate how old someone is and how long they’ve been working for us.  I’m not focusing on the exact report design for this example so go wild with however you would like this to look! 
  • After creating the basics of a report go to the Report menu in the toolbar and select Report Properties. Go to the References page and click Add to add a new assembly.
  • Click the ellipsis then click the browse tab to add the assembly we just created. Browse to the C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin folder and select AgeAssembly then click OK. Click OK again once you return to the Report Properties window.  Even though we’ve selected the assembly from the Reporting Services folder in actuality the dll deployed to the GAC is what is used. 

image

  • Change the HireDate field in your report tablix to an expression using the following code: =AgeAssembly.Age.CalculateAge(Fields!HireDate.Value) Then click OK.
  • Rename the column header above the expression to Service Years.
  • Change the BirthDate field to an expression using the following code: =AgeAssembly.Age.CalculateAge(Fields!BirthDate.Value) Then click OK.
  • Rename the column header above the expression to Age.
  • Preview the report to see the result of adding the custom assembly. Results may vary from my screen shot depending on when you run the report because they may have gotten older!

image

I hoped this step by step helps.  If this topic is something that interest you then you may also be interested in a SSRS Master (Advanced) Class I’m teaching.  You can find when the next one is here.

Session Code – Extending Reporting Services with Custom Code

This is my wish list of things I’d like to cover in todays session at 11 AM EST https://www1.gotomeeting.com/register/935071481 but seeing that I know I’ll only get to a third of these thought I would post these for everyone.  If you’re seeing this late the sessions are recorded and can be viewed on www.pragmaticworks.com/Resources/webinars.

Next week look for a while paper I’ll be releasing on the same topic but of course with more details and screenshots.  This post is purely to support the webinar Smile.

Embedded Code Examples

1.  Change Background Color based on Data

Public Shared Function SetColor(ByVal Value As Integer) As String

SetColor = “Green”

If Value < 500 Then
SetColor = “Maroon”

ElseIf Value < 1000 Then
SetColor = “Yellow”

ElseIf Value < 2000 Then
SetColor = “Orange”
End If

End Function

Using Code in SSRS Expression

=Code.SetColor(Fields!OrderQuantity.Value)

2.  UPPERCASE all Text FUNCTION

Function UpperData(ByVal cField As String) As String
Return cField.ToUpper()
End Function

Using Code in SSRS Expression

=Code.UpperData(Fields!CompanyName.Value)

3.  Alternating Row color FUNCTION (From Reporting Services Recipe book)

Private bOddRow As Boolean
Function AlternateColor(ByVal OddColor As String, _
ByVal EvenColor As String, ByVal Toggle As Boolean) As String
If Toggle Then bOddRow = Not bOddRow
If bOddRow Then
Return OddColor
Else
Return EvenColor
End If
End Function

Using Code in SSRS Expression

=Code.AlternateColor(“AliceBlue”, “White”, True)

4.  Comma Separated Parameter Value FUNCTION

Public Function ShowParmValues(ByVal parm as Parameter) as string
Dim s as String

For i as integer = 0 to parm.Count-1
s &= CStr(parm.value(i)) & IIF( i < parm.Count-1, “, “,””)
Next
Return s
End Function

Using Code in SSRS Expression

=Code.ShowParmValues(Parameters!Department)

5.  Standardizing Phone Numbers (From Reporting Services Recipe book)

Function PhoneFormat(PhoneNumber As String) As String
Select Case PhoneNumber.Length
Case 7
Return PhoneNumber.Substring(0,3) & “-” & PhoneNumber.Substring(3,4)
Case 10
Return “(” & PhoneNumber.Substring(0,3) &”)” & PhoneNumber.Substring(3,3)&”-” & PhoneNumber.Substring(6,4)
Case 12
Return “(” & PhoneNumber.Substring(0,3) &”)” & PhoneNumber.Substring(4,3)&”-” & PhoneNumber.Substring(8,4)
Case Else
Return PhoneNumber
End Select
End Function

Using Code in SSRS Expression

=Code.PhoneFormat(Fields!Phone.Value)

6. Calculating Age

Public Shared Function CalculateAge(ByVal BirthDate As Date) As Integer
Return DateDiff(“yyyy”, BirthDate, DateTime.Now())
End Function

Using Code in SSRS Expression

=Code.CalculateAge(Fields!BirthDate.Value)

External Assembly Example

1. Standardizing Phone Numbers

Public Class ReportFormat
Public Shared Function PhoneFormat(ByVal PhoneNumber As String) As String
Select Case PhoneNumber.Length
Case 7
Return PhoneNumber.Substring(0, 3) & “-” & PhoneNumber.Substring(3, 4)
Case 10
Return “(” & PhoneNumber.Substring(0, 3) & “)” & PhoneNumber.Substring(3, 3) & “-” & PhoneNumber.Substring(6, 4)
Case 12
Return “(” & PhoneNumber.Substring(0, 3) & “)” & PhoneNumber.Substring(4, 3) & “-” & PhoneNumber.Substring(8, 4)
Case Else
Return PhoneNumber
End Select
End Function
End Class

Steps to create assembly

  • Use above code in Class file
  • Set Strong Name -Project Properties – Signing – Strong Name
  • Set Security – AssemblyInfo.vb
    • Imports System.Security
    • <Assembly: AllowPartiallyTrustedCallers()>
  • Build project and put the .dll in:
    • Global Assembly Cache (GAC) C:\Windows\assembly
    • C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin
  • Place dll reference in the report
  • Use this in the report
    =ReportAssembly.ReportFormat.PhoneFormat(Fields!Phone.Value)

2. Change Background Color based on Data
Public Class ValueFormat
Public Shared Function SetColor(ByVal Value As Integer) As String

SetColor = “Green”

If Value < 500 Then
SetColor = “Maroon”

ElseIf Value < 1000 Then
SetColor = “Yellow”

ElseIf Value < 2000 Then
SetColor = “Orange”
End If

End Function
End Class

Using Code in SSRS Expression
=ColorFormat.ValueFormat.SetColor(SUM(Fields!OrderQuantity.Value))

January 2012 Speaking Events

It has been a quiet past few months while my family has adjusted to having two new additions (twins!), but I have slowly been working my way back into several speaking events.  Having said that January is a busy month of events and I thought I would share them in case you are interested in attending any of them. 

Date: 01/12/2012

Group: South East Michigan SQL Server User Group

Topic: Creating Dashboards with PerformancePoint Services

This is a user group that I will be speaking virtually for but is based in Detroit.  I’m really looking forward to this one for a couple reasons.  First, the group is run by Joe Fleming (@MuadDBA) who is a great guy.  I’ve actually spoke for the group several months ago while Brian Knight and I were in Detroit for a SSAS Workshop.  The group is also run by Rick Brewis who is by far one of the best MS Technology Specialist I’ve met.  The other reason I’m looking forward to this event is because I really love the topic.  PerformancePoint is such a fun tool and I love showing it off to people and see the “wow” in their eyes as they imagine using it themselves.

Date: 01/19/2012

Group: Pragmatic Works Webinar

Topic: Extending Reporting Services with Custom Code

This webinar is free and is a part of the webinar series that Pragmatic Works runs twice a week.  This session is designed to give you a preview of one of topics that will be discussed in the new Pragmatic Master SSRS 2008 class.  The class will be taught later in January and is basically and Advanced Reporting Services class… but hey Master is better for marketing.

Date: 01/30/2012 – 02/02/2012

Group: Pragmatic Works Training

Topic: Pragmatic Master SSRS 2008

This class is a 4 day class that is 3 hours a day.  It is designed as “next step” from the traditional SSRS 2008 class that Pragmatic Works offers.  This is considered an Advanced class so be prepared for more difficult topics than traditional Reporting Services sessions.  Topics will include .Net integration, reporting off a cube and scaling Reporting Services.

Full list of speaking events past, present and future can be found on my site: http://devinknightsql.com/speaking-events/

SSRS – Embedded Custom Code

When developing reports in Reporting Services you will often use the built-in expression language to make report data and formatting dynamic.  The expression language can do a lot to within Reporting Services but any gaps in functionality that it has can be filled with custom code. 

There are two ways to implement custom code in Reporting Services.  The first way, which I will show in the post, is with embedded code.  The other method is to create an external assembly that can be imported into a report.  I will discuss this method in a future post.

Embedded code requires that you use VB but you do not have to go through the process of creating an assembly in Visual Studio like you would with the external assembly method.  Let’s go through the process of creating a report using embedded custom code and discuss some of the other benefits and disadvantages of this approach.

Here’s a basic report that I’ve developed with a list of employees and phone numbers.

image

What I’d like to do is format all the phone numbers to look like this (###)###-####.  This could easily be accomplished with some custom code.  To add custom code in Reporting Services select the Report->Report Properties menu.

image

Go to the Code page and use the following code to apply the phone number formatting:

Function PhoneFormat(PhoneNumber As String) As String
    Select Case PhoneNumber.Length
    Case 7
        Return PhoneNumber.Substring(0,3) & "-" & PhoneNumber.Substring(3,4)
    Case 10
    Return "(" & PhoneNumber.Substring(0,3) &")" & PhoneNumber.Substring(3,3)&"-" & PhoneNumber.Substring(6,4)
    Case 12
    Return "(" & PhoneNumber.Substring(0,3) &")" & PhoneNumber.Substring(4,3)&"-" & PhoneNumber.Substring(8,4)
    Case Else
    Return PhoneNumber
    End Select
End Function

image

Click OK once this code has been entered.  This code reads in different variations of phone numbers that could be provided and converts it to the appropriate format.  We can now use this code in our report.  I can replace the current column that stores the phone number data with an expression that calls this custom code to correct that data presented in the report.  That expression would look like this:

=Code.PhoneFormat(Fields!Phone.Value)

image

When you preview this report now the data will look like this (I’ve kept the original column so you can tell the difference):

image

This is a basic example but shows how powerful custom code can be.  Now there are a few things I should point out about this embedded code example that are not ideal.  Some embedded code disadvantages are:

  • You must use Visual Basic (no C#)
  • No Intellisense in the code window like you experience in Visual Studio
  • Code errors are not visible until you actually preview the report

While embedded code is powerful you will find external assemblies have even more benefits.  My next post will walk you through the process of creating a custom assembly and using it in Reporting Services. 

A real quick plug for a new class I will be teaching this month.  Pragmatic Works will start offering in January a new Reporting Services Masters class for those of you that feel experienced with the basics of SSRS.  The Masters class will assume you know all the basics of building reports and will focus on advanced topics only.  Check out the class outline and register for the class now here:

http://pragmaticworks.com/Services/training/Course/Pragmatic-Master-SSRS-2008.aspx

Follow

Get every new post delivered to your Inbox.

Join 36 other followers