Lesson 1: Using the Report Wizard to Set Up the Advanced Parameters Tutorial
New: 17 July 2006
The AdventureWorks sample database contains sales orders for the fictional company Adventure Works Cycles from individual customers as well as from stores (called resellers) that resell the merchandise. To explore advanced features that use parameters, you will use the Report Wizard to create a report that shows reseller sales for each store. This is the base report for the lessons in the Advanced Parameters tutorial.
In this lesson, you will create a new report server project called Advanced Parameters Tutorial and run the Report Wizard to create a report that shows sales for each reseller. You will also modify date and currency formatting, add a processing timestamp, and publish the report.
To create a report server project
Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click Business Intelligence Development Studio.
On the File menu, point to New, and then click Project.
In the Project Types list, click Business Intelligence Projects.
In the Templates list, click Report Server Project.
In Name, type Advanced Parameter Tutorial.
Click OK to create the project. The Advanced Parameter Tutorial solution is displayed in Solution Explorer.
To create a report using the Report Wizard
In Solution Explorer, right-click the Reports folder and select Add New Report. The Report Wizard opens to the Welcome to the Report Wizard page.
Click Next. The Select the Data Source page opens.
In the Name text box, type Resellers. This value will be used for both the name of the data source and the name of the dataset in the report definition.
In the Type drop-down list, verify Microsoft SQL Server is selected.
Next to the Connection string pane, click the Edit button. The Connection Properties dialog box opens.
In the Server Name text box, type in the server for the AdventureWorks database. For example,
localhost.In the Connect to a database section, in the Select or enter a database name drop-down list, select AdventureWorks.
In the Log on to the server section, select the authentication to use. Windows authentication uses Microsoft Windows to authenticate the client. SQL Server authentication compares the user name and password you supply to a list of valid user names and passwords maintained in SQL Server.
Click OK. The Select the Data Source wizard page shows the connection string created from your selections on the Connection Properties dialog box.
At the bottom of the page, select the Make this a shared data source option.
Click Next. The Design the Query wizard page opens.
Paste the following query string into the query pane.
SELECT S.CustomerID, SO.SalesOrderNumber, SO.OrderDate, SO.TotalDue, S.Name AS Store, A.City, SP.Name AS StateProvince, CR.Name AS CountryRegion, SC.ContactID As StoreContactID, T.[Group] As TerritoryGroup, Year(SO.OrderDate) as Year FROM Sales.Store AS S JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID JOIN Person.Address AS A ON A.AddressID = CA.AddressID JOIN Person.StateProvince SP ON SP.StateProvinceID = A.StateProvinceID JOIN Person.CountryRegion CR ON CR.CountryRegionCode = SP.CountryRegionCode JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID JOIN Sales.StoreContact SC ON S.CustomerID = SC.CustomerID JOIN Sales.Customer C on S.CustomerID = C.CustomerID JOIN Sales.SalesTerritory T on C.TerritoryID = T.TerritoryID ORDER BY S.CustomerIDSome of the fields in this query will be used in Advanced Parameter tutorial lessons that follow this lesson.
Click Next. The Select the Report Type page opens.
Verify Tabular is selected.
Click Next. The Design the Table page opens.
In the Available fields pane, select Store. Click the Group button.
By assigning Store to a group, this will organize the report by store.
In the Available fields pane, select SalesOrderNumber, OrderDate, and TotalDue.
To select more than one field, press the CTRL key while clicking each field.
Click the Details button.
In the Displayed fields pane, click OrderDate, and then the UP arrow button. OrderDate moves to the top of the fields list.
Click Next. The Choose the Table Layout wizard page opens.
Verify Stepped is selected.
Select the Subtotals option.
Select the Enable drilldown option. You will be adding a parameter later in this tutorial to control initial drilldown state.
Click Next. The Choose the Table Style wizard page opens.
Verify the style Slate is selected. Click Next. The Completing the Wizard page opens.
In the Report name text box, replace the default report name Report1 with Resellers Worldwide.
At the bottom of the page, select the Preview report option.
Click Finish. The report opens in preview view.
Click on the expand button (+) next to A Bike Store to expand the drilldown node. In the next procedure, you will modify the TotalDue format as currency in whole numbers and the OrderDate format to show only the date portion of the DateTime field.
To change the default report format for dates and currency
Click the Layout tab.
In the table, click the OrderDate field located in the Order Date column table detail row. The OrderDate field properties appear in the Properties window.
In the Properties window, scroll to the Format property. Click in the text box next to the Format property and type d. Press the TAB key to exit the edit mode. This changes the format from the default DateTime format showing both date and time to the format showing just date.
In the table, click the TotalDue field located in the Total Due column detail row. Press the CTRL key and click the text box immediately above the TotalDue detail row. This is the text box that contains the group subtotal for each store. Two text boxes are now selected.
In the Properties window, scroll to the Format property. Click in the text box next to the Format property and type C0. Press the TAB key to exit the edit mode. This changes the format from the default format to the currency format for both selected text boxes.
(Optional) Click the Store column header. In the Properties window, scroll to the Width property. Type 2.4 or whatever value you need for your display resolution and default measurement unit to show each group header row on a single line.
(Optional) Click the Total Due column header. In the Properties window, scroll to the Width property. Type 1.25 or whatever value you need for your display resolution and default measurement unit to show each group header row on a single line.
Click Preview. Expand a drilldown node and note the change in format for the date and currency values.
(Optional) You can include on your report a timestamp indication when the report started processing. If the report takes a substantial amount of time to process, the data in the report might be more recent than the timestamp indicates.
(Optional) To add a timestamp to a report
Click the Layout tab to change to Layout view.
Click in the table. The table handles appear.
Click the corner handle to select the table. The table appears with a grayed outline.
Press the down arrow key three times to create white space on the report page between the title box and the table.
From the Toolbox, drag a text box to the whitespace above the table and below the title.
Paste the following expression in this text box:
="Report Processed Date: " & Globals!ExecutionTime.ToShortDateString() & " " & Globals!ExecutionTime.ToShortTimeString()This provides a timestamp for when the report began its processing. Note that if the report takes a substantial amount of time to run, this timestamp should not be used as a data timestamp. For cached reports or report histories, this time provides the time the report data was processed, which can be different than when the report is viewed.
With the text box selected, on the report toolbar, from the FontSize drop-down list, click 8.
On the report toolbar, click the ForegroundColor button. Choose DarkRed.
Click the Align Left button.
Select the Title text box. Press the CTRL key and select the newly added text box. From the Format menu, select Align, then Left to align the left edge of the two text boxes. The first text box you select acts as the anchor for the alignment.
From the Format menu, select Make Same Size, and then Width.
With both text boxes still selected, from the Format menu, select Vertical Spacing, then Remove to remove whitespace between the text boxes.
(Optional) Click Preview to view the report execution timestamp.
To add a report description
Click the Layout tab.
From the Report menu, select Report Properties. The Report Properties dialog box opens.
In the Description text box, type the following text: Resellers Worldwide report to demonstrate ways you can use parameters.
Click OK.
To publish a report
In Solution Explorer, right-click the Advanced Parameters Tutorial project, and then click Properties.
Click Configuration Manager.
In the Configuration Manager dialog box, in Active Solution Configuration, select Production.
Click Close.
You are back in the project Property Pages dialog box.
In the Property Pages dialog box, in TargetServerURL, type the report server virtual directory; for example, http://servername/reportserver. (This is the virtual directory of the report server, not Report Manager.)
Note
If the report server is on the same computer as Report Designer, you can use localhost as the server name, for example, https://localhost/reportserver or https://localhost/reportserver$SQLEXPRESS. For more information about report server names, see Configuring Report Server Virtual Directories.
Expand the Debug properties node if it is not already open to show the StartItem property. Click in the text box next to StartItem and from the drop-down list, select the report Resellers Worldwide.rdl.
Click OK.
Save the report project. On the File menu, click Save All.
Publish the report. On the Debug menu, click Start Without Debugging.
When publishing is complete, Report Designer opens Internet Explorer. Click Resellers Worldwide to view the report.
In Business Intelligence Development Studio, the Output window displays actions and messages as the report deploys to the report server. If the Output window is not visible, from the View menu, select Output.
Next Steps
You have successfully created the base report Resellers Worldwide for the advanced parameters tutorial. In the next lesson, you will learn to add a series of cascading parameters that show reseller stores by successively narrower categories for sales territory. See Lesson 2: Adding Cascading Parameters to a Report.
See Also
Other Resources
Working with Parameters in Reporting Services