New Feature in SQL Server 2012 Reporting Services for Developers


Sept 18, 2012

Introduction

Microsoft has released SQL Server 2012 which was also known to the SQL Server Community by its code name SQL Server Denali in the first half of 2012. In this article we will explore some of the new feature in SQL Server 2012 Reporting Services which SSRS Developers can leverage in there day-to-day work.

New Feature in SQL Server 2012 Reporting Services include:-

  • Power View
  • Improved SharePoint Integration Mode
  • Word Rendering for Microsoft Word 2007-2010 and Microsoft Word 2003
  • Excel Rendering for Microsoft Excel 2007-2010 and Microsoft Excel 2003
  • Self-Service Alerting or Data Alerts
  • SQL Server Data Tools add in for Visual Studio 2010 for Report Server Projects

Microsoft SQL Server 2012 introduces lot many new features for Business Intelligence Developers, TSQL Developers and Database Administrators. This article gives you an overview of some of the new features in SQL Server 2012 for SSRS Developers.

Power View

SQL Server 2012 Reporting Services introduces a new add in for Microsoft SharePoint Server 2010 Enterprise Edition. Power View is an interactive data exploration, visualization, and presentation experience. Using Power View you can design visually appealing report with charts along with graph. To use Power View feature you need to buy a license of Microsoft SharePoint Server 2010 Enterprise Edition. To create Power View reports you can use either a Tabular Mode in SQL Server 2012 Analysis Services or you can use Power Pivot workbooks. For more information on how to create Power Pivot Workbooks in excel refer the following MSDN Article titled “Roadmap to Creating PowerPivot Workbooks in Excel”.

More Resources on Power View Feature:-



Improved SharePoint Integration Mode

In SQL Server 2012 Reporting Services SharePoint Configuration Mode needs to be configured using SharePoint Central Administrator or by using Reporting Services SharePoint mode PowerShell cmdlets. Configuration of Reporting Services SharePoint Mode is no longer supported using Reporting Services Configuration Manager as link in previous versions of SQL Server. Couple of other major enhancements include support for viewing reports in SharePoint cross farm, new SSRS shared service is hosted in SharePoint Shared Service Application Pool etc.


Word Rendering for Microsoft Word 2007-2010 and Microsoft Word 2003

In SQL Server 2012 Reporting Services you can export the reports in Microsoft Word 2007 - 2010 format which uses Office Open XML format with DOCX as extension. By default, Microsoft Excel 2007 - 2010 format is available in the export list. In case if you still wish to use Word 1997 - 2003 format then you need to manually modify the reporting services configuration file and make XLS extension available in the list of supported extensions.

Advantages of using Microsoft Word 2007 – 2010 format are:-

  • Smaller file size when exporting reports in Word 2007 – 2010 format


Excel Rendering for Microsoft Excel 2007-2010 and Microsoft Excel 2003

In SQL Server 2012 Reporting Services you can export the reports in Microsoft Excel 2007 - 2010 format which uses Office Open XML format with XLSX as extension. By default, Microsoft Excel 2007 - 2010 format is available in the export list. In case if you still wish to use Excel 1997 - 2003 format then you need to manually modify the reporting services configuration file and make XLS extension available in the list of supported extensions.

Advantages of using Microsoft Excel 2007 – 2010 format are:-

  • Maximum number of rows supported per worksheet is 1,048,576 i.e., 1 Million
  • Maximum number of columns supported per worksheet is 16,384
  • Smaller file size when exporting reports in Excel 2007 – 2010 format

Self-Service Alerting or Data Alerts

SQL Server 2012 Reporting Services introduces Self-Service Alerting or Data Alerts feature. Using Data Alerts feature a user can create alerts for the data retrieved from the reports which could be of your interest. Alerts are sent as an email message and the user can choose the frequency of the alerts depending up on business needs.

For example, if you are running a grocery store and you would like to get alerted whenever inventory for fast moving items dips below a certain value then using Data Alerts you can schedule alerts to be triggered when inventory reports are rendered. You can create data alerts using Data Alert Designer, Data Alerts Manager for Users and Data Alert Manager for Alerting Administrators. For more information on Data Alerts feature read the following MSDN Article.

Important Note:- The data alerts feature will work only when you have installed SQL Server 2012 Reporting Services in SharePoint mode. When you install SQL Server 2012 Reporting Services in SharePoint Integration mode SQL Server 2012 setup automatically creates the alerting database which will stores data alert definitions and alerting metadata along with two SharePoint pages for managing alerts and adds Data Alert Designer to the SharePoint site.


SQL Server Data Tools add in for Visual Studio 2010 for Report Server Projects

In Visual Studio 2010 supports using SQL Server Data Tools (SSDT) as a add-in to manage Report Server Projects. Using SQL Server Data Tools you can open report server projects created in SQL Server 2008 R2 directly. However, report server projects created in SQL Server 2008 will be upgraded before they are opened in SQL Server Data Tools. To know more about SQL Server Data Tools and to download read the following MSDN Article titled "Get Started with Microsoft SQL Server Data Tools"

Deprecated Features in SQL Server Reporting Services in SQL Server 2012

To know more about some of the deprecated features in SQL Server 2012 Reporting Services refer the following link.

Reference: What’s New in SQL Server 2012 Reporting Services

Conclusion

One of the primary reasons to upgrade to SQL Server 2012 Reporting Services is the ability to render reports in Excel 2007 – 2010 format. In the earlier versions of SQL Server rendering reports in Excel format had limitation as excel could support just 65,535 rows and 256 columns. However, in order to leverage new features such as Power View, Data Alerts etc. you need to install Microsoft SharePoint Server 2010 which will be an additional cost.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."