Please join me and other 2017 PASS Summit speakers for 24 Hours of PASS: Summit Preview on July 19th and 20th. 24HOP is a series of 60 minute on-line sessions presented back-to-back for 24 hours, from the same professionals who will deliver preconference and main conference sessions during the Summit the first week of November this year. These online sessions are free of charge and normally attended by thousands of individuals to gain insight and knowledge about the topics. 24 Hours of PASS features free educational webinars delivered over 24 hours. Topics covered in this edition include Performance Tuning, SQL Server 2017, Linux, DevOps, Azure, PowerShell, SSRS, Power BI and much more. Browse all sessions. These webinars provide a sneak peek at some of the best practices, expert tips and demos you’ll find at this year’s PASS Summit in Seattle. Continue reading
I am thrilled to be presenting a full-day preconference session before the PASS Summit, on October 31st. Please join me for a deep tour of the new capabilities and BI platform integrations for SQL Server Reporting Services 2016 & 2017. The session will also review the essential skills and tasks to setup and configure the report server and web portal, report design and modern report solution planning. Continue reading
This page is a table of contents for for several new and forthcoming posts. I’m posting a series of excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports. Each of the posts is a condensed version of the material covered in a corresponding chapter from the book. Although I would love for you to buy the book to get the full edition of each topic, each post will contain valuable information that I hope will be informative and educational on it’s own; whether you buy the book or not. I’ll update this index with topics and links as I continue to add each post. Until then, some of these will serve as placeholders for future posts.
Please post comments or contact me through my blog if you have questions or feedback, or if you are interested in training and consulting related to this material. – Paul
NEWS FLASH: Power BI reports can be deployed to SQL Server Reporting Services web portal. The production-ready release is targeted for mid 2017. This is much sooner than most folks in the community were anticipating. An installable technical preview is targeted for January of 2017. This announcement was just made on the SQL Server Reporting Services Team Blog.
From the announcement:
Which Power BI capabilities do you plan to add to SSRS?
We’re focusing our efforts on adding Power BI reports to SSRS and on supporting the features Power BI Desktop offers for use within these reports, including a variety of data connectors and visualizations. Beyond the current Technical Preview, we plan to add support for
- Custom visuals
- Additional data connectors (besides Analysis Services), cached data, and scheduled data refresh
- Power BI mobile apps (viewing Power BI reports stored in SSRS)
Given our focus on Power BI reports, we have no current plans to add other Power BI features (such as “dashboards,” Q&A, Quick Insights, and others) to SSRS.
What can we expect in the next Technical Preview of Power BI reports in SSRS?
With the current Technical Preview, we used a pre-configured Azure VM to offer you a preview that’s quick and easy to try. Our focus for the next Technical Preview is on a version you can download and install on your own VM or server, a necessary next step toward a production-ready version. Aside from this aspect, the functionality will be similar to the current Technical Preview’s.
When will we have this next Technical Preview?
We’re targeting January 2017 to release this next Technical Preview.
What’s the release vehicle for a production-ready version?
We plan to release the production-ready version in the next SQL Server release wave. We won’t be releasing it in a Service Pack, Cumulative Update, or other form of update for SSRS 2016.
When will we have a production-ready version?
We’re targeting availability in mid-2017.
Can I deploy SSRS 2016 today and migrate to SSRS with Power BI reports when it’s available?
Yes, we aim to make it easy to migrate to SSRS with Power BI reports from SSRS 2016 and previous versions.
The complete blog post can be found here: https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/12/16/power-bi-reports-in-sql-server-reporting-services-feedback-on-the-technical-preview/
everyone who attended my session today titled “Reporting Services 2016, The Force Awakens” We had a packed house with attendees standing along all of the walls in two joining session rooms. This is indeed a very popular topic! The session was recorded and will be available to watch with all the Summit 2016 session recordings. You can download the slides deck here. Samples and demo material are available from these resources (which are on the References & Sample slide):
Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports
Self-paced online SSRS 2016 training
EdX course DAT214x: “Analyzing and Visualizing Data with SQL Server Reporting Services”
The Essential Guide to SQL Server 2016 Reporting Services
SQL Server Pro Magazine / SQLMag.com
What am I most excited about as I prepare for the PASS Summit here in Seattle this week? A lot of things. Preparing for my session, which will be on Thursday at 1:30, by far the most popular and interesting topics are about integration and tool choice. Today’s public announcement on the SSRS product team blog about on-premises Power BI integration with Reporting Services is really big news. It’s great to see two of the technologies I love working together. Whether in the cloud or on-premises, Power BI and Reporting Services can be used together.
Thursday’s session is titled “Reporting Services 2016, the Force Awakens”. The consensus among industry users is that Reporting Services was stalled and left alone for far too long. Technology and industry trends have moved forward over the past five years or so but Microsoft have invested in other tools and products, aside from Reporting Services. The SSRS server architecture is capable but the rendering and delivery components needed to be modernized.
The good news is that the modernization effort has been underway for quite some time. Resources that were directed to other investments have been recommitted, not only to the Reporting Services product, but to integration across the product stack. It’s really hard to build a reporting platform that addresses every need. I think Microsoft have a history of creating false expectations by putting experimental and incomplete features out in the industry that weren’t on the committed roadmap. I know better than to believe that the pattern won’t ever be repeated but I firmly believe that there is a well-defined reporting and BI roadmap and product teams are making rapid progress against it. It is once again an exciting time to invest in the Microsoft analytics, reporting and BI tool set.
I’ll publish the entire deck after the 2016 Summit. Until then, here are some selected slides…
Comparing the progress of new features added to SSRS over the release cycles, the product really slept from 2010 until 2016.
Microsoft’s reporting & analytic platform has shifted significant as they moved from products to services, and from servers to the cloud. The dust is settling on this major refocusing effort. Capabilities that were introduced in Azure, Cortana Analytics and Power BI are now becoming available to “the box”, on-premises or in hybrid cloud scenarios. We can expect more announcements on this as the roadmap is played-out and gaps are filled-in.
Reporting Services is a central player in the on-prem delivery of the BI, analytics and reporting tools offered by Microsoft. These include Excel, Paginated reports created with SSDT/report designer or Report Builder, Mobile Reports and Power BI Desktop.
…more to come!
check back for updates.
(first excerpt from the upcoming book)
“Professional SQL Server 2016 Reporting Services and Mobile Reports” from Wrox Press is content complete! The book is well into the editing stage– still, another grueling phase of the publishing process, but there is light at the end of the proverbial tunnel. What is it like to write an 800+ page technical book? I’ve been trying to come up with the best metaphor to describe a year of persistent weekend-and-evening content development – building labs, demonstrations and exercises; performing research and and hounding my friends on the product team. A big thanks thanks to Chris Finlan, Riccardo Muti and Robert Bruckner on the SSRS product team for their continued help and support.
I’ve admittedly been a bit quiet on the community front for a while I’ve been heads-down on this book project but I hope to start redirecting more energy to community content.
Intense book writing can be a life-sucker:
This is Paul’s brain…
…this is Paul’s brain after writing a technical book:
Don’t get me wrong – I enjoy book writing. I’ve spent a lot of my career writing technical books and courseware because I enjoy the challenge. Writing helps me see things from different perspectives and at different levels of depth and difficulty for different audiences.
Give my brain a couple of weeks to heal and I’ll channel some of that energy into blogging and producing more community-facing training content.
So, what’s in the book? This is the Fifth Edition of the Wrox Professional SQL Server Reporting Services series which I’ve been leading and co-authoring since 2003. Amazon still has a working title listed but this is normal. As a next-edition, some of the 2012 edition evolved forward but most of the book is all new material. Four chapters are dedicated to the new Mobile Report features introduced in SSRS 2016. Before someone asks… when will the book be out? I don’t know exactly; there’s still work to be done. Soon. How’s that?
Over the next few weeks, I’ll post excerpts from the book to give you a taste of what’s coming and provide some valuable information about SSRS 2016.
Here’s a short except from Chapter 2:
What’s New in SQL Server 2016 Reporting Services?
Reporting Services was released as an add-in tool for SQL Server 2000 back in early 2004. At the time, the feature set was light by comparison to the product today but the foundational architecture was hasn’t changed significantly. Essential features included basic charts, sub-reports data regions and single-value textboxes.
The second release in 2005 added a self-service report authoring tool called Report Builder (later named Report Builder 1.0) paired with a semantic modelling tool in the designer. The original modelling and ad hoc report tool has since been deprecated but it inspired more capable replacement technologies like Power Pivot and the later generations of Report Builder. Not to be confused with the original Report Builder tool, Report Builder 2.0 and 3.0 produce report definition files compatible with the report project tools that are integrated with Visual Studio; originally called Business Intelligence Development Studio (or “BIDS”).
Report Builder and Designer Enhancements
The report design experience for standard “paginated” reports hasn’t really changed much over the past few product versions but there have been incremental improvements. Report Builder is restyled to conform to Office 2016 standards. The installation process for Report Builder changes to an “Evergreen” app. This means that Microsoft maintains updates for frequent download, rather than the old “ClickOnce” installation from your on-prem server. Similar to prior versions, users can elect to install Report Builder from the Report Portal menu. Report Builder is updated with a modern look-and-feel, simple and sleek, Changes are mostly cosmetic while the fundamental features are the same.
The Visual Studio integrated Report Designer is now part of SQL Server Data Tools (SSDT), a downloadable add-in for Visual Studio. Although the tool set hasn’t changed significantly, there are some subtle changes to the way SSDT is installed and the way updates are delivered. First of all, confusion about the name “SSDT” is dispelled because the former “SSDT” (the previous version add-in for database projects) and “SSDT for BI” (the previous version add-in for SSIS, SSAS and SSRS projects) are now a combined package, simply called SSDT. Secondly, you can simply download and install a version of SSDT that will work with the current version of Visual Studio or a few versions back. The SSDT add-in will be updated frequently and you will have the option to install updates on-demand from within Visual Studio.
Modern Browser Rendering
One of the most significant product improvements in 2016 may be one of the least apparent under casual observation. The entire HTML rendering engine has been completely overhauled across the platform. The report portal, used to navigate and manage report content, and the actual report content are rendered to modern HTML 5 standards, which are supported by all modern web browsers. The shift to modern HTML output means that web content produced by Reporting Services is consistently consumable on any device, regardless of the operating system or web browser, so long as it supports modern standards. The benefits are readily apparent when reports simply work on smartphones of any type, on tablets and laptop and desktop machines; regardless of the brand or operating system.
Parameter Layout Control
You will have improved control over parameter formatting and placement. Since the inception of Reporting Services about twelve years ago, parameters have always been arbitrarily arranged in a narrow bar at the top of the browser window, from left-to-right, and then top-down. The report designer has a grid to manage the placement of parameters, in the parameter bar, in any configuration, within definable rows and columns.
The addition of mobile dashboards to the SSRS platform is based on the Datazen product acquisition from ComponentArt in 2015. Mobile reports are primarily designed to enable data interactivity in dashboard-style reports created by a mobile report developer. Managing this expectation is important because this tool is significantly different than conventional Reporting Services.
Mobile reports can be viewed in the browser but are optimized for phone and tablet devices through native, installed applications running on all the major mobile OS platforms. They are not a replacement for high-fidelity paginated reports created with Reporting Services, or self-service analytics in Power BI; they serve an entirely different purpose.
At first, the mobile dashboard experience may seem to be a simple drop-in of the Datazen product; but, it is apparent that some integration with the SSRS architecture has already taken place, and more adaptations are likely on the horizon. The first notable difference is the Datazen server is entirely replaced by the SQL Server report server, and queries are now managed as SSRS shared datasets.
New Key Performance Indicators (KPIs) integrated with the new Report Portal are also based on the Datazen product acquisition. These KPI visuals are created and managed entirely within the portal. In additional to the standard traffic light style comparison of actual vs target values, KPIs can include a trend line or segment chart. The KPI is driven by data from one or more shared datasets that were created in the SSDT report designer. For ease and simplicity, any value of the KPI can be entered manually through the design page.
Although they are visualized in the Report Portal, KPIs are delivered to mobile devices through the Power BI mobile applications on every popular device platform.
Native Printing Control
The previous printing capability in SSRS replied on an ActiveX control which was only supported on Windows desktops and in certain web browsers. Even in tightly controlled Windows server environments, system administrators would rescind ActiveX support and disallow report printing from the server. The modern printing solution uses the PDF renderer to printable output and then the Adobe document viewer to perform the actual printing
Users have had the option to export and render report content to Excel for several SSRS versions. Output to Word was added in SQL Server 2008 and then both of these rendering options were improved and updated in the 2008 R2 version. Now a third Office application format will be supported with the introduction of PowerPoint document rendering.
Most report items and data regions are converted to individual image objects in the resulting PowerPoint sides with one side generated per report page. Additional slides are created based on the report content size and layout. Textboxes are created for titles and report text, which support some report actions and textbox properties.
Integrated and Improved Report Portal
A new Report Portal web interface is introduced to replace the Report Manager. Like Report Manager, the portal is an ASP.NET web application used to access, run and manage reports in the web browser. The new portal has a look-and-feel we are accustomed to seeing in other modern apps from Microsoft these days; with responsive design for constancy on different device form factors. Report Portal will be the home for mobile reports, KPIs and paginated reports – the new name for RDL reports authored with Reporting Services. In the future, we may see support for additional content types.
Report Portal supports in all modern web browser by emitting responsive HTML5 with adaptations for mobile devices and screen orientations.
New Charts and Visual Enhancements
With the addition of two new chart types, visualization improvements are inched forward in Reporting Services. The new Sunburst and Treemap charts apply multi-level field groups visualized in both color and visual boundaries.
New and updated report visuals are likely to be an area of focus for future Reporting Services enhancements, given the success of self-service BI tools like Power BI. The design interface is identical to existing chart types and the only real difference is that groups of rows are visualized in these unique formats. The Sunburst chart is also capable of consuming unbalanced hierarchies with slices generated for different levels only where data points exist.
Standardized, Modern Browser Rendering
At first, you may not notice significant changes from previous versions, but the HTML renderer has been completely overhauled and updated. Now, reports are rendered to HTML 5 standards whereas, they should consistently maintain the same appearance and behavior in all modern browsers that support the HTML 5 standard such as Microsoft Edge, IE 11, and newer versions of Google Chrome, Safari and Firefox. This change is a welcome improvement which should clear up many problems with inconsistent and quirky report layouts while using different web browsers and devices. By the same token, the change means there is no specific backward-compatibility for outdated browsers; consequently, reports that may have worked (or partially worked) in an old version of Internet Explorer may no longer work until the user upgrades.
Power BI Dashboard Pinning
For organizations who have invested in the Power BI cloud service, the Power BI integration feature allows users to pin graphical SSRS report visuals to their online dashboards. In order to use this feature, an administrator must register the report server with an existing Power BI subscription, and a report user must have access to the Power BI subscription.
Reporting Services Configuration Manager includes a new page to manage Power BI Integration. This is where you register the report server instance with the Power BI subscription. When a report with “pinnable” items (such as images, charts and gauges) is viewed in Report Portal, the Power BI icon is displayed on the toolbar.
“pinnable” items are highlighted in Report Portal. When a visual is selected, you are prompted to select a Power BI dashboard and the refresh frequency. This schedules an Agent job on the report server to push updated visuals to the dashboard at the selected frequency.
The Select frequency of updates option utilizes the SSRS subscription architecture by scheduling a SQL Server Agent job on the database server with the report server catalog. The Agent job re-queries the report data and then a report server component refreshes the Power BI dashboard tile with an updated report visual.
Pinned report visuals appear on the dashboard alongside the Power BI report and Excel visuals. Clicking on one of these visuals will drill-through to the report back on the on-premises report server. This gives users a seamless navigation experience between cloud-hosted Power BI content and selected report visual elements on your own report server.
The integrated Power BI experience is a big step forward in providing a completely integrated IT-hosted and self-service reporting, BI and analytics solution.