By: Koen Verbeeck | Comments (1) | Related: > Reporting Services Development
Problem
We hosted a webcast with the subject What's New for Reporting Services 2016 on October 26th 2016. There were a couple of questions for which we didn't have time left to answer, so this tip takes the opportunity to answer those questions. Below are the questions, along with their answers. If you still have questions left, you can submit them in the comments section. You can watch a recording of the webcast here.
Solution
Q: Of all the features, which one do you like the best?
A tough question, there are so many great features. If I have to choose, it’s the brand new reporting portal. It’s fast, it looks good and it uses HTML5, which means you can access your SSRS reports on multiple browsers! You can also customize the look ‘n feel using brand packages.
Q: What features do you still want to see?
Of course a good integration of Power BI Desktop on premises. It’s already in the works and a preview is available, so I won’t have to wait too long. This is a feature that a lot of customers are waiting for.
Personally I’d like to see a better handling of parameters and their prompts. Currently, the support is really minimal. You can organize the layout through the parameter pane, but that’s it. If you take a look at other products, such as Business Objects or Cognos, you’ll see those have very advanced prompts. Good calendar prompts, advanced search, you name it. The prompts there are also in a separate page or in a pop-up, instead of a tiny bar at the top of the report.
I’d also like the rendering of the reports themselves to be improved. For example, SSRS can be a bit weird when report objects overlap. I would also like to be able to add annotations for example.
Finally, I would like to be able to add a simple regression line to a line chart. With SSRS, you can add all sorts of calculated series to a graph, but a simple linear trend line needs to be added manually through code. In Excel and Power BI this is just a few clicks.
Q: When should you use SSRS vs. PowerBI vs. SSAS?
First of all, SSAS (Analysis Services) is more of a semantic layer between your data warehouse and your reports. So it’s not exactly a reporting tool, but more of a data source for SSRS or Power BI. You can connect directly to SSAS with Excel, but that’s just the same as you would be using Power BI Desktop.
So I’ll rephrase the question as: when would you use SSRS vs. Power BI vs. Excel?
If you want to do a quick ad-hoc analysis, I would probably use Excel. If you just need a simple pivot table, then Excel is a good choice.
Power BI can also be used for ad-hoc analysis, but its primary goal is to provide you with intuitive, easy-to-build interactive reports and dashboards. Anyone can build reports using Power BI, especially if SSAS is the data source since it’s simple drag and drop. Power BI is also very mobile friendly.
Reporting Services on the other hand is oriented towards developers. You typically use Visual Studio to build your SSRS reports and you need to write your queries yourself, unless you use SSAS as a data source. SSRS is less interactive and less mobile friendly. If you want to do special tricks, you need to use expressions a lot – using a style of Visual Basic – or use .NET code. Because SSRS reports are more static, they are typically used for actual reporting, such as profit/loss reports, balance sheets and so on. Personally I would rather use SSRS to build a detailed financial report than Power BI. SSRS reports are typically reports that you can print out on a piece of paper and hand over to your manager.
SSRS Mobile Reports are a bit special. They require less coding and they are also drag and drop. They are interactive and especially mobile friendly. So they are a bit like Power BI. However, Power BI is more flexible, since you can use custom visuals, R code and other tools. When Power BI can be hosted on premises, I probably will prefer Power BI Desktop reports over the SSRS mobile reports.
Q: Hello, can I use DAX queries for a report?
Sure. If your data source is SSAS Tabular, you can use DAX queries in your data set. The trick is to switch to the DMX designer instead of using the MDX designer. In the DMX designer, you can enter your DAX query. More info: Write DAX queries in Report Builder.
Q: Do you have to have any specific version of SQL Server 2016 to utilize all of the new capabilities? e.g. Standard vs. Enterprise vs. BI versions
First of all, there’s no Business Intelligence edition any more in SQL Server 2016. Using Standard edition, you can use most of the new features like the new chart types and the parameter pane. However, mobile reports and the KPIs (aka the DataZen technology) is Enterprise edition only. Also, using custom brand packages in the portal is also an Enterprise feature.
You can find more information on the MSDN page Reporting Services Features Supported by the Editions of SQL Server 2016.
Q: VERY heavy experience with Crystal Report - trying to add SSRS - study material recommendation for getting started?
You can follow some SSRS tutorials on the MSDN website. I looked at Microsoft Virtual Academy, but there are no real beginner courses over there. The following books might be worth a read:
- SQL Server 2012 Reporting Services Blueprints
- Microsoft SQL Server 2016 Reporting Services, 5th Edition
- Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports (yet to be released at the time of writing)
Q: If you have SSRS in a Power BI dashboard, will a Power BI slicer also work on the SSRS report?
No. The SSRS object which was pinned on the Power BI dashboard is independent of the other items. It’s basically a screenshot of your report.
Q: In the latest version can we disable phone-home feedback in SQL 2016 Developer, Express, and Evaluation Editions of SSRS?
I guess you mean the telemetry data that automatically gets sent to Microsoft? As far as I’m aware, it’s still not possible to turn this off in the free versions of SQL Server. If you want that, you can vote on this connect item.
Q: I found Visual Studio 2015 caused me to commit to SSRS 2016 with no backwards compatibility. Is that correct?
Technically there should be backwards compatibility. You have to make sure though you select the correct version in the TargetServerVersion property of the project. But, even if it is set to SQL Server 2008 R2, 2012 or 2014, it’s possible you get some issues. Tim Radney explains some of those in his blog post Issue publishing to SSRS 2012 with SSDT 2015. You also have to make sure you don’t use any of the new features of SQL Server 2016, which can be troublesome with the parameter pane.
Q: Am I correct that the new SSRS 2016 doesn't show the Report Description anywhere? I miss the old "List" view which shows a list of reports and their descriptions.
You are correct. Luckily, the List view will be added back in a future release. It’s unclear if this will be in a cumulative update or in a service pack. Hopefully we don’t have to wait till the next version of SQL Server. The following screenshot was taken from the PASS Summit in 2016, where the list view was being shown:
Q: You can not navigate to manage from the runtime version of report, unlike in report manager. Unless that has been changed?
I’m not exactly sure what this question is about. If you run a report, there’s no edit button so that you can quickly edit the report. I’m not sure this was available in Report Manager as well. If you mean you cannot open Report Builder for a specific report in the portal, that’s possible. When you click on the three dots in the upper right corner of a report, you can choose Manage. There you can choose to edit the report with Report Builder.
Q: Thank you for this presentation! I was wondering when MS would get things moving.
Thanks! I hope you enjoyed watching the webinar. I’m pleased as well that SSRS is improving once again.
Next Steps
- If you watched the webcast or the recording and you still have questions, you can always ask them in the comments. If you asked a question during the webinar and it is not featured in this tip, it's possible the question wasn't clear to me. Please use the comment section to clarify.
- Take a look at the MSSQLTips.com website to see which webcasts are planned: Free SQL Server Webcasts, Videos and Webinars.
- For more SQL Server 2016, read these other SQL Server 2016 Tips.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
