Implementing Row-Level Security in Power BI

The best method to implement row-level security in a published Power BI model or SSAS Tabular model consumed from the Power BI service will depend to some degree on your data structure and requirements.  The method I demonstrate here is one of the most flexible approaches and one that I commonly use in my projects.

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.

Modern Reporting with SQL Server 2016 and 2017 Reporting Services full day pre-conference session

I am thrilled to be presenting a full-day preconference session before the PASS Summit, on October 31st.  Please join me for a deep tour of the new capabilities and BI platform integrations for SQL Server Reporting Services 2016 & 2017.  The session will also review the essential skills and tasks to setup and configure the report server and web portal, report design and modern report solution planning.

Here’s an overview of the session:

The 2016 product release was a big upgrade that, once again, brought SSRS into center stage as a market-leading reporting and dashboard design tool for operational and BI reporting.  The big news was that SSRS reports truly run in all modern web browsers and work on mobile devices and all desktops in a new modern web portal.  The introduction of Mobile Reports, formerly known as Datazen, gives mobile device users interactive, tactile and actionable dashboard style reporting on all the popular mobile smartphones and tablets.  The 2017 product brings even more goodness with the integration of Power BI Report Server, on-premises Power BI report integration in a cloud-free platform.  Join Paul for a full-day pre-conference session where you will explore new capabilities and learn to integrate modern reporting into your data platform.

This session covers:

  • What’s new in Reporting Services 2017 (and 2016)?
  • Reporting Services and Power BI Report Server Architecture
  • Parameter improvements
  • Overview of report solution design general best practices
  • Integrating and managing Power BI on-premises
  • Integrating SSRS with the Power BI service
  • Graphical report design
  • Visualization choices
  • Chart visual types and new visual options
  • Indicators and sparklines
  • Creating dashboard reports
  • Advanced and analytic reporting
  • Advanced design techniques
  • Conditional visibility & drill-down
  • Pagination & flow control
  • Error management
  • Drill-through navigation
  • Query design beyond the basics
  • Reporting with SQL Server and Analysis Services
  • Using the DAX and MDX query editors
  • Analytical and Business intelligence reporting
  • Managing report projects and consolidating reports
  • Upgrading from prior versions of SSRS
  • Framing and integrating reports the easy way
  • Introducing Mobile Reports
  • Multi-device mobile reporting, business cases & scenarios
  • Visual control categories & behavior
  • Mobile reports and parameters
  • Filtering an navigating mobile reports
  • Building mobile report solutions
  • Mobile drillthrough – targeting mobile reports and paginated reports
  • Content management
  • Managing the web portal
  • SSRS and Power BI Report Server administration
  • Custom branding
  • Instance caching
  • Server management and configuration

Register for the PASS Summit and preconference here.  Also, look out for a sneak peek of this and many other sessions in the 24 Hours Of PASS which will run continuously through July 19th and 20th.  Watch the PASS Learning Channel for announcements.

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.