Can’t We Just Get Along? Making SSRS, Power BI and Excel Play Well Together

Please join me and other 2017 PASS Summit speakers for 24 Hours of PASS: Summit Preview on July 19th and 20th.  24HOP is a series of 60 minute on-line sessions presented back-to-back for 24 hours, from the same professionals who will deliver preconference and main conference sessions during the Summit the first week of November this year.  These online sessions are free of charge and normally attended by thousands of individuals to gain insight and knowledge about the topics.  24 Hours of PASS features free educational webinars delivered over 24 hours. Topics covered in this edition include Performance Tuning, SQL Server 2017, Linux, DevOps, Azure, PowerShell, SSRS, Power BI and much more. Browse all sessions. These webinars provide a sneak peek at some of the best practices, expert tips and demos you’ll find at this year’s PASS Summit in Seattle.

image

My session this year is titled: Can’t We Just Get Along? Making SSRS, Power BI and Excel Play Well Together and begins at 7:00 PM Pacific Time.  Make sure to double-check the 24HOP schedule to confirm when you register for this and any other sessions.

Session description:

Choosing the right reporting tool can be a challenge but can different reporting tools be blended to create a unified solution?  Learn the strengths and limits of each; for Self-service, IT-driven and Business-Driven BI, reporting and analytics.  We’ll review an actual solution to see how these tools are being used together to meet the needs of users and technical professionals in different roles in a large organization.

In this 60 minute session, I will preview some of the material covered in the full-day preconference: Modern Reporting with SQL Server 2016 and 2017 Reporting Services full day pre-conference session on October 31st in Seattle.

Session Recordings: Advanced Data Transformation Techniques, and Power BI Options for the Enterprise

Absolutely amazing….  The Microsoft Data Insights Summit was a series of one great learning experience after another.  My greatest impressions of the summit were that the Power BI community is coming together as a cohesive group of data professionals.  I think Power BI and Microsoft data platform have a solid story now as a solution rather than just a product and it was apparent that business people and technologies are taking it seriously.

In my post before the Data Insight Summit, I shared an overview of the two sessions we were preparing to deliver.  It took the organizers only one day to post all the session recordings from the entire conference for public viewing.  Let me say that again… every session, in its entirety, is available to watch for free online.  Here are my two sessions.

I’m posting this just two days after the summit and the Advanced Data Transformation Techniques session recording has been viewed well over 1,000 times!  Thank you for the tremendous interest in these topics.  I teamed up with Brian Grant to demonstrate several real-world techniques for transforming data with Power Query.

In Power BI Options and Approaches, I showed how Power BI can be used as a first-class presentation tool to analyze and visualize enterprise data in on-premises databases such as SQL Server and Analysis Services.

 

From the Keynote at the Microsoft Data Insights Summit

In sitting up front in the Data Insights Summit waiting for the keynote to begin.  It’s Monday, June 12, 2017.  I’ll update this post with announcements.  We’ve been told to expect some important news and announcement today.  Internet connectivity is pretty spotty so you may see some updates later in the keynote session.

I’m sitting in the Press section with fellow bloggers, who are also sending updates from the Summit keynote.  Check out their notes as well. 

Reza Rad:  http://radacad.com 

Marco Russo: http://sqlbi.com

Matt Allington: http://exceleratorbi.com.au/exceleratorbiblog

James Phillips on stage: “we need to get a bigger room… we’re over capacity. please don’t tell the fire marshal  …just kidding.  No, really… I’m just kidding”

IMG_1178 (002)

Power BI has more data connectors than any other BI product in the industry.

Custom visuals has been a big game changer.  Surprisingly, many are company and industry-specific.  Developers don;t have to use “regular” visuals when they have unique needs.

Power BI is “Planet Scale” …deployed in every geo data center we have on the globe.

Certified & HIPPA-compliant.  The numbers reflect that Microsoft is now the clear leader in the Data Analytics industry.

IMG_1181 (002)

Sirui demonstrating Power BI Premium, which enables you to have “Special Powers”
Premium allows schedule refresh up to 48 time per day.

James: “Actionable BI”  in Power BI and PowerApps  …the loop of amazingness.

Amir Netz: New features being released today:
– EM1, embedded content starts at $625/month
– SharePoint Online embedding
– Teams… select a workspace & reports to embed into a Teams discussion
– Visio Diagram visual control…  shapes are mapped to fields in the model and can be conditionally colored.

IMG_1185 (002)
– PowerApp visual provides real-time write-back embedded in a Power BI report

Import custom visuals directly from the Store in PBI Desktop

Data bars with conditional formatting – in tables…. super cool!

WE HAVE DRILL-THROUGH from page to page!!!!

Bookmarks… “remembers” the context of the page filters and slicers.  New Bookmarks pane allows named bookmarks to be saved.

Spotlight… creates “pop-up” experience for a visual

Show/hide…  different visuals related to bookmarks

Navigation buttons:  need I say more? 

“What-if Parameters” – works like a disconnected table.  Interactive values are reference-able in DAX formulas.

Explain the Increase: like Quick Insights, uses AI algorithm to visualize a data change.

Q&A on the phone app:  UX is like a text message conversation

IMG_1186 (002)

The new feature summary and wrap-up:

IMG_1189

Microsoft Data Insights Summit Live Streaming & Free Sessions

image

(6/15/17 update: view these recorded sessions & notes from the opening keynote address)

If you are fortunate enough to be attending the Microsoft Data Insights Summit in Seattle along with about 2,000 others on June 12 & 13, I’ll look forward to seeing you there.  This will be a great community event and a chance to meet, network and learn from well-known speakers, authors and product team leaders responsible for the best data analytics tools in the industry.  The summit has been sold-out for a few weeks.  If you can’t make it to Seattle to be there in person, you can still attend the conference!  I don’t just mean that you can watch a few cherry-picked sessions and keynote addresses…  You can watch every session for free.  Selected sessions, which do include the keynote addresses, will be streamed lived during the conference but all of the sessions are recorded and will be available within two weeks after the last sessions wrap-up on June 13.  This an awesome learning experience.  I will post links and more details here about the recorded sessions after they become available.

If you are going to the summit, please attend my two sessions:

Monday, June 12, at 1:00 PM
Power BI Solution Options and Approaches
Power BI is a terrific self-service data analysis tool but it is also an enterprise-class reporting and dashboard tool for serious business data stored in a data center. Which options are best and most cost-effective as you plan your solutions? Learn how to build serious, secure, and scalable business solutions with on-premises data sources, SQL server, analysis services and other business systems. You’ll also see how you can architect solutions with security and user-access control utilizing groups and content packs.
(BI Pro / 200 LEVEL / Power BI, SQL Server Analysis Services, SQL Server Reporting Services / Administration and Governance)

Tuesday, June 13, at 10:30 AM
Advanced Data Transformation Techniques
Learn how to solve challenging data import and cleansing problems with Power Query and M functions. This session covers advanced data transformations and shaping techniques for unique data formats and structures. See how to transpose and pivot data, and work with structured data sources like XML & JSON. We will explore the “M” Data Mashup language, create and use query functions and parameters to create durable & manageable automated data refresh processes.
Co-presented with Brian Grant from CSG Pro
(BI Pro / 300 LEVEL / Excel, Power BI / Data Prep and Modeling)

Here’s a sneak peek at the material from both sessions.  You’ll be able to download the slide decks when the sessions are available to view online.

image

In “Power BI Solution Options“, I’ll introduce different ways Power BI can be used with enterprise data & on-premises data sources.  Using Power BI to create self-service data models is a great capability.  In the right hands and used in the right way, it can be a valuable business asset.  Used incorrectly and in the hands of inexperienced users, good data can product bad results.  How can you prevent a train wreck?

image

Power BI, used as a client tool for managed and governed data sources, whether managed on-premises or in managed cloud services, can provide a solution with the best of both worlds; control and governance over enterprise data and flexibility for reporting and analysis.

image

The Power BI platform provides all the components needed to manage analytical data; including queries for data transformation and shaping, data modeling, calculations and visualizations.

image

In enterprise-scale solutions, Power BI is a great data presentation tool but may just be the tip of the iceberg.  Below the surface, we may use SQL Server Analysis Services to manage models and calculations, and data transformation tools – such as SSIS -for data acquisition and transformations.  Formal solutions may incorporate an enterprise data warehouse, data marts and master data management.

image

image

Categorically, there are four options to use Power BI with enterprise data.  These include:

  • Using scheduled refresh with a conventional embedded data model, a cache of data is stored in the Power BI model and refreshed at regular intervals.
  • Direct Connect uses an on-premises or cloud-based Analysis Services data model.  Larger data volumes are managed and secured within the model and calculations are stored within a centrally-managed repository.  This option often provides the best balance between IT governance and reporting flexibility.
  • DirectQuery allows data to remain in SQL Server or another supported on-premises database platform.  This is a good option for solution teams having strong relational database skills who don’t need complex calculations or the flexibility of a tabular or multidimensional model.
  • Developers and solution architects can stream data directly to Power BI dashboard tiles through a REST API.  Streaming can be performed from other data streaming data sources and using the streaming service available through Power BI Premium.

image

image

image

I will demonstrate using Direct Connect and DirectQuery using SSAS and SQL Server on-premises through the On-premises Gateway.

 

In the session titled “Advanced Data Transformation Techniques”, I’ve teamed-up with Power Query guru, Brian Grant.  I love watching Brian teach people how to use these tools, which he knows back-and-forth and always has interesting analogies and object lessons to make learning M and Power Query easier.

image

These are the topics we are covering in our 50 session.  It will be lightening fast and nearly all live demos!

image

First the basics… real quick.  When you use menu options in the Power BI Query Editor, the design tool actually generates steps as M script.

image

One of the most powerful features of the Power Query (officially called “Get Data” feature) is the ability to preview data at the completion of any step in a query.  Essentially, you have the ability to rewind and play-forward each of the query steps and see the data as it will exist after each transformation step.  This rapid design preview experience allows you to effectively build queries in a highly-interactive fashion.

image

Here are three common examples of data that would need to be transformed prior to consumption in a tabular data model.

image

We show you how to transform structured data in a JSON document .

image

How can you pivot Data?  Get it… Pivot Data!  Corny, I know.  There are actually several different ways that data can be transposed, pivoted or unpivoted.  In short, transposing means that all rows become columns and all columns become rows.  …easy.   To Pivot data, we must establish an anchor value of some kind (like the push pin in the corner of the Lt. Commander Data picture ) that will bind like-rows together so other rows can be rotated and transformed into columns.  We’ll show you a practical example by extending the analogy.

imageTo

Error handling is a reality in most any data project and there are several approaches that may be appropriate depending on your data quality requirements.  We share a simple flow chart for deciding how to handle and manage erroneous data.

image

The bottom line is that we must either deal with the erroneous data or not.  If he erroneous rows can be transformed and corrected, life is bliss and we ca move on.  In a financial accounting system where every record must e accounted for, efforts would be made to find and correct all te errors.  If a sampling of rows aren’t worthy to pass the transformation test, and loosing them would not significantly affect the statistical outcome, that may be an acceptable loss.  In that case, removing the noise caused by the erroneous records would be the better option.

image

We will explore the “M” or Data Mashup formula language and demonstrate using some useful examples.

image

 

image

 

image

 

image

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 “+”.

clip_image004

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.

clip_image006

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.

clip_image010

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.

clip_image014

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

clip_image016

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.

clip_image019

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.

clip_image024

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.

clip_image027

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.

clip_image030

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.

clip_image033

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.

clip_image038

FIGURE 18-11: Data page with simulated datasets.

clip_image040

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

clip_image003the 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.

clip_image009

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.

clip_image012

FIGURE 18-15: Creating a shared dataset.

clip_image015

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.

clip_image017[6]

FIGURE 18-17: Add data options.

clip_image020[4]

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.

clip_image022[4]

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.

clip_image026

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.

clip_image028[6]

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.

clip_image030[4]

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.

clip_image032

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.

clip_image035[6]

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.

clip_image038[4]

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.

clip_image041[6]

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.

clip_image043

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.

clip_image046

FIGURE 18-28: Phone layout preview.

clip_image049

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.

clip_image053

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.

clip_image056

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.

clip_image058

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

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

clip_image004

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

clip_image007

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.

clip_image011

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.

clip_image020

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

clip_image025

  • 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

clip_image028

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.

clip_image031

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.

clip_image034

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.

clip_image036clip_image038

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

clip_image041

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

clip_image043

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

clip_image045

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.

Chapter 9 – Advanced Queries and Parameters

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.

  • Understanding T-SQL queries and parameters
  • Understanding MDX queries, parameters, and expressions
  • Understanding DAX queries, parameters, and expressions
  • Managing report parameters
  • Using parameter expressions

Chapter 5, “Database Query Basics,” introduced you to query and report parameters. This chapter introduces you to a few simple techniques to parameterize queries. The discussion in this chapter steps through the design of each sample report, and then delves deeper into less- common and more advanced design patterns.

The parameter architecture in Reporting Services has remained the same since the product’s inception. But recent enhancements in SQL Server 2016 give you control of the parameter placement in the parameter bar displayed at the top of the browser. In this chapter, you see how to define specialized parameter items you can use to return all (or a range of) dataset records with a single parameter selection. Many of the same techniques can be used with MDX queries for SQL Server Analysis Services (SSAS). However, you will need an understanding of the unique needs and capabilities of the MDX language and query objects.

NOTE Unlike in previous chapters, this chapter does not walk you through every step of the included exercises, but provides the necessary instructions to apply the skills you have acquired in the previous chapters.

T-SQL QUERIES AND PARAMETERS

As the native query language of Microsoft SQL Server, Transact-SQL (T-SQL) provides a great deal of flexibility and many creative ways to dynamically filter datasets with parameters. Out of the box, you get several sample reports and instructions to demonstrate different parameter techniques (such as using parameter lists, simple and multi-select parameters, inter-dependent cascading parameters, and so on).

Parameter Lists and Multi-select

The Ch09 – Parameter In List report has two datasets: one to populate a parameter list, and one for the main report query.

The ProductList dataset uses the following query:

SELECT
ProductKey, ProductName
FROM Product
ORDER BY ProductName
;

Another dataset, named ReportDate, uses the following query:

SELECT s.OrderDate, s.SalesOrderNumber p.ProductCategory p.ProductSubcategory, p.ProductName p.ProductKey p.StandardCost p.ListPrice, s.OrderQuantity, s.SalesAmount
FROM
vSales2013 s
inner join dbo.Product p on s.ProductKey = p.ProductKey
WHERE
p.ProductKey IN ( @ProductKeys )
;

When this query is executed in the designer, the ProductKeys parameter is added to the report, and a corresponding dataset parameter is created. These two objects have the same name, so they can be easily confused, but it is important to understand the difference. Starting with the dataset, let’s work from the inside out. Figure 9-1 shows the Parameters page of the Dataset Properties dialog where the report parameter, ProductKeys, is mapped to the dataset parameter having the same name.

clip_image004

FIGURE 9-1: Parameters page of Dataset Properties dialog.

Inspect the report parameter in the Report Data window and note the following properties (as shown in Figure 9-2):

The “Data type” should be set to Integer instead of the default Text—Setting “Data type” to match the filtered field can be more efficient. Certain data types (such as Date and Boolean) change the input control displayed in the parameter bar. Date-type parameters use a calendar picker control, and Boolean-type parameters display a pair of radio buttons to facilitate selecting either True or False values. All other types use a plain input box, unless values are provided in the Available Values page. In that case, a drop-down list is displayed.

“Allow multiple values” is checked—The drop-down list (which contains parameter values for selection) shows a check box before each item. This causes a comma-separated list of the selected values to be sent to the query parameter and converted to a single text string.

A multi-select report parameter is an array of key/value pairs named Value and Label.

clip_image007

FIGURE 9-2: Report Parameter Properties page of the Report Data window.

On the Available Values page shown in Figure 9-3, you can use the ProductList dataset to provide the product values based on the query. The “Value field” property is set to use the ProductKey and the “Label field” property will use the ProductName. This means that report users will see the names, but key values will be used internally.

You can select any combination of parameter values.

How do you show users what parameter values they selected in the report header? When a report is printed as shown in Figure 9-4, it may be important to capture the parameter selection so that the readers understand the context of the report.

clip_image010

FIGURE 9-3: Specifying the “Value field” and “Label field” properties.

Before looking at the finished expression, here is an experiment for educational purposes. Add a new textbox to the report header area above the table. Right-click and choose Expression… to open the Expression builder dialog. Use the parameter list and double-click the ProductKeys parameter, which creates the following expression:

=Parameters!ProductKeys.Value(0)

There are two problems with this expression. It will display the numeric product key (rather than the product name) in the textbox, and it will only display the first selected item. To correct this, you use the “Label field” property instead of the “Value field” property. A multi-value parameter is stored as an array, so you can’t just display it as a single value. The VB.NET JOIN function will

iterate through each array element, and represent the key/value pairs for each selected item. Here’s the working expression to display a comma-separated list of every selected parameter value:

=JOIN(Parameters!ProductKeys.Label, “, ”

clip_image013

FIGURE 9-4: Clarifying the context in the printed report.

Figure 9-5 shows the finished report with the parameter list in the heading.

clip_image016

FIGURE 9-5: Finished report with the parameter list in the heading.

Cascading Parameters

In the previous example, the parameter list is a little long and inconvenient. With so many values to choose from, multiple parameters can be used to break a list down into a manageable hierarchy. A parameter can depend on another parameter so that the list of avail- able values is filtered based on another parameter selection. For example, if you offer users a list of product categories and another list of product subcategories, the subcategory list would show only subcategories for a selected category. Figure 9-6 shows an example. A selection from the Categories parameter filters the product subcategories, and that selection filters the products list.

The sample report Ch09 – Cascading Parameters

clip_image018

FIGURE 9-6: Filtering product subcategories.

shows how to build this. You start with three separate datasets that are used to populate the parameter lists. Dataset names are listed before query. Note the logical dependencies between each query

in order.

CategoryList:

SELECT DISTINCT ProductCategory
FROM Product
;

SubcategoryList:

SELECT DISTINCT ProductSubcategory
FROM Product
WHERE ProductCategory IN ( @Categories )

ProductList:

SELECT ProductKey, ProductName
FROM Product
WHERE ProductSubcategory IN ( @ProductSubcategories )

The main report dataset (named ReportData in the sample report) uses only the ProductKeys parameter as a predicate to filter sales records.

SELECT s.OrderDate, s.SalesOrderNumber p.ProductCategory, p.ProductSubcategory p.ProductName, p.ProductKey, p.StandardCost p.ListPrice,
FROM
s.OrderQuantity s.SalesAmount
vSales2013 s
inner join dbo.Product p on s.ProductKey = p.ProductKey
WHERE
p.ProductKey IN ( @ProductKeys )

Recall from the previous example that the ProductKeys parameter used two different fields for the product key value and label. Conversely, for the Category and Product subcategory parameters, it is unnecessary to have different fields. Figure 9-7 shows the Available Values settings for the Categories parameter where the ProductCategory field is used for both the “Value field” and “Label field” properties. Using the name rather than a key value to filter from a parameter typically works, as long as the values are unique and it is a relatively short list.

clip_image021

FIGURE 9-7: Available Values settings.

The ProductSubcategory parameter settings are similar to the Category, with the ProductCategory field used for the “Field value” and “Label value” properties. The ProductKeys parameter is essentially the same as it was in the previous report example, with the ProductKey used for the “Value field” and the ProductName for the “Label field.” Cascading parameters must be arranged in order of dependency, which, in this case, is Category Product Subcategory, and Product. Use the up and down arrows in the Report Data toolbar header to change the order if necessary.

Arranging Parameters in the Parameter Bar

SQL Server 2016 introduces a new report design feature that provides control over the placement of report parameters in the parameter bar displayed above a rendered report. Note that the parameter bar is displayed for reports in a report server configured for native mode.

NOTE In SharePoint integrated mode, parameters continue to be arranged vertically in a panel on the right side of the browser window.

The parameter bar is customizable. As shown in Figure 9-8, use the right-click menu to add and remove columns and rows to and from the grid, and then drag and drop the parameters into any cell. Right-click and remove unneeded rows and columns.

clip_image025

FIGURE 9-8: Using the right-click menu to add and remove columns and rows.

Managing Long Parameter Lists

Multi-select parameters typically work well when the user will select a manageable number of parameter items. There is no stated limit either to the number of items that can be included in a parameter list, or to the number of items that can be selected. The only control that you really have is to limit the items you display in the list. Both the number of values on this list and the number of selected items can affect report performance.

The sample database returns 397 products, which are included in the parameter list shown in Figure 9-9. Any more than this and you would see a noticeably longer report rendering time. Even more impactful is the effect of selecting many values and passing them into the query for filtering.

clip_image027

FIGURE 9-9: Products in parameter list.

Multi-select parameters always display a “(Select All)” item at the top of the list. Choosing this item checks the boxes for every item in the list, and unchecking it deselects all of the listed items.

WARNING Long parameter lists can affect report performance. Multi-select parameters include an item at the top of the list labeled “(Select All).” Choosing this item selects every item on the parameter list and passes all of those values into the report query. You cannot disable this feature, nor limit the number of items selected. You should limit parameter lists to a few hundred items.

Consider what happens if the “(Select All)” option were used in this sample report. The following query is executed after the parameter values are parsed, which explicitly includes every available ProductKey:

SELECT s.OrderDate s.SalesOrderNumber p.ProductCategory p.ProductSubcategory, p.ProductName, p.ProductKey, s.OrderQuantity, s.SalesAmount
FROM
vSales2013 s
inner join dbo.Product p on s.ProductKey = p.ProductKey
WHERE
p.ProductKey IN ( 486,223,224,225,484,447,559,473,472,471,
485,555,552,470,469,468,466,467,464,465,462,463,451,452
483,603,558,393,396,304,305,306,296,297,298,299,301,302
303,300,307,308,309,288,289,290,291,293,294,295,292,412
401,402,544,421,517,537,439,440,441,442,443,444,210,437,
438,241,242,243,244,245,246,247,248,249,250,251,252,211
238,239,240,415,407,408,547,424,520,540,497,498,499,500
494,495,496,492,554,523,487,601,556,391,394,550,531,532
533,534,551,524,525,526,527,410,397,398,542,419,515,535
279,280,281,282,283,284,285,286,287,253,254,255,256,257,
258,259,260,261,262,263,264,265,266,267,268,269,270,271
272,273,413,403,404,545,422,518,538,510,502,503,504,505,
506,507,508,509,493,553,521,232,233,234,229,230,231,226,
227,228,235,236,237,461,460,459,454,453,445,455,448,602
557,392,395,409,426,427,428,549,511,512,513,411,399,400
543,420,516,536,274,275,276,277,278,417,418,429,430,431,
432,433,434,435,436,414,405,406,546,423,519,539,522,219
218,478,449,528,348,349,350,351,344,345,346,347,358,359
360,361,362,363,352,353,354,355,356,357,364,365,366,367,
587,588,589,590,596,597,598,599,600,591,592,593,594,595
480,482,481,514,501,479,529,311,312,313,314,310,373,374
375,376,377,378,379,380,368,369,370,371,372,580,581,582,
583,317,318,319,315,316,381,382,383,384,385,386,387,388,
389,390,338,339,340,341,342,343,332,333,334,335,336,337
326,327,328,329,330,331,320,321,322,323,324,325,604,605
606,584,490,489,488,491,215,216,217,220,221,222,212,213
214,450,416,548,425,541,530,573,574,575,576,561,562,563
564,577,578,579,560,585,586,565,566,567,568,569,570,571,
572,446,477,476,475,474,458,457,456 );

This query was tested with and without the FROM clause, which revealed a return of the same set of results. Surprisingly, the difference in performance is negligible, and the verbose query takes only milliseconds longer. Perhaps if you use twice the number of product keys, a larger data volume, or you are running on a production server with other competing operations, you might see a greater impact. The point is that, under certain conditions, queries like this play a role in causing performance issues. So you should take steps to manage them. Using cascading parameters can help, because the user wouldn’t be able to select every product at once. The following technique may also be helpful.

All Value Selection

Rather than allowing users to select all of the parameter items if they don’t want to exclude any data, adding a custom item to the list can help you manage the query logic with more efficiency. In the next scenario demonstrated in the sample report Ch09 – All Parameter Selection 1, the goal is to provide users with the option either to select a single country or to return results for all countries. Figure 9-10 shows the parameter list showing an item labeled “(All Countries).”

clip_image032

FIGURE 9-10: Using “(All Countries)” to provide users with an option.

The query for my CountryList dataset looks like the following. Note that this is actually two SELECT statements.

SELECT
‘(All Countries)’ AS Country
UNION
SELECT DISTINCT Country
FROM SalesTerritory
;

In T-SQL, two queries that return the same set of columns can be appended using the UNION statement. This adds the “(All Countries)” row to the top of the list ahead of the country names from the SalesTerritory table.

The magic happens here in the following main report query where a logical decision is performed in the WHERE clause:

SELECT s.OrderDate, s.SalesOrderNumber p.ProductCategory, p.ProductSubcategory p.ProductName, p.ProductKey, p.StandardCost p.ListPrice, s.OrderQuantity s.SalesAmount
FROM
vSales2013 s
WHERE
inner join dbo.Product p on s.ProductKey = p.ProductKey
inner join dbo.SalesTerritory t on s.SalesTerritoryKey = t.TerritoryKey
t.Country = @Country OR @Country = ‘(All Countries)
;

Using an OR operator, one of two conditions must be met for a given row to be output in this query. Starting on the left side of the OR, the Country column value must match the selected @Country parameter value. If this branch is used, only orders for the selected country would be output. But what about the expression on the right side of the OR operator? That statement says, if the selected

@Country parameter value is “(All Countries),” let every row through. The OR operator trumps the other statement and just returns everything.

Here’s one more example, which is very similar to the first, but uses a numeric key rather than text. In this scenario, the parameter displays the sales country and region values concatenated together for the “Label field” (Figure 9-11) and returns the TerritoryKey for the “Value field.”

clip_image035
FIGURE 9-11: Using a numeric key rather than text.

The design of the Ch09 – All Parameter Selection 2 sample report uses a slightly different approach.

As shown here, like the previous example, the TerritoryList dataset has an extra row generated using – for the TerritoryKey and the text All Territories). This row is appended to all of the table values in the query using the UNION operator.

SELECT
-1 AS TerritoryKey
‘(All Territories)’ AS TerritoryName
UNION SELECT
TerritoryKey
CASE WHEN Country = Region THEN Region ELSE Region + ‘ ‘ + Country
END AS TerritoryName
FROM SalesTerritory
;

TIP When adding special-purpose items to a parameter list query, using a negative number for the key can ensure that it doesn’t duplicate the key value for an actual record.

Here is the main report query, which uses an expression on the WHERE clause to test the TerritoryKey query parameter. If the value is – , this indicates that the user selected the “(All Territories)” item. In this case, the OR operator effectively disregards the first filter clause and returns all rows.

SELECT s.OrderDate, s.SalesOrderNumber p.ProductCategory, p.ProductSubcategory p.ProductName, p.ProductKey p.StandardCost, p.ListPrice s.OrderQuantity s.SalesAmount
FROM
vSales2013 s
inner join dbo.Product p on s.ProductKey = p.ProductKey
inner join dbo.SalesTerritory t on s.SalesTerritoryKey = t.TerritoryKey
WHERE
s.SalesTerritoryKey = @TerritoryKey OR @TerritoryKey = -1

Handling Conditional Logic

Once you have mastered these essential parameter techniques, you can combine them to address real business reporting challenges.

The remainder of the chapter builds on these techniques to provide support for date range parameters, MDX query parameters, single and multi-value parameters and defaults, member ranges and sets.