Section 5.1. Create a Report Server Project

5.1. Create a Report Server Project

First, create a report server project using Business Intelligence Development Studio. Follow these steps.

1. Open Business Intelligence Development Studio by selecting All Programs Microsoft SQL Server 2005 SQL Server Business Intelligence Development Studio from the Start menu.

2. Select File New Project... from the main menu to open the New Project dialog.

3. Specify Business Intelligence Projects as the project type, select the Report Server Project template, name the project SS2005RE Report Project , and specify its location (or accept the default location). The completed New Project dialog is shown in the following figure.

   Figure 5-1. New Project Dialog

   

4. Click OK to close the dialog and create the project.

5.2. Create a Report

In this section we will create a tabular report by defining the underlying report query and the report layout. We will then enhance the report by adding grouping, sorting, filtering, and aggregates. Finally, we will parameterize the report.

Follow these steps to create a report.

1. Add a report to the project . Right-click the Reports node in the Solution Explorer pane and click Add New Item... from the context menu to open the Add New Item dialog.

2. Create the report . Select the Report template and name the report EmployeeList 2.rdl . The completed dialog is shown in the following figure.

   Figure 5-2. Add New Item Dialog

   

Click the Add button to close the Add New Item dialog and create the report.

The IDE now displays two new panes:

• Datasets: A hierarchical list of datasets, tables, and fields of data available for use in the report.

• Design: A three tabbed design surface for building the report query (Data tab), designing the report layout (Layout tab), rendering a preview of the report (Preview tab).

The IDE is shown in the following figure.

Figure 5-3. Business Intelligence Development Studio IDE

5.2.1. Define a Report Query

1. Define a data source for the report . In the Data tab of the design pane, select <New Dataset...> from the Dataset drop-down list to display the Data Source dialog.

2. Name the data source Adventure Works 2 .

3. Enter the connection string directly into the Connection string text box (replacing <SQL Server Name> with the name of your SQL Server) or click the Edit... button to launch the Connection Properties dialog that will help build the connection string.

   The completed Data Source dialog is shown in the following figure.

   Figure 5-4. Data Source Dialog

   

   If you decided to use the Connection Properties dialog to help build the connection string, use the drop-down list to select the Server name (or type it). Select the database name AdventureWorks from the drop-down list. The completed Connection Properties dialog is shown in the following figure.

   Figure 5-5. Connection Properties Dialog

   

   Click OK to close the Connection Properties dialog and populate the Connection string in the data source dialog with the generated connection string.

4. Click OK in the Data Source dialog to create the data source and to close the dialog. AdventureWorks is added to the Dataset pane.

   Figure 5-6. Generic Query Designer

   

   Define the query for the report. After you have created the AdventureWorks dataset, the Data tab displays a generic query designera tool used to write queries that are not supported by the graphical query designer. You simply enter a report query directly into the query pane (text box above grid) of the generic query designer shown in the preceding figure.

   We will use the graphical query designer. Switch to the graphical query designer by un-clicking the generic query designer toolbar button identified in the preceding figure. The following figure shows the graphical query designer panethe figure contains the completed query for the report that you will build in the subsequent discussion.

   Figure 5-7. Graphical Query Designer

   

   The graphical query designer contains four panesdiagram, grid, SQL, and resultsthat are used together to define a report query.

5. Right-click in the diagram pane and select Add Table... from the context menu (or click the Add Table toolbar button) to add a table to the query. This displays the Add Table dialog that contains a list of tables in the database as shown in the following figure.

   Figure 5-8. Add Table Dialog

   

6. Select the Contact ( Person ) and Employee ( Human Resources ) tables and click Add to add the tables to the query. After the tables are added to the diagram pane, click the Close button to return to the query designer. Notice that the SQL pane is updated to show an empty query from the two tables, automatically joined based on the relationship between them defined in the AdventureWorks database.

7. In the diagram pane, check the NationalIDNumber and BirthDate fields in the Employee table and check the FirstName and LastName fields in the Contact table. The fields are added to the grid pane and the SQL pane is updated to include the fields in the SELECT statement.

8. Right-click in any of the four panes and select Execute SQL from the context menu to show the results of the query in the results pane.

9. Save the report. Notice that the AdventureWorks dataset (once expanded) in the Datasets pane is updated with the selected fields as shown in the following figure.

   Figure 5-9. Datasets Pane

   

The report query is now defined.

5.2.2. Define a Report Layout

You will define the report layout next. Follow these instructions.

1. Click the Layout tab of the Design pane to display the layout design surface shown in the following figure.

   Figure 5-10. Layout Tab: Initial Design Surface

   

2. Display the Toolbox if it is not visible by selecting View Toolbox from the main menu or by clicking the Toolbox icon or tab. The Toolbox is shown in the following figure.

   Figure 5-11. Toolbox

   

3. Drag the Table Report Item (since we are building a tabular report) from the Toolbox onto the layout design surface. A three-column, three-row table is drawn on the layout design surface as shown in the following figure.

   NOTE

   In addition to the Table Report Item, the Toolbox contains report items for building reports with matrix, list, and chart layouts. Building reports with these layouts is similar to building tabular reports, but beyond the scope of this book. For more information about these layouts, and about using other Toolbox Report Items including Image and Subreport, see SQL Server Books Online .

   Figure 5-12. Layout Tab: Table Control

   

4. Add a column to the report . Right-click on a column header in the table. Select Insert Column to the Right from the context menu to add the fourth column.

5. Associate fields with the report columns . Drag the NationalIDNumber field from the Dataset pane and drop it onto the first column in the Detail (middle) row of the table in the layout design surface. Drag the BirthDate, FirstName , and LastName fields onto the adjacent three cells in the row. Notice that Report Designer automatically populates the column headers with more human-readable column names. The following figure shows the layout design surface with all four fields added.

   Figure 5-13. Layout Tab: National IDNumber, Birth Date, First Name, and Last Name Columns

   

6. Render and view the report by clicking the Preview tab. The following figure shows the result.

   Figure 5-14. Preview Tab

   

7. Modify the BirthDate field to display only the month and year of the date . Click the Layout tab to return to the layout design surface. Right-click in the BirthDate field (cell containing value =Fields!BirthDate.Value ) and select Properties from the context menu to display the Textbox Properties dialog. Select the Format tab and set the Format code to MMMM dd as shown in the Textbox Properties dialog shown in following figure.

   Figure 5-15. Textbox Properties: Format Tab

   

   In addition to the formatting we have applied, notice that you can use the Textbox Properties dialog to control visibility, navigation, font, interactive sort, and data output. For more information about these topics, see SQL Server Books Online .

   Click OK to close the dialog and return to the layout design surface.

8. Select the Birth Date column in the table control. In the Properties pane, set the TextAlign property to Left as shown in the following figure.

   Figure 5-16. Birth Data Column Properties

   

9. Click the Preview tab in the Design pane to render and view the report as shown in the following figure.

   Figure 5-17. Preview Tab: Birth Date without Year

   

5.2.3. Add Grouping, Sorting, Filtering, and Aggregates to a Report

Next, we'll modify the report to demonstrate grouping, sorting, filtering, and aggregates. Follow these steps.

1. If you closed the report project in the preceding example, open it by opening Business Intelligence Development Studio, selecting File Open from the main menu, and selecting the report solution SS2005RE Report Project.sln in the Open Project dialog.

2. Add a column for the Hire Quarter field to the report . In the Layout tab, click the table control and right-click on the first column header (above the National IDNumber header). Select Insert Column to the Left from the context menu to add the new column.

3. Add a column for the Sick Leave Hours field to the report . In the Layout tab, click the table control and right-click on the last column header (above the Last Name header). Select Insert Column to the Right from the context menu to add the new column.

4. Make the HireDate and SickLeaveHours fields available for use in the report . Select the Data tab in the Design pane. Check the HireDate and SickLeaveHours fields in the Employee table in the diagram pane to add the fields to the query. Update the information displayed in the Datasets pane by right-clicking the Report Datasets node and selecting Refresh from the context menu.

5. Create a new field named HireQuarter containing the quarter in which an employee was hired . Right-click on the AdventureWorks node in the Datasets pane and select Add... from the context menu to display the Add New field dialog shown in the following figure.

   Set the Name property to HireQuarter . Click the Calculated field radio button and set the Calculated field value to ="Q" & DatePart("q", Fields!HireDate.Value) either by entering the value directly or by clicking the function button and using the Edit Expression dialog. The completed dialog is shown in the following figure.

   Figure 5-18. Add New Field Dialog

   

   Click OK to add the calculated field HireQuarter to the AdventureWorks dataset.

6. Left align the Hire Quarter column output . In the Layout tab, select the Hire Quarter column in the table control. In the Properties pane, set the TextAlign property to Left .

7. Add the HireQuarter and SickLeaveHours fields to the report . Drag the HireQuarter field from the Dataset pane onto the first column of the detail row in the table control. Drag the SickLeaveHours field onto the last column of the detail row in the table control. The Layout tab now appears as shown in the following figure.

   Figure 5-19. Layout Tab: Add HireQuarter and SickLeaveHours Fields

   

8. Preview the report and examine the newly added Hire Quarter and Sick Leave Hours columns as shown in the following figure.

   Figure 5-20. Preview Tab

   

9. Group the output rows by the value of the Hire Quarter column . Select the table control and right click on the grey area to the left of the first column. Click Insert Group from the context menu to display the Grouping and Sorting Properties dialog. In the General tab, select =Fields!HireQuarter.Value from the drop-down list in the first row of the Group on list as shown in the following figure.

   Figure 5-21. Grouping and Sorting Properties Dialog: General Tab

   

10. Sort the group on the Hire Quarter column . In the Sorting tab, select =Fields!HireQuarter.Value from the drop-down list in the first row of the Sort on list as shown in the following figure.

    Figure 5-22. Grouping and Sorting Properties Dialog: Sorting Tab

    

11. Click OK to close the Grouping and Sorting Properties dialog and return to the Layout tab. Grouping header and footer rows are added to the table control (above and below the detail row) as shown in the following figure.

    Figure 5-23. Layout Tab: Add Grouping

    

12. Define sorting on the National IDNumber column within the group . In the Layout tab, select the table control and click on its top left corner so that the column and row handles appear as shown in the following figure.

    Figure 5-24. Layout Tab: Table Control Handles

    

    Right-click on the shaded border around the table control and select Properties from the context menu to display the Table Properties dialog shown in the following figure.

    Figure 5-25. Table Properties Dialog: General Tab

    

    Select the Sorting tab. Set the first Sort on expression to =Fields!NationalIDNumber.Value as shown in the following figure.

    Figure 5-26. Table Properties Dialog: Sorting Tab

    

13. Add filtering to report only employees with at least 60 hours of sick leave . Select the Filters tab in the Table Properties dialog. Set the first Filter list expression to =Fields!SickLeaveHours.Value , the Operator to >= , and the Value to =60 as shown in the following figure.

    Figure 5-27. Table Properties Dialog: Filters Tab

    

14. Click OK to close the Table Properties dialog.

Select the Preview tab to examine the effect of the grouping and filtering you have applied.

1. Add an aggregate to the reportthe subtotal of Sick Leave Hours . Select the Layout tab. Right-click on the footer group cell below the Sick Leave Hours detail field and select Expression... from the context menu. Enter the expression =Sum(Fields!SickLeaveHours.Value) into the cell. Enter the string Quarter Subtotal into the group footer cell below the National IDNumber cell.

2. Add a second aggregate to the reportthe total of Sick Leave Hours for the entire report . Enter the expression =Sum(Fields!SickLeaveHours.Value) into the footer (bottom row) cell in the Sick Leave Hours column. Enter the string Total in the footer cell in the National IDNumber column.

The Layout tab with subtotals and total aggregates is shown in the following figure.

Figure 5-28. Layout Tab: Add Totals

Select the Preview tab to examine the aggregates you have created.

5.2.4. Add a Parameter to a Report

In this section, you will add a parameter in the report that will prompt you to enter the sick leave hour threshold for employees to include in the report. Follow these steps.

1. Select the Data tab in the Design pane.

2. Select Report Report Parameters... from the Business Intelligence Development Studio main menu to open the Report Parameters dialog.

3. Click the Add button. Enter the values for the Name, Data type, Prompt , and Default values properties to complete the dialog as shown in the following figure.

   Figure 5-29. Report Parameters Dialog

   

   Press OK to close the dialog and create the new report parameter.

4. Modify the query in the report to use the new parameter . In the grid pane of the graphical query designer, add the filter >= @SickLeaveHoursThreshold to the SickLeaveHours row as shown in the following figure.

   Figure 5-30. Graphical Query Designer: Add Parameter

   

   Notice that the SQL pane is automatically updated with a WHERE clause.

5. Remove the filter created on the Sick Leave Hours column earlier in this section so that the report is filtered only by the new report parameter . In the Layout tab, select the table control and click on its top left corner so that the column and row handles appear. Right-click on the shaded border around the table control and select Properties from the context menu to display the Table Properties dialog. Select the Filters tab and delete the expression in the Filter list. Click OK to close the dialog.

6. Select the Preview tab. The prompt for the parameter has been added in the area above the report toolbar as shown in the following figure.

   Figure 5-31. Preview Tab: New Report Parameter

   

7. Enter a value for the Sick Leave Hours Threshold parameter and click the View Report button to examine the effect.