DOCX

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

By Crystal Black,2014-06-18 10:22
10 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. Map Gallery The Map Gallery refers to a set of Reporting Services reports which contain

    embedded spatial data. By selecting a map from the Map Gallery as the source of spatial data,

    the spatial data from the map gallery report is in turn embedded in the report being created. By

    default the Map Gallery for the Report Designer is installed in the

    folder %ProgramFiles(x86)%\Microsoft Visual Studio

    9.0\Common7\IDE\PrivateAssemblies\MapGallery. The Map Gallery for Report Builder is

    installed, by default, in the folder %ProgramFiles(x86)%\Microsoft SQL Server\Report Builder

    3.0\MapGallery.

    2. ESRI Shapefile The Environmental Systems Research Institute, Inc. (ESRI) developed and

    regulates a spatial data format for geographic data. According to the specification, the

    geographic data is stored in a set of files. Reporting Services requires two files: a .SHP file and

    a .DBF file. The ESRI specification includes additional files such as a .SHX file or .PRJ file which

    are not required by Reporting Services. The data from an ESRI shapefile can be embedded in the

    report or it can be retrieved dynamically at runtime if the .SHP and .DBF files are stored in an

    accessible location. For a SharePoint integrated instance of Reporting Services this might be a

    shared document library.

    3. SQL Server Spatial Query Spatial data stored in SQLGeometry or SQLGeography columns of a

    SQL Server relational database table can be used as the source data for maps.

    In this exercise, you will use the Report Designer to create a map report sourced from both the Map Gallery and a SQL Server spatial query. For this you will be using Business Intelligence Development

    Studio. A solution has been created that contains the report project that would result from completing this exercise.

    Note: Although this exercise walks through the steps of creating the report using Business Intelligence Development Studio, the map report can just as easily be created using Report Builder 3.0.

Task 1 Creating a Map Report

    1. Start SQL Server Business Intelligence Development Studio from Start | All Programs |

    Microsoft SQL Server 2008 R2.

    2. Create a new Report Server Project. To do this, point to New on the File menu, and then click

    Project. In the Project types list, click Business Intelligence Projects. In the Templates pane,

    click Report Server Project template. Finally, set the project's name to SSRSR2Lab, set the name

    of the solution to Begin, select the Create directory for solution option, set the location to the

    Ex1-MapReport folder in the Source folder of this lab, and then click OK.

    Figure 1

    Creating a new Report Server Project

3. Create a new report named United States Reseller Sales. To do this, right-click the Reports

    folder in Solution Explorer, point to Add and then click New Item. In the Add New Item dialog,

    select the Report template, set the report’s name to United States Reseller Sales and then click

    Add. The Report Designer interface will appear.

    Note: Maps cannot be created using the Report Wizard; therefore you must add a new item to

    the report rather than adding a new report (which would launch the Report Wizard).

    Figure 2

    Adding a new report

    4. Add a Map item to the United States Reseller Sales report. To do this, press CTRL+ALT+X to

    open the Toolbox and drag a Map item () to the Design surface. This will start the New Map

    Layer wizard.

    5. In the New Map Layer wizard, select SQL Server spatial query and click Next as displayed in

    Figure 3.

    Note: The first step is to specify the source for the spatial data. As discussed above, the choices are: Map Gallery, ESRI shapefile and SQL Server spatial query. We want to show the

    locations US resellers. Geographic data about resellers is available in the AdventureWorks2008R2 SQL Server database.

Figure 3

    Choosing the source of spatial data

    6. In the Choose a dataset with SQL Server spatial data step, select the Add a new dataset with

    SQL Server spatial data radio button and click Next.

Figure 4

    Choosing a dataset with SQL Server spatial data

    7. In the Choose a connection to a SQL Server spatial data source step, create a data source connection to our spatial data. To do this, click New to open the Data Source Properties dialog and follow these steps:

    a. Set the data source’s name to AdventureWorks2008R2.

    b. Keep the Embedded Connection and Microsoft SQL Server (in Type combo box) default

    selections.

    c. Click Edit to open the Connections Properties dialog. Set the server name to the

    SqlServerTrainingKitAlias server alias and select AdventureWorks2008R2 from the

    Select or Enter a Database Name dropdown list. Optionally click Test Connection, and

    finally click OK.

Report this document

For any questions or suggestions please email
cust-service@docsford.com