To begin, open Microsoft SQL Server Report Builder, connect to the report server, create a new report, and add a data source. These steps are the same as described in the previous section. See Create a New Blank Report and Add a Data Source sections.

First, we'll add a report title and some standard built-in fields like page numbers.

To add a title, simply type it in the field at the top of the report area.

To add page numbers:

1. Delete the [&ExecutionTime] field from the report footer. We'll replace it with the page number field.

2. Select the Text Box item on the ribbon and then click at the desired position on the page footer to insert the text box. Move the box as needed.

3. Expand the Built-in Fields folder in the left pane, select the Page Number field and drag it to the text box.

This section describes how to create a report that uses charts and advanced techniques to display the information about servers in the farm, grouped by server type.

This is the sample report that is installed by default when you enable the Custom Reports functionality in the RAS Console. The following sections describe how to create this report.

Add a dataset as follows:

1. In the Report Builder, right-click the Datasets folder in the left pane and then click Add Dataset.

2. In the Dataset Properties dialog, type a data set name and then select the Use a dataset embedded in my report option.

3. In the Data source field, select the data source that you created earlier.

4. In the Query type section, select Text to use an embedded SQL as your query.

5. Enter the following query into the Query edit box:

   SELECT

   m.Name as [Machine Name],

   mt.MachineType as [Machine Type],

   mt.SortingID as [SortingID]

   FROM

   dbo.Machines m RIGHT OUTER JOIN

   dbo.MachineType mt ON mt.MachineType_ID = m.MachineType_fk

6. To test the query, click the Query Designer button. In the dialog that opens, click the exclamation sign icon (at the top) to run it. If the query is valid, you will see the result set.

7. Close the Query Designer dialog and then click OK to save the dataset.

We will now add a bar chart that will illustrate the "machines by type" information.

To add a chart:

1. On the main menu, click Insert > Chart > Insert Chart and then click in the middle of the main report area.

2. In the Select Chart Type dialog that opens, select the first item in the Column section and click OK.

Double click on the chart and then delete the Details group from the Category Groups section by right-clicking it and choosing Delete Category Group.

Now do the following:

1. Double-click the chart header and type Summary as the title.

2. Add the Machine_Name field to the Values section by clicking the green plus-sign icon.

3. Add the Machine_Type field to the Series Groups section.

4. Click on the arrow on the Machine_Type row and choose Series Group Properties.

In the Series Group Properties dialog, select the Sorting tab and set the Sort by field to [SortingID]. Click OK.

We'll now add a pie chart that will show the server information. Once again, click Insert > Chart and choose the pie shape.

On the main report screen, do the following:

• Change the chart title to Summary.

• Add Machine_Name to Values.

• Add Machine_Type to Category Groups.

• Expand the Machines_Type drop-down list and choose Category Group Properties.

Select the Sorting tab and set the Sort by field to SortingID. Click OK.

Click Insert > Matrix:

Drag and drop Machine_Name. Remove the existing Machine_Name (highlighted in blue on the screenshot below):

Drag and drop Machine_Type:

Right-click on the Data box and choose Select > Image:

In the Image Properties dialog:

1. Name the image.

2. Make sure that Embedded is selected in the Select the image source field.

3. Click the Import button and select an image.

4. Click OK.

We'll now add some formatting to our report.

Right-click in the indicated area (see the screenshot below) and choose Row Visibility.

In the Row Visibility dialog, select the Show or hide based on an expressions option and then click the Fx button (on the right). In the Expression dialog that opens, enter the following expression:

=If(IsNothing(Fields!Machine_Name.Value <> Fields!Machine_Type.Value, false, true)

The expression will hide the empty machine names.

Right-click on the image and select Image Properties. In the Image Properties dialog, set padding to position the image in the center:

In the Visibility tab, select Show or hide based on an expression and click the Fx button. Enter the expression as shown below:

Right-click on Parameters and choose Add Parameter:

Set the parameter name and the prompt:

In the Available Values tab, select Specify values and add the required labels:

In the Default Values tab, set a default value from the previous list:

On the main report screen, right-click on the pie chart and select Chart Properties. In the Chart Properties dialog, select Show or hide based on an expression and click the Fx button. In the Expression dialog, enter the following:

On the main report screen, right-click on the bar chart and select Chart Properties. In the Chart Properties dialog, select Show or hide based on an expression and click the Fx button. In the Expression dialog, enter the following:

Save the report. You can now run it in the Parallels RAS Console: