Creating the Sales_Order_Detail_2008R2 Report (SSRS)

This tutorial helps you build the Sales_Order_Detail_2008R2 report from the AdventureWorks 2008R2 sample reports.

This report is the detail report that is the target of the drillthrough action in the Employee_Sales_Summary_2008R2 report, for the SalesOrderNumber field. The report displays the sales header and sales line item details for a consecutive range of sales orders. Detail information includes the order number, store name, billing and shipping addresses, sales person contact information, and other details available from the AdventureWorks2008R2 database. For more information about the suite of reports and their relationships, see Tutorials: Creating AdventureWorks 2008R2 Sample Reports (SSRS).

What You Will Learn

In this tutorial you will do the following tasks:

  • Add a dataset and query that retrieves sales order detail data for a range of sales orders.

  • Configure the report parameters for the first and last sales order number in the range.

  • Add the following information to the page header:

    • Page names. If you export the report to Excel, the tab name of a worksheet is based on the page name.

    • Page numbers for the entire report.

    • Page numbers within each sales order.

    • Instructions for the report user about how to return to the main report.

  • Add a List with a single row group based on SalesOrderID. To the List, you will add the following:

    • A nested Rectangle as a container to help control the layout of the sales order header and detail information.

    • Multiple text boxes to display sales order header information.

    • A subreport to display phone numbers for multiple store contacts.

    • A table to display details for each line item in a sales order.

  • Add custom code to calculate page totals and running page totals for successive pages.

  • Define a report variable to simplify the running total expression.

  • Define a custom group expression on the detail rows to start a new page every 25 rows.

Estimated time to complete this tutorial: 30 minutes.

Requirements

For information about requirements, see Prerequisites for AdventureWorks 2008R2 Sample Reports (SSRS).

This tutorial assumes that you have completed Creating the Report Server Project and the AdventureWorks2008R2_Base Report (SSRS).

Tips

The sales order detail report is a free form report that uses data regions, rectangles, nested data regions, and nested text boxes to organize the data. The containment hierarchy of these items is important. Review the tips in Report Design Tips (Report Builder 3.0 and SSRS), and then use the following tips to help build this report:

  • It is recommended that you open the sample report Sales_Order_Detail_2008R2 and view it along with this tutorial. After you open Sales_Order_Detail_2008R2, view the report item hierarchy in the Document Outline pane. As you build the report, verify the containment relationships between tablix report items and rectangles.

    Note

    To open the Document Outline pane, from the View menu, click Other Windows, and then click Document Outline.

  • A list is a free form tablix layout. By default, a list contains a single tablix cell that contains a rectangle. In this report, you will add the following:

    • An additional nested rectangle to contain the logo, the sales order number, and sales order header information.

    • A table to display the sales order details for each line.

  • You might find it easier to copy and paste data regions or rectangle containers between the original report and your copy rather than do all the work of creating the report items and layout. You can copy and paste report items between tabbed views of open reports. After you copy items from one report, click the tab of the report that you want to copy to, and then click the location where you want the items to be pasted. The paste action is context-sensitive. For example, you cannot paste a data region into a page header.

To open the project and make a copy of a 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 Sales_Order_Detail_2008R2.rdl.

To create the dataset for the individual sales

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

    SELECT SOH.SalesOrderNumber, S.BusinessEntityID, S.Name, 
      SOH.SalesOrderID, SOH.SalesPersonID, SOH.TotalDue,
      SOH.OrderDate, SOH.PurchaseOrderNumber,
      SOH.BillToAddressID, SOH.ShipToAddressID, SOH.ShipMethodID,
      SM.Name AS ShipMethod, BA.AddressLine1 AS BillAddress1, BA.City AS BillCity, 
      BA.PostalCode AS BillPostalCode, BSP.Name AS BillStateProvince, 
      BCR.Name AS BillCountryRegion, 
      SA.AddressLine1 AS ShipAddress1,SA.City AS ShipCity, SA.PostalCode AS ShipPostalCode, 
      SSP.Name AS ShipStateProvince, SCR.Name AS ShipCountryRegion,
      e.JobTitle, per.[FirstName] + N' ' + per.[LastName] AS [SalesPerson], 
      ph.PhoneNumber,
      SD.SalesOrderDetailID, SD.OrderQty, SD.UnitPrice, 
      CASE WHEN SD.UnitPriceDiscount IS NULL THEN 0 ELSE SD.UnitPriceDiscount END 
         AS UnitPriceDiscount, 
      SD.LineTotal, SD.CarrierTrackingNumber, P.Name as ProductName, P.ProductNumber
      FROM [Sales].[SalesOrderHeader] SOH 
        INNER JOIN Sales.Customer C ON SOH.CustomerID = C.CustomerID
        INNER JOIN Sales.Store S ON C.StoreID = S.BusinessEntityID
        INNER JOIN Person.Address SA ON SA.AddressID = SOH.ShipToAddressID -- Shipping address
        INNER JOIN Person.StateProvince SSP ON SA.StateProvinceID = SSP.StateProvinceID 
        INNER JOIN Person.CountryRegion SCR ON SSP.CountryRegionCode = SCR.CountryRegionCode 
        INNER JOIN Person.Address BA ON SOH.BillToAddressID = BA.AddressID -- Billing Address
        INNER JOIN Person.StateProvince BSP ON BA.StateProvinceID = BSP.StateProvinceID 
        INNER JOIN Person.CountryRegion BCR ON BSP.CountryRegionCode = BCR.CountryRegionCode 
        INNER JOIN Purchasing.ShipMethod SM ON SOH.ShipMethodID = SM.ShipMethodID 
        INNER JOIN [Sales].[SalesPerson] sp ON sp.[BusinessEntityID] = SOH.[SalesPersonID]  
        INNER JOIN [HumanResources].[Employee] e ON SOH.[SalesPersonID] = e.[BusinessEntityID] 
        INNER JOIN [Person].[Person] per ON per.[BusinessEntityID] = sp.[BusinessEntityID]
        INNER JOIN Person.PersonPhone ph ON per.[BusinessEntityID] = ph.[BusinessEntityID]
        INNER JOIN Sales.SalesOrderDetail SD ON SD.SalesOrderID = SOH.SalesOrderID
        INNER JOIN Production.Product P ON SD.ProductID = P.ProductID 
    WHERE (SOH.SalesOrderID BETWEEN (@SalesOrderIDStart) AND (@SalesOrderIDEnd))
    

The dataset query returns data for multiple sales orders in a range.

To configure the report parameter @SalesOrderIDStart

  1. Open Parameter Properties for @SalesOrderIDStart.

  2. Change Prompt to First Order ID?

  3. Change Data type to Integer.

  4. On Default Values, add a value and set it to 57030.

To configure the report parameter @SalesOrderIDEnd

  1. Open Parameter Properties for @SalesOrderIDEnd.

  2. Change Prompt to Last Order ID?

  3. Change Data type to Integer.

  4. On Default Values, add a value and set it to 57032.

Add Names and Numbers to the Page Header

To add page names

  1. In the page header, to the text box that contains [&ReportName], add the following text on a new line: Page Name:.

  2. In the Report Data pane, expand Built-in Fields, and drag PageName next to the text that you just added.

  3. Click the report background to display Report Properties in the Property pane.

  4. In InitialPageName, type SalesOrder.

  5. In the Grouping pane, click the SalesOrderID group. Verify that Tablix Member properties appear in the Properties pane.

  6. Expand Group, find PageName, and type =Fields!SalesOrderNumber.Value.

If you export this file to Excel, tab names are based on the page name.

To add page numbers

  1. In the page header, add a text box with the following text:

    • Sales Order Page: [&PageNumber] of [&TotalPages]

    • Report Page: [&OverallPageNumber] of [&OverallTotalPages]

  2. In the Grouping pane, click the SalesOrderID group.

  3. In the Properties pane, verify that a Tablix Member is selected. Expand Group, expand PageBreak, and set ResetPageNumber to true.

As you page through the report, the page number for the group and the page number for the whole report are both displayed.

Add Report User Instructions

To add instructions for the report user to return to the main report

  1. Just below the page header, add a text box for instructions to the end user about how to return to the master report. Add the following text:

    Use the browser Back button to return to the main report.

  2. Format the text boxes as needed.

Add and Configure a List

To add a list with group row

  1. Below the scenario toggle text instructions, insert a List.

    By default, the list contains a single tablix cell in a detail row. In steps 2-6, you will add a group row and remove the detail row so that the list contains a single tablix cell in a group row.

  2. From the SalesOrder dataset, drag [SalesOrderID] to Row Groups in the Grouping pane, and drop it above the detail group.

  3. In the Grouping pane, right-click the Details group, and then click Delete Group.

  4. In the Delete Group dialog box, click Delete group and related rows and columns.

  5. In the selected list, verify that the row handle displays a single bracket, which indicates a single row group.

  6. Right-click the column handle for the first column, and then click Delete Columns.

  7. On the Delete Columns dialog box, click Delete columns only.

    The tablix now has a single cell that contains a rectangle and is grouped by [SalesOrderID]. This rectangle is the container for information about a single sales order. In later steps, you will add sales order header and sales order details to this container.

  8. Right-click the cell, and then click Rectangle Properties.

  9. Change the name to OrderHeader_Contents.

  10. In the Grouping pane, right-click the SalesOrderID group, and open Group Properties.

  11. On Page Breaks, select Between each instance of a group.

  12. By design, each sales order begins on a new page.

To verify the configuration of the list

  1. Click the empty cell in the list, and on the toolbar, set the Background Color to White Smoke. As you work with items nested in a rectangle, it helps to see the container.

  2. To verify the list configuration do the following:

    1. From the dataset SalesOrder, drag SalesOrderID and TotalDue to the rectangle.

    2. Right-click SalesOrderID, point to Summarize By, and then click First.

      Because this is a group row, use aggregate functions for fields in an expression.

    3. Format TotalDue as currency.

    4. Run the report.

    For each sales order instance, the list appears on a new page and displays the sales order number and the total sales. There should be three pages, one each for sales order 57030, 57031, and 57032. As you page through the report, the page header values change.

    When you use this report to provide details for a single sales order, you will set @SalesOrderIDStart and @SalesOrderIDEnd to the same SalesOrderID.

  3. In Design view, delete the text boxes from the list.

Build the Order Header Layout

You will build the order header layout in two separate rectangle containers, combine them, and then add the outer rectangle to the List.

  1. A parent rectangle container for the logo, the order number, and the OrderHeaderData rectangle.

  2. A rectangle container named OrderHeaderData for the order header data fields. As you build this layout, it helps to envision the information in the following three horizontal sections:

    1. The billing and shipping information

    2. The store contact information

    3. The remaining information: date, sales person, purchase order number, and shipment method

To build the order header in a rectangle container

  1. Outside the list, insert a Rectangle.

  2. Move the logo from the page header to the rectangle.

  3. Insert a text box with the following text:

    1. Sales Order

    2. Order #: [SalesOrderNumber]

  4. Drag the logo to the left side of the container and the text box to the right side. Format as needed.

To add a rectangle for the order header data fields

  1. Outside the list, insert a Rectangle, and change the name to OrderHeaderData.

  2. Expand the rectangle across the page to make room for all the sales order header information.

To this rectangle, add each horizontal information section described in the next procedures.

To build the billing and shipping information section

  1. Add a text box. In edit mode, type Bill to:

  2. Add a text box that displays the following lines of text:

    1. [Name]

    2. [BillAddress1]

    3. [BillCity], [BillStateProvince] [BillPostalCode]

    4. [BillCountryRegion]

  3. Add a text box. In edit mode, type Ship to:

  4. Add a text box that displays the following lines of text:

    1. [Name]

    2. [ShipAddress1]

    3. [ShipCity], [ShipStateProvince] [ShipPostalCode]

    4. [ShipCountryRegion]

  5. Align the tops of these text boxes.

To build the store contact information section

  1. Add a text box. In edit mode, type Contact:

  2. Insert a Subreport and do the following:

    1. On the General page, select the subreport Store_Contacts_2008R2.

    2. On the Parameters page, add a parameter.

    3. Set Name to StoreID and Value to [BusinessEntityID].

  3. Align the top of the text box and the subreport.

To build the remaining information section

  1. Add a text box that displays the following lines of text:

    1. Date

    2. Placeholder text. Set the label to ProcessedDate and the value to [&ExecutionTime].

  2. Add a text box that displays the following lines of text:

    1. Order Date

    2. [OrderDate]

  3. Add a text box that displays the following lines of text:

    1. Sales Person

    2. [SalesPerson], [JobTitle]

    3. [PhoneNumber]

  4. Add a text box that displays the following lines of text:

    1. Purchase Order

    2. [PurchaseOrderNumber]

  5. Add a text box that displays the following lines of text:

    1. Shipment Method

    2. [ShipMethod]

  6. Align the tops of these text boxes and adjust their width as needed.

To combine order header information and move it to the List

  1. Grab the OrderHeaderData rectangle and drag it below the logo.

  2. Drag the sales order header rectangle to the list.

  3. The rectangle with the logo expands to include the order data.

    The list cell expands to include the sales order header information.

Build the Sales Order Detail Layout

Display the line items, including a line item number, for each sales order.

To add a table to show the line items

  1. In the list, grab the row handle and expand the row height.

  2. Below the sales order header in the spact that you just created, insert a Table.

  3. In the table, drag the following data from the SalesOrder dataset to the detail row:

    1. [OrderQty]

    2. [ProductNumber]

    3. [ProductName]

    4. [CarrierTrackingNumber]

    5. [UnitPrice]

    6. Add placeholder text. Set the label to [Subtotal] and the value to the following expression: =Fields!OrderQty.Value * Fields!UnitPrice.Value

    7. Add placeholder text. Set the label to [Discount] and the value to the following expression: =0 - Fields!UnitPrice.Value * Fields!OrderQty.Value * Fields!UnitPriceDiscount.Value

    8. [LineTotal]

      Note

      Notice that this text box is named ItemTotal. You will use this textbox name later when you write an expression to calculate the total for all items on the page.

  4. Insert a column at the beginning of the table.

  5. In the column header, type Line.

  6. In the text box in the row, type =RowNum("OrderDetail")

  7. Format as needed.

To add a total row

  1. Right-click the cell that contains [Qty], and then click Add Total.

  2. In the text box in the last row, in the fourth column, type Total Discount:.

  3. In the adjacent text box, add placeholder text.

  4. Set the label to Total Discount and the value to =Sum(Fields!UnitPrice.Value * Fields!OrderQty.Value * Fields!UnitPriceDiscount.Value)

  5. In the text box in the last row, in the eighth column, type Total:.

  6. In the adjacent text box, add placeholder text.

  7. Set the label to Grand Total and the value to [Sum(LineTotal)].

  8. Format as needed.

  9. Remove all extra space between text boxes, tables, and rectangles.

Define a Page Break Every 25 Rows in the Sales Order Detail Table

To set a page break every 25 rows for the detail group

  1. In the Grouping pane, right-click OrderDetails_Details_Group, point to Add Group, and then click Parent Group.

  2. Add a group, and use the following expression: =Ceiling(RowNumber("OrderDetail")/25)

  3. On the Page Breaks page, select Between each instance of a group.

Define a Report Variable for RunningTotals

To add a report variable

  1. Right-click the report background and open Report Properties.

  2. On the Variables page, add a report variable named RunningTotal.

  3. Set Value to =0.0

Define Custom Code to Calculate Running Totals

To add custom code

  1. Right-click the report background and open Report Properties.

  2. On the Code page, add the following code:

    Public Function AddToVariable(var As Microsoft.ReportingServices.ReportProcessing.OnDemandReportObjectModel.Variable, ByVal increment As Double) As Double 
       var.Value = var.Value + increment
       return var.Value
    End Function 
    
    Public Function GetOrResetVariable(var As Microsoft.ReportingServices.ReportProcessing.OnDemandReportObjectModel.Variable, ByVal executeReset As Boolean) 
       if executeReset then
           var.Value = 0
       end if 
       return var.Value
    End Function
    

Add the Description

To add the description

  1. Insert a Rectangle in the page footer.

  2. Move the description text box into the rectangle.

  3. Replace the description with the following text:

    Purpose: Detail report. Displays details for a range of orders, and is a drillthrough report for an individual sales order from Employee_Sales_Summary_2008R2. Includes free form layout, tablix used as list and table, a subreport, session variables for calculating running page totals, custom code, image, expression-based page breaks based on the number of detail rows, page names, page numbering with report and within group, and conditionally hidden text.

  4. Click the report background to display Report Properties in the Properties pane.

  5. In Description, paste the description text.

To add running page totals

  1. Add a text box for the following text: Previous Page Total

  2. Add an adjacent text box with the following expression:

    =Code.GetOrResetVariable(Variables!RunningTotal, Globals!OverallPageNumber = 1)

  3. Add a text box for the following text: Current Page Total

  4. Add an adjacent text box with the following expression:

    =Sum(ReportItems!ItemTotal.Value)

  5. Add a text box with the following text: Running Page Total

  6. Add an adjacent text box with the following expression:

  7. =Code.AddToVariable(Variables!RunningTotal, Sum(Reportitems!ItemTotal.Value))

Add a Spacer Text Box

Description text has conditional visibility. When the description is hidden, the page total text renders on the left side of the page. To maintain the position of the page total text on the rig, you must add a text box that does not have conditional visibility.

To add a spacer text box

  1. To the rectangle, add an additional text box above the description.

  2. Set the text box width to the same width as the rectangle.

The empty text box keeps the running totals in place when the description is not visible.

Preview and Verify

To deploy and verify the report

  • Preview the report and verify the following:

    1. The store header and details areas render as expected.

    2. The subreport displays store contact information.

    3. Page totals are calculated for the current page and the previous page.

    4. Page totals render in the same location whether or not description text is visible.

Next Steps

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

See Also

Other Resources

Change History

Updated content

  • Changed report to be based on AdventureWorks2008R2_Base.rdl