Creating the Store_Contacts_2008R2 Report (SSRS)

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.

• 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)
  

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.

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.

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.

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 () 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.

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.

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.