**************************************************************************** Location : Microsoft Reporting Services Host : bnbdatabase1 Path : Sales_Order_Detail_2008R2 **************************************************************************** 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