Professional SSRS 2016 Preview: Chapter 5 – Data Access & Query Basics

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.

• Database and query essentials
• Understanding relational database principles and concepts
• Data source management
• Datasets and using query design tools
• Query authoring using the Report Builder query designer
• Query authoring with SQL Server Management Studio
• Using single and multi-select parameters

Data source queries are the foundation of reporting and writing effective queries and are essential to design.

If your reports use the SQL Server relational engine, or SQL Server Analysis Services for data sources, you may prefer to use a familiar and more-sophisticated query design tool like SQL Server Management Studio (SSMS). By contrast, the report designer includes useful but simplified query tools. Many other data sources are supported with more rudimentary query design options, as well. This chapter addresses T-SQL query design for SQL Server.

NOTE If you are new to the T-SQL language and are designing reports for SQL Server, pick up a copy of Microsoft SQL Server 2012 T-SQL Fundamentals (or one of his many other fantastic books).

(Microsoft Press: Redmond, WA, 2012) by Itzik Ben-Gan.

Reporting Services strives to reach two audiences—namely, a broad spectrum of users who can be grouped into two camps. The first is the business user needing to create simple reports easily, and the second is the experienced developer or database professional who needs to create sophisticated reports using complex queries and intricate program logic. History has proven that meeting both needs is not an easy balance. As a result, there are two report design tools and myriad features addressing the needs of the novice, as well as the advanced report designers. Report Builder is right- sized for business users, making it an ideal choice for those who will use database objects that have been prepared for them by their corporate IT staff. You will continue to use Report Builder and then you will also use SQL Server Management Studio for T-SQL query design with the SQL Server relational database engine.

DATABASE ESSENTIALS

You have learned that using Report Builder to get data from a database view is pretty simple. The Query Designer in Report Builder is relatively simple, but it lacks some of the sophistication avail- able in the SQL Server Data Tools (SSDT) Report Designer. If you have the luxury of encapsulating query logic into database objects like views and stored procedures, in theory, you should not need to embed complex queries in your reports. That may be true in a perfect world, where you have per- mission to create objects, or your database administrator is actively involved in report projects and creating views and stored procedures for you, but that’s not always practical.

SSDT Report Designer leverages query design tools that get installed with the SQL Server client tools. These tools give more advanced users a great deal of functionality and capabilities that are usually appropriate for application developers and IT professionals. As an IT professional and tenured report designer, I often prefer to use Report Builder in many cases. If I need to build complex queries, I step out of Report Builder and use the SQL Server Management Studio (SSMS) query design tools. For me, this is the best of both worlds. Whether using Report Builder or SSDT to create reports, SSMS is a superior query authoring experience.

Relational Database Concepts

Have you heard of the five-minute university? You would have to be as old as I am to remember this classic comedy routine from Don Novello (the entertainer known as Father Guido Sarducci) says that after four years of college, after you’ve finished studying and passing all the exams, all you’re going to remember years later can be taught in five minutes. That’s probably a stretch of the truth, but the basics can be quite simple. So, what follows is the five-minute course on T-SQL.

What’s a Sequel?

This is a true story. Back in the early days of database technology, as the products and languages used today were in their infancy, the math geniuses at IBM were pioneering relational database principles and the rules of normal form. That part of history, and the design patterns that ensued, is sacred ground that few would ever challenge. Several development teams and companies followed, all with their own database products modeled after the same founding ideas and language constructs.

The Battle of the SQL Acronym

One early incarnation of the relational database was actually named “SEQUEL,” which stood for Select English QUEry Language. It was one of many attempts to create a memorable phonetic contraction. The word “English” fell out of favor because there were those with other persuasions in the community, and the driving concept was that the language had “Structure” and not so much the ability to just “Select” things. By committee and community consensus, the official language became known as SQL, which stood for “Structured Query Language.” People with slightly different product alignments or even “religious” persuasions about their toolset will often butcher the name. Pronouncing SQL correctly is a little bit like the traditional pronunciation of some U.S. cities like Louisville, Kentucky (pronounced “Lew-A–Vul”), Aloha, Oregon (pronounced “Ah–Low–Uh”) or New Orleans (let’s not even try). Likewise (and just to be clear), the language for Microsoft’s database product is SQL pronounced “See-Kwel,” not “Es-Kew-El,” and not “Em Es Es Kew El.”

The Battle of the Brand

There are quite a few dialects of SQL used with various database products. Some have MySQL (pronounced “My See-Kwel”) and others have PL/SQL (pronounced “Pee-El See-Kwel”) and others just have plain-old SQL. But we have T-SQL. Why T-SQL? End-to-end, the purpose of the SQL data- manipulation language is to execute database commands and manage transactional integrity by ensuring that all database operations are consistent, reliable, and durable. The term “Transaction” reaffirms SQL Server’s commitment to enforcing transactional integrity, and, thus, the product name was eventually shortened to T-SQL.

DATA SOURCE MANAGEMENT

A data source contains the connection information for a dataset. Data sources can be created for a specific report dataset, or can be shared among different reports. Because most reports get data from a common data source, it often makes sense to create a shared data source. Using shared data sources has a few advantages. Even if you don’t have several reports that need to share a central data source, it takes no additional effort to create a shared data source. This may be advantageous in this case because the data source is managed separately from each report and can be easily updated if necessary. Then, as you add new reports, the shared data source will already be established and deployed to the report server.

Embedded and Shared Data Sources

Data sources can be embedded into each report or deployed to the report server as a shared object. The latter option has the advantage of giving you or your report server administrator one place to manage connection information for several reports. Using shared connections is generally a recommended practice, especially in a large-scale environment. SQL Server Data Tools is introduced in Chapter 6, “Grouping and Expressions,” and that’s the tool you will use to define shared data sources. A shared data source can also be created directly on the server using the web portal, but you can only consume a shared data source created this way using Report Builder.

Query Design Tools

For SQL Server data sources, you have some Query Designer options to choose from. The best choice will depend on the complexity of your needs and the tool you’re accustomed to using.

The Report Designer add-in for Visual Studio (called SSDT) relies on shared components installed with the SQL Server client tools. If you’re a developer with prior SQL Server experience and you know your way around the T-SQL query language, you’ll probably prefer to use SSMS or the query tool in SSDT Report Designer. If you are using Report Builder and have not installed the SQL Server client tools, you will not have access to the SSMS query designer. If you are planning to design complex queries, it would be advisable to install and learn to use SSMS.

Experienced report developers often choose Report Builder over SSDT for simple report design work, but may not be big fans of the Report Builder Query Designer. This is primarily because they are more accustomed to using SSMS and prefer to hand-write T-SQL queries. In the example that follows, you’ll see that the Query Designer actually generates pretty decent SQL script, so, in the end, it really comes down to using the method you are most comfortable with, as long as it meets your needs.

Using the Report Builder Query Designer

This section gets you started with the most basic query tool. You use SSMS to author a similar query in the exercise that follows. Let’s start with the Report Builder designer to give you experience using both tools. First, create a data source. In the same manner you created a dataset in Chapter 4, “Report Layout and Formatting,” choose the option to create a new embedded dataset, select the data source, and then click the Query Designer… button.

The Query Designer displays all of the objects in the database. Adding columns from multiple tables to the query output is a matter of expanding each table and checking the box next to each column. Columns are added to the query in the order you select them. Three sections on the right (“Selected fields,” Relationships, and “Applied filters”) can each be expanded and collapsed using the chevron (double arrow) icon on the right side of the section toolbar.

Unless you need the query to return every detail row, it is always a good idea to group the results by the non-measure columns and then aggregate the numeric measure columns. For example, Figure 5-1 shows that the MonthNumber, Year Country, OrderQuantity, and SalesAmount columns have been selected. The last two columns are numeric measures, which means that if you group results by the first three columns, you must aggregate these column values. This is easy to do. First, note that the MonthNumber column has been selected before the Year column (done so for

demonstration purposes). Before grouping, the columns should be listed in logical order, so you use the up and down arrows next to “Group and Aggregate” to set the column order of the first three columns to Year MonthNumber, and then Country

In the Aggregate column of the “Selected fields” list, choose “Group by” for the first three columns and choose “Sum” for the two numeric measures. Because the selected tables are related to each other in the database, using the Auto Detect feature should add corresponding joins using the appropriate key columns. Figure 5-2 shows that this feature is enabled by default. You should always double-check to ensure sure that the right tables and columns are used to join the tables.

You can add, remove, and modify joins between tables by expanding the Relationships section. Add filters and parameters using the “Applied filters” section. In Figure 5-3, the Year column has been added. After dropping down the Value list, 2013 was entered for the filter value. By checking the Parameter check box, this adds a report parameter using the field name.

The Query Designer generates a T-SQL query that can be viewed using the “Edit as Text” button, as shown in Figure 5-4.

The resulting T-SQL query is quite well-written. The script is efficient, well-formatted, and easy to read. In the future, you may use the graphical designer to generate this query, switch to the text view, and just hand-write the query script here, or write the query in SSMS and then copy-and-paste the resulting query script into this view. My preference is the latter and that’s the technique you will use in the exercise later in this chapter.

DATASETS AND FIELDS

A query or command statement that produces a set of report data is called a dataset. There is more to a dataset than just a query, though. It’s actually a fairly sophisticated object used to centrally manage all of the data and metadata associated with the query. A dataset is essentially the glue that maps report parameters to query parameters, columns returned by the query to fields used in the report, and optional filtering expressions that are applied after the data is returned by the query. In simple reports, all these objects are just created by the designer and may be of little concern. In more advanced reports, the dataset can provide complex capabilities to manipulate and manage dynamic report data.

For the purposes of this chapter’s exercise, you write a T-SQL query using a single query parameter. The query designer will map the query parameter to a report parameter, and the columns returned by the query will be mapped to dataset fields to populate a matrix data region. Later, when learning about advanced report designs, you will use expressions to map the parameters through the dataset and apply conditional filter logic.

Embedded and Shared Datasets

Like data sources, datasets can be contained entirely within a report design, or they can be published to the report server as a shared object to be used in multiple reports. This is a powerful feature for creating efficient reporting solutions. Shared datasets are designed just like an embedded dataset, using the SSDT or Report Builder query designers, but rather than residing within each report, shared datasets are deployed and stored as named objects on the report server.

NOTE I wasn’t a fan of shared datasets after I started using them when the feature was introduced a few versions back. It wasn’t until I learned how to embrace the caching behavior (and turn it off in some cases) that I began to have a good experience with shared datasets. Now that this is a requirement for Mobile Reports, learning to use this feature is a necessity.

One advantage of using shared datasets is that they cache result sets in such a way that when the same query is executed with the same parameter values (within a configurable period of time), cached results from a previous execution are used to improve performance and conserve server resources. The trade-off is that users may not see data changes that have occurred since the previous query execution. Now, this is all configurable on the report server but you need to be mindful of these settings and the caching behavior.

In Chapters 17 through 20 you will use shared datasets for mobile report designs. Since Mobile Reports don’t support embedded datasets like paginated reports, you must design and deploy shared datasets before consuming data in the report.

Exercises

The following hands-on exercises will guide you through the query authoring steps in SSMS, creating a dataset in the Report Designer, and building a simple report. You will enhance the query and report design using a parameter to filter data and then employ a multi-value parameter.

Authoring a Query with SQL Server Management Studio

…exercise…