 It is time to put your knowledge to practical use. By now, you have sufficient knowledge to put fairly complex reports together. Let's start with a Sales Order summary report. Adventure Works' management requested a report that displays selected properties of an order header (ship and bill to addresses, contact information, and billing summary) and selected properties of an order's line items (product name , unit price, order quantity, and line total). Adventure Works requires each report to have a company logo. -
Create a new report with two data sets: Order_Header (data selected from join between SalesOrderHeader, Address , and StateProvince tables) and Order_Detail (data selected from join between SalesOrderHeader , SalesOrderDetail , and Product tables). To retrieve a specific order, you will use parameter @SalesOrderNumber in the WHERE clause of both data sets: WHERE SalesOrderHeader.SalesOrderNumber = @SalesOrderNumber ). -
To have a more complete picture of an order and include both shipping and billing addresses, you need to include Address and StateProvince tables twice in the Order_Header data set. Create aliases for the first set of Address and StateProvince tables as BillToAddress and StateProvinceBill, use ShipToAddress and StateProvinceShip aliases for the second set of tables. To create an alias for a table, right-click a table in a Graphical Query Designer, select Properties from the shortcut menu, and fill the Alias field as needed. Alternatively, you can edit the query text directly. -
Create an alias for each field you want to include on a report. You can prefix fields with Ship or Bill for tables related to shipping and billing addresses, respectively. -
Add the company logo image report item. From the Toolbox, drag the image item and drop it onto the report body. Click Next to skip the Welcome screen of the Image Wizard. Select Embedded on the Select the Image Source dialog box (refer to Figure 11.2). -
If the image is already embedded, select it from the Choose the Embedded Image dialog box; otherwise , select New Image and browse for the image or type its location. For images stored in the project, the process is similar. For the images stored in the database, you need to select Dataset , Image Field , and MIME Type properties. A web image requires you to specify a URL. -
Add a List by dragging a List item from the Toolbox. As was noted previously, you can take advantage of the Dataset property of the List item. As an experiment, drag and drop the FirstName field of Order_Header outside of the list. -
Note the value of the created text box is =First(Fields!FirstName.Value, "Order_Header") . As a comparison, drag and drop the FirstName field on the list. Note the value of the created text box is =Fields!FirstName.Value . Also note that the Dataset property of the List item is now set to Order_Header and it was blank prior. -
Add a report heading. Drag and drop a text box from the Toolbox. Enter the following expression as a value: ="Sales Order Number" & " - " & Fields!SalesOrderNumber.Value . This expression concatenates the constant "Sales Order Number - " and the value of the SalesOrderNumber field. To highlight the heading of the report, increase the font size and change the text box background. -
Add and arrange data fields in the page header by dragging and dropping data set fields on the list: Street, City, State , and Zip from both billing and shipping addresses. Second, add contact information and billing summary fields. Add text box items to denote values that were added. Change the heading for information sections to bold font. Add lines to help separate informational pieces. Note that not all the web browsers support overlapping controls, such as lines. In your particular case, you might need to have several lines bordering each other as opposed to crossed lines as on a sample report. Add a table to display details of an order. Drag and drop a Table item from the Toolbox. The default table has three rows and three columns. Right-click on the header of the rightmost column and select Add Columns to the Right from the shortcut menu. Drag and drop Order_Detail fields to the Detail area of the table, and note how the heading is changed to the name of the field. -
To summarize line item charges, drag and drop the LineTotal field from the Order_Detail data set to the footer of the table, and note that SSRS inserted the aggregation function Sum() and the value of the footer cell is now =Sum(Fields!LineTotal.Value) . The resulting design-time view of the report should look similar to Figure 11.7. The complete report can be found in samples that accompany this book at CodeSamples\part2.relationaldbsamplereports\part2_simple_samples\Sales Order.rdl . Figure 11.7. Design-time picture of an Order Detail report. 
|
