What's New in SSRS for SQL Server 2016: Part 1

Hi all! Yes, it's been a while since I've blogged, but I have a great new subject to talk about. Last week, I struggled through a cold to deliver a webinar on SSRS 2016, and the new existing features coming with this new release. As a follow up to the webinar, I wanted to blog and answer many of the questions that I didn't have a chance to answer during the webinar.

First, I intended on showing a few extra features (like branding!) but ran out of time. One of us from Pragmatic Works will most like follow up with another presentation on this as Microsoft gets closer to releasing the product. Since my webinar last week, they have added a few new features in the RC2 release. So keep your eye out for some more presentations from Pragmatic Works on this topic! If you missed my webinar, here is a link you can use to rewatch the presentation: http://pragmaticworks.com/Training/Details/Whats-New-in-SSRS-for-SQL-Server-2016 and click here for the slides. I received a lot of good questions during and after the presentation - way too many for a single blog post!

So, here is the first group of questions, and my answers. I want to remind everyone that, as I said in the webinar, I'm not an SSRS expert, but I do know several so I've done my best to answer everyone's questions. Also, this is a pretty long post, so read down to the bottom to see if I answered your question. On with the questions!

Iman A. asked: "Are there any virtual labs that we can use to try it out?" Answer: Yes there is at least one virtual lab from Microsoft that you can use, here is the link: https://technet.microsoft.com/en-us/virtuallabs. Things change fast at MSFT, so there could be other virtual labs that pop up quickly, I’d check their site often. I did find several labs on different new features in 2016, and I’d expect more to come as it gets released.

Reuben A. asked: "Are they doing the flow thing like Power BI... relaying the tiles depending on form factor?" Answer: If I understand your question properly, they are allowing you to set the tiles up for the various form factors (Desktop, Tablet, and Phone). In my demo yesterday we had a weird thing happen where the phone and tablet test displays looked the same, but the goal is that you have the opportunity to set the display according to the appropriate form factor. Having said that, I haven’t tested to see what happens if you only design a single form, but you use it on a different one.

Sekhar A. asked: "How are the mobile reports delivered? Is that via mobile browser / app?" Answer: Mobile Reports are delivered via browsers connected to the Report Server. The Browsers supported have increased to include IE10, Edge, Chrome, Firefox, Safari, and potentially any browser that supports HTML5. If your corporate Report Server is connected to the Internet, then phones and tablets can connect, as well as the traditional desktops and laptops, from anywhere on the Internet. You can also connect to your reports via the Power BI application that works on Apple, Android, and Windows phones.

Ryan B. asked: "Is the slide deck from this webinar going to be made available for download?" Answer: Yes, it is! Here is the link to the slide deck: http://pragmaticworks.com/Training/Details/Whats-New-in-SSRS-for-SQL-Server-2016

Cris B. asked: "What types of graphs are available. Do they include non-linear regression for example? Can you aggregate Group variables in SSRS 2016, e.g., Sum all subgroup variables at the parent group? This is needed if each subgroup must be calculated separately, and sometimes differently, but then need to be summarized at the parent group level." Answer: First, the graphs that are available are pretty simple – dashboard graphs, with trend charts are the majority of the graphs. The reports themselves do very aggregations other than a basic aggregation of source data – no subgrouping or other levels of hierarchy detail. The only drill through ability is to drill through to another mobile report that is parameter driven, so it’s a bit limited in its initial release. The majority of this feature is based on presentation of complex data, not actually deriving complex analysis. You could to the non-linear regression in the database or analysis cube, then present the results using the data sets to these presentation level charts and graphs. Remember that mobile reporting is not meant to replace desktop reporting applications, it’s meant to compliment those with simple data driven reports that give people some ability to report – but it doesn’t replace full-fledged analysis systems. Feel free to email me a follow up question if this doesn’t answer your questions.

Mark B., Shauna H., and Brian B. asked similar questions: "What versions of SQL Server does SSRS 2016 work with? Can you run SSRS 2016 with a SQL 2012 or 2014 source db?" Answer: According to the documentation, SSRS needs a SQL 2016 instance of the Server Engine to support the Report Server and Temp Report Server db’s. But, you can upgrade an older version of SSRS and it will still support older reports. You can read data sourced from many versions of SQL Server, but the engine itself must be installed in a SQL Server 2016 installation with the correct version for the Reporting Service database.

Minta B. asked: "The Power View, Power Query Power Pivot are still Ad-Ins which need to be download separately, or they are part of SSRS 2016?" Answer: PowerPivot, PowerQuery and PowerView are all desktop tools that run inside of Excel, and do need to be downloaded (depending on the version) and installed with Excel. They are not a part of SSRS 2016.

Neil B. asked: "When will SQL 2016 be publicly available?" Answer: SQL 2016 has not yet announced a general release date, but it is currently available in pre-release forms. You can download the latest version (RC2) from the Microsoft website here: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Celita C. asked: "Do we still have to use shared SQL authentication accounts when using subscriptions?" Answer: No. The subscription service can handle Windows accounts rather than SQL Authentication accounts, but this is not a new feature. What is new is the ability to use a “File server” account to store those credentials for a report that is stored on a file server. This is a pretty large topic, so here is the link to the Microsoft documentation for more information on subscriptions: https://msdn.microsoft.com/en-us/library/ms159762.aspx

Don C. asked: "Is SSMS 2016 required for SSRS 2016?" Answer: Mostly no, but maybe yes. You don’t need SSMS at all to run SSRS 2016 and build reports, you can use the Report Manager and the Mobile Report Builder. However, SSMS allows you to do more with the engine itself and to create SQL Scripts to manage many of the features in SQL Server, especially the latest features. You can do basic SSMS functions in older releases of the software, but I’d recommend updating SSMS if you are installing SQL 2016. However, it’s not “required” to run SQL 2016.

Kathy C. asked: "From what I understand, the version of SQL Server Data Tools that will get released when SQL 2016 is released, will support SSRS development for version 2016, 2014 and will run within Visual Studio 2015. Is this also your understanding?" Answer: Yes that is my understanding as well. I downloaded the evaluation version of Visual Studio 2015 to do my demo, and had played around a little with building reports using SSDT templates for SQL 2016. I just ran out of demo time to show any of that!

Tiffany C. asked: "Please follow-up on decreasing multiple relationships down to only 1 when creating the dataset, ex. your example where date related to 3 different relationships and wouldn't work. Thanks" Answer: Sure!!! Here is what happened: Using the Report Builder application, I had already created a connection to the AdventureWorksDW2016 database I had loaded earlier. I clicked on the option to create a new DataSet, and then used the GUI to select my tables. The tables I selected (FactInternetSales) has three Date fields, as shown in this image:  

The OrderDateKey, DueDateKey, and ShipDateKey all have foreign key relationships to the DimDate table in AdventureWorks. This allows validation of the DateKey for all three date fields. This metadata is used to automatically detect relationships between tables in the Report Builder. If you depend on the “Auto Detect” feature in the Report Builder, it will try and build a query where all three relationships are satisfied in the Join, as shown in this image: The resulting query that tries to load data will evaluate this query and try to join to the DimDate, but the query will never result in a match unless the OrderDateKey, DueDateKey, and ShipDateKey all have the same value. Effectively it’s doing one join when it should be doing three joins, one for each key. Here is the same query in text format:

SELECT   DimDate.DateKey
       , FactInternetSales.ProductKey
       , FactInternetSales.OrderDateKey
FROM DimDate   -- Notice only one join
INNER JOIN FactInternetSales
        ON DimDate.DateKey = FactInternetSales.OrderDateKey
       AND DimDate.DateKey = FactInternetSales.DueDateKey
       AND DimDate.DateKey = FactInternetSales.ShipDateKey

To resolve this query problem, I have to manually delete the relationships that are not involved in MY query. In my case, I choose to analyze data by the OrderDate, so I will delete the relationships for the DueDateKey and ShipDateKey. So what I had to do was expand the relationship editor panel, click on the Auto Detect button (to turn it OFF), then click on Edit to manually edit my relationships, as seen in this image:

By deleting (right click on the field highlighted and select ‘Delete’) the relationships that I don’t need for my query, I can remove them. IF for whatever reason I need to analyze on two copies of the date table, I would need to find an alternative way to write that query since the query visual editor can only join to the table one time. If you need help on this, feel free to follow up with me, and I can always write a more detailed blog post.

Nova D. asked: "We get a lot of requests to export to Excel and one of the issues we run into is merging cells when exporting, which makes sorting and filtering in Excel difficult. Do you know if we have more control over how much cell merging happens when exporting to Excel?" Answer: I doubt much has changed with the Export to Excel, but I couldn’t find any good references to this when I did my research. I might be able to follow up on this, if it’s a really critical situation feel free to set up a Virtual Mentoring session with one of us at Pragmatic Works to follow up on this!

Phil D. asked: "I'm having a hard time trying to find the Report Builder download." Answer: Here is the link! https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016 And here’s my tip: any time you are looking for downloads for SSRS, check the SSRS team blog, that’s how I find all of these links: https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/03/18/sql-server-2016-rc1-whats-new-in-reporting-services/

LaDeana D. asked: "Can we migrate 2008 and 2012 rdl files into 2016?" Answer: Yes! Read the top of this page regarding the conversion of rdl files to SQL 2016 https://msdn.microsoft.com/en-us/library/ms143674.aspx

Michele D. asked: "Can data sets be populated by a stored procedure?" Answer: Yes. As an example, I used the system stored procedure SP_WHO as the input for my dataset. Instead of using the graphic designer, I executed the stored procedure and it returned the values to my dataset. You can also use parameters to pass information to a stored procedure, but that’s a topic for a longer post!

Leigh Anne D. asked: "Does the new masking feature extend to when you export results of the report? Or is it limited to just the UI?" Answer: The masking extends to the export as long as you are using an external tool. One way to get around the masking is to do a SELECT * INTO a new table, that will ignore the masking. But as long as you are exporting your result, the masking will stay in place because the values are modified as the data is loaded into the presentation layer. I will remind everyone that this is not a SECURITY feature, it’s really designed so that your basic presentation layer or application layer tools will not see the data when it is sent to them. There are lots of ways to get around masking, so be careful when you use it – it’s pretty awesome for reporting, but just be careful.

Rick F. asks: "Will we hear how SSRS 2016 integrates with SharePoint?" Answer: Well, it was too short of a presentation to talk about SSRS with SharePoint. But what I can do is point you to some good info from Microsoft on that topic. Microsoft has quite a bit of documentation on this, here’s the place I’d start: https://msdn.microsoft.com/en-us/library/hh231671.aspx

Tyler F. asked: "Will we still have to resolve the Double Hop problem?" Answer: I think that depends on what you mean by “resolved”… there are ways to configure Kerberos to work with SSRS and handle the proper delegation of credentials. I am not a security person, but I found quite a few articles on the web regarding how to resolve the double hop problem. Again, if you need help with that, don’t hesitate to reach out to one of our security experts on a Virtual Mentoring call, and we’d be glad to help out.

Kevin F. asked: "Can you talk about how Report Manager gets replaced -- how one migrates an SSRS site using Report Manager to 2016?" Answer: Better than that, I can give you the Microsoft guide!! There are quite a few ways to handle a migration from earlier releases of SQL, including side by side and inplace upgrades. You should plan your migration carefully based on your specific needs, including your hardware, downtime, and application/user requirements. Each release of SQL Server usually comes with a full installation/release guide, I find those very helpful. You might also consider the Upgrade Advisor utilities that Microsoft usually releases with the General release of the software to help you figure out how your installation/upgrade will work.

Alex G. asked: "Are the "traditional" (rows and columns) reports printable with 2016? The current ones cannot be printed which is a huge problem for finance users." Answer: I’m going to give you the traditional consultant’s answer: It depends ;-) I have customers who print rows and columns reports all the time using SSRS without problems. But, they can have issues when a report is too wide, or has too many columns to fit on a page properly. A lot of the ability to print reports to paper depends on the report design. One of the report formats in the Report Builder is a tabular (rows and columns) report, so the answer is Yes, you can print them – but you can also make them unprintable.

Jeff G. asked: "Is there any specific support for multi-tenancy use cases in SSRS?" Answer: While I couldn’t find anything specific regarding multi tenancy for SSRS, I think the features are getting closer with custom branding, row level security, and other security features. It wouldn’t be a built in feature, but you certainly could create a multiple-tenant environment that kept the slices of data separate. Ping me if you want to talk more about this, and I can see if we can find more information.

Fred H. asked: "I am puzzled with the different versions of visual studio and accessing newer features of SSRS. Can I access SSRS 2016 features using Visual Studio 2012? What version is SSRS 2016 expected to use?" Answer: Don’t feel bad, it confuses me. Here’s what I am reading: To use the newest report templates and features, a new version of SQL Server Data Tools will be available for use with Visual Studio 2015. Here is more information on the SSDT compatibility and the link for the download: https://msdn.microsoft.com/en-us/library/mt204009.aspx. Also on that page is a link to previous versions, which will run side-by-side, for development in previous versions of Visual Studio. I hope that helps!!

John H. asked: "Does Kathy have Twitter?" Answer: Yes! You can find me on Twitter at @MsKathyV. I also have a blog site at http://mskathyv.com . If you follow me on Twitter, be sure to tweet me and let me know how you found me, that way I can follow you back! I have a bad habit of being more social on my photography than my SQL work, but I’m going to try this year to be more social about SQL!!!

Edward J. asked: "Can we combine KPI or graphs in rows & columns paginated report? For example, we have sales, expenses & revenues report by states or regions. So we need KPI & graphs to show the sales / expenses & revenues status for each state / region."  Answer: For Mobile Reports, it’s easy to use KPIs and Graphs, but it’s a bit more limited in Paginated Reports. If you don’t need to print the report, I’d suggest switching to the Mobile Report Designer to design reports that can take advantage of those KPIs and use better graphs. For the Paginated Reports, the designer only supports Column, Line, Pie, Bar, and Area graphs. You certainly can import data values that include KPI values (Value, Target, and Status), but the Mobile Reports use this data more effectively. Unless you need to physically print the reports, you should consider making these reports Mobile reports because of the wider variety of graphing and KPI tools available. If you do need to print – consider using .PDF files to print your web pages. Remember those Mobile Reports are being generated in the Web, and you can always print that information to a .PDF file.

Sonal J. asked: "Do you need to create a separate mobile version of your existing SSRS report or would your existing report just work as it is?" Answer: If you want to take advantage of the newer graphics and KPIs then you would be recreating that report as a Mobile report instead of a Paginated (existing) report. However, if you already have existing reports that have graphics or have the information you need, you can upgrade those reports to 2016, and enhance them. But as I said in the previous question, to take advantage of the newest graphics and KPI features, you’d need to build new Mobile reports.

Raghav K. asked: "What does the ‘Add to favorites’ option do?" Answer: On the Reporting Services portal, you can create your own Custom view of all of the things from the Main portal. So for example, if you only care about 3 KPIs and 2 Reports, you can “pin” those to your favorites and have fast access without having to search through what could be a very long list of assets on that portal page!! It works the same as your regular browser, and pins those things to the Favorites page so you can access them quickly.

Whew! That was a lot of questions. I should have Part 2 of this blog post up within the next week! Thanks for your patience! If I haven't answered your questions yet, it should be in the next post.