With SQL Server 2016, as most of us are aware, there are numerous features that bring some amazing functionality to end users and really strive to complete the end-to-end user experience. Microsoft has improved all key areas of the BI stack over the last decade by leaps and bounds up through SQL Server 2014…..with the exception of SSRS. Yes, there have been improvements, but they’ve been minor. In my humble yet honest opinion, Microsoft has fallen short of meeting customer expectations in this area. Their visualizations were incomplete, the user experience was a bit clunky, and performance was never terribly great.
There were constant hurdles to overcome when using SSRS. Personally, I would snapshot everything, cache as much as possible, automate delivery during off-hours and do my best to have SSRS display reports and do nothing more because performance seemed sub-part. All of my stored procedures would do all of the heavy lifting on the database side, and the majority of my “report development” time would be spent on the data architecture piece. SSRS would simply render and display the data.
Fast forward to February 4, 2014…..the day Satya Nadella was appointed CEO of Microsoft and the exact day that Microsoft began the path toward data and cloud leadership. He has done more for this company than imaginable and brought it from the brink of obscurity to the industry(ies) leader. He has accepted the shortcomings that the product stack has had and began planning to overcome each and every one of them.
Microsoft knew that the user community wasn’t terribly fond of SSRS, licensing was clear evidence of this. Tableau was the recognized market leader with their flashy visualizations and that “shiny new object” appeal. Rather than trying to build in-house with personnel that may not have had the creativity nor vision, they went after a company that clearly illustrated visualization leadership, Datazen.
What came next was a combination of the great visualizations of mobile reports (and still including their paginated reports) with the one piece of SSRS that was stable and highly respected, the security layer.
The Business Case for SSRS 2016 and Mobile Reports
So the obvious question here is, “Well I already have SSRS, why do I need this?” Or “I already have Tableau, Cognos, or some other reporting platform. Why would I want to switch to this?”
So for me, the above image makes one, and possibly the most important [particularly among leadership], aspect crystal clear…..consumability….the ability to consume reports on ANY devices ranging from your PC to your tablet to your mobile phone, all of which have a level of control allowing you to maximize your screen real estate.
Business insights through rich visualizations on any device. Native apps for Windows, iOS and Android
- Access your data from anywhere
- Touch optimized data exploration and perfect scaling to any screen form-factor
- Collaborate with colleagues on the go
- Acquisition of Datazen
- Datazen Migration Assistant (for those that currently ues Datazen)
- New Web Portal
- Updated Report Builder
- Mobile Report Publisher
- Higher DPI
- Printing (no ActiveX)
- HTML 5
- Visual Studio 2015
With all of this stated, it’s important to understand…..
Mobile Reports vs Traditional (Paginated) SSRS Reports
SSRS 2016 Overview
Now that we have a decent understanding of the inception of SSRS 2016, as well as what mobile reporting is, let’s dive into what SSRS 2016 IS and how Microsoft came to the conclusions they did by looking at the roadmap below. As we can see, the yellow circled items reflect SSRS 2016, but there’s an entire ecosystem of business intelligence that includes Power BI, mobile apps, Excel, etc.
SSRS 2016 also includes the following improvements:
- HTML 5 Rendering Engine
- Cross browser compliant (Edge, Explorer, Firefox, Safari, etc.)
- PDF Replaces ActiveX for Remote Printing
- No ActiveX controls required (security hole)
- Modern paginated reports
- New, modern styles for charts, gauges, maps and other visualizations
- File Export
- XML, CSV, PDF, MHTML, Excel, PowerPoint, TIFF and Word *(advanced features)
- Custom Branding
- Customize the web portal logo and colors by using a branding pack
- Create data-driven and contextual KPIs in the web portal, including the ability to drill-through to more detailed reports (mobile, paginated, etc.)
- Save your daily/favorite content, including KPIs, Mobile/Paginated Reports, and PBI Reports
SSRS Web Portal
One of the great improvements is the addition of the “Web Portal”. I generally change the URL to reflect this nomenclature as well so it becomes http://localhost/WebPortal/. I don’t follow the traditional “reports” nomenclature because it’s so much more than that now. It’s a “one stop shop” for all of your reporting needs, from data sources to data sets, to KPIs to mobile reports, to paginated reports, to Power BI reports (to be discussed later), to any other file of your choosing (similar to SharePoint repository).
Similar to SharePoint and modifying the cascading style sheets (CSS), users can modify some of the basic items such as the logo, title, title colors, background colors, etc.
A brand package for Reporting Services consists of three items and is packaged as a zip file, which must then be uploaded to SSRS in the Branding section of the Settings page.
Once at the Power BI Web Portal, users have the ability to add their “every day” items to their list of Favorites. This way, every time they log into the portal, their Favorites are displayed only. This helps to overcome the overbearing directory structure that is traditional seen in SSRS. Users don’t need to remember which items reside in which path, they simply need to “Star” it and it’s now on their home page at their convenience. There is still the ability to search for specific reports, but I would only use that when you are unsure of what you’re looking for.
How to Create SSRS 2016 Mobile Reports
Before we discuss how to create SSRS 2016 Mobile Reports, let’s quickly review the evolution of the tools from SQL Server inception to current day.
As we can see, some tools have come and gone, while one has remained relatively stable and that’s Visual Studio. This tool will still be the primary and recommended development tool for standard (paginated) SSRS reports. Assuming most have used this tool and written SSRS reports, we’re going to focus on SSRS 2016 Mobile Reports, and the new tool we use to create them.
SQL Server Mobile Report Publisher
Install the Mobile Report Publisher tool to create mobile reports. Use this SQL Server Mobile Report Publisher link to download the tool.
This application requires .NET Framework 4.5 (or later) and Visual C++ Redistributable for Visual Studio 2012 (x86).
Once installation is complete, fire up the Mobile Report Publisher!
You’ll immediately notice a completely different UI compared to both the classic Report Builder and Visual Studio. There are 6 basic settings you need to understand in order to create reports.
- Layout (Navigators, Gauges, Charts)
- Data (Add, Refresh, Export)
- Report Settings
- Grid Control
- Layout Control
Before we dig into more detail, let’s decide which approach we want to take when creating our mobile report.
In the design-first approach, create a mobile report layout first without importing any data. This is a good way to create a mobile report when unsure that the data is formatted correctly. Without real data, gallery elements are automatically bound to generated simulated data, which can be exported and used as a template to describe the required data.
The data-first approach is to import all required data first, then design the mobile report and set data properties on the mobile report elements. This has the advantage of being able to connect each element to real data when added to the layout. When using a data-first approach, be sure that the real data is formatted correctly for use with Mobile Report Publisher.
My personal preference is the Data First approach. I like to understand how each and every role of user is going to interact with the data, at which level of granularity, etc. Then once that’s established, I create data sets at the lowest level first, then aggregate up so each and evern layer of reporting can be satisfied (detailed level [paginated], mobile report, KPI, etc.)
I feel that this process is akin to building a house. The foundation is your data architecture, the windows, curtains, doors, etc. are your reporting solution (final and decorative pieces), while your framing and walls are your data strategy which helps combine the two.
Once you’ve selected your approach, create a data source and a data set with either the standard Report Builder or Visual Studio. This is a pain point I hope they fix soon, because as of now you cannot create a data source or dataset with the SQL Server Mobile Report Publisher.
Proper Chart Types
Once you’ve created both the data source and data set, select the proper chart type. There is some overlap with the standard SSRS paginated report chart types, so select the one matching the type and granularity of data. The chart below should be helpful.
Mobile Specific Chart Types
For chart types specific to the mobile reporting component of SSRS, reference the following chart to follow best practices and recommendations.
Number of Charts per Layout
Once you’ve decided which chart type to use, click and drag it to the grid. You can then resize, change the data source, add drill through actions, etc. from there. To maximize the user experience, be sure to configure each report size with a specific layout. Screen real estate is tricky. Too much is overpowering, while too little leaves something to be desired. Consider putting more report parts on the computer layout, with less on the tablet, and the fewest on the mobile. Prioritize according to chart importance and insight value to the anticipated audience.
Once this is all completed and your high level approach is completed, dig into the details with the Layout, Data and Settings tabs to specify the following information:
- Number Format
- Data Structure
- Drill-through (URL and parameter)
- Aggregations (Sum, Avg, etc.)
- Currency (USD, EU, etc.)
- Date Information
We won’t dig into all of this, or specifics around mobile report development, as the intent of this was to demonstrate the overall value and capabilities of SSRS 2016 Mobile Reports.
One final thing to note is that there is an option to Preview the report similar to Report Builder and Visual Studio, so be sure to use that before you deploy (save) to the SSRS 2016 server.
SSRS 2016 and Power BI
Many people mistakenly think that SSRS 2016 and Power BI are the same thing, but in my opinion, they are completely different. The functionality, the licensing, etc. are all different. The only things they have in common are that they’re reporting solutions (geared to different audiences), and they play well with one another.
I’ll be posting another blog in the coming weeks comparing and contrasting the two, as well as possibly leveraging BOTH!
The current version (as of now is SP1) currently supports uploading Power BI files, but they function similarly to SharePoint and any other website. When the user clicks on the file in the download bar, your browser detects the MIME type and opens the associated thick client (if available), in this case Power BI Desktop. SSRS 2016 currently DOES NOT render Power BI reports. This means that each and every person that would like to use the Power BI files MUST have the Desktop application installed. There is a workaround where you can create a HTML page that has a iFrame embedded that you can get from the Power BI portal, but bear in mind this isn’t truly an on-premises solution. The report and data (usually) is still coming FROM the Power BI portal.
However, with all of that said, the crowd has been clamoring for an on-premises solution for Power BI. Previously, Microsoft’s answer would be SSRS 2016…..and it looks like it still is.
SSRS 2016 Technical Preview
Currently in technical preview, Microsoft has answered the call and will support Power BI on-premises. SSRS 2016 Technical Preview can currently RENDER Power BI reports, not just store them. Here are a few important points on the current state.
- On-premises solution to Power BI Sites (Cloud)
- Create reports with Power BI Desktop
- Connect “live” to Analysis Services models – both Tabular and Multidimensional (cubes)
- Visually explore data and create an interactive report
- Save reports to SSRS 2016 Technical Preview
- View and interact with the report in your web browser
- Same version of Power BI Desktop
So it looks like this may be released with SQL Server 2016 SP2, which would be a great addition. It’s still a mystery as to which features will be supported and which will not. Things like the Natural Language, Quick Insights and dashboards probably will not, while a myriad of additional data sources and drill-throughs probably will. This is all speculation, but time will tell and there are exciting times ahead!
To see/hear the full Allegient webinar, be sure to check it out here!