SQL11UPD06-TSCRIPT-05

This wiki is a transcript of a previously recorded video.

Related content assets:

  • Presentation: Introducing SQL Server 2012 Reporting Services Improvements (SQL11UPD06-DECK-03)
  • Video: Introducing SQL Server 2012 Reporting Services Improvements (SQL11UPD06-REC-05)

Introducing SQL Server 2012 Reporting Services Improvements

Welcome to this presentation, Introducing SQL Server 2012 Reporting Services Improvements. My name is Peter Myers. I’m a SQL Server MVP and a Mentor with SolidQ.

In this presentation we’re going to introduce what is new in Reporting Services (SSRS) and specifically focus on data alerts and SharePoint integration. We’ll also introduce SQL Azure Reporting and describe developer opportunities.

Reporting Services in SQL Server 2012 has been upgraded with new features and capabilities. Power View is perhaps the most prominent and the most exciting feature delivered in Reporting Services in this release. Note the Power View topic includes a dedicated for discussion in the following presentation. There is increased productivity with user-defined data alerts, improved performance and administration with the SharePoint integration scenario, and the two renderers for Office, Excel and Word, have been updated to the Open Office XML format. The Excel renderer will now deliver and .xlsx file and the Word renderer will deliver a .docx file. Finally, the report designers have been upgraded to work in SQL Server Data Tools (SSDT).

Data alerts can be defined on a report feed. In SQL Server 2008 R2, a new rendering format, the ATOM renderer, was delivered. Essentially this capability is exploited by this new feature supported in Reporting Services in SharePoint mode that allows data alerts to be configured and, where data conditions have been met, notifications in the form of e-mail can be sent. It is expected that the business user will define these. Intuitive alert rules can be based on “And” and “Or” conditions. Scheduling settings can allow scheduling right down to the minute. Advanced settings can configure the validity of the alert: when does it start, when does it finish, and should it continue to notify you if results change. You can configure e-mail addresses, subject, and description. Be aware that data alerts can only be based on reports that you use data sources with stored credentials. Alerts can be managed by both users and administrators – they can view status, edit, delete, and run alerts. This feature is available in Reporting Services in SharePoint mode and is available in SharePoint Foundation edition or above.

What we see in this screen is the new Data Alerts window. On the left-hand side, the dropdown list that we see here allows us to select a different feed from the report and actually runs the report and shows us the feed data here on the left. On the right we can provide a name for the data alert. We can configure a rule – for example, the profit must be less than zero in order for a notification to take place. Scheduling is configured here – it’s presently for once a day. The e-mail settings for notification are configured beneath this.

The demonstration shows this in good detail. It creates a shared data source using stored credentials – it uploads a report that will then used that shared data source, and then we can configure a data alert. At first there is no alert that needs to be notified because the profit value is actually meeting expectation. We go ahead then and rig the data  and change the profit value and rerun the alert to see that an email has in fact been sent. I encourage you to watch this demonstration that is available in a separate recording.

The next topic is SharePoint mode. Reporting Services in SQL Server 2012 is now configured and monitored like other SharePoint service applications. When you’re working in Native Mode there’s no change. Be aware of that. In SQL Server 2012, Native Mode is what it was in prior versions. However, SharePoint mode now acts as a service application. What that means is that, for the SharePoint IT management, it’s actually much easier for them to manage because it’s like any other service application – e.g., Excel Services, PerformancePoint Services, Reporting Services. All configuration is now simplified and easier. You use Central Administration or PowerShell. It leverages SharePoint’s in-built scale capabilities to scale across the servers within the farm. Be aware that there was always a concern that SharePoint mode was slower than Native Mode, and in fact it was true because of the communications, the security checks across boundaries from SharePoint out to the Report Server meant it delayed the response. The great news is that it’s all contained within the SharePoint farm now and you can expect to get almost equal performance to what you would get in Native Mode. If you ever had the consideration to work in SharePoint mode but you were concerned about performance, this is no longer a concern in SQL Server 2012.

When Reporting Services is integrated with SharePoint, files types are understood. Report data sources, report definitions, it then surfaces context menus to manage reports, configure alerts, and so on. A new content type is installed for the BISM Connection File that allows us to define a connection to a tabular BI Semantic Model (BISM), either a PowerPivot workbook that has been published to SharePoint or a tabular database hosted on a Tabular mode instance of Analysis Services. The BISM Connection File allows us to create a document that points to our model and then users can use this to go ahead and use it as a data source or launch Power View.

In this final topic, SQL Azure Reporting, we will introduce this upcoming capability. It’s not technically part of SQL Server 2012 release, but it is due for release not long after the SQL Server 12 release. Essentially this is the ability to go ahead and have a hosted platform as a service, Reporting Services in the cloud. You can continue building reports using Report Designer, you can deploy them direct to the cloud and the Report Server, and you may embed these reports using the Visual Studio ReportViewer control.

Continue to use familiar technologies. Report Designer, RDL, report items and visualizations, Report Server Web Service and URL access, and the ReportViewer control. There are some things you need to be aware of. Only SQL authentication is supported. The Admin user is configured during provisioning. The Admin user has all rights on the instance. You provision users using the portal or web service. You then are responsible for configuring roles and user access and granting permission to folders and reports.

Differences in comparison to on-premise Reporting Services is that you want to focus on the logical versus physical administration. The report catalog and temporary databases are automatically provisioned for you. You use the Windows Azure Management Portal instead of Report Manager. SSL is required for connectivity. And you may only report from SQL Azure data sources. Note that there are some unsupported features such as Report Builder, subscriptions, schedules, and caching, custom extensions, report models, and external images.

So what are the developer opportunities here? You can optimize reports for self-service data alert creation by restricting the output of data elements not suitable for data alert usage. Be aware that when you create reports by default, data regions will emit a data feed. You can turn these off. You can rename them. You can turn off parts or elements of the feeds. It’s probably useful if you know that the data alerts will be created from them to provide friendly names for the output of these data elements. Developers can also consider provisioning SQL Azure Reporting and deploying reports based on SQL Azure data, and embed SQL Azure Reporting reports into their solutions by using the ReportViewer control.

In summary, the new visual design experience with Power View, we didn’t focus on that here but we’ll explore that in good detail in the following presentation. Reporting Services also includes increased productivity with user-defined data alerts and increased performance and improved administration within SharePoint when configuring SharePoint mode. There are new Open Office XML renderers for both Excel and Word, and use familiar tools and designers to produce reporting services based on resources in the cloud.

From a resources point of view, we have TechEd North America 2011 recordings about what’s new in SQL Server Code names “Denali”, which we now understand is SQL Server 2012. There is the Reporting Services team blog, a great TechNet article on data alerts and their architecture, Books Online for SQL Server about Reporting Services, and MSDN has a great introduction to SQL Azure Reporting as a preview.

There’s a Hands-On Lab that accompanies this presentation, “Exploring Data Alerts with SQL Server 2012 Reporting Services”.

Thanks very much for watching this presentation.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)