Knight's Microsoft Business Intelligence 24-Hour Trainer

Leveraging Microsoft SQL Server Integration, Analysis, and Reporting Services with Excel and SharePoint

Brian Knight, Devin Knight, Adam Jorgensen, Patrick LeBlanc, Mike Davis

• Publisher: Wrox
• Year: 2011
• ISBN:
  9781118287477
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

A book-and-video introduction to Microsoft's Business Intelligence tools

If you are just starting to get a handle on Microsoft Business Intelligence (BI) tools, this book and accompanying video provides you with the just the right amount of information to perform basic business analysis and reporting. You'll explore the components and related tools that comprise the Microsoft BI toolset as well as the new BI features of Office 2010. After a basic primer on BI and data modeling, the expert team of authors provides you with step-by-step lessons in the book and videos on the accompanying DVD on how to use SQL Server Integration Services, SQL Server Analysis Services, SQL Server Reporting Services, Excel BI (including PowerPivot), and SharePoint.

• Integrates instructional videos with each of the lessons found in the book to enhance your learning experience
• Explores the Microsoft Business Intelligence (BI) toolset as well as the new BI features of Office 2010
• Encourages you to practice what you've learned in "Try It Out" sections
• Contains video demonstrations that walk you through how to tackle each lesson featured in the book

With Knight's Microsoft Business Intelligence 24-Hour Trainer, veteran authors present you with an ideal introductory book-and-video package so that you can get started working with the BI toolset immediately!

Note: As part of the print version of this title, video lessons are included on DVD. For e-book versions, video lessons can be accessed at wrox.com using a link provided in the interior of the e-book.

Section IV: SQL Server Reporting Services

• Lesson 21: Understanding SSRS
• Lesson 22: Using Report Wizard
• Lesson 23: Building a Matrix Report
• Lesson 24: Parameterizing Your Reports
• Lesson 25: Building Reports on Your Cube
• Lesson 26: Using Maps in Your Report
• Lesson 27: Building a Dashboard
• Lesson 28: Deploying and Administering SSRS
• Lesson 29: New Reporting Services Visualizations — Sparklines, Data Bars, and Indicators
• Lesson 30: Using Report Builder

Chapter 21

Understanding SSRS

In any business it is important to be able to view your data. SQL Server Reporting Services (SSRS) gives developers the tools needed to show data to the end users in a report format that is easy to understand. An end user can be anyone in a company, from a secretary to the CEO. When the end users have good data presented in an easy-to-understand format, it allows them to make informed business decisions. Questions often asked in a corporate boardroom can be answered by good reports — questions like “Which customers are most likely to close their accounts?” “Should we be open on Saturday?” and “How much should we spend on advertising?” Just taking a stab in the dark with these questions can be the death of a company. Having the right data in front of the company’s decision-makers can avert costly mistakes.

Reports can also show predictions about whether profits will go up or down. This can help in determining staffing, in scheduling, and in predicting overhead cost. Predictions like this are based on algorithms built into tools like SQL Server Analysis Services. Reporting Services gives you a way to present these predictions in the form of tables, matrixes, maps, charts, and graphs. With these reports in hand, the end users will be confident they are making informed choices for the company.

As a report developer or a Business Intelligence consultant, you must create these reports. Therefore it is important to become familiar with the tools used in creating them. SQL Server Reporting Services 2008 R2 has many reporting tools to show the data. Before you get into showing the data to the users, you need to understand how to get this data into Reporting Services.

Some of the key concepts covered in this chapter include data sources, data sets, some of the Toolbox items, designing a basic table report, and finalizing reports. These concepts are the cornerstone of understanding and developing good reports in Reporting Services.

Building Your First Report

Assume your manager has asked you to build a report showing a list of your company’s products. This product data is held in your product table. The columns your manager wants on the report are Product ID, Name, Color, List Price, Standard Cost, Sell Start Date, and Sell End Date. She wants the report to have a header showing the report title and the date on which the report ran. She also wants a page footer showing the page number and total page count.

The first step is to open Business Intelligence Development Studio (BIDS) and create a report project. The shortcut to BIDS can be found in your Start menu under SQL Server 2008 R2. After opening BIDS you can click File and then New Project to bring up the New Project window seen in Figure 21-1. In the left-hand pane select Business Intelligence Projects. In the right-hand pane select Report Server Project. Enter a name for the project in the bottom of the New Project window and click OK. You have created a Report Server Project.

Figure 21-1

On the left-hand side of the screen you should see the Solution Explorer, as shown in Figure 21-2. If it is not visible, the window positions have been customized. The following chapters on Reporting Services will assume the windows in BIDS are in their default configuration. To reset the windows to their default positions you can click Reset Window Layout under the Window drop-down at the top of BIDS. You can also find the shortcut for many windows under the View menu, also at the top of BIDS.

Figure 21-2

In the Solution Explorer you will find three folders:

• Shared Data Sources
• Shared Datasets
• Reports

The first item you need to create is a shared data source. A shared data source contains the server name and the name of the database where the report will query data. To create a shared data source, right-click the Shared Data Sources folder and select Add New Data Source. In the Name box type in AW. This represents the Adventure Works database. Make sure the type is set to Microsoft SQL Server. Click Edit and then select the name of your server from the Server drop-down menu. Or you may want to type in the name of the server. The drop-down menu tends to take a long time to render the server list. In the Database Name box enter AdventureWorks2008R2. Your windows should look similar to what is shown in Figure 21-3. (The server name may be different.) AdventureWorks2008R2 is the free sample database from Microsoft. You can download this database from www.codeplex.com.

Figure 21-3

The shared data set is a new feature added to SQL Server R2. A shared data set will contain a query or call a stored procedure to retrieve data from a data source. To create a shared data set right-click the Shared Datasets folder and select Add New Data Set. Give the data set the name Products. Ensure ...