Export (0) Print
Expand All

Creating the Store_Contacts_2008R2 Report (SSRS)

SQL Server 2008 R2

This tutorial helps you create the Store_Contacts_2008R2 report from the AdventureWorks 2008R2 sample reports.

This report is designed as a subreport that is used by the Sales_Order_Detail_2008R2 report. The report parameter @StoreID is a store identifier. Based on the @StoreID value, the dataset query retrieves store contact information from the AdventureWorks2008R2 database. There can be multiple contacts for one store. For more information about the suite of reports and their relationships, see Tutorials: Creating AdventureWorks 2008R2 Sample Reports (SSRS).

In this tutorial you will to do the following tasks:

  • Add a table that is designed to be used in a subreport in a main report.

  • Add page header and page footer information that appears when this report runs as a stand-alone report but does not appear when subreport instances appear in the main report.

Estimated time to complete this tutorial: 15 minutes.

To open the project and add a new report

  1. In Business Intelligence Development Studio, open the report server project AdventureWorks 2008R2.

  2. In Solution Explorer, do the following:

    1. Right-click the report AdventureWorks2008R2_Base.rdl, and then click Copy.

    2. Right-click the project node, and then click Paste.

    3. Rename the copied report to Store_Contacts_2008R2.rdl.

To create the dataset the contains store contact information

  • In the Report Data pane, add an embedded dataset named StoreContacts. Use the AdventureWorks2008R2 shared data source and the following query:

    SELECT s.BusinessEntityID, s.Name AS Store, ct.Name AS Title, p.FirstName, p.LastName, ph.PhoneNumber
    FROM Sales.Store AS s INNER JOIN
       Person.BusinessEntityContact AS bec ON bec.BusinessEntityID = s.BusinessEntityID INNER JOIN
       Person.ContactType AS ct ON ct.ContactTypeID = bec.ContactTypeID INNER JOIN
       Person.Person AS p ON p.BusinessEntityID = bec.PersonID INNER JOIN
       Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
    WHERE (s.BusinessEntityID = @StoreID)
    

To create the dataset that contains store information

  1. Add an embedded dataset named Stores.

  2. Use the following query:

    SELECT DISTINCT BusinessEntityID, Name as Store
    FROM Sales.Store
    

You will use this dataset to provide available values to the report parameter @StoreID.

To configure the report parameter @StoreID

  1. In the Report Data pane, use the Move Up button to move @StoreID to the first position.

  2. Open the Parameter Properties for @StoreID.

  3. Change Prompt to Store:.

  4. On Available Values, select Get values from a query.

  5. For Dataset, select Stores.

  6. For Value, select BusinessEntityID.

  7. For Label, select Store.

  8. On Default Values, add a value and set it to 642.

This value corresponds to the store named Advanced Bike Components. This store has two store contacts: a purchasing agent and a purchasing manager.

To set up the report as a subreport

  1. When a subreport appears in a main report, all content except the page header and page footer appear. Remove report items that are not specfic to the purpose of the report as a subreport.

    White space in the report body is preserved. After you add the table in a later step, you will remove all extra white space in the report.

  2. Delete the two scenario text boxes.

To add a table with one detail row

  1. Add the table to display the store contact information.

  2. Insert a Table named tblxContactInformation.

  3. Delete the header row.

    The table has one row. The row handle (Row handle with 3 parallel lines for details row) shows that this is a details row.

  4. From the dataset StoreContacts, add [FirstName], a space, and then [LastName] to the first cell.

  5. To the second cell, add [Title].

  6. To the third cell, add [PhoneNumber].

  7. Expand the width of each column so that the name and title displays on a single line.

  8. Click the row handle, and in the Properties pane, expand BorderStyle, and set Default to None. .

  9. Grab the bottom of the row handle and squeeze the row to the minimum height. When the main report renders, the subreport row grows vertically and horizontally as needed.

  10. Remove extra white space.

  11. Remove all extra white space around it.

To add the description

  1. In the page footer, replace the text in description text box with the following text: Purpose: Subreport. Displays store contact information. Used in Sales_Order_Details_2008R2 to display multiple store contacts. Includes page header and footer items that are not included when the report is rendered as a subreport.

  2. Click the report background to display Report Properties in the property pane.

  3. In Description, paste the description text.

To deploy and verify the report

  1. The report runs automatically.

  2. When you select a store, the corresponding store contact information is displayed.

  3. When you set Show all information to True, the report description appears on the last page.

You have completed building this report. To build other AdventureWorks sample reports, see Tutorials: Creating AdventureWorks 2008R2 Sample Reports (SSRS).

Updated content

  • Changed report to be based on AdventureWorks2008R2_Base.rdl

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft