DOCX

Hands On Lab Using the New Features of Reporting Services 2008 R2

By Crystal Black,2014-06-18 10:22
11 views 0
Hands On Lab Using the New Features of Reporting Services 2008 R2

Hands-On Lab

    Using the New Features of Reporting

    Services 2008 R2

Lab version: 1.0.0

    Last updated: 6/18/2012

CONTENTS

    OVERVIEW ............................................................................................................................................. 3

    EXERCISE 1: CREATING A MAP REPORT ........................................................................................... 6

    Task 1 Creating a Map Report ....................................................................................................... 7

    Task 2 Adding a Polygon Layer to the Map .................................................................................. 17

    Task 3 Setting the Marker Type Rule for a Point Layer ................................................................. 21

    Task 4 Modifying Point Properties ............................................................................................... 26

    Task 5 Setting the Color Rule for a Polygon Layer ........................................................................ 31

    Task 6 Setting the Map Viewport Zoom ....................................................................................... 40

    Task 7 Adding a Bing Maps Tile Layer .......................................................................................... 42 EXERCISE 2: USING LOOKUP FUNCTIONS ....................................................................................... 45

    Task 1 Creating a Tabular Report ................................................................................................. 47

    Task 2 Retrieving a Single Value from a Different Dataset............................................................ 56

    Task 3 Retrieving Multiple Values from a Different Dataset ......................................................... 63 EXERCISE 3: GENERATING AN ATOM SERVICE DOCUMENT FROM A REPORT ........................... 70

    Task 1 Exporting a Report to a Data Feed .................................................................................... 71

    Task 2 Assigning Friendly Names to Report Elements .................................................................. 76 EXERCISE 4: EXPLORING THE NEW FEATURES IN REPORT BUILDER 3.0 .................................... 79

    Task 1 Experiencing Edit Sessions for Improved Previewing ......................................................... 80

    Task 2 Configuring Edit Sessions .................................................................................................. 90

    SUMMARY ............................................................................................................................................ 93

Overview

    Microsoft SQL Server 2008 R2 Reporting Services introduces several exciting new features for report development. This lab includes exercises to help you in becoming familiar with these new features. Specifically, the lab covers the following topics:

    ; Creating map reports using the Report Designer in Microsoft SQL Server Business Intelligence

    Development Studio

    ; Using lookup functions in report expressions

    ; Creating Atom service documents

    ; Understanding edit sessions in Report Builder 3.0

    Microsoft SQL Server 2008 added support for spatial data types. The new map item in Reporting Services 2008 R2 is a natural extension to that addition, allowing the spatial data from SQL Server databases to be displayed in reports. Spatial data for reports is not limited to SQL Server. Spatial data from Environmental Systems Research Institute, Inc. (ESRI) shapefiles, Bing Maps, and a Map Gallery which is installed as part of Reporting Services can also be used in reports. In fact Reporting Services allows you to bring together spatial and analytical data from multiple sources in a single map report. The Exercise 1 of this lab gives an example of creating such a report.

    A perceived shortcoming of data regions in reports is that they can be bound to only one report dataset. Prior to Reporting Services 2008 R2, this meant that data from disparate sources would need to be combined before being brought into Reporting Services or custom code would need to be written in order to display data from multiple data sets in the same table or chart. The introduction of three lookup functions to report expressions should obviate much of the need for such workarounds. The Exercise 2 of this lab demonstrates how to use these functions in report expressions.

    Pre-formatted reports have an important role in Business Intelligence applications:

    ; They offer users across an organization a single point of reference for business data

    ; They can be designed to be suitable for distribution outside of the organization

    ; They offer non-technical users an easy way to consume business data

    Oftentimes, however, business users need to take the data analysis a step beyond the formatted report. The ability to export report data to a data feed and use it in other applications, most notably the new PowerPivot for Excel, gives business analysts the ability to take that step without involvement from IT. The Exercise 3 of the lab shows how reports are exported as Atom data feeds.

    With the implementation of server-mode in Report Builder 3.0, more than ever before, the standalone report designer can be seen as interchangeable with the Report Designer in Business Intelligence Development Studio. With server mode comes the concept of an “edit session” in Report Builder. Edit sessions create a data cache so that repeated report previews in Report Builder do not mean repeated

    query executions against the data source. This is an important time saver for reports with long running queries when during the design phase the reports are frequently previewed as the layout and formatting are adjusted. The new features of Report Builder 3.0 are reviewed in the introduction to Exercise 4, and the exercise demonstrates a case when the data cache is used for the preview, and when the cache is refreshed.

    Objectives

    In this lab you will learn to:

    ; Create reports displaying geospatial data

    ; Show data from multiple datasets in a single data region

    ; Export report data as Atom data feeds

    ; Benefit from edit sessions in Report Builder 3.0

System Requirements

    You must have the following items installed to complete this lab:

    ; Microsoft SQL Server 2008 R2

    ? Database engine

    ? Reporting Services installed in Native or SharePoint Integrated mode (required only for

    Exercise 3)

    ; SQL Server AdventureWorksDW2008R2 and AdventureWorks2008R2 sample databases

    ; Microsoft SQL Server 2008 R2 Management Studio

    ; Microsoft SQL Server Business Intelligence Development Studio

    ; Microsoft SQL Server 2008 R2 Rerport Builder 3.0

    ; Microsoft SharePoint 2010 (required only for Exercise 3)

    Note: To display Bing map tiles in a report, the report server must be configured to access the Web in order to access Microsoft Bing Maps Web Services.

Setup

    All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is

    correctly configured, follow these steps.

    Note: To perform the setup steps you need to run the scripts in a command window with administrator privileges.

    1. Launch the Configuration Wizard for this Lab by running the Setup.cmd script located under the

    Setup folder in the Source folder of this lab. Install any pre-requisites that are missing

    (rescanning if necessary) and complete the wizard.

    Note: The Configuration Wizard also creates the 'SQLServerTrainingKitAlias' sever alias used in

    this lab to establish a connection with the database server.

Exercises

    The following exercises are included in this Hands-On Lab:

    1. Creating a Map Report

    2. Using Lookup Functions

    3. Generating an Atom Service Document from a Report (requires Microsoft SharePoint 2010)

    4. Exploring the New Features in Report Builder 3.0.

    Note: Inside each exercise you will find an end folder with the resulting solution you should obtain after completing the exercise. The resulting solution in each exercise can be used as the starting point for the subsequent exercise. You can use the solutions as a guide if you need additional help working through the exercises.

Estimated time to complete this lab: 60 minutes.

Exercise 1: Creating a Map Report

    To create a map report you use the Map item, which is available both in the Report Designer in Business

    Intelligence Development Studio and in Report Builder 3.0. Dragging a map item from the Toolbox onto

    the Design Surface in Report Designer launches the New Map Layer wizard which guides you through

    the steps of adding a map to your report. The same wizard is launched when you choose the Map Item from the Design Surface in Report Builder 3.0.

    Reporting Services maps are comprised of layers. There are four types of layers, each corresponding to the different types of spatial data that can be displayed in Reporting Services maps:

    1. Polygon the polygon layer is used to show enclosed areas such as countries or states. A

    polygon layer in a planar system could also be used to draw shapes such as the rectangles of a

    heat map

    2. Line the line layer is used for displaying paths or routes

    3. Point the point layer is used to display specific locations on a map or a specific coordinate on a

    plane

    4. Tile tile layers are used to display Bing maps in reports

    Each map has at least one layer. The Map Wizard guides users through the configuration of one map layer. After the map is created, additional layers can be added using the Layer Wizard. The first step of the Wizard is to specify the source of spatial data for the map. There are three possible sources for spatial data:

    1.