Report Information
Description 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.
Author Mary Lingel MSFT based on report by Robert Bruckner MSFT.
Initial Page Name SalesOrder
ReportID 602b0e43-347c-4a5b-8326-d0c562c9ddcb
Data Sources
AdventureWorks2008R2
Name AdventureWorks2008R2
Security Type None
Datasets
SalesOrders
Name SalesOrders
Query
Data Source AdventureWorks2008R2
CommandText 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))
Parameters
@SalesOrderIDStart =Parameters!SalesOrderIDStart.Value
@SalesOrderIDEnd =Parameters!SalesOrderIDEnd.Value
Fields
SalesOrderNumber
Name SalesOrderNumber
Source SalesOrderNumber
Data Type String
BusinessEntityID
Name BusinessEntityID
Source BusinessEntityID
Data Type Int32
Name
Name Name
Source Name
Data Type String
SalesOrderID
Name SalesOrderID
Source SalesOrderID
Data Type Int32
SalesPersonID
Name SalesPersonID
Source SalesPersonID
Data Type Int32
TotalDue
Name TotalDue
Source TotalDue
Data Type Decimal
OrderDate
Name OrderDate
Source OrderDate
Data Type DateTime
PurchaseOrderNumber
Name PurchaseOrderNumber
Source PurchaseOrderNumber
Data Type String
BillToAddressID
Name BillToAddressID
Source BillToAddressID
Data Type Int32
ShipToAddressID
Name ShipToAddressID
Source ShipToAddressID
Data Type Int32
ShipMethodID
Name ShipMethodID
Source ShipMethodID
Data Type Int32
ShipMethod
Name ShipMethod
Source ShipMethod
Data Type String
BillAddress1
Name BillAddress1
Source BillAddress1
Data Type String
BillCity
Name BillCity
Source BillCity
Data Type String
BillPostalCode
Name BillPostalCode
Source BillPostalCode
Data Type String
BillStateProvince
Name BillStateProvince
Source BillStateProvince
Data Type String
BillCountryRegion
Name BillCountryRegion
Source BillCountryRegion
Data Type String
ShipAddress1
Name ShipAddress1
Source ShipAddress1
Data Type String
ShipCity
Name ShipCity
Source ShipCity
Data Type String
ShipPostalCode
Name ShipPostalCode
Source ShipPostalCode
Data Type String
ShipStateProvince
Name ShipStateProvince
Source ShipStateProvince
Data Type String
ShipCountryRegion
Name ShipCountryRegion
Source ShipCountryRegion
Data Type String
JobTitle
Name JobTitle
Source JobTitle
Data Type String
SalesPerson
Name SalesPerson
Source SalesPerson
Data Type String
PhoneNumber
Name PhoneNumber
Source PhoneNumber
Data Type String
SalesOrderDetailID
Name SalesOrderDetailID
Source SalesOrderDetailID
Data Type Int32
OrderQty
Name OrderQty
Source OrderQty
Data Type Int16
UnitPrice
Name UnitPrice
Source UnitPrice
Data Type Decimal
UnitPriceDiscount
Name UnitPriceDiscount
Source UnitPriceDiscount
Data Type Decimal
LineTotal
Name LineTotal
Source LineTotal
Data Type Decimal
CarrierTrackingNumber
Name CarrierTrackingNumber
Source CarrierTrackingNumber
Data Type String
ProductName
Name ProductName
Source ProductName
Data Type String
ProductNumber
Name ProductNumber
Source ProductNumber
Data Type String
Parameters
SalesOrderIDStart
Name SalesOrderIDStart
Data Type Integer
Prompt First Order ID?
Default Value
Dataset Reference
Values 57030
Valid Value
Dataset Reference
SalesOrderIDEnd
Name SalesOrderIDEnd
Data Type Integer
Prompt Last Order ID?
Default Value
Dataset Reference
Values =Parameters!SalesOrderIDStart.Value + 2
Valid Value
Dataset Reference
ShowAll
Name ShowAll
Data Type Boolean
Prompt Show all information?
Default Value
Dataset Reference
Values false
Valid Value
Dataset Reference
Embedded Images
AdventureWorksLogo
Name AdventureWorksLogo
Type jpeg
Custom 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
Variables
RunningTotal
Name RunningTotal
Value =0.0
Page Header
Report Items
ReportName
Name ReportName
Type Text Box
Content =Globals!ReportName
Page Name: =Globals!PageName
tbPageNumbers
Name tbPageNumbers
Type Text Box
Content Sales Order Page: =Globals!PageNumber of =Globals!TotalPages
Report Page: =Globals!OverallPageNumber of =Globals!OverallTotalPages
tbBackInstructions
Name tbBackInstructions
Type Text Box
Content To return to the main report, use the browser Back button.
Body
Report Items
tbToggle
Name tbToggle
Type Text Box
Content Click + to view the scenario story.
tbScenarioText
Name tbScenarioText
Type Text Box
Content As a stand-alone report, specify a start and end sales order number. By default, the end order number is the start order number + 2. As a drillthrough report, specify the start and end number as the same sales order.
Visibility
Show or hide based on expression true
Display can be toggled by this report item tbToggle
rectSalesOrderContents
Name rectSalesOrderContents
Type Rectangle
rectOrderHeaderContents
Name rectOrderHeaderContents
Type Rectangle
Logo
Name Logo
Type Image
Source Type Embedded Image Name
Embedded Image Name AdventureWorksLogo
Textbox8
Name Textbox8
Type Text Box
Content Sales Order
Order #: =Fields!SalesOrderNumber.Value
tbBillToLabel
Name tbBillToLabel
Type Text Box
Content Bill to:
tbBillTo
Name tbBillTo
Type Text Box
Content =Fields!Name.Value
=Fields!BillAddress1.Value
=Fields!BillCity.Value, =Fields!BillStateProvince.Value =Fields!BillPostalCode.Value
=Fields!BillCountryRegion.Value
tbShipToLabel
Name tbShipToLabel
Type Text Box
Content Ship To:
tbShipTo
Name tbShipTo
Type Text Box
Content =Fields!Name.Value
=Fields!ShipAddress1.Value
=Fields!ShipCity.Value, =Fields!ShipStateProvince.Value =Fields!ShipPostalCode.Value
=Fields!ShipCountryRegion.Value
tbContactLabel
Name tbContactLabel
Type Text Box
Content Contact:
tbProcessedDate
Name tbProcessedDate
Type Text Box
Content Date
=Globals!ExecutionTime
tbOrderDate
Name tbOrderDate
Type Text Box
Content Order Date
=Fields!OrderDate.Value
tbSalesPerson
Name tbSalesPerson
Type Text Box
Content Sales Person
=Fields!SalesPerson.Value, =Fields!JobTitle.Value
=Fields!PhoneNumber.Value
tbPurchaseOrder
Name tbPurchaseOrder
Type Text Box
Content Purchase Order
=Fields!PurchaseOrderNumber.Value
tbShipMethod
Name tbShipMethod
Type Text Box
Content Shipment Method
=Fields!ShipMethod.Value
Textbox38
Name Textbox38
Type Text Box
Content Line
Textbox19
Name Textbox19
Type Text Box
Content Order Qty
Textbox21
Name Textbox21
Type Text Box
Content Product Number
Textbox23
Name Textbox23
Type Text Box
Content Product Name
Textbox25
Name Textbox25
Type Text Box
Content Carrier Tracking Number
Textbox27
Name Textbox27
Type Text Box
Content Unit Price
Textbox29
Name Textbox29
Type Text Box
Content Subtotal
Textbox31
Name Textbox31
Type Text Box
Content Discount
Textbox33
Name Textbox33
Type Text Box
Content Line Total
Textbox39
Name Textbox39
Type Text Box
Content =RowNumber("tblxTable_SalesOrderDetails")
OrderQty
Name OrderQty
Type Text Box
Content =Fields!OrderQty.Value
ProductNumber
Name ProductNumber
Type Text Box
Content =Fields!ProductNumber.Value
ProductName
Name ProductName
Type Text Box
Content =Fields!ProductName.Value
CarrierTrackingNumber
Name CarrierTrackingNumber
Type Text Box
Content =Fields!CarrierTrackingNumber.Value
UnitPrice
Name UnitPrice
Type Text Box
Content =Fields!UnitPrice.Value
UnitPrice1
Name UnitPrice1
Type Text Box
Content =Fields!OrderQty.Value * Fields!UnitPrice.Value
UnitPriceDiscount
Name UnitPriceDiscount
Type Text Box
Content =0 - Fields!UnitPrice.Value * Fields!OrderQty.Value * Fields!UnitPriceDiscount.Value
tbLineTotal
Name tbLineTotal
Type Text Box
Content =Fields!LineTotal.Value
Textbox12
Name Textbox12
Type Text Box
Content Sales Order Total:
Textbox15
Name Textbox15
Type Text Box
Content =Sum(Fields!LineTotal.Value, "SalesOrderID")
Textbox16
Name Textbox16
Type Text Box
Textbox17
Name Textbox17
Type Text Box
Content Total Discount:
Textbox18
Name Textbox18
Type Text Box
Content =Sum(Fields!UnitPrice.Value * Fields!OrderQty.Value * Fields!UnitPriceDiscount.Value)
Textbox20
Name Textbox20
Type Text Box
Content Total:
Textbox22
Name Textbox22
Type Text Box
Content =Sum(Fields!LineTotal.Value)
Data Regions
tblxList_SalesOrder
Name tblxList_SalesOrder
Type Table
Row Member 1
SalesOrderID
Name SalesOrderID
Expressions =Fields!SalesOrderID.Value
Sort Expressions =Fields!SalesOrderID.Value
Source Dataset SalesOrders
tblxTable_SalesOrderDetails
Name tblxTable_SalesOrderDetails
Type Table
Row Member 1
PageBreakEvery25Rows
Name PageBreakEvery25Rows
Expressions =Ceiling(RowNumber("tblxTable_SalesOrderDetails")/25)
Row Member 1
Details
Name Details
Row Member 2
Source Dataset SalesOrders
Subreports
subStoreContacts
Name subStoreContacts
Subreport Name/Location Store_Contacts_2008R2
Parameters
StoreID
Name StoreID
Value =Fields!BusinessEntityID.Value
Page Footer
Report Items
tbDescription
Name tbDescription
Type Text Box
Content 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.
Visibility
Show or hide based on expression =NOT Parameters!ShowAll.Value
tbRunningTotalCalculations
Name tbRunningTotalCalculations
Type Text Box
Content Previous Page Total: =Code.GetOrResetVariable(Variables!RunningTotal, Globals!OverallPageNumber = 1)
This Page Total: =Sum(ReportItems!tbLineTotal.Value)
Running Total: =Code.AddToVariable(Variables!RunningTotal, Sum(Reportitems!tbLineTotal.Value))
tbSpacer
Name tbSpacer
Type Text Box