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
This just in from the Reporting Services product team:
“Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available” This feature addition will allow Power BI reports to be published to a local SQL Server Reporting Services server, entirely-on-premises without using the Power BI cloud service.
The January 2017 Technical Preview can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=54610
I literally just received this update from Microsoft and plan to download and start working with the preview. I’ll post updates with my experience.
This is not an update for SQL Server 2016 but it is actually the next version of SQL Server which is due out in a few months.
Please check back for updates!
More information on the SQL Server Reporting Services Team Blog
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/
I recently needed to replicate all of the reports on one report server to a different server. I started downloading each report one at a time to a file system folder, moving them to the other machine and then uploading each RDL file. Needless to say, that’s a slow and painful process. Since a new set of PowerShell command lets (Cmdets) were introduced for Reporting Service just a couple of weeks ago, I decided to give them a try and share the results.
Aaron Nelson, long-time Microsoft Data Platform MVP and PowerShell fanatic, has literally been hounding me for the past few weeks to take some time and look at a new PowerShell library and scripts originally developed by Parth Shah from the SSRS product team. Jaime Tarquino also played a big role in automating the build process. It’s the personal commitment of folks at Microsoft like Parth and Jaime that really make a product like SSRS shine.
Aaron can be pretty persistent and I admit it’s ironic that I’ve been too busy finishing up the final editing work on the Wrox SSRS 2016 book and the new edX SSRS course to look at these RS PowerShell CmdLets but I’m glad that I did because they’re an absolute gold mine.
Back in July Aaron asked me to post some questions about what PowerShell support members of the Reporting Services community would like to see. That post is here. We got some good feedback to help the product team prioritize their efforts in this area. Aaron and and Chrissy LeMaire worked closely with the Reporting Services team to improve the commands before they were recently announced, and the team cited their help in this announcement: https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/11/07/community-contributions-to-the-powershell-scripts-for-reporting-services/
Back to my project… I have developed several demo and lab exercise reports for the new edX SSRS course: Analyzing and Visualizing Data with SQL Server Reporting Services. The development environment is an Azure virtual machine that I recently didn’t have access to while travelling and needed to move all the reports to a local VM to continue my work. I needed to make sure that current versions of these dozens of reports are on both servers. Using a couple of new PowerShell commands, this was quite easy. Here’s how it worked:
Step 1: I open the PowerShell ISE console on the server where I need to archive the reports (shown with the script I’ll run in the next few steps)
Step 2: run a command to download and install the rstools library (see line 1):
Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)
Step 3: Line 5 downloads all the reports in the “DAT214x Course” folder on my Azure VM report server to a local file system folder:
Step 4: On the destination server, I also run the command to install the rstools library…
Step 5: After copying the files to a local folder (or you could use a UNC path to access a shared folder), run Write-RsFolderContent to upload all the reports to the same destination folder:
Progress is displayed while the files are uploaded:
With shared data sources in the same places, all of the files are uploaded and working:
This saved me hours of time and provided peace of mind, knowing that I could easily automate large migrations in the future.
Here is the complete list of commands. You can get help, execution details and optional parameter information by prefixing the command with the word “help “:
Some additional notes (from Aaron)…
You can download these commands into your PowerShell environment very easily by running this simple command:
Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)
You need to be running PowerShell as Administrator in order to do this.
My favorite command among them is this one which will upload an entire folder worth of Reports & Datasets to an SSRS server for you. Here’s an example using Jamie Thomson’s SSIS Reporting Pack (attached):
Write-RsFolderContent -ReportServerUri ‘http://localhost/ReportServer_SQL2016’ -Path ‘C:\SSIS Reporting Pack\Reports’ -Destination /SSISReporting
You can get the full list of commands by running this:
Get-Command -Module ReportingServicesTools
If you have access to multiple report servers, you only need to run PowerShell commands on one machine.
Folder recursion is supported if you have nested folders.
I think you’ll find these commands can help speed up your Development Lifecycle.
Thanks again to Parth, Jaime, Aaron, Chrissy, Riccardo and the rest of the product team for making this possible!
Since the Reporting Services product team announced last year that it was their intention to provide an on premises deployment option for Power BI reports, there has been tremendous interest. Despite their determination to provide clarity around their efforts , there has still been some speculation about how and when we’ll see this capability. I don’t want to jump on the bandwagon and add to the rumors or debate the details. The fact is that product leadership have been more than forthright about their progress and must work through the usual rigor of packaging and supporting a major feature addition when it is ready for release.
At the PASS Summit in Seattle a few weeks ago, Riccardo Muti and Chris Finlan announced that the team planned to release a new preview of the enhancements to SQL Server 2016 Reporting Services that could support Power BI reports hosted within the SSRS web portal. Today, they are delivering the goods with an Azure virtual machine that we can use to preview these features. Riccardo was very clear that the VM would be a very early preview and that the actual released product wouldn’t be announced until it was ready. The question on the minds of several community leaders is whether these features will be available in some kind of update for SQL Server 2016 or whether it will be in the next version of SQL Server. I checked with Riccardo Muti, Group Program Manager for SSRS and he says “We’re releasing Power BI reports in ‘SSRS vNext’ as part of the SQL Server vNext wave, but don’t assume ‘vNext’ must mean a couple of years.“ …and, “it’ll be here in 2017“. Now we have a definitive answer.
Dan English posted about this today in an article titled On-Premise Power BI–A Microsoft Story. I encourage you to read his entire post and understand his thoughts on the matter. At the PASS Summit session, I had the impression that there would be an interim update for SQL Server 2016 but after reading a lengthy email thread today, that doesn’t seem to be the case. My conclusion is that plans can change and there has not been on official announcement on the release plan.
The official announcement was posted today on the Reporting Services product team blog by Chris Finlan, titled Technical Preview of Power BI reports in SQL Server Reporting Services now available. Chris told me about this plan a few weeks head of the announcement and said that they weighed their options carefully before they decided to go down this route. The trade-off was that this decision would likely raise unanswered questions and speculation out in the community. In the end, they decided it was best to err on the side of being open and transparent with their customers.
From Chris’ post on the team blog:
As we brainstormed creative ways to let people try this functionality as early as possible, we had three very specific goals we wanted to achieve:
- Provide access to the new functionality publicly as early as possible while ensuring the end-user experience was something you’d find valuable
- Create a self-contained experience and environment that allowed users of any skill level an easy way to get started
- In no way disrupt or delay the initial preview of a downloadable and installable version
By using the Azure Marketplace to distribute this early technical preview, we feel we have not only met those goals, but also established a repeatable way to distribute content in the future. For users who would prefer to run this technical preview on an on-premises server, you are welcome to provision a virtual machine and then download the image as a .vhd file and use Hyper-V functionality to do so.