You now need to create a data source for your report. A data source contains the database connection properties, authentication method, and some other instructions.

To create a data source:

1. In the Report Builder, right-click on Data Sources in the left pane and then click Add Data Source.

2. In the Data Source Properties dialog, type a name for the data source and then select the Use a shared connection or report model option. The other option ("Use a connection embedded in my report") allows you to specify the connection string manually, but since Parallels RAS Reporting already has the shared connection source defined, we'll use the first option instead.

3. Click the Browse button. The Select Data Sources dialog opens:

4. Double-click the RASReportingDS folder and select the dsReporting object. Click Open.

5. The dsReporting object should now appear in the list in the Data Source Properties dialog.

6. Click the Test Connection button. If the connection is successful, you should see a confirmation message.

7. Click OK to close the Data Source Properties dialog.

The next step is adding a data set, which will contain a SQL query that will retrieve the report data from the database.

To create a dataset:

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 and enter the sample query as shown on the screenshot above.

5. 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.

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

Note: When you are ready to create queries for your own custom reports, please refer to RAS Reporting Database Schema.

Target Audience

• IT professionals, such as server, network, and system administrators familiar with Microsoft SQL Server environments.

• Existing RAS administrators.

Prerequisites

• Experience administering a Parallels RAS Farm.

• Experience querying databases using Transact-SQL or Microsoft SQL Server Management Studio.

The Parallels RAS Reporting component must be installed and configured in order to use the information provided in this chapter. See Install Parallels RAS Reporting.

You can also find some additional information in the following KB article about Parallels RAS custom reports: https://kb.parallels.com/en/124648

This chapter describes how to create your own custom reports using Microsoft SQL Server Report Builder and then run the reports in the Parallels RAS Console.

To create your own reports, you need to download and install Microsoft SQL Server Report Builder.

To install Report Builder:

1. Run the Report Builder installation wizard.

2. When asked to enter the Default target server URL (optional), leave the field blank (you will specify it later).

3. Click Next and then click Install to begin the installation.

4. Click Finish when the installation completes.

After adding a data set, you can design the visual representation of your report.

Report Builder gives you many options of how the data can be represented on a report. You can use tables, lists, matrices, charts, etc. You can also insert text boxes, lines, images, and other graphics. In this tutorial, we will create a simple report that displays the data in a table format.

To insert a table into the report:

1. In Report Builder, make sure you have the main design view displayed.

2. Right-click in the report's empty space and then click Insert > Table.

3. Expand the Datasets folder in the left pane and then expand your data set, so you can see the fields included in the set.

4. Drag a data field to a desired column in the table. Repeat for other fields.

To test the report, click Run or press F5. To return back to the designer, click Design.

When you are satisfied with how your report looks in the Report Builder, you can save it in the database as follows:

1. In Report Builder, click File > Save As.

2. In the Save As Report dialog, select your report server URL in the drop-down list at the top of the dialog.

3. Double-click the Custom reports folder, type a name for your report and click Save.

In this chapter, we've demonstrated how to:

• Enable custom reports in the Parallels RAS Console.

• Create a report using the SQL Server Report Builder.

• Use a simple query to retrieve data from the Parallels RAS reporting database.

• Design the report and save it in the database.

• Run the report in the Parallels RAS Console.

The above covers the essentials of the Parallels RAS reporting functionality, so you can start creating your own reports at any time.

In the next section, we'll create a more advanced sample report that uses charts, expressions, and other features of Microsoft SQL Server Report Builder.

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.

Before you create custom reports, you need to enable the Custom Reports functionality in the RAS Console. Initially, this will perform one important action, it will create a virtual folder on the SQL Server Reporting Services side for storing your custom reports and will display it in the RAS Console together with the predefined folders.

To enable custom reports:

1. In the RAS Console, navigate to Administration > Reporting.

2. Make sure that the Enable RAS Reporting option is enabled.

3. Click the Advanced settings button.

4. In the dialog that opens, select the Enable custom reports option (at the bottom) and specify a folder name, or use the default name "Custom reports". The folder will appear on the Reports list in the Reporting category.

5. Click OK and then click the Apply button in the RAS Console.

The virtual folder for storing custom reports will be created on the SQL Server Reporting Services side. To view it in the RAS Console, select the Reporting category and click the "Refresh" icon. If you see individual reports (not folders) click the "Folders" icon at the top of the Reports pane. The "Custom reports" folder will appear in the list. By default, the folder contains a single sample report.

Managing custom reports in SQL Server Report Manager

To view the "Custom reports" virtual folder in the SQL Server Report Manager, open the following URL in a web browser:

http://[server-name]:8085/Reports_RASREPORTING

where [server-name] is the name or IP address of the server where you have the SQL Server instance installed. The "RASREPORTING" part is the SQL Server instance name. If you used a different name when you installed the instance, use that name instead of "RASREPORTING".

The SQL Server Report Manager allows you to view and manage virtual folders and reports that they contain. You can download or upload reports as files to/from your local file system, set a report data source, and perform some other tasks. We will talk about some of these tasks later in this chapter.

To run the report in the RAS Console:

1. Open the Reporting category.

2. Click the "refresh" icon at the top of the Reports list (middle pane).

3. Your new report should appear in the Custom reports folder.

4. Double-click the report to run it. The report will be displayed in the right pane.

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 and 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'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:

To create a new report:

1. Open Report Builder (Start > Apps > Microsoft SQL Server Report Builder > Report Builder).

2. In the Getting Started dialog, select New Report and then click Blank Report.

3. The main Report Builder window opens.

4. In the lower left-hand corner of the window, it should say, "No current report server. Connect".

5. Click the Connect link and then enter the Report server URL using the following format:

   http://[server-name]:8085/ReportServer_RASREPORTING

   where [server-name] is the name or IP address of the server where you have the SQL Server instance installed. The "RASREPORTING" part is the SQL Server instance name. If you used a different name when you installed the instance, use that name instead of "RASREPORTING".

6. Click Connect. The Report Builder will connect to the report server and will display the server URL in the lower left-hand corner of the window.

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.