LINQPad for SSRS Custom Code

This post assumes that you know how to write SSRS custom code, you just don’t want to spend all of your time doing it. LINQPad Standard Edition is free and ideal for those of us who like to spend less time in BIDS creating reports. You could write all of your SSRS custom code right inside BIDS Design mode, and debug it by putting unneeded fields on the report and switching over to Preview mode to see how close you got. Rinse and repeat. That gets really old quickly for me. I’ve seen advice out there to just create throw away Visual Studio projects – again, gets really old, really quickly for me. I found LINQPad years ago, and I think it is ideally suited for situations like this, and many more!

LINQPad installation is straightforward, so I’ll leave that as an exercise for the reader. I’ll jump straight into how I use LINQPad to help write SSRS custom code faster. First things first, SSRS is all about VB, so (after opening LINQPad) change the Language to VB Program. This allows you to not only write, but test the VB Functions that you create. Now, go ahead and create a function that is challenging enough that you couldn’t do it blindfolded. For example, SQL Server isn’t the best place to do string concatenation, so maybe you have a table with a handful of individual nullable fields. And, in the report, you are supposed to concatenate and comma separate that list of fields into a single textbox on the report. Copy and paste the code below into LINQPad, make sure VB Program is selected, and press F5 (or click the green arrow if you are a mouser).

 Sub Main
     ToCommaSeparatedList("Red", "Green", "Blue").Dump
     ToCommaSeparatedList("Red", Nothing, "Blue").Dump
     ToCommaSeparatedList(Nothing, "Green", Nothing).Dump
 End Sub

 Public Function ToCommaSeparatedList(Value1 As String, Value2 As String, Value3 As String) As String
     Dim result As String = String.Empty
     Dim resultList As New System.Collections.Generic.List(Of String)

     If Not Value1 Is Nothing Then resultList.Add(Value1)
     If Not Value2 Is Nothing Then resultList.Add(Value2)
     If Not Value3 Is Nothing Then resultList.Add(Value3)
     result = String.Join(", ", resultList.ToArray())
     Return result
 End Function</pre>

Now, there are several ways to write the code to accomplish this, but that’s not the point. The point is that writing and testing custom code in SSRS is painful, and LINQPad can take some of that pain away. Give it a try, and let me know what you think.