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

Chapter 8 – Graphical Report Design

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 visual design principles and the fashion of visualization
  • Understanding chart types and design approaches
  • Getting to know the anatomy of a chart
  • Creating a multi-series chart
  • Using multiple chart areas
  • Learning useful properties and settings

By some estimates, 60 to 70 percent of the population are visual thinkers. That means even when consuming numbers and information presented in text, most people perceive a visual representation of the same information in their minds. We have grown accustomed to data displayed in visual form. In fact, we expect important data to be visualized, especially when the presentation communicates comparisons and trends.

When used correctly, a chart more effectively answers questions and empowers consumers to take action on a set of data rather than a grid full of numbers. Conversely, a table filled with details may provide necessary context and balance with the completeness of a detail table.

A financial controller or accountant will not balance the books to a chart, but a chart may be a great way for the CEO to see the improved profitability trend based on the same details as the balance sheet.

This chapter begins with the guiding principles of visual report design and discusses how charts are used in analytical reporting. Then we examine the different chart types offered in Reporting Services, and criteria to choose the correct type of chart for different business purposes and data scenarios. The essential components of the most useful and popular charts are explained in simple terms, and then we’ll take a look at some of the many detailed properties and features used to create more advanced chart reports. The two exercises for this charter include a time-series chart using a line chart and column chart. You will create a multi-series chart with two separate vertical axis scales and then move to a multi-area chart with synchronized horizontal axis.

VISUAL DESIGN PRINCIPLES

Visual reports are often referred to as analytic reports or dashboard components. Indeed, the term “dashboard” is used very loosely in different products by different vendors to describe anything from a simple gauge graphic to a huge screen full of visual widgets. So, what, exactly, is the difference between a “visual report” and a “dashboard”? I think the reason that the industry can’t offer a precise definition of “dashboard” that can set a boundary between the two terms is because the term “dashboard” is a metaphor for something tangible that we all use, and metaphors are subject to interpretation.

Whatever the specific definition is, the meaning is clear. The purpose of a dashboard or visual report is to summarize and display information for quick and convenient consumption so that users can understand what’s going on in their business without taking their eyes off the road.

While working on a product engineering team at Hewlett-Packard, I gained a valuable perspective about successful design that has stayed with me throughout my career. Everything that we design has the following three elements, which exist in balance, and these elements should be considered in everything you design:

Form—How does it look and how does it make me feel? Is it attractive, eye-catching, and interesting? People like to use things that are familiar and make them comfortable.

Fit—How does it suit the need or solve the issue? Begin with a well-defined problem statement that supports the design, thus creating a useful tool that finds an answer, or completes an important task.

Function—Is it easy to use, intuitive, and practical? Does it work in all environments and scenarios where it is needed? In a report or user interface, will the report work on a mobile device with a touch interface away from the office, or is it limited to a desktop? Is it interactive or static; for print or display?

Keep Charts Simple

Basic chart design can be quite simple if the default chart styles suit your needs. After placing the chart in the report body, you can drag fields from the Dataset window directly onto the chart design surface. At the minimum, a chart should have one aggregated field for the value and one grouped field for the category. The category and series groups represent the x-axis and y-axis in bar, column, line, area, and point charts.

Before things get complicated, let’s make it simple. Don’t go crazy with chart styling at first. Keep charts simple and clean, and use visual properties to emphasize the data, rather than the graphics. Heavy borders, backgrounds, shadows, and three-dimensional (3-D) effects distract from the message and presentation of important information.

NOTE My rule of thumb for visuals is that the amount of “ink” used in a graphic should correspond to the importance of the information. Generally speaking, dark, heavy borders and large fonts should be used to accentuate important information. Light, thin borders, backgrounds, and fonts should be used for supporting composition. Unnecessary stylings should be eliminated.

Visual design is art as much as it is science, and thus it is difficult to articulate hard rules for visual design. Sometimes it just needs to look right. Avoid clutter and use white space to provide balance. Work from examples of established design patterns. Experiment and find a presentation style that works for you and your audience, and then work within that theme. You can always fine-tune when the important design work is finished, and then improve the aesthetic design as you go.

You will find several excellent examples in books from Nathan Yao and his website, FlowingData.com. Stephen Few provides outstanding guidance about the principles and concepts of effective, simple visual design in his books and website, PercetualEdge.com. There you will find many bad examples to avoid, as well as good examples to pursue.

Properties, Oh My!

Reporting Services provides about 60 different chart styles, if you count all the variations. For each, you have fine control of detailed properties used to control border styles, fills, colors, and sizes of

just about everything you can imagine. With all this flexibility comes the potential for a good deal of complexity. All told, the chart data region and its constituent objects support nearly 200 individual properties. Some of these properties apply to only certain chart types. But no matter how you look at it, that’s a lot of properties to dig through.

Ever since Microsoft acquired the code base for Dundas Software’s .NET charting components

and added newer versions along the way, they have done a remarkable job of simplifying the design interface. You can take chart design as far as you need to. The necessary effort to design charts may range from simple to tedious. Having been down this road many times, I recommend that after you familiarize yourself with charting basics, you approach the design with specific objectives in mind. Otherwise, you’re likely to get lost in the interface.

The Fashion of Visualization

Perfect data visualization is the holy grail of analytic reporting. I’ve read a lot of books and attended many lectures about “right” and “wrong” data visualization practices. Scores of dashboarding, scorecarding, and visualization products exist, all with the promise of filling a huge gap in the industry left by all the other vendors who, according to competitors, can’t seem to get data visualization right.

As I write this, it’s only been a few days since the news of pop legend David Bowie’s passing. Bowie was synonymous with fashion (and, of course, he performed a song about it). Think of fashion trends over the past few decades—long hair, short hair, big hair; bell-bottoms, straight legs; neon, pastels, plaid, and grunge. Funny thing is that when we look back on the fashions of a previous generation, they seem so incredibly ridiculous that it’s hard to believe that people took any of it seriously. But somehow, a few pop stars and celebrities can make extreme fashion look cool and trendy. Charts are like that, too.

When I began using Reporting Services in 2003, 3-D charts were all the rage. Visual reports had big, rounded borders with drop shadows, beveled edges, and gradient-filled backgrounds. Do

that now and the reports will immediately be branded as “fluff” and “eye candy.” Ten years later, “modern” looking charts were flat with light-shaded bars on a white background. What were we thinking back then? Give us another few years and maybe we’ll all be wearing our bell-bottoms and designing 3-D charts.

Visual Storytelling

The purpose of a chart is to highlight important information and let it tell a story. Different report types can effectively convey comparisons or trends, but it’s important to use the right chart for your data. Before moving on, I want to make an important point. If you work in a field where specialized visualizations are useful and appropriate, you may find some of the more abstract and special-purpose charts of great value. But for day-to-day business reporting, it’s common to use just a handful of traditional chart types to visualize business metrics. So, even though Polar, Stock, and Funnel charts look cool, they may not help you convey an effective message. In business, 99 percent of the industry uses about 5 percent of the available chart types—namely, columns, bars, lines, and a few variations of the same types.

Perspective and Skewing

Hopefully, I’ve already talked you out of using 3-D charts as a practice, but I suppose there are isolated cases where it might make some sense. Consider a case where you are preparing a PowerPoint presentation. The data is static with the numbers right in front of you. Now, you want

to choose the most effective and impactful visual for your presentation, and maybe a bold 3-D chart makes the right statement. You can tweak it, spin it around, and make adjustments so the data points are all visible and in exactly the right place. This is a much different experience than designing a report for changing data. As soon as the values change, the last row of columns in a

3-D perspective chart may no longer be visible.

Here’s a rhetorical question. Figure 8-1 shows two renditions of the same chart and the same data in two different styles. Which is easier to read? This is purposely an extreme example, but it makes the point that wasting screen space on visual styling (not to mention ink or toner if printed) is just unnecessary.

The purpose of marketing is to persuade a consumer to perform an action, often by using psychology and emotion, to amplify and de-emphasize certain information. The carefully crafted presentation of information can alter a user’s perception, and his or her belief about certain facts. In analysis (which is a tool of science), information is presented in a uniform and standard format so that the consumer can make an honest and unbiased assessment.

clip_image007

FIGURE 8-1: Two renditions of the same chart.

CHART TYPES

Some of the more common chart types (such as column, bar, line, and area) can be used for different views of the same data. Pie charts present a more simplified view and work well with fewer category values. Other charts are more specialized and may be appropriate for multi-value data points, range values, and variances. All the chart types support dynamic capabilities, such as actions and tooltips. Using these features in report design, a chart user can get more information and details by hovering over or clicking a range, point, or area of the chart. Let’s take a brief look all the major chart types.

Chart Type Summary

Twelve general chart types are available, as described in Table 8-1.

TABLE 8-1: Chart Type Categories

CHART TYPE

DESCRIPTION

BEST USE

Column

A classic vertical bar chart with columns representing values along the y-axis. Like-valued items along

the x-axis are grouped, and bars representing the same x-axis values in each group have the same colors or patterns. Series values may also be grouped and subgrouped. Columns can have point labels, and the colored bars can be labeled using a legend. Columns can be arranged side by side (along the x-axis) or in front of one another (along the z-axis.) Columns may appear to be extruded from their base using a rectangular or circular (cylindrical) shape.

Discrete group values on the Category

(x) axis. Also effective with linear time-series periods broken into discrete buckets (such as days, weeks, or months).

continues

TABLE 8-1 (continued)

CHART TYPE

DESCRIPTION

BEST USE

Bar

Functionally the same as a column chart turned 90 degrees. It has the advantage of more accurately depicting value comparisons for layouts in which you have

more available horizontal space.

Used only with discrete group values, not for linear series groups.

Line

Like a column chart but with a trend line drawn from one point to the next in the series. This type of chart is appropriate for a series of values that tend to progress over a relatively even plane that describes a “level,” “up,” or “down” trend. It is inappropriate for series values that tend to jump around.

This type of chart is useful for comparing multiple series (along the z-axis) without obscuring trend lines behind a series.

Time-series and linear interval category groups (time, dates, and progressive numeric values).

Area

Like a line chart but the area encompassed by the line

is filled. The solid shading of the charted area depicts a volume of data values.

Opaque fill colors may be appropriate when the lines don’t intersect. Otherwise, transparent fill colors may be useful to discern overlapping fill areas.

Time-series and

linear interval category groups.

Pie

The classic pie chart can be an good tool for comparing a small number of relative values but it may not be as accurate as a tree map, bar, or column chart. Unlike these other charts, the aggregate value isn’t quantified. For simple comparisons, users understand pie charts because they put comparative values into a proportional context and can drive quick decision support at a

glance. Pie chart views can be exploded to visually separate each slice.

Use only for discrete category groups and never for linear series values. However, may be used for discrete

bucket values. Typically, use pie charts with

no more than 6 to 10 slices.

Doughnut

A doughnut is a pie with a hole in the middle. It’s more effective as a bold marketing visual. A 3-D doughnut rendering may expose smaller slices more clearly than

a pie chart because each slice has four sides rather than three.

Use with a subtle 3-D effect for conveying a bold statement and not for accurately measuring business metrics. Typically, limit slices to

6 or 10.

CHAR T TYPE

DESCRIPTION

BEST USE

Scatter

Plots several points in a range (both x and y) to show trends and variations in value. The result is more like a cloudy band of points, rather than a specific aggregated point or line.

Used with dozens

to hundreds of data points when analyzing the general trend is

of greater value than seeing a specific point.

Bubble

A technique for charting points on three dimensions. Values are plotted using different-sized points (or bubbles) on a two-dimensional (2-D) grid. The size of the bubble indicates the related value along the z-axis.

Appropriate when measuring two different series values along two different linear axes where size represents one value and position represents the other.

Range

(Gantt)

Range and Gantt charts are often used to visualize project phases and the progress of stages in a process along a linear series.

Used when each data item (project, commodity, unit of work) has

a beginning and end value on a linear axis.

Stock

This category of charts (sometimes called candlestick

r whisker graphs) plots values vertically like a column chart having variable start and end points. For each item along the y-axis series, a vertical line indicates a start

and end value for the range. A tick mark in the line can indicate a significant value in that range or an aggregation of the range. This type of chart is useful for showing trading stocks with opening, closing, and purchase values; wholesale, retail, and discount prices; and so on.

A specialized kind of visual for discrete data items that have multiple events along a linear axis, typically multiple start and end values.

Shapes

Shape charts like the funnel and pyramid are effectively a single, stacked column chart. These typically are used to model sales and production against goals, and sales opportunity pipelines.

The Tree map and sunburst charts are 2016 product additions. The tree map chart is considered by many visualization experts to be a more versatile replacement for pie charts because it provides a more accurate

comparison of values and can handle a larger number of data points.

Used in specific business scenarios where data items progress through ranked stages.

continues

TABLE 8-1 (continued)

CHAR T TYPE

DESCRIPTION

BEST USE

Polar

(Radar)

Polar and radar charts plot points from a central hub at different angles and distances in a radial fashion. This kind of visual does have some useful applications, but traditional nonlinear charts (such as a column or bar) are often more suitable.

A specialized visual. Can be used with discrete, nonlinear but related categories. Best used to express that no category exists at the beginning or end of the range.

Column and Stacked Charts

With so many variations of this chart type, the right choice will depend on the objective. Is it sufficient to demonstrate that one data point is less than or greater than another, or do these points need to be strictly measurable? An artistic chart might help to make an impact, but a flatter view is usually more appropriate to maintain accuracy.

Stacked charts effectively show the proportion of series values within each category group. The stacked column chart shown in Figure 8-2 demonstrates that each series value increases the column height in proportion, but the trade-off with this visual is that it’s more difficult to compare the individual series values across each category.

clip_image013

FIGURE 8-2: Stacked column chart.

To emphasize the proportion of like values rather than the comparative accumulation, the 100 percent stacked view makes all the bars in the chart the same length, rather than depicting the sum of all the values in the bar.

Area and Line Charts

As shown in Figure 8-3, an area chart plots the values of each point, and then draws a line from point to point to show the progression of values along the series. This is an effective method for analyzing trends, and works well when values tend to climb, decline, or remain level in the series. This type of chart is accurate when data exists for all category values on the x-axis. It typically doesn’t work well to express a series of values that are not in a relatively uniform plane.

clip_image016

FIGURE 8-3: Area chart.

A line chart is a variation of an area chart using a line or ribbon rather than a solid area. The line chart works better than the area chart for comparing multiple categories for a series of values, because one layer may obscure another in the area view. In the preceding example, the area chart works because of how the values are sorted. Larger values are in the background, and other points in the foreground are smaller, and the trend increases back to front.

Pie and Doughnut Charts

A pie chart can be a good tool for comparing proportional values when you have a relatively small number of categories, and each group is in an easily comparable range. If your goal is to use screen space effectively, geometry proves that this not the best visual. Simply put, a circle-shaped chart inside a rectangle-shaped page wastes a lot of screen or page space. Pie charts have received very bad

press for some good reasons amplified by the strong opinions of a few noted data visualization industry experts. Right or wrong, some people simply don’t take this visual very seriously anymore. That said, it’s still one of the most effective ways to convey a simple proportion of values in an uncluttered view or public presentation. The bottom line is that you should make sure this is the right visual for your purpose, and consider alternatives if in doubt.

Pie charts have fallen out of vogue in recent years due to their limited ability to visualize relatively similar values. Often, a pie chart is chosen when it works with a sample set of data that has a few categories with drastically different values, but ends up being a poor choice when the actual report data is less suitable for this type of chart. Consider using a tree map, column, or bar chart when more accurate comparisons are needed.

Here’s a quick experiment. Open the report named C08 – Pie and Column Chart in the Wrox SSRS 2016 Samples project. Preview the report and try to quickly determine the sales ranking of the three Bike product categories in 2013. Figure 8-4 shows the data visualized in a pie chart. That’s a tough one, huh?

clip_image019

FIGURE 8-4: Pie chart for Bike product categories.

You can see that I’ve added a report action in a textbox that shows and hides a column chart in the same report. I’m not going to give away the answer so you’ll have to use the sample report to find the answer for yourself, but you may be surprised.

Pie charts are also a very poor choice in a number of scenarios where column and bar charts would provide a more accurate presentation.

The purpose of a chart is to tell a complete story about the data, and it’s important to label it clearly so that you know what the groups represent. Group values can be titled using point labels, call-out labels, or in a legend.

A doughnut chart is a pie chart with a hole. Yes, it’s really that simple. Variations of the pie and doughnut charts allow you to separate the slices and, of course, twist and bend them using 3-D effects. Again, under the right conditions, this might be prudent with small, management result sets, but always consider your options before going down this route.

A significant limitation of this chart style is evident when the number of grouped values exceeds single digits. Consider the following example. Open the sample report Ch08 – Pie and Bar Chart with Many Values. You will notice I have added a visible parameter that allows you to specify the number of products to display in this sales summary. Figure 8-5 shows that it is difficult to discern between 25 different values. Below the pie chart is a bar chart visualizing the same results. In this case, the bar chart is a much better choice. The text arrangement of the vertical axis is ideal when comparing many values.

clip_image022

FIGURE 8-5: Doughnut chart versus bar chart.

The advantage of a bar chart over a doughnut chart, in this scenario, is that vertical page space is virtually unlimited in multiple page reports.

But Wait. There’s More!

A couple of product versions ago, a nifty trick was introduced that allows column and bar charts to grow dynamically. This feature is not as simple as just switching it on, though. Like most of

the cool capabilities in Reporting Services, you have to write a little code. If you take a look at the

DynamicHeight property of the bar chart in Design view, you’ll see how this works.

=(COUNT(Fields!ProductName.Value, “ProductSalesSummary”) / 5) & ” in”

The expression counts the number of rows returned from the dataset used in a calculation and concatenates a string value that resolves to the number of inches for the height of the chart. The result will be one plus the number of rows divided by five. Adding one avoids a division-by-zero error. The chart will allow room for about five rows per inch. I find it necessary to adjust this math in different reports to avoid adding extra white space.

Like the previous sample, I added a textbox with a drill-through action, but this one controls the chart series labels on the pie chart. Having data labels enabled does help you see the value that each slice represents, but, again, this is only useful with a limited number of axis values. Now that you see how this works, preview the report once more. Now, select 200 for the Top Products parameter list and press Enter. Click the “Show data labels” link below the pie chart and, as shown in Figure 8-6, it should be obvious that the bar chart is a better choice.

clip_image025

FIGURE 8-6: Top 200 Product Sales report.

Bubble and Stock Charts

Bubble charts are essentially a point plotted in a grid representing three dimensions. The value of the z-axis is expressed by the size of the bubble. Imagine that the bubble exists in a 3-D plane and appears larger if it is closer to you. Actually, the bubble can be a circle, square, triangle, diamond, or cross shape. This also means that a combination of shapes can be used to represent different data elements in the same chart space. The example shown in Figure 8-7 is included in the sample project and is named Ch08 – Bubble Chart Product Subcategory Profit Analysis

clip_image028

FIGURE 8-7: Bubble chart.

For sales orders grouped by subcategory, for a selected year and country, the product cost is plot- ted on the vertical axis and order quantity on the horizontal axis. Bubble size represents the simple profit calculated in the query. The example shown in Figure 8-7 helps you discover a valuable insight by revealing that Mountain Bike sales were particularly profitable in Canada.

The bubble chart properties are most easily set in the Series Properties dialog shown in Figure 8-8. With the sample report open in Design view, click the chart to show the Chart Data window and then use the call-out menu for the series in the Values field list. Here, the StandardCost, SimpleProfit, and OrderQuantity fields are assigned to the Bubble, “Bubble size,” and Category fields, respectively.

New Chart Types

Two new chart types introduced in SQL Server 2016 Reporting Services are the Tree Map and Sunburst charts. Sample reports are provided for both, which you can see in Figures 8-9 and 8-10.

It is notable the chart designer is generically designed to manage settings for every chart type. Consequently, the Category and Series group assignments, used to visualize fields differently in each chart type, can be quite arbitrary and some experimentation may be necessary to make sense of it.

clip_image031

FIGURE 8-8: Series Properties page for bubble chart.

clip_image033

FIGURE 8-9: Tree Map chart.

clip_image035

FIGURE 8-10: Sunburst chart.

The Anatomy of a Chart

The best way to learn how to use the charts in Reporting Services is to work through a few examples and exercises. An exhaustive review of all the properties and settings (some of which are shown in Figure 8-11) usually leads to getting lost and confused. Like learning to drive, start with the basic process and rules, and then just do it. You’ll figure out the details along the way.

Chart objects are organized into the hierarchy shown in Figure 8-12. Understanding this structure and the relationships between these objects will save you a lot of time and effort.

The Chart object is really is just a container. The Chart Area object does most of the work, and contains most of the useful properties. Take some time to explore the chart objects in the design interface. Because there are so many different objects, selecting the right object can be tricky at first, but with a little experience, you will become comfortable with the interface.

With a chart open in the designer, show the Properties window and then click different areas of the chart to select and view the names of various objects. Use the chart objects hierarchy as a reference as you click different areas to select objects. Additionally, you can right-click an object to reveal menu options that will take you to subordinate objects and collections. For example, if you were to right-click the Chart object, the Properties window and the right-click menu will let you find the Chart Area objects. Taking time to do this now will save you time and effort when you follow the exercise later in this chapter.

clip_image038

FIGURE 8-11: Chart properties and settings.

clip_image040

FIGURE 8-12: Hierarchy of chart objects.

Note that many of these objects are organized into collections to make them fit neatly into the standard Properties windows and design interface. As a rule, you can select an object and set its properties in one of two ways.

If you right-click an object in the chart designer, you see a menu item to edit the object’s properties. That menu may also include related objects that you can edit. Choosing this menu option opens a custom property page for the object. Choosing an option in a property page to edit another object opens another property page. These are stacked in the order in which they were opened. Some properties are actually object collections. Clicking an ellipsis (…) button for that collection opens a dialog with the object collection and associated properties.

In addition to the custom properties pages for each chart-related object, you can edit properties in the standard Properties window.

The remainder of the chapter covers charts with Multiple Series, Axes, and Areas; using the following examples:

image

image

EXERCISES

The exercises in the book include:

Exercise 1: Creating and Styling a Simple Chart

Exercise 2: Creating a Multi-series Chart

Power BI Quick Measures is a Game Changer

Every few months a feature is added to Power BI that gets my attention and really seems to make  difference.  Quick Measures is one such feature that has my attention.  As a long-time SQL Server Analysis Services practitioner, I subscribe to the belief that anything that you want to calculate should be created as a measure.  The counter argument is that, just like in Excel, every numeric column can be assigned an aggregate function in the “Summarize By” property which makes the column behave like a simple measure.  I fought this at first and then learned to embrace the default behavior because that’s what most self-service BI users will expect.  These “implicit measures” work just fine in Power BI report visuals.  The catch is that client tools like Excel can only use real measures instead of numeric columns, to perform calculations.  In serious SSAS and Power BI solutions I still create measures for any numeric values that should be aggregated.  Here’s a peek at the new Quick measures dialog:

image

For my training classes and workshops, I have been demonstrating the Quick Calcs feature.  In a workshop yesterday, I went to find it and the feature wasn’t there.  This is the screenshot from my slide deck.

image

Change is inevitable but it can be hard to keep-up.  The Quick Calcs feature doesn’t generate any code and it doesn’t create a real measure (so it won’t work if I use Excel as a front-end for the Power BI model.)

I had added the new Quick Measures feature to Power BI Desktop in the Options/Preview page.  This, apparently disables Quick Calcs and enables Quick Measures.  Although it flustered me me for a minute in front of an audience, I found this to be welcome news.  So, what is Quick Measures?  It’s a DAX calculation generator that automatically writes useful measures.  Here’s how it works…  Start by right-clicking or clicking on the ellipsis for a numeric column in the Field list and choose Quick measure…

The Quick measures dialog prompts for the necessary fields, which might be different for each calculation.  The tool generates appropriately formatted DAX calculations.  It even includes conditions to raise errors if used in the wrong context.  There are currently 19 different calculation variations that the tool will generate.  Following are two examples.  Creating a Quick measure from my [Flights] measure and choosing the Airline field for categorization produces this calculation:

Average Flights per Airline =
AVERAGEX(
    KEEPFILTERS(VALUES(‘Airlines'[Airline])),
    CALCULATE(‘Airline Performance'[Flights])
)

I’m not a fan of this naming convention.  It’s silly to begin a measure name with “Average” or “Sum”.  Users struggle enough to find what they need in the field list.  As a rule-of-thumb, name measure with the most important information first.  In this case, perhaps “Flights Avg per Airline” would be a better choice.  If there were several variations of the “Flights” measure, they should be listed together in the field list.

This one calculates the average year-to-date departure delay:

DepDelay YTD =
IF(
    ISFILTERED(‘Flight Date'[Flight Date]),
    ERROR(“Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy”),
    TOTALYTD(
        AVERAGE(‘Airline Performance'[DepDelay]),
        ‘Flight Date'[Flight Date].[Date]
    )
)

Example is often the best teacher.  Beyond the obvious benefit of creating useful calculations, this feature provide several excellent examples that you can use to learn DAX function syntax and usage.  For me, this will be the most value.  I can easily write basic calculations but don’t always remember to use filtering and context preservation functions is ISFILTERED() or KEEPFILTERS(). 

Chapter 7 – Advanced Report Design

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.

  • Pagination, and page headers and footers
  • Report headers and footers
  • Text formatting and textbox properties
  • Embedded formatting and HTML text styling
  • Master/detail reports
  • Working with subreports
  • Creating a document map

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The samples and exercises for this chapter are included in the SSDT solution that was introduced in Chapter 3. If you have not set up the book samples and exercises, return to Chapter 3 and complete those tasks.

The real power behind Reporting Services is its ability to creatively use data groups and cobinations of report items and data regions. You can add calculations and conditional format- ting by using simple programming code. By programming code, I mean anything from a single line of code to an entire library. Whether you are an application developer or a business report designer, this chapter contains important information to help you design reports to meet your users’ requirements and to raise the bar with compelling report features

PAGINATION AND FLOW CONTROL

With respect to page layout, reports have two sizing modes: interactive and printable. When users run a report in their web browser and use it interactively, they typically don’t care that much about the page size. This is particularly true with reports that have wide content like a matrix region that can dynamically grow horizontally with the data. When a report is printed or rendered to a print- able format like a PDF or Word file, we need to be mindful about fitting the content on pages.

The report designer does not make page sizing and dimensions particularly obvious so it’s an easy thing to miss. Fortunately, the science behind page sizing is pretty simple. Page dimension properties are grouped into two objects that you can select in the designer; these are shown in Figure 7-1. With the Properties window visible, click outside the report body to show properties for the report. Here you will see the InteractiveSize and PageSize properties. Expand these to see the individual Width and Height properties for each group.

clip_image002Click here to select Report

Click to select Body

FIGURE 7-1: Report properties in the designer.

If you are not using this default paper size, you can get more options by right-clicking the Report area and choosing the option to show the Report Properties dialog (shown in Figure 7-2). Either way, you will be setting the same properties.

clip_image003clip_image003[1]clip_image003[2]clip_image003[3]

clip_image004Pagination and Flow Control 177

clip_image006

FIGURE 7-2: Report Properties, Page Setup page.

Aside from the default US paper sizes shown here because my machine is set up with an English/ US

locale, you can also choose from metric sizes or several specialized layouts. The margins are set to

clip_image007

1 inch by default, which means that we lose two inches of printable space in each page. I’ve changed my margins to a quarter inch, which will maximize the page space and work with most modern printers.

TIP Quarterinch margins (about .64 cm) work with most modern printers. Some older laser printers can require up to a half inch of “gripper space” at the top of the page. This is the area of the paper the printer grabs and attaches to the drum during the print cycle. If you are trying maximize the print area, either test the report on each printer model or just be more conservative with the margin settings.

Click the Body (the area within the designer report boundaries) to check the Size properties, which are shown in Figure 7-3. To fit within a printable page, the body Width must be less than the report width added to the left and right margins.

clip_image009clip_image010FIGURE 7-3: Properties window, Size properties group.

TIP To prevent blank pages:

Body Width + Left Margin + Right Margin

must be less than Report Width. If not, the page will spill onto a second page. If there is no printable content, just empty space, the printer will feed blank paper between each page of report content. You can test this by saving to a PDF file to see if alternate pages are blank.

HEADERS AND FOOTERS

The terms “header” and “footer” can refer to three different areas of a report: the report header and footer, the page header and footer, and the header and footer areas of any data region(s) in the report. Table and matrix regions may have headers and footers associated with groups within the region.

NOTE In this paragraph, I refer to the “effective” report header and footer. My use of this term is important to understand because Reporting Services doesn’t actually designate a specific header or footer area. The area of the report body above a table or other repeating data region is “effectively” the page header.

The report header and the footer each occur only once: on the first page, at the top of the entire report (header), and at the end of the report, on the bottom of the last page (footer). In Reporting Services, there is no designated report header and footer area. The effective report header is simply the blank space containing textboxes and other report items placed above any data regions in the report body. Likewise, the effective report footer is the space below any data regions. If you were to place a table two centimeters below the top of the report body, this would give you a report header two centimeters tall. It’s as simple as that and because there is no set limit to the number of data regions or other items you can add to a report (and you can designate page breaks at any location), all the space above, below, and between these items is essentially header and footer space.

Follow along by opening the completed Ch07 – Headers and Footers report, which is in the Wrox

SSRS 2016 Sample project.

You have a lot of flexibility when displaying header and footer content. In addition to the standard report and page headers and footers, data region sections can be repeated on each page, creating additional page header and footer content. Figure 7-4 shows a table report with each of the header and footer areas labeled.

Group Row Headers

Table Header

clip_image029

Page Header

clip_image030clip_image030[1]Effective Report Header

Detail Row

Group Footers

Effective Report Footer

FIGURE 7-4: Headers and footers in the designer for a typical table report.

Page Footer

Figure 7-5 shows the first rendered page of this report. Comparing the design view of the report in Figure 7-4 with the first page rendered in Figure 7-5, note the name of the report in bold text followed by execution date and summary of years and months included in the report, in the (report) header area. The page header is omitted from the first page. Below the table, you see the page footer showing the report user’s network ID and the page number summary.

clip_image032FIGURE 7-5: Rendered table report with headers and footers.

Figure 7-6 shows the second page, beginning with the page header. Of particular note is the sum- mary of data displayed on this page in “phone book” or “dictionary” style where the year and month of the first and last items on the page are summarized in the header. Also note that even though the group for 2012 is split between pages one and two, the table header is displayed at the top of the page and the year is repeated for quarter 3. In the table, after all four quarters, a group footer shows a total of the year and then a grand total is displayed at the end of the table for all three years.

A common purpose of the page header is to display an abbreviated form of information in the report header. Naturally, you don’t want to show redundant information on the first page so it makes sense to hide the page header. Right-click the report body to add a page header and then right-click the page header to show the Page Header Properties dialog, used to set a number of related properties.

Uncheck the “Print on first page” property under “Print options.”

TIP Remember that any property displayed on a property page dialog like this is also available in the Properties window, to the right of the report designer

in SSDT. The Properties window can optionally be viewed in Report Builder, enabled from the View ribbon.

 

IGURE 7-6: Page header shown on the second page.

clip_image037

FIGURE 7-7: Page Header Properties dialog.

Tablix Headers and Detail Cells

clip_image039L

Listen up: This little golden nugget will help you make more sense of table and matrix reports that you inherit from other developers, or when you resume working on a report you haven’t touched for quite a while. When reviewing the sample report created for you, how do you know which cells in the table serve as group headers or are detail cells that aggregate values? Figure 7-8 shows you the secret. This feature is subtle but very useful.

FIGURE 7-8: Row group header boundary.

When designing a table, the fields that you drag into the Row Groups list create groups with row header cells. Fields that you drag into the table columns become ungrouped detail cells. A double broken vertical line (circled in Figure 7-8) is displayed to differentiate between group headers to the left and detail cells to the right of the line. It is apparent in this report that rows are grouped first

by Year and then by Quarter. The (Details) group, displayed in the Row Groups list, doesn’t have a group header, so the MonthName cell is on the right side of the double-broken line. Because the dataset already groups sales details by month and only returns one month per row, no month-level grouping is necessary in the table.

In a matrix, column group header cells are also separated from the detail cells with a horizontal double-broken line.

clip_image040Designing the Page Headers

NOTE As I describe these steps, don’t be concerned with following along. You will step through this process in the exercise at the end of the chapter.

With the Report Data window visible and Built-in Fields expanded, I will step you through the process as I create the header. I can drag-and-drop fields directly into the report body. This is actu- ally a slight misnomer because the “built-in” objects are not really fields per se, but global objects.

The word “field,” in this sense, refers to something that returns a value. Regardless, it is useful information you can add to the report header or footer areas. If I were to drag a built-in field, say [ExecutionTime , onto the report body, a textbox will be added at the drop location with a refer- ence to the intended object. If the object or field were dropped into an existing textbox, the inserted value becomes a placeholder for an expression. The expression can coexist with other expression placeholders, and literal text in various formats, within a textbox.

I begin with the Execution Time field, which I will drag to the existing textbox in the top-left cor- ner of the page header region. The textbox already contains the text Sales as of: in italicized format.

Next, I drop the Execution Time field/object just to the right of the existing text to create a com- posite message: Sales as of: [Execution Time]. Just peachy.

The others are similar. In the lower left-hand corner, I drag the UserID field to the textbox right after the text report user.

clip_image042In the textbox located in the lower-right corner of the page footer, I add these built-in fields to assemble the following phrase: Page [&PageNumber] of [&TotalPages] as shown in Figure 7-9.

Assembling the page footer.

You can see that the page header and footer decorations are nearly complete. Our objective is to show the first and last set of month/year values displayed on the page. Well, because the page has already been paginated by the page rendering engine, there is no need to do this work if it’s already been done for us.

Instead of using “field” references to get these values, we can use the aggregate functions FIRST()

and LAST( with the report items in the table that already return the values. I right-click in the

textbox to create a placeholder and then edit the placeholder to add a value expression. I use the callouts in Figure 7-10 to add these expressions in order; first the Month and Year report items are referenced in the range “From” group, with the FIRST functions. In the second, range “To” group, the LAST aggregate function returns the last Year and Month value for that page.

clip_image045

=FIRST(ReportItems!Year.Value)

=FIRST(ReportItems!MonthName.Value)

=LAST(ReportItems!MonthName.Value)

FIGURE 7-10: Assembling the page header.

=LAST(ReportItems!Year.Value)

TIP Pay close attention to the spaces added to the literal text when assembling static text and expressions in a textbox.

The textbox below the large report title contains two placeholders with expressions, shown in Figure 7-11. The textbox contains the static text “Includes: ” followed by an expression in the first placeholder that concatenates the first month and year in the dataset results. The static text “ to ” separates the second placeholder with an expression that concatenates the last month and year in the dataset.

clip_image048=First(Fields!MonthName.Value, “SalesSummaryByMonth”) & “, ” & First(Fields!Year.Value, “SalesSummaryByMonth”)

=Last(Fields!MonthName.Value, “SalesSummaryByMonth”) & “, ” & Last(Fields!Year.Value, “SalesSummaryByMonth”)

FIGURE 7-11: Assembling the report header.

This example demonstrates how values can be obtained from a dataset outside the boundaries of a data region like a table, list, or matrix. Outside of a data region, an aggregate function must be used to reduce multiple field rows to a single value and the dataset is passed in the second function argument.

Textbox Expressions and Placeholders

When combining values in a textbox, you really have two choices. The previous examples demon- strate the use of multiple placeholders within a textbox. Think of a placeholder as if it were a sepa- rate textbox embedded in the text for the textbox. The advantage of using multiple text placeholders in a textbox is that each can be styled with separate properties for styling features like the font, weight, and color.

NOTE Internally, placeholder text is stored as a “textrun” object within the textbox paragraph element. This is important to understand for error reporting and debugging expressions.

Internally, textboxes contain two levels of objects: paragraphs and textruns, which the visual designer calls a placeholder. When using multiple placeholders in a textbox like the previous exam- ple, this is actually a paragraph containing multiple textrun elements. This would be apparent if an incorrect expression were entered, resulting in an error. If you see the text #Error displayed, use the Output window to see the error text, which might look something like this:

[rsRuntimeErrorInExpression] The Value expression for the textrun

‘Textbox4.Paragraphs[0].TextRuns[3]’ contains an error: The expression references an item ‘TotalPages_’, which does not exist in the Globals collection. Letters in the names of Globals collection items must use the correct case

Preview complete — 0 errors, 1 warnings

TIP When you see an expression error displayed on the report as #Error, use the

Output window in SSDT to view the error or warning information.

The other approach for combining values in a textbox is to use a single expression to concatenate all the values into a single text value. Rather than the compounded placeholder text used to display the page numbers, the following expression would display the same text without the option for mixed styling features within the text:

=”Page ” & Globals!PageNumber & ” of ” & Globals!TotalPages

The Thing About Repeating Table Headers

You’ll notice that the table column headers are repeated on the second page, which is a sensible design pattern. If you look at the Tablix Properties (Figure 7-12), you’ll find settings to repeat column and row headers on each page. Seems pretty simple, right?

clip_image052

FIGURE 7-12: Tablix Properties dialog.

Somewhere in the product history, as the table and matrix data region evolved into the Tablix

(circa 2008), this feature stopped working but it is easy to fix. I do not have a good explanation for why the following step is necessary; and I think we can all agree that this might seem to be overly complicated, but I can at least show you how to get this working.

clip_image053With the table selected, click the little down arrow icon displayed to the right of the Column Groups in the Grouping Window and switch on Advanced Mode (see Figure 7-13). Advanced Mode shows several hidden objects in the Tablix data region that are used internally to manage header, groups, and cell properties. As you can see, the inner workings of the Tablix are quite complex.

WARNING I can tell you from experience that the advanced properties of the Tablix can be a Pandora’s Box if you don’t know exactly what you’re doing in there. If you plan to experiment with these properties, I suggest you make a backup copy of the Tablix or your report before you proceed.

clip_image055FIGURE 7-13: Setting up repeating page header in Advanced Mode.

Making this change is easy. Just select the first “Static” Tablix Member displayed in the Row

Groups list.

In the Properties window, change the RepeatOnNewPage property from False to True. That’s it! Use the down arrow icon to switch Advanced Mode off.

Test the report and you’ll see that the table header is repeated on each page.

COMPOSITE REPORTS AND EMBEDDED CONTENT

Beyond common, basic report design, more sophisticated reports are created by using more advanced and less common design techniques. In general, these fit into two different categories, which include using advanced properties of different report item components, and by combining multiple data regions to create composite report designs. The composite report design pattern is intriguing because there are virtually limitless options when allowing one grouped data region to repeat instances of another report item or region.

Before opening the door to the mind-blowing possibilities of composite data regions, let’s explore

the depth of some fundamental report item building blocks. OK, “mind-blowing” might be a stretch but some of the most fundamental report items have considerable capabilities, especially when used as components of a well-constructed solution.

UNLOCKING THE TEXTBOX

The textbox is one of the most fundamental and common report items. Generally, all text and data values are displayed using textboxes. The cells of a table and matrix contain individual textboxes. In addition to the text displayed, several useful properties manage the placement, style, and presentation of data.

The Font Color BackGroundColor, and BackGroundImage properties make it possible to dress up your report data with tremendous flexibility.

The BorderStyle properties of a textbox are similar to those of other report items (such as a rect- angle, list, table, and matrix). Once you have mastered the textbox properties, you should be able to use these other items in much the same way. With a table, group separation lines are created by set- ting the border properties for textboxes in header and footer rows (typically by selecting the entire row and setting the textbox properties as a group).

Three property groups are used for borders. In the Properties window, these groups are expanded using the plus sign (+) icon to reveal individual properties. The group summary text can actually be manipulated without expanding the properties, but it’s usually easier to work with specific property values. The BorderColor BorderStyle, and BorderWidth properties each contain a Default value that applies to individual properties (Left, Right Top, and Bottom) that have not otherwise been set. This provides a means to set general properties and then override the exceptions. By default, a textbox has a black BorderColor and a 1-point BorderWidth, with the BoderStyle set to None. To add a border to all four sides, simply set the default BorderStyle to Solid. Beyond this, you can

use individual properties to add more creative border effects.

Padding and Indenting

Most report items support padding properties, which are used to offset the placement of text and other related content within the item. Padding is specified in points. A unit of measure from the printing industry, a PostScript point is 1/72nd of an inch, or approximately 1/28th of a centimeter.

clip_image057

Figure 7-14 shows the four padding properties, in the Padding group of the Properties pane, applied to all textbox items. The Padding properties provide an offset between textbox borders and the contained text. You can use this to indent text and provide an appropriate balance of white space.

FIGURE 7-14: Textbox Padding properties.

Three similar properties provide more flexibility for text indentation. You can use the HangingIndent,

LeftIndent, and RightIndent properties to control paragraph-style text in rich-formatted text- boxes. These properties also enable the new Word rendering extension to apply hanging, static text indentations.

Embedded Formatting

This feature allows the text in a textbox to be structured and formatted, much like a document or web page. Textboxes support two modes: a single-value expression or a range of text containing multiple expression placeholders.

clip_image059

To format a range of text, simply highlight the text in the textbox and use the toolbar or Properties window to set properties for the selected text. Figure 7-15 shows a range of highlighted text with the HangingIndent and LeftIndent properties set to 18 points and 12 points, respectively. Note that certain keywords and phrases within the text are also set using bold and italic. Some title text has also been isolated with bold and larger fonts.

FIGURE 7-15: Text with embedded formatting.

Embedded HTML Formatting

Another option is to embed simple HTML tags within text. This provides a great deal of flexibility for using expressions or custom code to return formatted text. The HTML tags listed in Table 7-1 are supported.

TABLE 7-1: HTML tags supported with embedded formatting

TAG

DE SC RIP T I O N

<A>

Anchor.

For example: <A href=”http://www.somesite.com”>Click

Here</A>

<FONT>

Sets font attributes for a group of text. Used with the attributes color face point size size, and weight

For example: <FONT color=”Blue” face=”Arial” size=”6″>Hello</FONT>

clip_image060continues

TABLE 7-1 (continued)

TAG

DE SC RIP T I O N

H1>, H2>, <H3>,

H4>, …

Headings.

<SPAN>

Used to set text attributes for a range of text within a paragraph.

<DIV>

Used to set text attributes for a block of text.

<P>

Paragraph break.

<BR>

Line break.

<LI>

List new line.

<B>

Bold.

<I>

Italic.

<U>

Underscore.

<S>

Strikeout.

<OL>

Ordered list.

<UL>

Unordered list.

Embedded tags can be entered directly into a textbox or read from a dataset. When using static text, rather than text fed from a dataset, you must set the MarkupType property for the text placeholder. To do this, highlight the text containing the embedded HTML tags, right-click, and choose Text Properties. In the Text Properties dialog, on the General page, set the Markup type property to the selection shown in Figure 7-16, “HTML – Interpret HTML tags as styles.” When working with

data-bound text, the difference is subtle; highlight the field placeholder in the textbox, right-click, and choose Placeholder Properties….”

The Text Properties and Placeholder Properties dialogs have identical selections for the Markup type, shown in Figure 7-16.

I have provided an example in the sample report named Ch07 – Sales Order Notes. The

SalesOrderNote table contains formatted text with HTML markup tags like this:

<h2>Check This Order Before Shipping</h2>

Customer specifically needs 10 <b>RED</b> helmets.<br>She is <i>unhappy</i> that an earlier order was placed for <b>RED</b> helmets but helmets were <b>MAROON</b> colored.<br>If product is not bright red color, please:

<ol>

<li>Cancel and <b>do not ship</b> the order</li>

<li>Call the sales person</li>

<li>Do not charge the standard re-stocking fee</li>

</ol>

clip_image062

FIGURE 7-16: Placeholder Properties dialog.

When this text is interpreted, the formatting tags are applied and it will appear as it does in

Figure 7-17.

clip_image064

FIGURE 7-17: Previewed report with data-bound embedded formatting.

Would You Like Them in a Box? Using a Rectangle as a Container

I love containers. To be honest, I am a little obsessive about collecting boxes, bags, backpacks, and cases. Putting things in the right kind of container provides a sense of order and security. In Reporting Services, containers are used to encapsulate, repeat, and manage collections of content.

The rectangle report item is far more than a simple box to display on a report. It provides containment for multiple items that can be treated and managed as a single unit. The rectangle also has several properties for managing the flow and placement of information on report pages. For example, placing a group of textboxes and other items in a rectangle ensures that they all end up on the same page. You can set the rectangle to force a page break before or after its contents. By default, borders of a rectangle are not displayed, so out of the box the rectangle is really more functional than it is a visual control. By setting a few properties, you can use a rectangle to display a background image, fill color, and borders. It can also be set to repeat with an adjacent Tablix group, and on each page and the table, list, or matrix splits across pages.

The utility of a rectangle is clear when you compare the behavior of a table to a list data region. Both data regions are based on the Tablix object. Functionally, a list acts like a table with a single column and detail row and no headers with one significant difference. Every cell in a table and matrix contains a textbox. The area of a list data region is, in fact, a rectangle—which is the reason that any report items dragged into that area remain the size and position you place them. If you

were to drag a rectangle into a table cell, it would replace the cell textbox with the rectangle. Then, constituent items dragged into the same space would no longer fill the cell.

Rectangles can simplify report design when used conservatively. Because the designer shows a rectangle with gray borders, similar to a textbox, returning to a previously designed report may take some poking around to decipher where each of the container objects reside.

clip_image066Figure 7-18 shows an example of a rectangle used to contain several textboxes to display the report parameters. During design, I may need to adjust the size and placement of the elements, and using the rectangle allows me to move the entire block of items as a group.

FIGURE 7-18: Report header content contained in a rectangle.

clip_image068As requirements evolve, I can easily cut and paste the rectangle as a single object and place it in the page header (Figure 7-19) or put it below the matrix.

FIGURE 7-19: Page header content contained in a rectangle.

The following example shows how using a rectangle report item container manages page flow. In Figure 7-20, a page break is set with BreakLocation=End. This forces a page break after the rectangle is rendered so the matrix content would be on the next page of the report.

clip_image070FIGURE 7-20: Setting a page break in the rectangle properties.

The example report uses long parameter lists, which take up a lot of page space. I recently had a consulting client who asked that when a report similar to this one is used in interactive mode, the report header appear as usual, like the one shown in Figure 7-21, but when the report is rendered to Excel, the parameters be displayed only on a separate worksheet.

Using a rectangle to control the page name and conditional page break was quite easy by using

an expression on the Disabled property. Figure 7-22 shows the expression that conditionally tog- gles the Disabled property based on the built-in RenderFormat.Name property. This effectively disabled the page break in all cases where the report is not rendered to Excel.

clip_image072

FIGURE 7-21: Long parameter text in page header.

Expression

clip_image074

FIGURE 7-22: Setting the PageBreak Disabled property with an expression.

The resulting Excel workbook (shown in Figure 7-23) contains two worksheets, each named as a result of the PageName properties for the rectangle used to manage the parameters and the matrix. The Excel renderer translates any explicit page breaks in the reports as a new worksheet tab.

clip_image076

FIGURE 7-23: Report rendered to Excel.

DESIGNING MASTER/DETAIL REPORTS

Most data can be expressed in a hierarchal fashion. Whether stored in related tables in a relational database, as dimensional hierarchies in a cube or tabular structure, or as separate spreadsheets or files, structured data can usually be organized into different levels, which is often a natural way to present information for reporting. Common examples of master/detail data include invoices and line items, customers and orders, regions and sales, categories and products, colors and sizes, and man- agers and workers. The best way to organize this data in a master/detail report depends largely on how your users want to see it visualized. For each master record, details may be presented in a rigid tabular or spreadsheet-like form or in free-form layout with elements of different sizes and shapes placed at various locations within a repeating section. And, of course, details may also be expressed visually using charts, icons, and gauges.

The last consideration for master/detail report design is whether the data source for the master records and detail records can be combined into a single data stream. If records exist in different tables in the same database, this is a simple matter of joining tables using a query. If the records can’t be combined in a query or view, the two result sets should expose the fields necessary to join

them, and a subreport can be used. This section about composite reports explores techniques for combining data ranges to filter a single dataset and then uses subreports to combine two separate data sources.

When constructing a hierarchal report, you have a few different techniques at your disposal, includ- ing using a table, matrix, list, or subreport.

Repeating Data Regions: Table, Matrix, and List

In Chapter 6, you learned that the purpose of a data region is to repeat rows and columns for each instance of a group or detail record, and you’ve already seen several examples using a table and matrix. The purpose of a data region is to repeat report items. You can actually add just about any- thing in place of the default textbox or rectangle container that the designer creates in the detail cells for you.

Table as a Master/Detail Container

By default, all the cells in a table are textboxes unless you drag a different type of item into the

cell. Any embedded content will stretch to the dimensions of the cell and will grow vertically if text wrapping in any columns causes that row to grow. To prevent items from stretching, place a rectangle in the cell first and then add the new report item to the rectangle.

TIP To prevent items embedded in a table detail row from stretching vertically when an adjacent textbox wraps, place a rectangle in the cell first and then add the new report item to the rectangle.

Regions and items embedded in the detail row are repeated vertically, once for every unique detail group value. Detail rows in a table can be used as a container for all other report items and data regions. There is literally no limit to the number or level of embedded items that can be placed into a table, matrix, or list. All regions and report items contained in the table must be bound to the same dataset.

NOTE When using embedded data regions and report items within a table, matrix, or list, the contained data regions and items are exclusively bound to

the containing dataset via the DatasetName property. It is possible to reference a different dataset by using scoped and aggregated field expressions (for example, FIRST, SUM, and so on). You can also use LOOKUP and LOOKUPSET functions or custom code to reference another dataset.

The sample report named Ch07 – Product Category Sales Profile by Year contains a table data

region with drill-down on the first-level group (Year). The detail row contains an embedded column chart with the sales quantity grouped by product category values, as shown in Figure 7-24.

clip_image080FIGURE 7-24: Multi-level table report with drill-down and repeating chart.

Matrix as a Container

Like using a table as a master/detail container, a matrix can encompass any data region or combination of report items, and repeat an instance of these items both on rows and columns. The detail cells of a matrix are textboxes by default but can be replaced with most any type of report item.

clip_image082

A variation of the theme presented in the previous section, the sample report named Ch07 – Product Category Sales Profile by Year and Country (shown in Figure 7-25) has a matrix based on a dataset containing one additional field, which is used to group columns on the country. The same column chart used in the previous example is repeated both on rows (for each quarter) and on columns for each country.

FIGURE 7-25: Matrix report with repeating chart.

List Data Region as a Container

A list is essentially a single-row, single-column table with no headers. Instead of containing a text- box by default, the detail “cell” or area of a list contains a rectangle. Any items placed within the

list area will remain the same size and position, rather than stretching to fill the area in the designer. This would roughly be the same thing as if you were to replace a table detail cell textbox with a rectangle and remove the header and footer rows.

A list is a great tool for repeating a region of formatted report items with every group of parent values in a hierarchy. Because a list is based on a Tablix object, like a table and matrix, it has the same essential rules of behavior. The repeated data regions and report items in the list area are bound to the same dataset as the container.

The following examples are sample reports that contain one or more report items contained within the cells or repeated detail area of a data region. The sample report named Ch07 – Product Cost and List Price – Embedded Chart is shown in Figure 7-26. A list data region contains several textboxes and a pie chart.

clip_image084

FIGURE 7-26: List report with free-form layout and embedded chart.

The report named Ch07 – Product Cost and List Price – Embedded Table and Chart is shown in Figure 7-27. The list data region in this report contains textboxes, a pie chart, and a table that shows the order detail history for each product.

clip_image086

FIGURE 7-27: Combination report with list, embedded table, and chart.

NOTE An important design consideration when using multiple, embedded data regions is that all report items are based on only one dataset. The dataset must include fields to group the containing region and the details for the data region it contains.

Because only one dataset can be used for both data regions (the list and table) in this example, all necessary fields and details are required. The dataset used to drive the list grouped by products must include the order details for the embedded table.

Subreport

A subreport is a container for another report, visually embedded within the main report. As a method of object reuse, subreports can reduce redundant effort and circumnavigate some other bar- riers but they do impose some compatibility issues with certain rendering formats. Be cautious and test your designs thoroughly. As a rule, I don’t use subreports unless they are necessary.

As a master/detail design pattern, subreports allow a data region to encompass detail records from a different dataset, or even an entirely different data source. I will demonstrate how to use them in the “Designing Subreports” section later in this chapter.

Drill-Through Navigation

Actions and report navigation are covered in Chapter 12. I include this topic here because report navigation is often an effective replacement for a complex, multi-level hierarchal report. It can be

an effective complement to a summary report that enables report users to get more information and insight by navigating to details rather than including them in a single, comprehensive report. Using navigation actions correctly can help you architect reporting solutions rather than big, cumbersome and monolithic reports.

Consider that a report with four levels of drill-down groups must execute a large query that returns all the records at the detail level, just so the users have the option to drill to a particular branch of detail. Rather than expanding the drill-down branch within the same report, a group item could display a link that allows the user to navigate to another report. In a well-orchestrated solution,

the users’ perception is that they are simply moving around within their report dashboard solution, instead of moving from report to report.

Groups and Dataset Scope

One of the fundamental reasons that master/detail reports work—and are relatively easy to con- struct—is the principle of dataset scope. The term scope refers to the portion of data from a dataset that is available within a group. When a data region, such as a table, list, or matrix, is rendered, the data is sectioned into the subranges according to a group definition. Any report items or data region items placed in a grouped area, header, or footer are visible only to the data currently in scope. This eans that if a table, for example, has a group based on the ProductCategory field and another table is placed in the group header, a table is rendered for each distinct ProductCategory value. Each table instance “sees” a range of detail records filtered by this group value. This can be an incredibly powerful feature, because there is no stated limit on how many items can be embedded

within a group; nor is there a limit on group levels and nested embedded data regions. With that said, we have found it impractical to embed several data regions to create overly complex reports.

In this section, we will apply this principle of group embedded data regions for each data region container. This includes the list, table, and matrix.

More Aggregate Functions and Totals

Previously, you saw how the SUM and AVG functions work in a detail group and in group total rows. Reporting Services supports several aggregate functions and each function accepts one or two argu- ments that are passed in parentheses. The first argument is the field reference or the expression to aggregate. The second, optional argument is the name of a dataset, report item, or group name to indicate the scope of the aggregation. If not provided, the scope of the current data region or group is assumed. For example, suppose a table contains two nested groups based on the Category and Subcategory fields, respectively. If you were to drag the SalesAmount field into the Subcategory group footer, the designer would create an expression like this: =SUM(Fields!SalesAmount

Value). Remember that a field expression appears as a placeholder in the report design. For

example, the placeholder text [SUM(SalesAmount)] actually represents the full expression:

=SUM(Fields!SalesAmount.Value). Right-click and choose Expression to see the full expression.

The expression would return the sum of all SalesAmount field values within the scope of each distinct Subcategory group range. Table 7-2 shows all of the aggregate functions supported by Reporting Services with a brief description of each, for reference.

TABLE 7-2: Aggregate functions supported in report expressions

FUN C TI ON

DE SC RIP T I O N

AVG()

The average of all non-null values.

COUNT()

The count of values.

COUNTDISTINCT()

The count of distinct values.

COUNTROWS()

The count of all rows.

FIRST()

Returns the first value for a range of values.

LAST()

Returns the last value for a range of values.

MAX()

Returns the greatest value for a range of values.

MIN()

Returns the least value for a range of values.

STDEV()

Returns the standard deviation.

STDEVP()

Returns the population standard deviation.

SUM()

Returns a sum of all values.

VAR()

Returns the variance of all values.

VARP()

Returns the population variance of all values.

In addition to the aggregate functions, some special-purpose functions behave in a similar way as aggregates but have special features for reports, as shown in Table 7-3.

TABLE 7-3: Special-use dataset row functions

FUN C TI ON

DE SC RIP T I O N

LEVEL()

Returns an integer value for the group level within a recursive hierarchy. The group name is required.

ROWNUMBER()

Returns the row number for a group or range.

RUNNINGVALUE()

Returns an accumulative aggregation up to this row.

To demonstrate scoped aggregates, I start with a copy of the sample report: Ch06 – Sales Summary with Groups. As a refresher, Figure 7-28 shows the first page of the report with callouts containing the aggregate expressions for the last two columns.

clip_image090=SUM(Fields!SalesAmount.Value)

FIGURE 7-28: Different aggregate functions used in totals.

=AVG(Fields!SalesAmount.Value)

The expressions for these two columns are the same for the detail row and the ProductCategory group footer row. With only the field name passed to an aggregate function, SUM and AVG in this case, the current group level is implied. For the details group, the result would be the same as:

=Sum(Fields!SalesAmount.Value, “Details”)

For the ProductCategory group, the implied aggregate value would be the same as:

=Sum(Fields!SalesAmount.Value, “ProductCategory”

clip_image093With the group name specified in the second function argument, the expression will always apply to that group level. To demonstrate, I’ve removed the last column and then added a new column using this expression in the detail row. Figure 7-29 shows the results. As expected, the category total is applied to every calculation.

FIGURE 7-29: Using a scoped aggregation.

=SUM(Fields!SalesAmount.Value, “ProductCategory”)

Now that we have access to the aggregate value at any level in the group hierarchy, we can calculate the percentage of contribution to the bottom line. Let’s see it all the way through now. The final expression is pretty simple:

=Sum(Fields!SalesAmount.Value)/Sum(Fields!SalesAmount.Value, “ProductCategory”

clip_image095To assemble the solution in Figure 7-30, The same expression is applied to the detail and group total footer cells in the last column. I’ve also modified the format property for these cells appropriately to display a percentage value.

FIGURE 7-30: Putting the calculations together in a meaningful solution.

DESIGNING SUBREPORTS

When I started using Reporting Services to design reports with nested groups and data regions, my first impulse was to use subreports as much as possible. This seemed like the best approach because I could design simple, modular reports and then put them together. The programming world pro-

motes the notion of reusable objects. However, the downside of this approach is that subreports can create some challenges for the report rendering engine, resulting in formatting issues and poorer performance. In SQL Server 2000 and 2005, subreports didn’t render at all in Excel. Improvements have since been made for Excel rendering, but there are inherent challenges for subreports to render consistently for all report rendering formats; particularly with Excel. When using subreports, care- fully test the report to be sure that it will render in the target format.

NOTE Subreports are useful for implementing a variety of design patterns but they are not a cure-all. If you can design a report by embedding data regions into a list, table, or matrix, you are likely to get better results than if you use a subre- port to do the same thing.

A subreport is a standalone report that is embedded into another report. It can be independent, with its own dataset, or, using parameters, you can link the contents of a subreport to data in the main report.

NOTE Subreports fall into a small category of SSRS features that have some limitations that have changed and are likely to continue to change as the product evolves. This is why it is important to test your design in all the rendering for-

mats and scenarios where it will be used.

There are some limitations to the content and formatting that can be rendered within a subreport. For example, a multicolumn report simply doesn’t work in a subreport (depending on the render- ing format used). If you plan to use multiple columns in a subreport, test your report with the rendering formats you plan to use.

Subreports generally have two uses. The first is for embedding one instance of a separate report into the body of another report with an unassociated data source. The other scenario involves using the subreport as a custom data region to display repeated master and detail records in the body of the main report. From a design standpoint, this makes perfect sense. Using a subreport allows you to separate two related datasets and perhaps even data sources, linked as you would join tables in

a SQL query. It allows you to reuse an existing report so that you don’t have to redesign functional- ity you’ve already created. However, there may be a significant downside. If the master report will consume more than just a few records, this means that the subreport must execute its query and ren- der the content many times. For large volumes of data, this can prove to be an inefficient solution. Carefully reconsider the use of subreports with large result sets. It may be more efficient to construct one larger report with a more complex query and multiple levels of grouping, rather than assume the cost of executing a query many times.

NOTE I rarely use subreports in standard scenarios to produce master/detail reports. If I need to use a subreport, the main report is limited to just a few records.

A subreport can be linked to the main report using a correlated parameter and field reference so that it can be used like a data region, but this is not essential. A subreport could be used to show aggregated values unrelated to groupings or content in the rest of the report.

Creating a subreport is like creating any other report. You simply create a report and then add it to another report as a subreport. If you intend to use the main report and subreport as a Master/ Detail view of related data, the subreport should expose a parameter that can be linked to a field in the main report. In the following walk-through, you’ll build a simple report that lists products and

exposes a subcategory parameter. The main report will list categories and subcategories, and the product list report will then be used as a data region, like a table or list in previous examples.

Federating Data with a Subreport

When the data source for a master data region is different from the data source for detail records, using a subreport can be just the ticket for creating a master/detail report. The following example combines report data from two different data sources.

In the sample project, you will find two reports named Ch07 – Product Orders Subreport and Ch07

– Product Details (subreport container). The “container” report contains a list whose data source is the relational sample database: WroxSSRS2016. The other report contains a table with a data source based on the Adventure Works Multidimensional SSAS database.

This literally means that we’ll be using two different languages, T-SQL and MDX, and a parameter- ized expression will provide some translation between the two. Records in the Product table, located in the WroxSSRS2016 database, can be related using the ProductKey column. This column contains values from the Product table in the WroxSSRS2016 database.

clip_image100

The master report, Ch07 – Product Details (subreport container), is shown in Figure 7-31. This report contains a list data region that is bound to the following query and whose data source is the WroxSSRS2016 data warehouse database.

FIGURE 7-31: Adding a subreport to the main report.

The dataset for this report is pretty simple and just returns a set of products that have cost and price information. The ProductKey column is present so we can pass it to the subreport as a parameter:

SELECT

ProductKey, ProductName, ProductCategory, StandardCost, ListPrice

FROM

Product

WHERE (StandardCost IS NOT NULL) AND (ListPrice IS NOT NULL ORDER BY ProductName

Figure 7-32 shows the child report, Ch07 – Product Orders Subreport, in the Designer. This report is simply a table bound to an MDX query. The data source for this dataset is the Adventure Works Multidimensional SSAS database. I have added a callout to the figure to show the expression used to convert the report parameter to an MDX member reference passed into a query parameter named ProductUniqueName.

Within the dataset query expression, the ProductUniqueName parameter is used in the WHERE clause using the STRTOMEMBER MDX function. This is a standard filtering convention, commonly used in MDX queries.

clip_image102

FIGURE 7-32: Modify the dataset parameter using an expression.

NOTE For more examples of parameter-passing in MDX queries, refer to

Chapter 10.

SELECT

{

[Measures].[Reseller Sales Amount]

[Measures].[Reseller Order Quantity]

} on Columns, NON EMPTY [Date].[Date].[Date].Members

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_VALUE

on Rows

FROM [Adventure Works

WHERE

(

STRTOMEMBER( @ProductUniqueName ) [Date].[Calendar].[Calendar Year].&[2013

)

The details group for the master report list data region is set to the ProductName field. This satisfies the requirement that, for a data region to contain a nested data region object, it must have a group defined. You create the subreport by dragging and dropping the Ch07 – Product Orders Subreport report from the Solution Explorer into the list area.

Note that regardless of the dimensions of a subreport at design time, when dropped into a contain- ing report, it always appears as a square area that usually takes up more design space than neces- sary (which also expands the dimensions of its container). After resizing the subreport, I also had to resize the list to appear as it does in Figure 7-33.

clip_image105

Right-click the subreport and choose Subreport Properties to set the parameter/field mapping, as shown in Figure 7-33. The Subreport Properties dialog, shown in Figure 7-33, is used to map a field in the container report to a parameter in the subreport.

FIGURE 7-33: Product Details report with subreport in designer.

Navigate to the Parameters page, and then click Add to define a parameter mapping. Under the Name column, select the ProductKey parameter. Under the Value column, select the ProductKey field. Click OK to save these changes and close the Subreport Properties dialog.

This completes the report design. Using lists and subreports typically makes the design process more ad hoc and artful than when you use more rigid tables. Go back and check the size and placement

of items so that they fit neatly within the subreport space. You often have to go through a few itera- tions of preview and layout to make the appropriate adjustments.

clip_image107

At this point, you should be able to preview the report and see the nested table/subreport, as shown in Figure 7-34.

FIGURE 7-34: Product Details report in preview.

NAVIGATING REPORTS

Reports of yesterday were static, designed for print. At best, they could be previewed on a screen. To find important information, users had to browse through each page until they found the information they were looking for. Today, you have several options to provide dynamic navigation to important information—in the same report or to content in another report or an external resource.

Creating a Document Map

The document map is a simple navigation feature that allows the user to find a group label or item value in the report by using a tree displayed along the left side of the report. It’s sort of like a table of contents for report items that you can use to quickly navigate to a specific area of a large report.

You typically will want to include only group-level fields in the document map rather than including the detail rows.

NOTE The document map is limited to the HTML, Excel, and PDF rendering formats. In the Excel and HTML formats, the document map may not survive when you save report files to an older document format, such as Pocket Excel on an older Windows Mobile device.

The sample report Ch07 – Products by Category and SubCategory (Doc Map) demonstrates this feature. I’ve added the ProductCategory and ProductSubcategory groupings to the document map. In the Group Properties dialog for the Category row group, on the Advanced page, I set the Document map property using the drop-down list to the ProductCategory field.

WARNING Be careful to specify the document map label property only for items you want to include in the document map. For example, if you specify this property for a grouping, don’t do the same for a textbox containing the same value. Otherwise, you will see the same value appear twice in the document map.

Figure 7-35 shows a report with a document map. The report name is the top-level item in the document map, followed by the product category and subcategory names.

You can show or hide the document map using the leftmost icon in the Report Designer’s Preview or the Report View toolbar in the Report Manager or SharePoint Report Viewer web part after the report is deployed to the server.

NOTE My experience has been that the drill-down and document map features usually don’t work well together because they duplicate some functionality. Use the document map to navigate to a visible area of the report.

clip_image112

FIGURE 7-35: Multi-group table report with document map.

…EXERCISES…