Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

What's New in SQL Server 2012 Reporting Services

MSSQLTips author Scott Murray By:   |   Read Comments (7)   |   Related Tips: > Reporting Services Overview
Problem

SQL Server Reporting Service 2012 (SSRS) is now available. What are the main new features available in SSRS 2012?

Solution

SSRS 2012 contains two major enhancements.

  • First, files can now be exported into Excel 2007/2010 file formats.
  • Second, SSRS now includes data alerts.

The following tips will explain these new major features.

Excel 2010 File Export

This enhancement is a long time in coming. Prior to this upgrade, files were exported as just XLS files which limited a spreadsheet to just 65,536 rows. For many users, this item required a very inconvenient work around which often meant saving files in another format, such as csv, and then converting to Excel 2007/2010.  SSRS 2012 alleviates this pain point.

Excel2



Excel1

Data Alerts

The second major enhancement included in 2012 is much more complex than the file export update. Data Alerts allow a user to create alert thresholds which are evaluated on a user defined schedule. These threshold rules can contain complex criteria which trigger a new report to be sent at a prescribed interval. This new alert functionality does require several setup steps which we will cover below; additionally, SSRS must be installed in SharePoint Integrated mode in order to use data alerts.  Furthermore, the report for which the data alerts are based must store the credentials either within the report itself or it must store no credentials.

In order to create a new alert, navigate to a Report Server document library on a SharePoint site and then run the report.  Next, click on Actions and then select New Data Alert.

New Data Alert

The New Data Alert definition window opens and requires several items to be completed.  First, the Report Dataset / Region needs to be selected; once the dataset is selected, an example of the dataset is displayed below the dataset selection.  Next, the Alert Name can be defined or the default can be accepted.  Next, either the "Alert Me if Any Data has" or "Alert Me if No Data has" option must be selected. Now, one or more rules are setup; these rules can be simple or complex in nature and require single or multiple "and" or "or" criteria.  For example, in the screen print below, the field DistanceinMiles is selected for the rule column; the rule evaluates if the distance in miles is less than 50.  Other rule types include: "equal to", "greater than or equal to", "is below", or "contains".  The available rule clause is based on the data type of the field used in the criteria, so a date field, for instance, will have a different set of rule comparisons as compared to a field that is a text data type.

The next step is to define the schedule pattern upon which the rule will be evaluated.  In addition to defining the run schedule, you can also define an alert end date and whether the alert will only be sent if the data changes. This second option is handy for reporting an exception, only once.  The last part of the setup is to define the email recipient, the email subject, and the email text.

Data Alert Definitione

Once setup, alerts can be maintained by right clicking on the report requiring alert maintenance, and then selecting Manage Data Alerts. 

Manage Alert Select

The alert maintenance screen provides sufficient information in order to troubleshoot an alert.  The Last Run, the Status, and the Sent Alerts columns all provide important details about when an alert has run or errored.  Further, an alert can be edited to change items, such as, the rule criteria, the dataset, or the email recipient.  Certainly, the data alert can be deleted; alternately, the alert can be run once, on demand, from this screen. 

Manage Data Alert

Upon a successful completion of the data alert run, an email similar to the below figure is produced.  The email provides not only the detailed data matching the rule, but also displays the rule used and the parameters selected during report production.

data alert email

Conclusion

SSRS 2012 contains two important enhancements.  First, export using Excel 2007/2010 file formats is now available; users no longer have to contend with the Excel 2003 row limitations when exporting reports.  Secondly, Data Driven Alerts based user specified rules and schedules are now available.  The user can set the specific dataset to be evaluated for the data alert, and then only send alerts when the data meets a specified criteria or rule.  When the rule is met, an email is sent to the recipients defined in the alert.  Data Alerts are only available when SSRS is in SharePoint integrated mode.  Both of these enhancements provide much needed functionality to SSRS 2012.

Next Steps


Last Update: 11/28/2012


About the author
MSSQLTips author Scott Murray
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, November 29, 2012 - 6:20:38 PM - craig Read The Tip

Scott, nice summary on those features.  Another big one is "Power View" for interactive data exploration, visualization, and presentation. For a full list of what’s new, see http://msdn.microsoft.com/en-us/library/ms170438.aspx. The list now includes a summary of 2012 SP1 updates.

 


Thursday, November 29, 2012 - 10:07:43 PM - Scott Read The Tip

Yes... Powerview is an excellent addition to SQL 2012;  you need the Enterprise edition of SharePoint in order to us it though.


Wednesday, September 25, 2013 - 9:29:37 AM - Manish Read The Tip

Very nice explanation, thanks Scott.


Thursday, April 10, 2014 - 9:44:25 AM - John Read The Tip

Scott,

Can you please outline what's new in SQL Server 2014 Reporting Services?

 

Cheers


Friday, May 02, 2014 - 9:16:55 AM - Jung Read The Tip

Hello,

I find Powerview a bit limited when working with more complex datasets.  Especially if your data sets are Stored Procedures and must do calculations that aren't simple aggregations.

It would be nice if you are able to create a custom calculation engine for Powerview.

SSRS is a great tool, but MS has not made any significant change or improvements.  Do you know if they will invest more in SSRS?  There are still a great deal to be desired in SSRS, such as better data visualization and formatting.  

 

Thank you


Friday, May 02, 2014 - 10:24:30 AM - scott murray Read The Tip

Jung,

 

I agree that the SSRS seems to be lacking on the visualization side. I am not sure what the future holds, but maybe a merging of the Power tools (PowerView, Power Pivot, Excel Services) with SSRS.


Thursday, May 08, 2014 - 1:50:05 PM - Paul Read The Tip

I remember when SSRS came out in 2004 it seemed very innovative and it saw quantum leaps in functionality and usability in SQL 2005 and 2008. Given its quick progression, I thought it would overtake Crystal reports in all aspects of custom report writing. However, it seems as though Microsoft has been focused on self service BI with the focus on Powerview and has left SSRS to wither on the vine. I've been developing reports in SSRS since 2003 and Crystal reports since 2010. I was initially biased towards SSRS but I find myself favoring Crystal reports as it outshines SSRS in a couple of key areas, especially the user interface.  



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.