Free SQL Help for Devs, DBAs, and the Curious

The SQL Server Reporting Services (SSRS) is a flexible and robust platform that allows developers to create comprehensive, data-driven solutions. Advanced visualization features in SSRS allow you to create reports that are both informative and visually appealing. One of the less explored areas of SSRS is the use of custom code. This post aims to shed some light on how you can leverage custom code to create advanced visuals in SSRS using SQL Server 2016 and SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL.

SSRS supports the use of custom code, which can be written in either VB.NET or C#. This allows developers to create functions that can be referenced in expressions or directly from report items. Let's consider a scenario where we have a database table storing sales data and we want to highlight the sales figures exceeding a certain threshold. Here's how you could accomplish this using custom code and T-SQL:

SELECT SalesPerson, SalesAmount 
FROM Sales 
WHERE SalesAmount > @Threshold

In SSRS, you would then use the custom code to format the sales figures. The following example shows how you can define a function in the "Code" section of the report properties:

Public Function HighlightSales(amount As Decimal, threshold As Decimal) As String 
 If amount > threshold Then 
 Return "Red" 
 Else 
 Return "Black" 
 End If 
End Function

This function returns "Red" if the sales amount exceeds the threshold, otherwise it returns "Black". You can then use this function in an expression to change the color of the sales figures based on their values.

This approach is not only limited to SQL Server databases. You can also use it with other databases like MySQL and DB2. For instance, if you have a MySQL database, you can retrieve the data using a similar SQL query:

SELECT SalesPerson, SalesAmount 
FROM Sales 
WHERE SalesAmount > ?;

DB2 users can also leverage this technique. Here's how you can retrieve sales data exceeding a certain threshold in DB2:

SELECT SalesPerson, SalesAmount 
FROM Sales 
WHERE SalesAmount > ?;

If you're using Azure SQL, you can still take advantage of this functionality. You just need to make sure that your Azure SQL database is accessible from SSRS.

In conclusion, using custom code in SSRS can open up a world of possibilities for advanced visuals. It allows you to provide a personalized touch to your reports, making them not only informative but also visually appealing. Whether you're using SQL Server, MySQL, DB2, or Azure SQL, you can leverage the power of custom code to take your SSRS reports to the next level.