Using embedded VB code in a SQL report

Sometimes, you will find that you can't do what you need in T-SQL and you need to do some high level programming at report level. One way to acheive this is by using embedded Visual Basic.net code in the BIDS or Vsiual Studio design layer.

Most of the examples you'll find on the net will assume you can already program in VB.net and will simply show you how to create a vb function and incorporate it into your report. Well I'll attempt to show you both.

First of all, the basics - how to get to the code editor and write a function. In Visual Studio or BIDS (the included Visual Studio interface which comes with SQL Server), select Report from the top menu, and click on Report Properties. In there click on the Code tab and you'll see a VB Editor. There's no wizards or anything like that to help you out here so you'll need to code it by hand from scratch.

For starters, we'll create a basic function so I can show you how it works. In your code editor type the following to create a programming classic, the 'Hello World' app:

Public Function HelloWorld() as String
Return "Hello World"
End Function

Now in your report, create a new textbox, right click and select Expression. In the expression box type '=Code.HelloWorld()".

Preview your report and you can see how easy it is to get started.

Now we'll up our game and do something more complicated and more useful.

The problem I faced today was having a table in which one of the metrics was a field showing a number of seconds. I wanted to display this in words. For example, instead of showing 12494 seconds, I needed it to display as '3 hour(s), 28 minute(s) & 14 second(s)'. Its much more readable and meaningful for the end user.

As I was displaying this in a table, for the main details of the report I could do this calulation in T-SQL and in fact I created a functionto do this in T-SQL but when displaying the data in a table, I created a totals column. I can't then revert back to my SQL code to get the total. I tried using WITH ROLLUP but as I was working with averages, this didn't really work as I wanted so this left me with the problem of having to do the calculation in the report. To this end I created an embedded custom function in Visual Studio and the code is below.

Public Function SecondsToWords (ByVal Seconds as Integer) as String

Dim Days as Integer
Dim Hours as Integer
Dim Minutes as Integer
Dim Words as String

Words=""

IF Seconds < 60 Then
Words = System.Convert.ToString(Seconds) + " seconds"

ElseIf Seconds >= 60 AND Seconds < (3600) Then
Minutes = Seconds / 60
Seconds = Seconds mod 60
Words = System.Convert.ToString(Minutes) + " minute(s), " + System.Convert.ToString(Seconds) + " seconds"

ElseIf Seconds >= 3600 AND Seconds <= 86399 Then
Hours = Seconds / 3600
Seconds = Seconds mod 3600
Minutes = Seconds / 60
Seconds = Seconds mod 60
Words = System.Convert.ToString(Hours) + " hour(s), " + System.Convert.ToString(Minutes) + " minute(s), " + System.Convert.ToString(Seconds) + " seconds"

ElseIf Seconds >= 86400 Then
Days = Seconds / 86400
Seconds = Seconds mod 86400
Hours = Seconds / 3600
Seconds = Seconds mod 3600
Minutes = Seconds / 60
Seconds = Seconds mod 60
Words = System.Convert.ToString(Days) + " day(s), " + System.Convert.ToString(Hours) + " hour(s), " + System.Convert.ToString(Minutes) + " minute(s), " + System.Convert.ToString(Seconds) + " seconds"

End If

Return Words

End Function

If you take the time to compare this to my T-SQL equivalent, you'll see that its almost the same. The only real differences are syntactical so if you can code in SQL, you can do this level of VB function.

The main differences are:
1. Declaration of variables. In VB, use Dim instead of DECLARE
2. Use System.Convert.ToString(fieldname) instead of CAST or CONVERT
3. Use double speech marks instead of single

That's pretty much it. I'm not going to go into more detail because if I can work it out then so can you. Its really not that hard although it may seem scary at first.

T-SQL function to convert seconds to words

Here's something I wrote to convert seconds as an integer into a usefully descriptive text format e.g. 2 hours, 3 minutes, 12 seconds.

There are various versions of this sort of thing on the web but until today I couldn't find this thing exactly so I decided to write it myself.

CREATE FUNCTION fn_SecondsToWords (
@Seconds int)

RETURNS varchar(500)
AS
BEGIN

DECLARE @Words varchar(500)

SET @Words = ''
DECLARE @Minutes int
DECLARE @Hours int
DECLARE @Days int

-- less than a minute
IF @Seconds < 60
BEGIN
SET @Words = CAST(@Seconds AS VARCHAR(2)) + ' seconds'
END

-- between a minute and an hour
IF @Seconds BETWEEN 60 AND ((60*60)-1)
BEGIN
SET @Minutes = @Seconds / 60
SET @Seconds = @Seconds % 60
SET @Words = CAST(@Minutes AS VARCHAR(2)) + ' minute(s), '
+ CAST(@Seconds AS VARCHAR(2)) + ' seconds'
END

-- between an hour and a day
IF @Seconds BETWEEN 3600 AND 86399
BEGIN
SET @Hours = @Seconds / 3600
SET @Seconds = @Seconds % 3600
SET @Minutes = @Seconds / 60
SET @Seconds = @Seconds % 60
SET @Words = CAST(@Hours AS VARCHAR(2)) + ' hour(s), '
+ CAST(@Minutes AS VARCHAR(2)) + ' minute(s), '
+ CAST(@Seconds AS VARCHAR(2)) + ' seconds'
END

-- over a day
IF @Seconds >= 86400
BEGIN
SET @Days = @Seconds / 86400
SET @Seconds = @Seconds % 86400
SET @Hours = @Seconds / 3600
SET @Seconds = @Seconds % 3600
SET @Minutes = @Seconds / 60
SET @Seconds = @Seconds % 60
SET @Words = CAST(@Days AS VARCHAR(3)) + ' day(s), '
+ CAST(@Hours AS VARCHAR(2)) + ' hour(s), '
+ CAST(@Minutes AS VARCHAR(2)) + ' minute(s), '
+ CAST(@Seconds AS VARCHAR(2)) + ' seconds'
END

RETURN @Words

END

If you'd like some sort of an explanation as to how it works then here goes:

First job is to determine of the number of seconds is less than 60 because that's easy. Just show the number of seconds and concatenate the word ' second(s)' onto the end.

Next is to deal with anything between a minute and an hour. in terms of seconds that's 60 to 3600 except we're going to use 3599 so we're just dealing with seconds and minutes. The line that says SET @Minutes = @Seconds / 60 is calculating the number of minutes simply by dividing the number of seconds by 60.
The code SET @Seconds = @Seconds % 60 uses the MOD(%) function which is a funny by extremely helpful fella. MOD will return the remainder when you divide a number by another number.
For example, if we were dealing with 95 seconds, the line SET @Seconds = @Seconds % 60 divides 95 by 60 and returns the remainder which is of course 35.
So we've got the result of 95 DIVIDED BY 60 (1) and 95 MOD 60 (35) and that outputs 1 minute and 35 seconds (95 seconds).

The rest of the function simply expands on this premise to calculate hours and even days. You now have the tools to add weeks and years onto it or to convert it so that its converting minutes or miliseconds or whatever you want.