Some common minor problems in SQL Server Report services

SQL Server Report Service (SSRS) is a useful tool for designing and browsing reports for developers and users. However, there are some features that are not provided as directly available in SSRS. These features are often needed by developers and users.

Display the server name or IP address in a report

When the report data is running on several report servers, users often get it wrong. You may have a test and development environment, but you don't know which server your report data comes from.

Obviously, one way to solve this problem is to display the report server name or its IP address in the report. Unfortunately, there is no direct function or method to get the server name. But there is this working environment.

In the report service, there is a global parameter called ReportServerUrl, which displays the report URL. The starting part of this value is from the C:\Program Files\Microsoft SQL server\mssql.#\reporting Services\ReportServer folder. RSReportServer.config files. In this file, there are nodes called UrlRoot. It is http://{ipaddress}/reportserver or http://{server name}/reportserver. You need to find a way to extract this IP address or server name, and you can use the following expression:

Mid (globals! Reportserverurl,8,instr (9,globals! ReportServerUrl, "/")-8)

What this expression does is to extract the name of the letter to/from the letter after/position, which is the server's server name or IP address.

Convert a numeric type to text

This is an issue that is often seen on the SSRS forum because many users need to display numeric types of data as text. Although there is a function in the Crystal Report, there is no function in the SSRS. However, you can write a function yourself in SSRS.

Let's use the following query to give an example:

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

To use this example, let's assume that we need to display the ID and quantity of the salesperson, and the quantity should be shown in text form.