Ensure SSRS feature compatibility across multiple SQL Server versions


By:   |   Updated: 2016-07-18   |   Comments   |   Related: > Reporting Services Development


Problem

Development teams are often faced with the challenge of developing Reporting Services solutions for multiple versions of SQL Server. Different features are introduced with new versions of SQL Server and at times it becomes hard to keep track of which feature is supported by which version. To add to the complexity, some features are also removed from SQL Server versions. Let's say I have the latest version of SQL Server installed and I have to develop reports for SQL Server 2008/2008 R2/2012/2014, it can become challenging to develop a solution that works for each of these versions. Read this tip to learn of an easy way to identify issues between versions. 

Solution

SQL Server Data Tools (SSDT) 2015 provides a new property named Target Server Version to check feature support / platform compatibility against the targeted server version. In this tip we will create a sample SQL Server Reporting Services (SSRS) report and use some of the latest features of SQL Server 2016 that are not supported by earlier versions of SQL Server to see how we can find these incompatibilities. Follow the steps mentioned below.

Create a Sample SSRS 2016 Report

First, we need to create a sample solution. In the tip Create a Treemap Graph in SQL Server Reporting Services 2016, there are instructions to create a TreeMap report. TreeMap is the new chart type which has been introduced with SSRS 2016. If you follow the instructions in the tip, you should be able to see a report similar to below and this report should work smoothly in the preview mode.

multiple_SQLServer_deployments

Testing Report for Other SSRS Versions

Now let's say, we want to determine whether this report would work with the SQL Server 2008. In order to verify the Solution's components (graphs or other) compatibility, it is better to verify before deployment. To do so, a new solution property named "TargetServerVersion" could be utilized to verify the SQL Server version dependency. Right click on the project, which in our scenario is "Sample Report Prj" and select "Properties" as shown below.

SQLServer2008R2

You will be able to see the TargetServerVersion property which has three values. It also has an option to detect the version on which the solution is being deployed. Let's change this to SQL Server 2008 and save the solution.

TargetServerVersion

If you try to preview the report now, you may get an error message as shown below.

preview the report

If we look at the output errors in the screen below, it clearly states the Treemap is not compatible with earlier versions of SQL Server.

output errors

Conclusion

In this way we can use the TargetServerVersion property to ensure the solution's platform compatibility with different versions of SQL Server.
Next Steps


Last Updated: 2016-07-18


get scripts

next tip button



About the author
MSSQLTips author Rahul Mehta Rahul Mehta is a Project Architect/Lead working at Tata Consultancy Services focusing on ECM.

View all my tips





Comments For This Article





download





Recommended Reading

SQL Server Reporting Services Using Multi-value Parameters

SQL Server Reporting Services Expressions Tips and Tricks

Resolving the Maximum Request Length Exceeded Exception in SQL Server Reporting Services

Multi-detail reports using sub reports in SQL Server Reporting Services

Multiple Row Grouping Levels in SSRS Report














get free sql tips
agree to terms