Chapter 18–Implementing a Mobile report with Design-First Development

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.

• Using design-first report development
• Creating and using shared datasets
• Using Time navigators
• Using Selectors
• Using Number gauges and charts
• Applying mobile layouts and color styling
• Deploying and testing a completed mobile report

The purpose of the exercise in this chapter is to introduce mobile report design from start to finish. Using a very simple mobile report scenario and sample sales data, we step through a simplified example exercising the design-first report development pattern.

DESIGN-FIRST MOBILE REPORT DEVELOPMENT EXERCISE

To compete all of the steps for this exercise, you need the following:

SQL Server 2016

Native mode report server

Samples and exercise projects used in earlier chapters

Mobile Report Publisher

Optionally, a mobile device (tablet or phone) with the Power BI mobile app installed

Create a new mobile report from the Web Portal by following these steps:

1. Navigate to your Reporting Services Web Portal.

If your report server was installed with default settings, the address is http://myreportserver/ Reports (where myreportserver is the name of your report server).

2. Click the Browse icon in the toolbar to show the contents of the Home folder rather than your Favorites.

3. Use the “+New” (or “+”) menu to add a folder named Sales Reports

4. Navigate to the Sales Reports folder.

5. Use the “+New” menu to select Mobile Report (Figure 18-1).

NOTE When the browser window for the Web Portal is wide enough to show the entire toolbar, the menu items are displayed with the icons along with a short description (for example “+New”) but when the window is smaller, the menu items are compacted and only show the icons like you see in Figure 18-1. In the figure, the “+New” menu is displayed as simply “+”.

FIGURE 18-1: Web Portal with compact menu items.

The Mobile Report Publisher opens or you are prompted to download and install it. If the Report Publisher has not been previously installed, use the link shown in Figure 18-2 to install the application. If Mobile Report Publisher does not open a few seconds after installing, choose the Mobile Report menu option again.

FIGURE 18-2: Web Portal after selecting “Mobile Report.”

The Mobile Report Publisher, shown in Figure 18-3, includes toolbar icons and tabs with larger icons, used to navigate between pages.

Common file management options listed on the toolbar include:

Create new mobile report

Open an existing mobile report

Save mobile report

Save mobile report as . . .

Connect and manage server connections

Larger icons on the tabs displayed on the left side of the Mobile Report Publisher design window include:

Layout

Data

Settings

Preview

6. Click the Settings icon to open the Settings page for the new mobile report.

7. For the Report title, enter the text Sales Summary by Country and Category, as shown in Figure 18-3.

FIGURE 18-3: Mobile Report Publisher—Settings page.

8. Click the “Save mobile report as . . .” icon on the toolbar to display the options shown in Figure 18-4.

TIP Before the report is saved the first time, both “Save mobile report” and “Save mobile report as . . .” icons will perform the same operation, prompting for the location to save the report. After that only the “Save as . . .” option will prompt for a new location.

FIGURE 18-4: “Save mobile report as” destination options.

9. Click “Save to server” (Figure 18-5), navigate to the Sales Reporting folder on your report server, and then save the report.

FIGURE 18-5: Report server and location properties.

10. Switch to the Layout page using the left-most tab in the upper left-hand side of the designer. As you can see in Figure 18-6, a grid is displayed on the report design surface. You can drag and drop visual controls from the panel on the left to any cell in the grid. After you drop

a control, use the handle displayed in the bottom-right corner of the control to resize it, stretching it to the right or down, to fill as many cells as you wish.

FIGURE 18-6: Report Mobile Publisher—Layout page.

You can change the number of grid rows and columns using the sliders in the top right-hand side of the designer. Next to the row and column sliders are drop-down lists used to select different mobile device layouts and color palettes. You’ll use these a little later on in this exercise.

WARNING Experienced SSRS report designers are inclined to change the grid rows and columns, adding as many controls as possible. Before you give in to this urge, consider this: the purpose is to design mobile reports optimized for small screens and touch interaction. When viewing a mobile report on a desktop computer, the report might appear overly simple if it has unusually large controls. For this reason, be sure to test mobile reports on a phone or tablet device to ensure that you are providing the best mobile user experience and screen layout.

Before you start adding controls to the mobile report, let’s review the high-level report requirements:

NOTE Normally, the high-level business and functional report requirements are gathered by interviewing business user stakeholders. The tenets of design-first report development allow you to start designing with only functional requirements and perhaps a modest understanding of the available data.

Mobile users should be able to select a date period (years, months, or days) or any range of date periods to see aggregated sales metrics.

Users should be able to select the country, or any combination of countries, to see the aggregated sales for those countries within the selected date period range.

The total sales amount, freight cost, taxes, and unit order quantity should be displayed as abbreviated and correctly formatted values.

The sales amount should also be visualized to compare totals for each product category, filtered by the selected date periods and countries.

Add Visual Controls

Now that you have a basic understanding of these requirement guidelines, you can add navigators, selectors, gauges, and other controls to the report:

1. From the Navigators group at the top of the controls panel, drag the Time navigator to the first cell (top-left corner) in the mobile report design grid.

2. Use the resize handle in the bottom-right corner of the Time navigator to resize the control to fill all the columns in the first row of the grid.

3. With the Time navigator selected, use the Visual properties panel displayed below the mobile report designer to update properties with the following tasks, using Figure 18-7 as a guide:

a. Use the Time levels drop-down list to select Years, Months, and Days.

b. Use the Time range presets drop-down list to deselect all items except All and Last Year.

FIGURE 18-7: Time navigator Time level presets.

4. Leave all other properties set to default selections.

5. Drag the Selection list to the left-most cell below the Time navigator and then resize it like Figure 18-8.

FIGURE 18-8: Setting properties for the Time navigator.

6. In the Visual properties panel, change the title to Select Country.

7. From the Gauges control group, add four Number gauges as shown in Figure 18-9.

8. Resize each gauge to be two cells wide.

9. Change the Title for each of the gauges to:

Sales Amount

Freight

Tax Amount

Order Quantity

10. Set the Number format for the first three gauges to Abbreviated currency.

11. Set the Number format for the Order Quantity gauge to Abbreviated.

12. From the Charts control group, drag the Category chart and resize it to fill the remaining space on the grid. Use Figure 18-9 to verify the chart properties.

13. Change the Title to Category Sales.

FIGURE 18-9: Category Sales chart added.

Preview the Mobile Report

View the report as it will appear when deployed to the server, and then test the control interactions.

1. Click the Preview icon on the right-most tab to run the report with simulated data.

As you see, simulated data is generated with values appropriate for each visual control. A range of date periods is generated with several years of usable date periods up to the current date.

Remember that this is a simulation of a mobile touch screen interface. On a tablet device, the large controls on this mobile report are better suited for touch navigation and may be presented a little differently so they are familiar to users accustomed to using different devices. If you are using a touch screen computer, you can use the screen to navigate. Otherwise, use the mouse.

2. Touch or click a year on the Time navigator to drill down to the months for the selected year. Note that the number gauges and chart filter values apply the data filtered for the selection.

3. Click and hold or swipe and drag across a range of months to select sales for a given range.

4. Click or touch an item in the Select Country list to filter the values even further. See

Figure 18-10.

FIGURE 18-10: Sales Summary by Country and Category report with selected Country.

What you have experienced is that by simply adding visual controls to the report, simulated data is generated that can be used to interact with all the controls.

NOTE Don’t underestimate the impact of this simple but powerful feature. Using design-first mobile report development, you can easily demonstrate functionality, report controls, and layout ideas with simulated data. Use this approach to demonstrate design concepts and to get user feedback.

This report is a very simple example that demonstrates the power of design-first mobile report development.

Add Data to the Report

With a working report using automatically generated simulated data, you can now replace the simulated datasets with real data:

1. Switch to the Data page and note the two datasets named SimulatedTable and Simulated FilterTable. The presence of the two datasets suggests that the best dataset design approach for this report might be to create two different datasets.

TIP The criteria for using a single dataset or multiple datasets are primarily based on the requirements of each control, and the interactions between controls. Use different datasets when one selector or navigator should filter a dataset.

In this example, a single dataset can be used for the Time navigator and all the visual controls on the report, except for the Country selector. We don’t want the Country selector to be filtered by the Time navigator so it should be driven by an independent dataset.

NOTE Some controls require a dataset with records grouped at the appropriate level of detail to support the visual. Other controls, which we explore later, also require two datasets correlated using matching key columns.

We will review the data requirements for all of the visual controls a bit later. The controls on this mobile report can consume datasets having various levels of detail. With an under- standing of how these controls work and the data structure required for them to work, you can create the necessary dataset queries.

2. Review the two simulated datasets shown in Figure 18-11. The SimulatedTable has a column of date, time, or date/time type values.

FIGURE 18-11: Data page with simulated datasets.

3. Review the controls in the mobile report, shown in Figure 18-12.

Visual controls on the report.

Referring to the callout areas in Figure 18-12:

Callout area 1: Time navigator—A Time navigator is the combination of both a date or time period selector and a categorized chart. It will generate (depending on the Time levels property selection) a contiguous range of date or time periods between the earliest and latest date/time values existing in a dataset. Unlike a date dimension table, a contiguous range of dates is not required in the query result. The Time navigator will actually fill in gaps left in the data and create all of the date or time periods for the range.

Callout area 2: Country Selection list—A Selection list control simply displays one item per row and does not group or aggregate values. A separate dataset is commonly used to drive this control and to avoid having items on the list filtered by other selector or navigator controls in the report.

Callout area 3: Number gauge controls and Category chart control—A gauge control simply aggregates all the row values for a numeric column without any kind of grouping. Due to its versatility, gauges often share datasets that can be optimized for more demanding controls. If the dataset returns one row, it displays the value of the column. If the dataset has any number of multiple rows, it rolls up the values in the column using a specified aggregate function.

Not all mobile report chart controls play by the same rules and behave the same way. The Category chart you used in this report is similar to charts in paginated reports. It consumes a dataset with multiple rows, groups all the records on a specified field value, and then aggregates numeric columns of the detail rows within the groupings.

How many datasets do you need for this mobile report?

The Time navigator can consume a detailed dataset at most any level of detail as long as it includes the range of date/time column values.

The Select Country Selection list control should have a dedicated dataset to return one row per country.

The number gauge and category chart in area 3 can share a dataset. Values of all controls in this area should be filtered by the Time navigator and by the Country selector.

If it were important for the Time navigator to remain independent from the controls in area 3, and perhaps not be filtered by the Country selection, it would make sense to isolate the datasets for areas 1 and 3. For simplicity’s sake, you will use one dataset for these two controls. The only possible consequence of using a filtered table for both controls would be that, if no data existed for a country or if only a limited range of records existed for a given country, the Time navigator would only show corresponding dates.

Adding Shared Datasets and Report Tables

Adding a shared dataset to a mobile report is quite simple. Figure 18-13 shows the Sales Summary by Country and Category mobile report after completing the steps in the next section, with tables

from two new datasets added and the simulated tables removed. Removing the simulated data is not important from the perspective that they add very little processing and storage overhead to

the report. However, I typically remove simulated data to ensure that all of the controls have been bound to the actual tables I added after importing replacement datasets. If you attempt to remove a table that has controls bound to it, the designer displays a warning and does not allow the table to be removed.

FIGURE 18-13: Datasets added in the Data page.

NOTE You can create shared datasets using either SSDT for Visual Studio or Report Builder. The only reason I have instructed you to use both is to provide experience with both tools.

Create a Shared Dataset with Report Builder

You can design shared datasets using two different tools. If you are a report project developer using Visual Studio and SSDT, you can use a shared data source in the SSDT project, add datasets, and deploy them from SSDT. You can also design and deploy shared datasets directly from Report Builder. Either way, the process is relatively similar. In the next steps and those in the following section, I give you a little experience using both tools. Let’s start with Report Builder:

1. In Web Portal, navigate to the Home folder and then to the Datasets subfolder.

NOTE By default, SSDT creates a Datasets folder so if you have previously deployed a report project, the Datasets folder will exist. If not, use the Web Portal to create a folder named Datasets in the Home folder. It really doesn’t matter in what folder your shared datasets reside as long as you know where to find them.

2. On the toolbar, click the “+New” menu item and then select the option to create a shared dataset. Report Builder opens with the New Report or Dataset dialog, shown in Figure 18-14.

FIGURE 18-14: Selecting a shared data source.

3. Click the WroxSSRS2016 shared data source and then click the Create button.

4. Enter this query directly into the Query box (or use the “Edit as Text” toolbar option in the query designer) and test it, and then save it to the Datasets folder on the report server. Name the dataset Country Code List

SELECT DISTINCT Country,
CountryRegionCode FROM SalesTerritory WHERE Country <> ‘NA’

Create a Shared Dataset with SSDT

Beginning in the SSDT project, Wrox SSRS 2016 Exercises, add a new shared dataset to the project that references the shared data source named WroxSSRS2016:

1. Name the dataset SalesSummaryByDateCategoryCountry

2. Enter and test the following query in the query designer. I prefer to use the Edit as Text option:

SELECT
[OrderDate], [ProductCategory] CountryRegionCode, [Country],
SUM([OrderQuantity]) AS [OrderQuantity] SUM([SalesAmount]) AS [SalesAmount] SUM([TaxAmt]) AS [TaxAmt]
SUM([Freight]) AS [Freight] FROM [vSalesDetails
GROUP BY [OrderDate], [ProductCategory] CountryRegionCode [Country
ORDER BY [OrderDate] [ProductCategory], CountryRegionCode [Country
;

3. After the query has been written, tested, and named correctly in the Shared Dataset Properties dialog shown in Figure 18-15, close and save the query.

4. Right-click the dataset and deploy it to the Datasets folder on the report server.

The destination should be set as the default deployment path in the project.

5. Use the Web Portal to inspect the Datasets folder. Refresh the browser windows if necessary and make sure that the two new shared datasets are there, similar to Figure 18-16.

Add Data Tables to the Mobile Report

The reason mobile reports use only shared datasets is that, unlike SSRS paginated reports, query definitions are not stored in the report definition files. The Mobile Report Publisher refers to the data structure object based on query results as a “table.” Subsequently, there is no query designer in the design tool.

FIGURE 18-15: Creating a shared dataset.

FIGURE 18-16: Shared datasets viewed in Web Portal.

Add tables from the shared datasets using these steps:

1. Return to the Mobile Report Publisher.

2. On the Data page, click the Add Data button on the top-right side of the window.

The Add Data options are displayed, as you see in Figure 18-17.

FIGURE 18-17: Add data options.

3. Choose the Report Server option on the right side to select a server. You can see my development server in Figure 18-18.

FIGURE 18-18: Select the report server.

A connection to your report server is added the first time you open Mobile Report Publisher from the Web Portal. You should see your report server and any other report servers you may have added in previous sessions.

4. Select your report server from the list and then browse to the CountryCodeList dataset in the Datasets folder. Selecting the dataset adds it to the mobile report.

5. Click the Add Data button again and add the SalesSummaryByDateCategoryCountry dataset to the report.

On the Data page of the Mobile Report Publisher, shown in Figure 18-19, you should see the two new tables imported from the selected datasets.

FIGURE 18-19: New tables added to report datasets.

6. Verify that you are setting the data properties for the Time navigator.

You should see the control instance named Time Navigator 1 displayed to the left of the properties panel below the data grid. To switch controls, use the Layout page, select the visual control, and then switch to the Data page.

Set Data Properties for the Time Navigator

In the steps in this section, carefully check the figures to make sure you have the correct control selected. To select a different control, return to the Layout page using the tabs on the left side of the mobile report designer grid. In the Data page, the control is displayed on the left-hand side of the Data properties panel.

The Time navigator control automatically detects date and time values and generates time periods for dates between the earliest and latest date/time values in the corresponding data table.

1. Ensure that you have selected the Time Navigator 1 control. If a different control is selected, return to the Layout page and select the Time navigator.

2. In the Data properties panel, shown in Figure 18-20, drop down the “Series for background chart” list and select the SalesSummaryByDateCategoryCountry dataset.

3. Use the drop-down list to the right and check the SalesAmount field, as you see in

Figure 18-20.

FIGURE 18-20: Time navigator background chart properties.

Set Data Properties for the Selection List

Selection list controls are used to filter other datasets on a mobile report. One dataset is needed to populate the selection list and another dataset is filtered using the items selected from the list. A set of matching key columns is used to perform matching and filtering between the two datasets.

1. Return to the Layout page and click the Select Country selection list on the report design grid.

2. Select the Data page and refer to Figure 18-21 to set the Data properties.

FIGURE 18-21: Filter options in the properties for the Select Country control.

3. From the Keys drop-down list, select the CountryCodeList table.

4. In the field drop-down list to the right, select the CountryRegionCode field.

You can ignore the two Options buttons. The data for this control will not be filtered by any other selection nor are there any numeric fields to be aggregated.

A panel is located to the right, titled The Tables Filtered by Select Country.

5. In the table filtering panel, check only the SalesSummaryByDateCategoryCountry dataset and drop down the adjacent list.

6. Choose the CountryRegionCode field. This is the key column in the target table that will be filtered using key values in the selection list table.

Set Data Properties for the Number Gauges

The most basic of all the visual controls, the number gauge simply aggregates all values in a specified column. All the other gauge controls require another column that is used for comparison, as a KPI or progress indicator. Values should be formatted appropriately, whether currency, percentages, decimal, or whole values.

1. Return to the Layout page and select the Sales Amount number gauge on the report design grid.

2. Select the Data page.

3. From the Main Value drop-down list, select the SalesSummaryByDateCategoryCountry table.

4. From the adjacent field drop-down list, select the SalesAmount field.

5. Click the Options button to display the Filter and Aggregation options, shown in Figure 18-22.

FIGURE 18-22: Number gauge data filter properties and aggregating function selection.

6. Check both the Time Navigator 1 and Select Country controls. These navigator and selection controls are used to filter data for the number gauge.

7. Verify that the Sum aggregation is selected.

8. Repeat the same steps for the three remaining number gauge controls. Choose the corresponding fields for the Freight, Tax Amount, and Order Quantity number gauge controls.

Set Data Properties for the Category Chart

The category chart is similar to charts and other data regions used in paginated reports. The Category Coordinate properties define a group for aggregation, similar to the group expression in paginated reports.

1. Return to the Layout page and select the Category Sales category chart.

2. Select the Data page and follow Figure 18-23 to complete the property assignments.

FIGURE 18-23: Category Sales chart Data properties.

3. Drop down the Category Coordinate list and select the SalesSummaryByDateCategoryCountry table.

4. Drop down the corresponding field list and select the ProductCategory field.

5. Use the field list to the right of the Main Series and select the SalesAmount field.

6. Use each of the Options buttons to check both the Time Navigator 1 and Select Country controls for filtering, and verify that the Sum aggregation is used.

Apply Mobile Layouts and Color Styling

The color palette options are defined in the brand package applied to the report server. To style the report, you select a color palette and a layout for mobile devices.

1. Return to the Layout page.

2. Use the color palette drop-down in the upper-right corner to select a themed style for the mobile report. See Figure 18-24 for some examples.

Color palettes correspond to the custom branding theme applied to the Web Portal.

3. Use the Preview tab to view the mobile report with data.

This time you see the real sales data from the SQL Server database.

4. Test the report by interacting with the Time navigator and “Select Country” Selection list.

Click or tap and hold on a column in the chart to see more information about the data point.

5. Switch to Layout view and then use the drop-down control to the left of the color palette selector (see Figure 18-25) to show the Master, Tablet, and Phone layouts.

FIGURE 18-24: Color palette selection.

FIGURE 18-25: Choosing a report layout.

Creating alternate mobile device layouts is very easy to do. The control instances that you had added to the original Master layout are displayed in the panel to the left of the mobile report design grid. The default Tablet layout for portrait orientation is five cells wide by ten cells tall.

6. Click the layout drop-down list and choose the Tablet layout.

7. Drag and drop controls into the grid, and resize and arrange them to resemble the example in Figure 18-26.

FIGURE 18-26: Report shown in phone layout.

8. Preview the mobile report in this layout to see how it will look and behave on a tablet in portrait orientation.

9. Switch back to Layout view.

10. Select the Phone layout and arrange the control instances to resemble Figure 18-27.

FIGURE 18-27: Report optimized for the phone layout.

11. Preview the mobile report in this layout to see how it will look when used on a smart phone.

Figure 18-28 shows the preview.

Test the Completed Mobile Report from the Server

Although previewing a mobile report in the Mobile Report Publisher should approximate a user’s experience with published reports, it is always a good idea to test the actual report in a production- like environment:

1. Save the mobile report to the report server before closing the Mobile Report Publisher.

If you had previously saved the report to the server, clicking the floppy disk icon in the tool- bar is sufficient.

2. Navigate to the published mobile report in the Web Portal and click to open the report in the browser.

3. Use the Time navigator and Selection list controls to explore and interact with the report data.

4. If you have a touch screen, use the touch interface to navigate the report, which is shown in Figure 18-29.

FIGURE 18-28: Phone layout preview.

FIGURE 18-29: Sales Summary by Country and Category report in Web Portal.

If you have Wifi network access to the report server from a mobile device, such as a tablet or smart phone, follow these steps to connect to the mobile report.

If your report server is behind a firewall or if Windows Firewall is running on the report server, you may need to follow the steps in the following article to open port 80 and allow report connectivity to the Internet or wireless network: https://msdn.microsoft.com/ en-us/library/bb934283.aspx.

TIP If you are using a development report server that has inbound traffic exposed to the Internet, you can quickly test report connectivity by temporarily turning off the firewall. Just remember to turn it back on when you are finished.

5. On your tablet or phone device, use the mobile vendor’s app store to find and install the Power BI Mobile app. The mobile application can be installed, free of charge, from the Apple, Google, or Microsoft app store.

6. Run the app and choose the option to connect to a server. Figure 18-30 shows the Power BI Mobile running on my iPad. To add the server connection, I expand the menu bar and tap Connect Server.

FIGURE 18-30: Power BI Mobile app on tablet.

7. Add the web address of your report server.

8. This will be the same address you use to access the report Web Portal on your web browser but without the http:// prefix. The default address is serv /Reports. You can also use the server’s IP address in place of the server name.

9. Enter a username and password to connect to the server. Depending on your network  environment, you may need to prefix the username with the domain name and a backslash (like domain\ sername). Figure 18-31 shows the server connection configuration on my iPad.

FIGURE 18-31: Connecting to server.

10. On your mobile device, use the new server connection to navigate to the report server, locate the mobile report, and open it.

11. If you are using a tablet device, rotate the screen to transition from Master to Tablet layout.

12. Figure 18-32 shows the live mobile report in portrait orientation. Interact with the report by using the Time navigator to drill down and select different time periods and select combinations or ranges of countries. Tap and hold the chart columns to view more details about the selected data point.

FIGURE 18-32: Report viewed in the mobile app.