Creating Report: Using the Table Wizard

Resources:

• http://msdn.microsoft.com/en-us/sql10r2byfbi-trainingcourse_sql10r2byfbi10-hol-01_topic2.aspx

Exercise 1: Creating a Report Using the Table Wizard

In this exercise, you will commence by launching Report Builder, and then create a report using the Table Wizard. Having developed the report layout, including the configuration of a report parameter, you will publish the report parameter and main dataset for use by the report authored in Exercise 2.

The finished report will resemble the following.

Figure 1

Previewing the Sales by State report

Task 1 – Launching Report Builder

In this task, you will open Report Manager to launch Report Builder.

1. Open Internet Explorer from Start | All Programs | Internet Explorer.
2. In the Internet Explorer window, in the URL box, enter http://<servername>/Reports, and then pressEnter.
   Note:

   You will need to substitute <servername> for the name of the machine that hosts Reporting Services.
3. To launch Report Builder, on the Report Manager toolbar, click Report Builder.
   
   Figure 1
   

   Launching Report Builder
4. If Report Builder has not already been installed, you will be prompted to download and install the application. When prompted to run the application, click Run.
   
   Figure 2
   

   Installing Report Builder

Task 2 – Creating a Report Using the Table Wizard

In this task, you will use the Table Wizard to create the report. This will involve the creation of a dataset, and arranging the dataset fields to produce the table design. The dataset will be configured to support a parameter that will enable users to request the report for a specific calendar quarter.

1. When Report Builder launches, to create a new table report, in the Getting Started window, click Table or Matrix Wizard.
   
   Figure 3
   

   Launching the Table or Matrix Wizard
2. In the New Table or Matrix window, in the Choose a Dataset step, ensure the Create a Dataset option is selected(located at the bottom of the window), and then click Next.
3. To browse to a published shared dataset, in the Choose a Connection to a Data Source step, clickBrowse.
4. In the Select Data Source window, double-click the Data Sources folder, select theAdventureWorksDW2008R2 data source, and then click Open.
5. In the New Table or Matrix window, click Next.
6. In the Design a Query step, in the Database View pane, expand the Views folder, and then check thevReportSalesByState view.
7. In the Selected Fields pane, notice that all the view columns become fields.
8. To preview the data, click Run Query.
   
   Figure 4
   

   Previewing the data
9. Notice that the CalendarQuarterKey field values are expressed as integers combining the calendar year and quarter. For example, 20053 represents the third quarter of calendar year 2005.
10. In the Selected Fields list, ensure that the CalendarQuarterKey is selected, and then in the Applied Filterspane, click Add Filter.
    
    Figure 5
    

    Adding the CalendarQuarterKey field as a filter
11. In the Applied Filters list, click inside the CalendarQuarterKey filter value, and enter 20082,and then pressEnter.
12. To create a report parameter for the filter, check the Parameter checkbox.
    
    Figure 6
    

    Configuring the CalendarQuarterKey filter
13. Click Next.
14. In the Arrange Fields step, in the Available Fields list, select the Country field, and then while pressing the Control key, select the State field also.
15. Drag the selected fields into the Row Groups box.
16. From the Available Fields list, drag the Sales field into the Values box.
17. Verify that the field arrangement looks like the following.
    
    Figure 7
    

    Verifying the field arrangement
18. Click Next.
19. In the Choose the Layout step, in the Options, select the Stepped, Subtotal Above option, and then clickNext.
20. In the Choose a Style step, review the available styles, and then click Finish.
21. In Report Builder, if necessary, maximize the window.
22. To save the report, click the top left corner button (for the purpose of this lab, it will be referred to as theReport Builder button), and then select Save.
    
    Figure 8
    

    Saving the report
23. In the Save as Report window, double-click the Sales Reports folder.
24. In the Name box, replace the text with Sales by State, and then click Save.

Task 3 – Developing the Table Report Layout

In this task, you will format the report layout that will involve adding a report header, configuring the title textbox, and formatting the table, its columns and number value formats.

1. To add a header to the report, on the Insert ribbon tab, inside the Header & Footer group, click Header, and then select Add Header.
2. Select the title textbox (the text in the title textbox reads Click to Add Title), and then drag it into the top left corner of the report header.
3. In the Report Data pane (located on the left), expand the Built-in Fields folder, and then drag the Report Name field into the title textbox.
   Note:

   The last step has assigned an expression that will dynamically assign the name of the report to the textbox.
4. While the report title textbox is selected, on the Home ribbon tab, inside the Border group, select the bottom border.
   
   Figure 9
   

   Formatting the textbox bottom border
5. To select the table, click anywhere inside it to reveal the column and row guides, and then click the top left corner.
   
   Figure 10
   

   Selecting the table
6. Click and drag the four-headed arrow to reposition the table to the top left corner of the body of the report.
7. To widen the first column, click anywhere inside the table to reveal the column and row guides, and then drag the right edge of the first column guide to widen the column to approximately twice its original size.
8. To select all of the second column’s textboxes, select the second column guide.
   
   Figure 11
   

   Selecting all of the second column’s textboxes
9. To right-align the textboxes, on the Home ribbon tab, inside the Paragraph group, click the Right align button.
   
   Figure 12
   

   Clicking the Right Align button
10. To format the Sales values, select the first [Sum(Sales)] textbox.
11. On the Home ribbon tab, inside the Number group, in the dropdown list, select Number.
12. Repeat the last two steps to format the other two sales textboxes.
13. To preview the report, on the Home ribbon tab, click Run (located on the far left).
14. Notice the report parameter and its default value requesting data for the second calendar quarter of 2008.
    Note:

    You will configure this report parameter to prompt a user-friendly name, and present a list of available values in the next task.
15. Expand Australia to reveal the states of Australia.
16. To save the report, click the Report Builder button, and the select Save.

Task 4 – Configuring the Report Parameter

In this task, you will develop the Quarter report parameter to prompt the user with available values and default to the first of those values. You will also extend that layout of the report to display the parameter selection in the header of the report.

1. To return to design mode, on the Run ribbon tab, click Design (located on the far left).
2. To add a dataset to provide available values for the report parameter, in the Report Data pane, expand the Data Sources folder, right-click the AdventureWorksDW2008R2 data source, and then select Add Dataset.
3. In the Dataset Properties window, in the Name box, replace the text with dsCalendarQuarter.
4. In the Query Type options, select Stored Procedure.
5. In the Select or Enter Stored Procedure Name dropdown list, select theuspReportParam_CalendarQuarterKey stored procedure, and then click OK.
6. In the Report Data pane, notice the addition of the dataset and that it contains two fields:CalendarQuarterKey and CalendarQuarterLabel.
7. To configure the report parameter, in the Report Data pane, expand the Parameters folder, right-click theCalendarQuarterKey report parameter, and then select Parameter Properties.
8. In the Report Parameter Properties window, in the Prompt box, replace the text with Quarter.
9. Select the Available Values page.
10. Select the Get Values From a Query option, and then configure the following properties.

    Property	Value
    Dataset	dsCalendarQuarter
    Value Field	CalendarQuarterKey
    Label Field	CalendarQuarterLabel

    
    Figure 13
    

    Configuring the report parameter available values
11. Select the Default Values page.
12. Select the Get Values From a Query option, and then configure the following properties.

    Property	Value
    Dataset	dsCalendarQuarter
    Value Field	CalendarQuarterKey

    
    Figure 14
    

    Configuring the report parameter default value
    Note:

    This configuration will ensure that the first row retrieved from the dataset becomes the report parameter’s default value.
13. Click OK.
14. To add the parameter selection to the report header, in the Report Data pane, drag theCalendarQuarterKey report parameter and drop it into the report header, directly beneath the report title.
15. Click and drag the four-headed arrow of the textbox to reposition it directly beneath the report title and aligned to the very left of the report title textbox.
16. To configure the textbox expression, right-click inside the report parameter textbox, and then selectExpression.
    
    Figure 15
    

    Configuring the textbox expression
17. In the Expression window, modify the expression by replacing the Value property with the Label property, and then click OK.
    Visual Basic

    =Parameters!CalendarQuarterKey.Label
18. To preview the report, on the Home ribbon tab, click Run.
19. In the Quarter parameter dropdown list, select CY2008 Q1, and then click View Report (located at the far right).
20. Notice that the report header displays the parameter selection.
21. To save the report, click the Report Builder button, and the select Save.

Task 5 – Publishing Report Parts

In this task, you will publish the CalendarQuarterKey report parameter and the dataset used by the table. In the next exercise, you will create a new report that will use both of these shared components.

1. To return to design mode, on the Run ribbon tab, click Design.
2. To publish the report parts, click the Report Builder button, and the select Publish Report Parts.
3. In the Publish Report Parts window, select the Review and Modify Report Parts Before Publishing.
   
   Figure 16
   

   Publishing report parts
4. To select and configure the report parts to publish, in the Report Parts group, notice that theCalendarQuarterKey report parameter is already checked.
5. Uncheck the Tablix1 report part.
6. In the Datasets group, check the DataSet1 dataset.
7. Click the word DataSet1, and then modify the text to SalesByState.
8. To expand the dataset details, click the arrow to the left of the SalesByState dataset.
   
   Figure 17
   

   Configuring the SalesByState dataset
9. To configure a different location to store the dataset, click the Browse button.
   
   Figure 18
   

   Clicking the Browse button
10. In the Select Folder window, click the Up One Level button.
    
    Figure 19
    

    Clicking the Up One Level button
11. Select the Datasets folder, and then click OK.
12. Verify that your configuration looks like the following.
    
    Figure 20
    

    Reviewing the Publish Report Parts configuration
13. To publish the selected parts to the report server, click Publish.
14. Click Close.
15. In the Report Data pane, notice that DataSet1 icon now includes an arrow which signifies that it references a shared dataset.
    
    Figure 21
    

    Reviewing the shared dataset icon
16. To save the report, click the Report Builder button, and the select Save.