Chapter 17–Introducing Reporting Services Mobile Reports

This chapter is the first in a series of three chapters comprising PART IV, which includes coverage for these topics:

• Basic mobile report design approaches and applications
• Appropriate use of navigators, selectors, gauges, charts, maps, and data grids
• Advanced report design techniques with complex visual controls
• Filtering and interactions
• Report navigation
• How to use parameters for dashboard filtering
• User parameters for drill-through navigation
• How to drill-through to other reporting tools with URL paths and parameters

In the next four chapters, you become acquainted with the new mobile reporting and dash- board capabilities introduced in SQL Server 2016. We begin with an introduction to the features and capabilities, as well as a discussion about the best use cases for Mobile Reports. You learn more advanced applications as you progress through a series of exercises.

Additionally, you learn about the use for each visual control, which enables you to apply the knowledge and design simple reports to address specific business needs. Starting with the unique pattern of “design-first development,” you prototype report designs and capabilities, and furthermore, add datasets to support the behavior of those reports. We continue with a tour of sophisticated capabilities used to integrate reports into a complete business intelligence and enterprise reporting solution. You learn how parameters and expressions are used to pass selections and context to another mobile report, paginated report, or website.

This post is part of the series: Professional SSRS 2016 Book Preview Posts which are 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.  The goal for this posts is to provide useful and meaningful information you can use.  For more comprehensive details, I refer readers to the rather lengthy book itself.  Note that large portions are copied directly from the book manuscript that may refer to figures and screen capture images that have been removed for brevity.

Chapter 17 covers:

• Using Mobile Report Publisher
• Designing datasets for mobile reporting
• Learn when to use mobile reports
• Understanding visual control categories

The purpose of this chapter is to introduce Mobile Reports and the best use for different types of visual controls. We begin by comparing the capabilities of mobile and paginated reports, and then explore the essential building blocks of mobile report design. This chapter introduces each of the visual control categories and explains the best use of each control in a mobile report solution.

Having choices and options provides freedom to use different tools to create reporting and data presentation experiences for different purposes. Freedom and flexibility bring the need to make more decisions, and sometimes choosing the right tool is a trade-off between the strengths of one tool and the limits of another that is used to achieve different results. As I have used SQL Server Reporting Services and watched the platform mature over the past fourteen years or so, one thing became very clear: Reporting Services was primarily intended for and is optimized to be used in a web browser on a desktop computer. I have used previous versions of SSRS to create reports for smaller screens and mobile devices. It met the basic need to display information in a simple layout with bold graphics and text and with sufficient rendering fidelity and navigation, but it was not a truly modern mobile experience.

Responding to the proliferation of mobile devices used by business professionals who consume data and make decisions, Microsoft created multiple tools for the mobile professional.

A partner company in the Microsoft development community had created a mobile business intelligence report and dashboard delivery product called Datazen. Microsoft acquired Datazen from ComponentArt in 2015. Much like Reporting Services, Datazen was built on Windows services, ASP.NET web services, and had a server architecture very similar to SSRS, with the notable exception that reports were delivered client-side using installed mobile applications freely distributed in all the mobile device app stores. Going forward, the Datazen product will be the Mobile Reports feature of Reporting Services, managed entirely with the report server and Web Portal installed with SQL Server 2016.

THE MOBILE REPORT EXPERIENCE AND BUSINESS CASE

An important thing to understand about Mobile Reports is that it is not the same thing as conventional SSRS paginated reports, and one is not a replacement for the other. Mobile reports are simple and bold, optimized for touch on mobile devices. Secondarily, a mobile report can be used in a web browser and viewed on a desktop computer.

NOTE Shortly after Datazen was acquired by Microsoft in 2015, I had numerous conversations with consulting clients who were convinced that they could use it as a replacement for their reporting platform. Before the integration with Reporting Services in SQL Server 2016, Microsoft made it available free for SQL Server Enterprise customers. Those who thought they would replace SSRS and other operational reporting tools soon learned that Datazen, and now SSRS Mobile Reports, serves a specific purpose alongside paginated reports.

Using the Power BI Mobile app on my iPad, I can open a mobile report that resides on an SSRS report server. Figure 17-1 shows a simple mobile report running in my iPad using full screen layout.

The touch experience is very responsive. I can touch and hold on a data point to see large callouts with more information. The Time navigator allows me swipe and drag across a range of date values or tap to drill down to the next level. Tapping a country in the selector on the left immediately filters the chart and summary values. On my iPad or other tablet device, I see a simplified view of the report when I hold the device in vertical orientation, but if I want to see more details, I can rotate the device horizontally as shown in Figure 17-1 to see the full desktop layout. We explore this capability in the following chapters.

The same report can be opened in the web browser on a mobile device or desktop computer. Figure 17-2 shows a simple mobile report in the browser that I opened from the Web Portal on my server

FIGURE 17-1: A running mobile report in full-screen mode on an iPad.

FIGURE 17-2: A simple mobile report displayed in a web browser.

As you will see in the following chapters, you can use several visual controls to assemble more intricate and complex mobile report designs. However, the essence of effective mobile reporting is simplicity and ease of use.

The user experience is very similar to that on the tablet but with a slight delay, as filter selections and data are delivered through HTML from the web server. The report behavior is nearly the same, but some differences are noticeable between the immediate and tactile response in the mobile app and a bit of latency when rendered for the web.

On my mobile phone, the differences are not as subtle, where I have a smaller screen and less avail- able space. On this report, shown in Figure 17-3, the most important information is displayed in fewer visual controls that I navigate by tapping and swiping with my finger or thumb.

FIGURE 17-3: Report viewed in the phone app.

You can see that the controls are arranged differently and are simplified for the smaller screen. The experience of tapping, swiping, and holding a point on the screen is slightly different, adapted for smaller screen resolution and hand-held navigation. Some controls behave a little bit differently on each device because users are accustomed to using the native controls that are inherently part of the device operating system. For example, here on my iPhone, tapping the drop-down list displays the “slot-machine” vertical scrolling selector familiar to mobile iOS users.

Report Drill-Through Navigation

As you have already seen in my previous paginated report designs, I am a big proponent of report navigation. For report users to access more content, you can either put more on a report or allow the user to navigate to other reports to progressively reveal more detail or more specific context. Drill- through navigation is a strong theme in mobile report design.

We have less screen real estate on mobile devices and we have fewer intricate features in mobile report controls, so we can chain reports together to provide a rich navigation experience. One mobile report can navigate to another mobile report within the context of the filters and selected items on a report. Similar to paginated reports, each visual control can have a separate drill-through navigation target. For example, tapping a bar on a chart can send the user to a report filtered on the item represented by the bar in the chart. Additionally, a mobile report can navigate to a paginated report, which might be a better choice for displaying details and transactional source records, enabling users to see the numbers behind charts and aggregated totals.

When to Use Mobile Reports

Before we explore the capabilities of Mobile Reports and talk about what they are, let’s talk about what they are not. I find that having this discussion with consulting clients and students helps to define boundaries and simplify trade-off decisions between different tools.

I am not going to sugar-coat this message. The fact is, the Mobile Reports toolset is not as mature or feature-rich as conventional paginated reports in some respects, but it fills an important need in the reporting portfolio. When Datazen was a completely separate product, crafted by a different company, it was easy for me to accept that it employed a different design philosophy than anything from Microsoft. Now that it is part of SQL Server Reporting Services, the differences are more apparent; at the same time the fact remains this tool brings unique value and capability to the Microsoft BI and reporting platform. Do not get hung up on the differences; learn to use the two products together. To some degree, you will need to unlearn some techniques you have acquired for paginated reports in order to embrace different design patterns in mobile reports. That is just the way it is.

Having worked with the Datazen for a couple of years prior to the Microsoft acquisition, I have some specific thoughts about using this tool successfully. Specifically, it is important to use Mobile Reports in a scenario appropriately suited for the product. In that context, you can add capabilities and business value not previously possible. It should be no surprise that the Mobile Reports feature is designed to primarily deliver mobile reporting capabilities to business users in layouts and screen resolutions optimized for modern mobile devices. Simply put, do not cram a lot of detailed information on mobile reports. Keep them simple.

To appreciate the value of mobile reports, you need to use them on a mobile device. Because you design mobile reports on a desktop computer, when you preview and test reports, they may seem to be big and overly simplified. Mobile reports are optimized for small screens and for touch interaction.

As a long-time SSRS practitioner, I am accustomed to a reporting tool that handles a lot of the data grouping and aggregation work for me. Additionally, I am accustomed to using expressions extensively, and these are simply not features of this tool. Rather than wrestling with the design tool, use queries to shape the data.

Mobile Reports Are Not Self-Service BI

Although the fundamental report design experience is not that difficult, data preparation is necessary. Most reports require queries to be written so data is shaped correctly for different visual controls. Query design and preparation requires technical expertise. Although the actual report design effort is relatively simple, this task is typically performed by a report design professional. Users navigate and interact with reports but not in an ad hoc fashion, as they would with Power BI or Excel.

In particular, some visual controls do not group and aggregate the rows in a dataset so you must create a dataset specifically suited for that control. You still have the flexibility to present data in the right shape and format but you may need to create multiple datasets rather than relying on groups and expressions like you would in paginated report data regions, pivot tables, or Power BI visuals.

Mobile Reports Are Not Paginated Reports

Mobile report visuals are prepackaged with properties and layout options that adapt to their size and placement on the page. Consequently, they do not have properties that can be fine-tuned and modified when the report is designed in the same manner as paginated reports. As a long-time SSRS report designer, I am accustomed to tweaking and adjusting properties to make a report look the way I want. This has always been a time-consuming and tedious process, but it is the way the tool works. By contrast, mobile report visuals have very few styling properties, so they take far less time to design but you do not have fine control over the styling and layout of each control.

Mobile reports are not intended for printing or exporting to a file for consumption. Operational reports like transaction lists, balance sheets, and contracts are best designed as paginated reports.

Cached and On-demand Results

The primary design philosophy behind Mobile Reports is that a dataset produces a static result set that is cached and then filtered on the client. Cached dataset results may be scheduled to refresh at regular intervals or can be manually refreshed on-demand. This results in faster report execution and supports an interactive experience within the limits of the cached dataset. As with virtually any scheduled query execution, queries do not run in the user’s security context so cached datasets are not always ideal for user-specific filtering and security.

Furthermore, datasets can be parameterized to run live queries, but some interactive features, such as navigators and selectors, are not supported. Oftentimes, the best way to combine interactive mobile reports with on-demand capabilities is to build at least two different reports with navigation actions from one to the other. The first report uses a cached dataset with interactive selectors and navigators to query aggregated data and the second report uses query parameters to return live results.

CONNECTION AND DATASET DESIGN BASICS

The Mobile Report Publisher, which is the design tool for mobile reports, does not include a query design tool. Queries are designed using either an SSDT report project or Report Builder to publish shared datasets to your report server. This requires some planning and iterative design.

I will demonstrate an effective design pattern you can use to build datasets to support mobile report features.

Shared datasets have parameters used for filtering data, and can be used to run live queries against data sources or to cache data for more responsive report performance. Because mobile reports on a mobile device run client-side using installed mobile report viewer applications, data can also be securely cached on the device to improve performance and support offline reporting. When a user is online, cached datasets can be refreshed with newer query results.

INTRODUCING MOBILE REPORT PUBLISHER

One of the first impressions you are likely to have when you use the Mobile Report Publisher for the first time is that it has a different look and feel from SSDT or Report Builder. This is primarily because this product was designed with a “mobile-first” mindset. The original Datazen Dashboard Publisher was developed when Microsoft released Windows 8 and fully embraced the “Metro” or “Modern” user experience. In the same way that Windows 10 brought users back to a more familiar desktop Windows behavior, Mobile Report Publisher has a more conventional desktop feel with modern Windows styling.

NOTE We hear the term “modern” used freely to describe new tools and user experiences. Modern is a very relative term and what is considered “modern” by our standards today will probably have a very different perception in a short time, and might soon be considered “legacy.” The mobile report product will undoubtedly change quickly to adapt to a changing user community, newer devices, and the fashion of the industry. It will also likely maintain the title of a modern reporting tool through these adaptations. In short: be prepared for changes.

Mobile Report Publisher consists of four pages that are accessed using the large tabs on the top-left side of the report design window shown in Figure 17-4.

FIGURE 17-4: Pages on Mobile Report Publisher.

Layout View

Layout view is where you arrange controls and set visual properties for those controls. Drag-and- drop controls from the toolbox on the left to the design grid in the body of the report. The grid dimensions are defined using two sliders at the top of the designer and to the right of the report title.

When you initially add a control, a table of simulated data is automatically generated so you can preview the control with data values. This “design-first” approach is a radical shift in report design, and can be a marvelous rapid-design tool for testing prototypes and getting quick user feedback. You build an entire mobile report using this approach in Chapter 18.

Data View

Data view is where you wire up the controls to datasets and set properties related to data consumption and field-mapping. Each control has a unique set of data properties that enable it to group, aggregate, and filter data. First, datasets are imported so they become available to the controls. After controls are added to the grid in the Layout page, you can select each of those controls on this page so you can set the data properties.

Dashboard Settings

Use the Dashboard Settings page to set properties related to the report name, deployment destination, dates, and regional formatting. The metadata you set on this page is saved into the deployed report or file on the file system that affects the behavior of certain controls. The Currency property applies regional formatting to the report. The Fiscal year start, First day of the week, and Effective date properties all modify the behavior of Time navigators and time charts. In the US and without needing to deal with fiscal date reporting, I can leave these properties with default settings, but you should set them according to your needs.

Preview

The Preview page is intended for testing your report in the design environment. It approximates how the report will appear after it has been saved and run from the server.

Using the icons on the toolbar in the top-left hand side of the window, you can save locally or publish to server. Figure 17-5 shows the Layout page with the design controls and features used to design a report.

TIP Saving copies of a report is fairly easy but be careful as you save different version if the report locally and to the server. I advise saving a master copy locally as a backup. Continue to save changes as you go and then save the same version to the server. By keeping these copies in sync, you will always have a backup and there will be no confusion about having old and new versions of the report in different places.

Use the small icons in the top-left toolbar to create a new report, open, save, and connect to a report server. You can save reports to a folder in the file system or to a report server much as you would any document file.

Using the layout selection drop-down, you can create an alternate layout for tablet and phone devices after designing the report in the master layout, which is optimized for a desktop or tablet display in landscape orientation. If no device-specific layout exists, the mobile app will make a best- effort to fit the master layout controls to the device in the order they are arranged.

You can resize the grid in any layout. The default grid size for each of the layouts is as follows:

• Master is optimized for horizontal layout, 6 x 12 max grid
• Tablet is designed for rotation, 8 x 8 max grid
• Phone is designed for vertical layout, 6 x 4 grid

The tablet layout is for a tablet device rotated to portrait orientation. Generally, it’s a good idea to keep the grid dimensions relatively close to the defaults and then make adjustments to fit the selected visual controls.

• New report
• Open report
• Save, Save as
• Server connections
• Grid dimension slider

• Layout selection drop-down
  (Master, Tablet, Phone)
• Style selection drop-down
• Visual control toolbox
• Visual control settings
• Report design grid
• Visual control resize handle

FIGURE 17-5: Mobile Report Publisher Layout page.

Visual properties panel

The style selection drop-down includes thumbnail images of each of style defined in the site branding theme. Style colors and other properties are dynamically applied to reports if the site branding theme is updated.

VISUAL CONTROL CATEGORIES

On the left side of the page, controls are organized into the following categories, which are explained in the following sections:

• Navigators
• Gauges
• Charts
• Maps
• DataGrids
• Navigators

The Time navigator is essentially a dynamic column, area, or line chart grouped by a selected date or time hierarchy. It also specifies a set of time members that filter another dataset. You can specify the valid date parts for inclusion in the drill-down tree. For example, I chose a year that presents all the month periods for that year; the chart will then visualize days at the next level. In addition to selecting a single value to move down the hierarchical tree to the next level, you can swipe across or hold the Shift key to specify a range and select multiple days (or any other date part). Figure 17-6 shows controls in the Navigator category.

These controls are used to filter the data displayed in other controls:

Time navigator—Displays a range of time/date values. It supports years, quarters, months, days, hours. This control requires a column of date and/or time type values. Using the first date/time column in the dataset, it auto-generates each date/time level value in the range and doesn’t require a date lookup or “dimension” reference table to fill in any missing values. The Time navigator supports multiple metric fields that are visualized as a time-series chart using a column chart, stepped area, or line chart. You see examples of the Time navigator beginning in the next chapter.

Scorecard grid—Combines a selection list with a multi-field value KPI scorecard. This is a versatile control that groups and aggregates column values for a dataset. Optionally, a second table can be joined with a pair of key values and used for aggregation and comparison purposes. The selector functionality works exactly like the Selection list control.

Selection list—Groups like-valued rows in the dataset based on a single column and presents them in a list for selection. The selector supports single-select, multi-select, and an additional item at the top of the list titled All. When the All item is selected, the selection list is effectively not used to filter data.

Depending on the mobile device and the available screen real estate, the selector is displayed either as a scrollable list box or as a compact drop-down list control.

For the following examples, I have used the dataset shown in Figure 17-7 as the source table for a number of controls.

FIGURE 17-7: The dataset shown in the Data page.

Figure 17-8 shows an example of a Scorecard grid and a Selection list using the same set of data. Note that both of these controls present consolidated Category values rather than the repeated rows you see in the source data.

FIGURE 17-8: Grid and Selection list controls.

Gauges

This group of controls displays a single numeric field value rather than multiple values. The gauges shown in Figure 17-9 aggregate a numeric column into a single value and can consume a single row or multiple rows.

FIGURE 17-9: Gauge type controls.

Number control—Displays a single value with no target. This is the only gauge control that takes only one field. Like other controls, several named formats can be used to display numeric values.

All of the other gauge controls display a main value compared to a target value, and these two field values can come from one or two different datasets. These controls essentially do the same thing, presenting the main, target, and comparison values using different visual metaphors. For each control, the comparison of main value and target can be expressed in one of three different ways, using the Delta label property:

• Percentage from target
• Percentage of target
• Value and percentage of target
• Charts

Abbreviated topics contained in the remainder of the book chapter:

The Time chart and Comparison time chart are similar to Time navigator but are not used for selection and filtering. Additionally, they support more time units including auto and decade levels, which are derived from a date or date/time type column in the dataset.

These chart types are used to segment a numeric field by a category field:

• The Category chart
• Comparison category chart
• Waterfall chart
• Funnel chart
• Tree map

These charts are flexible and can be used to segment or group a measure value by a category or to compare multiple field values:

• Totals chart
• Comparison totals chart
• Pie chart
• Funnel chart

Category and Totals charts both group the Category field values and display the aggregated Sales Amount totals. The main difference between the two controls is that the Totals chart could also be used to show different measure field values side-by-side rather than grouping on the Category field. On the bottom row, the Comparison category and Comparison totals charts are essentially variations of the first two charts that show and compare the main and target values.

• Category chart
• Totals chart
• By columns
• By rows
• Tree map control

Maps

The map visualizations in Mobile Reports are quite simple. A map consists of multiple named shape definitions. Internally, each shape is really a series of points used to “connect-the-dots” and form boundaries. Multiple shapes fit together like a jigsaw puzzle to create the map. Maps are based on the de facto industry standard developed by the Environmental Systems Research Institute (ESRI), a well-known producer of Geographical Informational Systems and mapping software.

Gradient heat map

Range stop map

Bubble map

A small collection of maps is provided with Mobile Report Publisher and many other maps are available. I have collected several useful maps and provided them with the book download files. For legal reasons, Microsoft does not provide maps of many world countries. Because boundaries can change, any maps provided with the book downloads (or from any other source) should be verified and updated if necessary.

Standard maps consist of two paired files. The shape file (.shp) contains the boundary definition for all of the shapes in the map, and the dBase data file ( dbf) contains the shape names and keys.

TIP A reference table of map shape names is included in the database provided with the book download files. This will help you map shape names to the geography regions in your data. I’ve included shape names for the installed maps and those provided with the book download files.

Data Grids

Three data controls are used to display detailed information in a grid layout, along with several enhancements:

Simple Data grid—Useful when you have multiple rows and columns to display in columnar form—straight and simple. Data is based on one table and displays selected fields. This is the closest we get to transactional reporting in Mobile Reports.

Indicator Data grid—Based on one table but it displays selected fields as either columns or indicators/gauges.

Indicators require two fields for metric and target/comparison.

Chart Data grid—Supports features of Indicator DataGrid and is based on two tables with matching key fields. The second table is used for the category chart.

In the book, this chapter includes comprehensive coverage for these abbreviated topics.