New Feature in SQL Server 2012 Reporting Services for Developers
Sept 18, 2012
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.
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:-
- Power View (SSRS)
- Power View Overview
- Microsoft WhitePaper "Power View Infrastructure Configuration and Installation: Step-by-Step and Scripts"
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.
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...
Geeks who read this article also read…
- New Features in Microsoft SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- How to Attach Database Without a Transaction Log File in SQL Server
- How to Detect Virtual Log Files in SQL Server Transaction Log File
- How to Fix Virtual Log Files in SQL Server Transactional Log File
- How to Change SQL Server Database Auto Growth Settings
- Troubleshooting SYSPOLICY_PURGE_HISTORY Job Failure in Stand Alone Instance SQL Server 2008
- Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error
- Troubleshooting SQL Server blocked access to procedure sp_send_dbmail
- Performance Dashboard Reports in SQL Server 2012
- Tips to Avoid Account Lockout Issues
- Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option
- Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- How to identify if the database was upgraded from a previous version of SQL Server
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- Using Transparent Data Encryption Feature of SQL Server 2008
- New Date and Time Data Types in SQL Server 2008
- Date and Time Functions in SQL Server 2008
- Configuring Database Instant File Initialization Feature of SQL Server
- Changing the default location of SQL Server Data and Log files
- Changing Default SQL Server Backup Folder in SQL Server 2008
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to Get Exclusive Access to SQL Server Database
- Read More SQL Server Articles…