RSS Feed (xml)
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
The above code is adopted from web site in the code snippet.
Next, you have to call this function in your table. You need to enter the following function call at the column in which you need to have your number in word.
=Code.ExpandPrice(Fields!Amount.Value,".")
Finally you will see following screen.
Display a Grid with Alternative Colours
In SSRS, there is no straightforward way to display a grid in alternative colours. (Displaying your girds in alternative colours will improve readability of your reports. In Excel 2007 you can do this very easily but not in SSRS)
By combining an expression in the background colour and the RowNumber function, you can add alternative colours to your reports. RowNumber(Nothing) returns row number of the current record. For the background property of the each text box, you need to give following expression.
=iif(RowNumber(Nothing) Mod 2, "LightBlue", "SkyBlue")
From the above function, the rows with even numbers will display in a light blue colour while odd row numbers will display in a sky blue colour as illustrated below:
Display Given Number of Records per Page
If you want to display only a set number of rows per report page, it is not an easy task as number of rows will depend on the font size and other printer defaults. Apart from this problem, if there are rows with lengthy text it tends to move to another line which will upset the number of rows in your report. So it is necessary to add some coding to get this feature to your reports. Let us say, you want to have only 20 rows per page, add a group to the report with following expression.
=int((RowNumber(Nothing)-1)/20)
Then select page break at the end option for the group.
Suppressing Drill Down for Total Columns in Matrixes
When you have a matrix in your report, you know that you have the option of displaying row and column totals like this:
If you want to have a navigation to another report if you click any details cell, you can configure the navigation tab. However, the problem with this configuration is that this configuration will allow user sto click the total column and drill through to the navigation report. You cannot suppress this by including a expression because total column will act as one of the other columns.
The following is the query we are using for the above example.
SELECT YEAR(Sales.SalesOrderHeader.OrderDate) AS Year,
Production.Product.Name AS Name,
SUM(Sales.SalesOrderDetail.LineTotal) AS Amt
FROM Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE ( Production.Product.Name LIKE 'HL R%' )
AND ( YEAR(Sales.SalesOrderHeader.OrderDate) IN ( 2001, 2002 ) )
GROUP BY Production.Product.Name,
YEAR(Sales.SalesOrderHeader.OrderDate)
ORDER BY YEAR,
Name
Please note that I have included the where so that it is easy to see the total columns.
To tackle the above issue what we can do is include total values into the query rather than getting it from the reporting services.
SELECT ISNULL(YEAR(Sales.SalesOrderHeader.OrderDate), '9999') AS YEAR,
ISNULL(Production.Product.Name, 'Total') AS Name,
SUM(Sales.SalesOrderDetail.LineTotal) AS Amt
FROM Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE ( Production.Product.Name LIKE 'HL R%' )
AND ( YEAR(Sales.SalesOrderHeader.OrderDate) IN ( 2001, 2002 ) )
GROUP BY Production.Product.Name,
YEAR(Sales.SalesOrderHeader.OrderDate)
WITH CUBE
ORDER BY YEAR,
Name
The CUBE operator will give you total values for the year, and product name. The problem with this query is we cannot have a Total label for the year and for the total, as year is an integer. Therefore, in the above query an arbitrary 9999 is included.
For the total heading, you might have to write a simple expression like, IIF(Fields!Year.Value="9999","Total",Fields!Year.Value).
This is the output you will get which is the same as above.
Next is to write an expression for the navigation.
=SWITCH(Fields!Year.Value <>9999 AND Fields!Name.Value<>"Total","SubReport")
In the above expression, it is checked for the year and name total values so that it won’t navigate to the subreport.
Final Word
Above are few that I have come across during my SSRS development. Let me know any other issues you have when it comes to developing SSRS. In addition, if you need samples for above instances, I am just happy to send them to you. Drop me a mail to dineshasanka@dbfriend.net
No comments:
Post a Comment