By: Scott Murray | Updated: 2012-11-28 | Comments (8) | Related: > 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.
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.
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.
eOnce setup, alerts can be maintained by right clicking on the report requiring alert maintenance, and then selecting Manage Data Alerts.
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.
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.
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
- MSDN Data Alerts: http://msdn. microsoft. com/en-us/library/gg492252. aspx
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-11-28
