Over the years I have created a number of SQL Server Reporting Services reports. I remember when SSRS first came out, I believe somewhere around 2002, I was thinking that this will be a great product to work with. And indeed, it has turned out to be a great tool when used in the right situation.
From time to time I run a across something I have never done or seen before. That is the case with a project I am currently working on. This project is just a series of reports that will make use of the “Go to Report” functionality. In this case, a set of criteria will be evaluated to determine if a jump to another report or simply do nothing.
The criteria was based on the version of SQL Server and whether or not the connection is to an Azure SQL DB. The requirement was that if the criteria is met, allow for navigation to another report. On the other hand, if the criteria is not met do nothing. In addition to doing nothing, another requirement is to not have the cursor change when hovering over the link, in this case it is actually a text box. While this post is working with a text box on a report, the same process can be applied to text that will be used to jump to another report.
If you look at the bit of code below, you will see the criteria. You can use the code below to determine the version.
SELECT SERVERPROPERTY(‘ProductMajorversion’) AS ‘ProductMajorVersion’
This will return a number that will represent the major version of SQL Server. For example, 10 will be returned for SQL Server 2008 and 2008 r2. While running on the same code on a SQL Server 2019, you get 15 returned.
The requirement is if the SQL Server is 2012 or newer AND the ProductMajorVersion is not equal to 5, disable the link. Product Major Version of 5 means an Azure SQL Database. Any other number will represent a different version of SQL Server.
Below is the expression that I used. In this case if the version is greater than 10, 2012 or newer AND the Engine Edition of not equal to 5. The report will navigate to another report. The report in the block of code is MainReport.
IIF(Fields!ProductMajorVersion.Value > 10 and Fields!EngineEdition.Value <> 5, “MainReport”, Nothing)
Just a reminder. The IIF function in SQL Server Reporting Services can be used to make a change based on some type of criteria. This function accepts three parameters. The first being the expression to be evaluated. If it is evaluated to True, then the report will navigate to the “MainReport”. When you float your cursor over the link, the arrow will turn into a hand showing that there is a link. The third argument is what will be used if the expression is evaluated to False.
Using the above example, if major version is 14 and the Engine Edition is 4, then the link to allow the user to navigate to the “MainReport”. If both criteria are not met, it will use the keyword NOTHING.
It is this keyword NOTHING that will cause the Go To action to do nothing. The cursor won’t change and when clicked, nothing will happen.
Thanks for visiting my blog!!!