Trainers discussing SQL Server, Oracle and MySQL

Need to learn how to create a basic report in SQL Server Reporting Services 2008?  Reporting Services has changed dramatically since SQL Server 2005. The trick is to find out where to find the most used functions, such as Grouping and Data Sets.

I will break up the process into several posts.  Part I will explain how to create a simple table report.  Part II will explain how to add grouping, sorting and formatting.  Part III will show how to add summations at the different grouping levels.

Using the AdventureWorks database, let’s create a report on Products that AdventureWorks supplies.  To do this, you will create a multi-join query using the Product, ProductCategory, ProductSubcategory and ProductInventory tables.

  • ProductCategory and ProductSubcategory will be used for grouping.
  • Product.SafeteyLevel, Product.ReorderPoint and ProductInventory.Quantity are used for summation.

Let’s create the report.

Open up Business Intelligence and select a Report Server project.

  1. Name the Report Products

  1. Add a Shared Data.
  2. Name the datasource AdventureWorksDS
    Use Microsoft SQL Server as the type
    Click Edit  for the Connection String
  3. In the Connection Properties dialog box:
    Select Microsoft SQL Server (SqlClient)
    Select your Sql Server
    Log on using Windows Authentication
    Connect to AdventureWorks database

The next step is to create a Shared Dataset.

  1. Right-click on Shared Datasets and select Add New Dataset.
  2. In the Shard Dataset Properties add:
    Name:                 Production
    DataSource:        AdventureWorksDS(shared)
    Query Type:       Text
  3. Click the Query Designer button. This will bring up the Query Designer Window.
  4. To show the Add Table dialog box, click on the  icon of a table with a yellow plus sign.
  5. Select the Product, ProductCategory, Product Subcategory and  ProductInventory tables from the Add tables dialog box.
  6. Select the following fields by clicking on the check box before the field name.
    Production Table:
    Name
    ReorderPoint
    SafetyStockLevel

    ProductCategory:
    Name

    ProductSubcategory:
    Name

    The next step is to create Aliases for the Name Columns (SQL Server Reporting Services will use Exp1, Exp2, etc. when a field has the same name in multiple tables)

    1. ProductCategory name:        Category (Alias)
    2. ProductSubCategory name:  SubCategory

    The report should be ordered by Category, SubCategory and Products

    1. In the Sort Type column select Ascending for the following columns in order
      ProductCategory
      ProductSubcategory
      Products
    2. To test the query, click the red exclamation point .
    3. Click the OK Button to return to the Shared Dataset Properties.
    4. The query will now be listed in the query text window.  Click OK to finish.

    Now we are ready to add a basic table report.

    1. Right-click on the Reports folder
    2. Select add new item.
    3. Select Report and change the name to Production Reorder
    4. A blank report has been created.
    5. From the Toolbax, click and drag a table onto the design.
    6. In the Report Data panel, click New and select Add Dataset.
    7. Select shared dataset and click on Production and click OK.
    8. Click and drag the following fields from the dataset onto the table detail row:
      Category
      SubCategory
      Product
      Quantity
      Safety Reorder Level
      Reorder Point

      You will need to add extra columns to fit all the fields.
      To add extra columns:
      Right-click on the last column  and select Insert Columns to the right.

    9. Click Preview to see basic report.
    10. To add a Report Header, click and drag a textbox onto the top of the table.
    11. Click in the textbox and add the header “Production Reorder Report“.

    That’s it.  You have just created a basic table report using SSRS 2008.  In part II, we will add formatting and grouping to this report. To learn more, check out our SQL Server 2008 training

No TweetBacks yet. (Be the first to Tweet this post)

2 Responses to “Create a simple report with SQL Server Reporting Services 2008”

  1. [...] Part I, Creating a Simple Report, I showed how to create a basic report in SQL Server Reporting Services 2008.  This post will show [...]

  2. [...] Part I, Creating a Simple Report, I showed how to create a basic report in SQL Server Reporting Services 2008. Part II, Add Format [...]

Tweetbacks

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

© Webucator, Inc. All rights reserved. | Toll Free: 877-932-8228 | UK: 0808-101-3484 | From outside the USA: 315-849-2724 | Fax: 315-849-2723