Building an enterprise wide corporate reporting solution. Introducing SQL Server 2005 Reporting Services

LAB WORK #2.   Building an enterprise wide corporate reporting solution. Introducing SQL Server 2005 Reporting Services

Main objectives

After successful completion of the lab work students will be able to:

-  Create, design complex reports, connect them to various data sources using Business Intelligence Studio;

-  Deploy and publish reports for corporate use

TASK LIST

1.  Create a simple report

2.  Create a multilevel report with grouping and parameters

3.  Create linked interactive report

4.  Create a report with “parent-child” recursive grouping capabilities

5.  Deploy  reports for public use, administer access permissions, share report as a WEBPART page in collaboration portal

6.  Create a semantic report model. Enable end-users to create ad-hoc reports using Report Builder

Task № 2.1 action scenario

After completion:  You will get a simple sales report with sales sum breakdown by product.

1. Create new solution, new project named SSRS_1, add an empty report into it

·  Launch Business Intelligence Studio (Start->Programs->Microsoft Visual Studio 2005->Microsoft Visual Studio 2005)

·  Choose File \ New\ Project \ Report Server Project

Tip: It’s even better to give a project and solution your Family Name or Nick or something else instead of SSRS. It will help you to quickly find your work files on the disk (they are usually stored in “My Documents\Visual Studio 2005 Projects” folder

·  On the Solution Explorer panel right click the mouse on the Reports subfolder and choose from the context menu: Add New Item \ Report Wizard

·  Use Report Wizard step-by-step report creation procedure

2. Configure DataSource for the report

·  Choose AdventureWorksDW database, server – (local)

·  Next step (Design Query) – specify the query to retrieve recordset to feed the report

SELECT dbo.DimProductSubCategory.EnglishProductSubCategoryName, dbo.DimProduct.EnglishProductName, dbo.FactInternetSales.SalesAmount,

dbo.DimProductCategory.EnglishProductCategoryName

FROM   dbo.FactInternetSales INNER JOIN

       dbo.DimProduct ON dbo.FactInternetSales.ProductKey = dbo.DimProduct.ProductKey INNER JOIN

       dbo.DimProductSubCategory ON dbo.DimProduct.ProductSubCategoryKey = dbo.DimProductSubCategory.ProductSubCategoryKey INNER JOIN

       dbo.DimProductCategory ON dbo.DimProductSubCategory.ProductCategoryKey = dbo.DimProductCategory.ProductCategoryKey

WHERE EnglishProductCategoryName = 'Bikes'

·  Next step Choose Report Type - Tabular

Choose fields for Report Details section : EnglishProductName, SalesAmount

·  Next step – choose a Table Style

·  Next step – enter report name – Simple Bike Sales

3. Shape the report layout to make it more readable

·  In the Body section – make the column EnglishProductName wider

·  Edit the report header - “Bike Sales Report.”

4. Click Save all  .Navigate to  Preview pane  to fill the report design with data. You will see the preliminary results of running your report