Tweaks in SQL Server Reporting Services

SQL Server Reporting Services (SSRS) is very valuable tool for developers and users to design and view reports. However, there are few features which are not available directly in SSRS. These features are frequently required features for developers and users.

Display Server Name or IP Address in a Report
Often users will be mislead with report data when it is running on several reporting severs. You may have a test and a production environment and you do not know from which server your report is taken from.
Obvious one way you can tackle this issue is by displaying the report server name or its IP address in the report. Unfortunately, there is no direct function or a method to retrieve the server name. However there is a workaround.

In Reporting Services, there is a global parameter called ReportServerUrl which will display the report URL. The initial part of this value is taken from the RSReportServer.config file in the C:\Program Files\Microsoft SQL Server\MSSQL.#\Reporting Services\ReportServer folder. In this file, there is node called URLRoot. It can be either http://{IPAddress}/reportserver or http://{Server Name}/reportserver. You need to find a way to extract the IP Address or ServerName, which you can do by using the following expression:

Mid(Globals!ReportServerUrl,8,InStr(9,Globals!ReportServerUrl,"/") - 8)

What this expression does is, it will extract the name from the next character position of // to the next character position of /, which will be either the server name or the IP address of the server.

Convert Numeric Values into Words
This is very common question that you can find in SSRS forums as many users needs to display numeric data in words. Although there is a function in Crystal Reports, there is no function in SSRS. Nevertheless, you can do this in SSRS by writing your own function.

Lets us do this by an example using the following query:

SELECT Sales.SalesPerson.SalesPersonID AS SalesPerson,
SUM(Sales.SalesOrderDetail.OrderQty * Sales.SalesOrderDetail.UnitPrice) AS Amount
FROM Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
GROUP BY Sales.SalesPerson.SalesPersonID

For the sake of the example, let us assume that we need to display sales person id along with the amount and amount in words.

Follow the below steps to create a report.

1. Create a SSRS project.
2. Add new report the project.
3. Add a data source in which database is pointed to adventureworks.
4. Create a data set with the above query.
5. Drag and drop a table to layout from the toolbox and drag and drop the fields to the table.

The next step is to create a function to convert numeric values into words.

Go to the Report menu option and select report properties. Select the Code tab in that dialog box and copy and paste the following code:

' Source
' http://cc.msnscache.com/cache.aspx?q=72465960679242&mkt=en-US&lang=en-US&w=577f5001&FORM=CVRE8

SHARED suffixes AS String() = _
{"Thousand ", "Million ", "Billion ", "Trillion ", _
"Quadrillion ", "Quintillion ", "Sextillion "}

SHARED units AS String() = _
{"","One ", "Two ", "Three ", "Four ", "Five ", _
"Six ", "Seven ", "Eight ", "Nine "}

SHARED tens AS String() = _
{"Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", _
"Seventy ", "Eighty ", "Ninety "}

SHARED digits AS String() = _
{"Ten ","Eleven ", "Twelve ", "Thirteen ", "Fourteen ", _
"Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen"}

SHARED expr AS NEW _
System.Text.RegularExpressions.Regex("^-?\d+(\.\d{2})?$", _
System.Text.RegularExpressions.RegexOptions.None)

PUBLIC Function ExpandPrice(Price AS Double, _
Optional pSeparator AS String = ".") _
AS String
Dim pPrice As String
pPrice = FORMAT(Price,"##############.00")

Dim temp AS New System.Text.StringBuilder()
If Not expr.IsMatch(pPrice) Then
' temp.Append(pPrice) or whatever you want to do here
Else
Dim parts AS String() = pPrice.Split(pSeparator)
Dim dollars AS String = parts(0)
Dim cents AS String = parts(1)
If CDbl(dollars) > 1 Then
temp.Append(ExpandIntegerNumber(dollars) & "Dollars ")
If CInt(cents) > 0 Then
temp.Append("And ")
End If
ElseIf CDbl(dollars) = 0 Then
temp.Append(ExpandIntegerNumber(dollars) & "Zero Dollars ")
If CInt(cents) >= 0 Then
temp.Append("And ")
End If
ElseIf CDbl(dollars) = 1 Then
temp.Append(ExpandIntegerNumber(dollars) & "Dollar " )
End If

If CDbl(cents) > 1 Then
temp.Append(ExpandIntegerNumber(cents) & "Cents")
ElseIf CDbl(cents) = 0 Then
temp.Append(ExpandIntegerNumber(cents) & "Zero Cents ")
ElseIf CDbl(cents) = 1 Then
temp.Append(ExpandIntegerNumber(cents) & "Cent " )
End If
End If
RETURN temp.ToString()
End Function

Function ExpandIntegerNumber(pNumberStr AS String) AS String
Dim temp2 AS New System.Text.StringBuilder()
Dim number AS String = _
StrDup(3 - Len(pNumberStr) Mod 3, "0") & pNumberStr
Dim i AS Integer, j AS Integer = -1
Dim numPart AS String
For i = Len(number) - 2 To 1 Step -3
numPart = Mid(number, i, 3)
If Clng(numPart > 0) Then
If j > -1 Then
temp2.Insert(0,suffixes(j),1)
End If
End If
temp2.Insert(0,GetNumberUnder1000Str(numPart),1)
j += 1
Next
RETURN temp2.ToString()
End Function

Function GetNumberUnder1000Str(pNumber AS String) AS String
Dim temp1 AS New System.Text.StringBuilder()
If Len(pNumber) = 3 Then
If CLng(Left(pNumber, 1)) > 0 Then
temp1.Append(GetNumberUnder100Str(Left(pNumber, 1)) & "Hundred ")
End If
End If
temp1.Append(GetNumberUnder100Str(Right("0" & pNumber, 2)))
RETURN temp1.ToString()
End Function

Function GetNumberUnder100Str(pNumber AS String) AS String
If pNumber > 19 Then
RETURN tens(Left(pNumber, 1) - 2) & units(Right(pNumber, 1))
ElseIF pNumber >= 10 and pNumber <= 19 Then
RETURN digits(Right(pNumber, 1))
Else
RETURN units(Right(pNumber, 1))
End If
End Function