While the list of new features in SQL Server 2012 isn't as long as it was in previous versions, there is still much to be excited about. New features include better visualization and richer reports, data alerting capability and new export options. This article will give you an overview of the new features to help you decide whether they are of interest to your organization.
Probably the most exciting new feature is Power View (formerly known as
Power View includes many types of charts and visualization effects, including playable charts where the points on the chart move as you play with the graph along the timeline. You can view Power View reports in a browser and implement them as Silverlight applications. One thing to be aware of, however, is that this feature is not as readily available as regular Reporting Services reports. You need Microsoft SharePoint Server 2010 Enterprise Edition to use Power View. You create Power View reports using a PowerPivot model workbook or a tabular model running in SQL Server Analysis Services 2012. In order to take advantage of Power View, you have to invest in licensing a SharePoint server in addition to SQL Server. You also have to deal with a steep learning curve with both SharePoint and PowerPivot before you can even start using Power View. As a result of these factors, Power View is mainly used in larger companies with dedicated business intelligence departments while most small companies stick with traditional SQL Services Reporting Services (SSRS) reports.
A few years ago, Microsoft announced that they would no longer include SQL Server Notification Services in future versions. At the time, they mentioned that similar functionality would be available in a future version of Reporting Services. This promised functionality has finally surfaced in SQL Server Reporting Services 2012 and it's called Data Alerts. Now, you can configure a report and send out alerts based on rules and conditions you define in the Data Alert Designer. The designer allows you to create rules and conditions on an existing report and then combine them to create an alert. When you run a report, you can open the Data Alert Designer and create alerts using the data in the report. Once your alert definitions are done, you save the alert and create a schedule.
The alert definition gets saved in the alerting database. The schedule gets saved as a job in SQL Agent. When SQL Agent kicks off a data alert job, it reads the alert queue, runs the report, reads the report data and applies the rules you defined in the data alert. If the alert conditions are met, it creates an alert instance and sends alert messages by email; these emails can be sent to multiple recipients. You can create data alerts on reports created with Report Designer in Visual Studio or Report Builder, but you cannot create alerts on reports created in Power View. An even bigger restriction is that you can define data alerts only on reports saved or uploaded to a SharePoint library. So, this feature only works when your Reporting Services instance is installed in SharePoint mode. Just as with Power View, using data alerts requires SharePoint knowledge and a license, which somewhat limits the use and wider adaption of this feature.
For more on SQL Server Reporting Services
FAQs about SQL Server Reporting Services
Managing permissions in SQL Server Reporting Services
SQL Server Reporting Services analysis and tuning
There are also some changes in the way Reporting Services 2012 interacts with SharePoint. SharePoint integration has greatly improved and is now implemented as a SharePoint 2010 shared service. If you run Reporting Services in SharePoint mode, you no longer need to use Reporting Services Configuration Manager to administer configuration. Instead, you can use the SharePoint Central Administration tool and configure SSRS through management pages for Reporting Services service applications.
Finally, there are some changes in the way you export reports into Microsoft Excel and Microsoft Word formats. When exporting into Excel, the file is by default rendered as an Excel document compatible with Microsoft Excel 2007-2010. It uses the Office Open XML format with XSLX extension. This is the only format available in the export list by default. If you want to enable rendering in Excel 2003, you have to modify the Reporting Services configuration file and make this rendering extension available in the list of supported extensions. The new rendering extension for Excel 2007-2010 has several improvements over the 2003 extension. Among others, the maximum number of rows per spreadsheet is now 1,048,576, and the maximum columns per spreadsheet is now 16,384. (There were 65,535 rows and 256 columns in SQL Server 2008.) The changes in exporting to Microsoft Word are similar to Excel. Files are saved in Microsoft Word 2007-2010 format with a DOCX extension. Just like with Excel, you have to modify the config file to allow exporting in the Word 2003 format. An additional improvement is that when you export into the new Excel or Word 2007-2010 format, the rendered files will be smaller in size than they were in SSRS 2008.
As you can see, SQL Server Reporting Services 2012 provides several new and improved features worth looking into. Although Data Alerts and Power View require SharePoint and may not be suitable for smaller shops that do not have SharePoint expertise, you should at least be able to benefit from improved report rendering options when exporting into Microsoft Word or Excel.
This was first published in August 2012