Professional SSRS 2016 Preview: Chapter 4 – Basic 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.

• Using report design tools
• Using report data building blocks
• Preparing the report data
• Designing the report layout
• Setting formatting properties
• Validating report design and grouping data

Report design is both a science and an art. It is a science because there are definitely standard and repeatable methods for designing common report types.

Like other forms of science, report design can be a methodical and sometimes tedious process of repeating the same steps and tasks with a very predictable outcome in each report you create. Comparatively, report design is also an art because good reports have elements of composition and style. Experienced report designers learn to use color, font and styling choices, graphic elements, as well as shading and white space to create balance and attractive presentations. The trick is finding the right balance between the methodical science and expressive art of report design.

USING REPORT DESIGN TOOLS

Two different tools are used to design reports. These include a standalone design tool called Report Builder that enables you to design and deploy reports one-at-a-time, just as you would edit a document with Microsoft Word or Excel. The second tool, The Visual Studio Report Designer, is part of the SQL Server Data Tools that install with the SQL Server 2016 client tools. This tool is optimized for developers and project teams to create and manage multiple reports in a coordinated IT solution. Most of the skills learned with one tool will transfer to the other.

Following is brief description of each numbered callout:

1. Quick Access toolbar

Similar to modern Office applications, shortcuts to popular commands are available here.

2. Tabs and Ribbon area

All of the Report Builder commands are accessed from the File, Home, Insert, or View ribbons. Commands are arranged in groups with titles along the bottom of the ribbon, such as “Font,” “Paragraph,” “Border,” “Number,” and “Layout.”

3. Report Data pane

This provides access to data, built-in fields, and other items used in the report design. This is where you create and manage parameters, images, data sources, and datasets.

4. Report design canvas

This is the area where you design your report. Use the Insert ribbon to add data regions and report items to the canvas, and then interact with these items to complete the report design.

5. Row groups and Column groups

For a selected data region, this is where you add or modify groups used to consolidate and aggregate detail records.

6. Report server connection information

When Report Builder is launched from web portal, a connection is maintained to the report server. When connected, changes to the report are saved to the server rather than to the local file system.

7. Design, Run, and Zoom controls

The Design and Run icons have the same function as the Design/Run toggle icon on the Home ribbon. Report Builder offers a zoom feature, while the SQL Server Data Tools Report Designer does not have this capability. There are some additional window panes and features not shown by default. These options can be enabled on the View ribbon. These are discussed in more detail in later chapters.

UNDERSTANDING REPORT DATA BUILDING BLOCKS

Reports are made up of some basic components, which are shown in Figure 4-2. In this introductory chapter, I’ll cover these concepts just enough so that you can get started and see how the essential mechanics of a report work. You’ll learn more in the next few chapters.

Data Sources

You will learn more about both data sources and datasets in Chapter 5, “Database Query Basics,” when the discussion spotlights working with databases and designing queries. Every report will have at least one data source and one or more datasets.

A data source is a simple object that contains information needed to connect to a data source (usually a database). For a relational database product like Microsoft SQL Server, the data source contains the address and information needed to connect to a database server and a specific database.

It includes information about how the report should authenticate when it connects and runs queries.

A data source can either be embedded into each report, or a single data source can be shared among multiple reports so that the connection information can be maintained in one place. If the report administrator were to update the shared data source with the address of a new production server, every report would suddenly query the new server. Generally speaking, using shared data sources is considered a good practice, but some notable exceptions are addressed in Chapter 5. For now, let’s keep this simple.

Datasets

A dataset is the report object that handles the actual data and fields to populate a report region. Datasets are more than just a container for report queries because they also manage all the field metadata and the parameters used for filtering data. Some advanced dataset properties also allow the data to be filtered and manipulated before the data gets to the report data region.

Data Regions

The best way to begin to understand data regions and report items is to take a look at the Insert ribbon in Report Builder, as shown in Figure 4-3.

Three different data region components act as containers for report items. The Table, Matrix, and List data regions provide different grouping and layout options. The purpose of a data region is to repeat a region of data-bound report items based on detail data rows or a group of data field values.

Table

In its simplest form, a table is a grid with a detail row having one cell or column per field from the associated dataset. When the report is rendered, the detail row is repeated once per row returned

by the dataset query. Again, that’s the simple version. Optionally, the detail row can be grouped by any field or combination of fields. This causes the data to be summarized for unique values in the grouped field(s). Further, multiple groups and associated header and/or footer rows can be added

to the table to form a hierarchy of grouped values. Imagine sales orders grouped by year and then month with headings and totals for each group. Then for each month, each order date is displayed as a detail row.

The table report in Figure 4-4 shows orders grouped by Product Category, Product Subcategory, and then by Product Name. All of the subcategories with the Accessories category are summed into a group total.

Matrix

A matrix is a variation of a table where data is grouped by columns as well as rows. A couple of key differences between a table and matrix are that matrix reports typically don’t include detail cells (in the same way that a table would include detail rows) because values at the lowest level are a summary of both row and column groups. Another differentiator is that, because of the expanding column groups, matrix reports are often not confined to a printed page. There are exceptions, but matrix reports are intended to be flexible by design.

Figure 4-5 shows a matrix report with repeated columns for each year, followed by a column group total. If one more year of data was present in the results, the matrix would be two more columns wide.

List

Data regions can contain report items and other data regions. The list is a flexible container for data-bound report items and other data regions that can be positioned anywhere within the rectangular list area. As a data region, it’s really just a rectangular container bound to a dataset and grouped on a field value.

The example in Figure 4-6 shows a design view of a list grouped by the Year field, containing a textbox showing the year alongside a simplified copy of the table from the earlier example.

In Figure 4-7, you see the resulting report. Note that the year textbox and the table are completely independent as far as size and position, but the data in each instance of the table is filtered only for the corresponding year.

Report Items

A report item is the simplest object used in report design. Some report items (such as a textbox or image) can be bound to a dataset field, while others (such as a line or rectangle) are used for display or formatting purposes.

When Reporting Services was being created, the early documentation referred to objects like text- boxes, images, lines, and rectangles as “controls” because they were similar to the objects used by application developers to add visual elements to a form. Although the concepts are very similar, reports and custom-developed applications are different. This category of objects was given the name “report items” so it wouldn’t be confused with controls used in other types of Visual Studio projects. Sticking with textboxes for now, other report items are examined in later chapters.

Report Builder opens to the Getting Started page shown in Figure 4-10. The wizard dialogs may help you automate the first few steps and may be beneficial if you haven’t created a certain report style before. However, you will quickly graduate beyond the wizards, and I am not going to use them so that you can become more familiar with the basic and essential building blocks used in nearly all reports. Just trust me on this.

The Data Source Properties dialog shown in Figure 4-12 prompts you for a data source name, connection type, and information about your server and database. No spaces are allowed, but you can use mixed case with some punctuation characters like underscores and hyphens. Consider using the name of the database here. The specific connection information prompts may change depending on the connection type selection. You are also prompted to use a shared or embedded connection, which is discussed in Chapter 5. For simplicity, you will use an embedded connection in this report.

This opens the Connection Properties dialog shown in Figure 4-13, and prompts you for information about your SQL Server. Because you’re using a local instance of SQL Server, you will connect to the SQL Server service on your development machine. In a production environment, you would enter the server name. If you are connecting to a named instance of SQL Server, the name would be entered as SERVERNAME\INSTANCENAME

Note that in a large production environment when you know the server and instance names, avoid using the Server name drop-down list, which causes the SQL Server Browser service to search the entire network for servers, and can be time-consuming. When a default instance of SQL Server is installed locally, for convenience, you can use the aliases LocalHost

Designing the Report Layout

A table is one of the most fundamental yet useful data regions you will use in report design. This exercise uses a simple table with five columns for fields. Totals, headers, and footers are covered in the subsequent chapters, but for now, you will define a detail group to consolidate the dataset rows for each product.

Take a look at the dataset in the Report Data pane to the left and notice that a list of fields was added, as shown in Figure 4-17.

You have two different ways to add fields to a new table. You can either drag-and-drop fields from the dataset in the Report Data pane, or use the field selection list in the detail cells of the table. In this exercise, you will use the second method.

With a little experience, you will become accustomed to how tables behave in the designer. Until then, it is easy to fumble a bit. When a

table isn’t selected, clicking once on any cell will show a set of gray boxes above each column and to the left of each row. These are called column and row selectors r selection handles. If a cell is selected, a heavy border is displayed around that cell. Click in the cell but not on the text to select a cell. If you click once more, the cursor is placed within the textbox contained in the selected cell. Click on a different cell to deselect the textbox. Reviewing the Report

Take a look at your work so far. You can toggle between Design and Run views to preview how the report will look when it runs with data. These options are found both on the Home ribbon and on the right side of the status bar at the bottom of the Report Builder.

Since you opened Report Builder from the web portal, the report is saved to the report server rather than the file system.

Congratulations! You’ve created a table report showing a summary of product orders. At this point you could call the report done and move on but, as Columbo always said, there is one more thing…

Validating Report Design and Grouping Data

I do a lot of demos for consulting clients and at conferences. The thing about a demo is that it’s usually prepared and practiced so you never see the little flaws. I won’t do that to you. This report has a potential flaw that should be addressed. Recall that you used a view named vSalesSummary-YearProduct for the report dataset. This view summarizes all the sales orders and returns one row for every unique combination of Year, Product Category, Subcategory, and Product.

I purposely didn’t include the Year field in the report to demonstrate this common scenario. How much data is displayed in the report? If you look at the page count in the report toolbar, it doesn’t actually tell you. See where it says “1 of 2?”… Reporting Service doesn’t actually know how many pages there are because it has only rendered two pages so far.