This chapter describes how to install and configure Parallels RAS Reporting. If you already have Parallels RAS Reporting installed, you may skip this chapter and proceed to Creating Custom Reports.

The following table lists the Parallels RAS release history. Parallels RAS documentation is updated for every release. This guide refers to the latest Parallels RAS release from the table below. If you are using a newer Parallels RAS release or version, please download the current version of the guide from https://www.parallels.com/products/ras/resources/.

Follow the instructions provided at .

Microsoft SQL Server 2017 and 2019 allow you to install the database engine and SQL Server Reporting Services (SSRS) on separate hosts. Parallels RAS 17.1 (and newer) supports this deployment scenario and gives you the ability to use SQL Server Reporting Services and the SQL Server database engine installed on separate hosts.

For step-by-step instructions on how to install and configure Parallels RAS Reporting Service with SQL Server 2019 and Microsoft SSRS 2019, please read the following Parallels KB articles:

• Microsoft SQL Server 2017 and 2019 single server installation: .

• Microsoft SQL Server 2017 and 2019 multi-server installation: .

The Parallels RAS reporting database contains information about users, which may possibly include personal user information. To conform to GDPR, Parallels RAS gives you the ability to clear user data from the database at any time. Parallels RAS Reporting Tools is a simple application that you can use to perform this task. The tool is installed automatically when you install Parallels RAS.

To clear user data:

1. On the computer where you have Parallels RAS installed, navigate to C:\Program Files (x86)\Parallels\RAS Reporting.

2. In the folder specified above, locate and run the RASReportingTools application.

3. When the application starts, enter a user name in the User data field and click Find user. If the user is found, the user information is displayed. If the user is not found, it means that the RAS reporting database doesn't have any information about that user.

4. To see the user information contained in the RAS reporting database, click the Show full user information button. This will open the Full User Information report in a web browser (note that this report is also available in the Reporting category in the RAS Console). Examine the report to determine if any of the user information is subject to GDPR requirements.

5. To clear the user data, go back to the Parallels RAS Reporting Tool app and click the Clear user data button. When asked, confirm that you want to clear the data.

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

To install a SQL Server instance (SQL Server 2016 or earlier):

1. Run the Microsoft SQL Server installation program and select the Custom installation type. Wait for the necessary files to be downloaded to your computer.

2. Once the files are downloaded, the SQL Server Installation Center window opens.

3. On the Installation page, select New SQL Server stand-alone installation or add features to an existing installation.

4. Click Next and follow onscreen instructions until you get to the Feature Selection page.

5. On the Feature Selection page, make sure that at least the following SQL Server features are selected for installation:

   • Database Engine Services

   • Reporting Services - Native

6. Click Next.

7. On the Instance Configuration page, select the Named instance option and enter an instance name. When naming the instance, you have the following options:

   • Enter "RASREPORTING", which is the default instance name used by Parallels RAS Reporting. If you use this name, you don't have to specify it later when installing RAS Reporting and configure it in the RAS Console. This is the recommended option.

   • If you would like to use a different name, you can do that, but you will have to make sure that you use this name when installing and configuring RAS Reporting. The instructions for installing RAS Reporting (described later in this chapter) indicate where the instance name must be specified, so if you follow the instructions, you won't miss it. Note that the instance name cannot contain dashes, dots and some other characters.

     After entering the instance name, make sure that it is also set in the Instance ID field.

8. Click Next and proceed to the Database Engine Configuration page.

9. On the Database Engine Configuration page, select the Server Configuration tab and add the following users to the SQL Server administrators list:

   • Local administrator (e.g. Administrator)

   • AD administrator (if you are just testing Parallels RAS Reporting on a local server, you can exclude this account).

   • SYSTEM (click Add, type "SYSTEM", click Check Names and click OK; the account will appear in the list as "NT AUTHORITY\SYSTEM").

10. Complete the wizard using default settings on remaining pages.

11. Wait for the SQL Server installation to finish. On the Complete page, make sure that the installation was successful and exist the wizard.

Install SQL Server Management Tools

You should also install SQL Server management tools, specifically the SQL Server Management Studio. It is not required by RAS Reporting, but it is an essential SQL Server management tool that you might find useful. If you've never worked with SQL Server Management Studio before and not sure whether you need it, we suggest you install it. For example, you can use it to view RAS Reporting database tables, constraints, and stored procedures, which may help you better understand the RAS Reporting database design. The installation link is provided on the SQL Server Installation Center window.

Configure Microsoft SQL Server 2016 and earlier

When using Microsoft SQL Server 2016 and earlier version, it must be configured for remote connections as follows:

1. Open Microsoft SQL Server Management Studio.

2. Right-click on the server and select Properties.

3. Go to Connections and select Allow Remote.

4. Open SQL Server Configuration Manager and go to SQL Server Network Configuration > Protocols for RASREPORTING.

5. Right-click on TCP/IP and choose Properties.

6. Make sure the Enabled property is set to Yes.

7. Select the IP Address tab and locate the IPAll section. Set the TCP Dynamic Ports field to be blank and the TCP Port field to "1433".

8. Restart SQL Server. To do so, in the SQL Server Configuration Manager, right-click the SQL Server service and choose Restart.

9. After the restart, in the SQL Server Configuration Manager, right-click on SQL Server Browser and choose Properties.

10. Select the Service tab and set the Start Mode property to Automatic.

11. Start the SQL Server Browser.

Configure Microsoft SQL Server Reporting Services

To configure Microsoft SQL Server Reporting Services, follow these steps:

1. Run the Reporting Service Configuration Manager (Start > Apps > Microsoft SQL Server 2016 > Reporting Services Configuration Manager).

2. In the Reporting Services Configuration Connection dialog that opens, do the following:

   • Make sure the Server Name field contains the name of the server hosting the SQL Server instance.

   • Make sure the Report Server Instance field contains the name of the SQL Server instance that you've created earlier. If you used the default Parallels RAS name, it will appear as "RASREPORTING". If you used a different instance name, select that name.

3. Click Connect. If the connection is successful, the Reporting Services Configuration Manager window opens.

4. Select the Web Service URL category (not to be confused with Web Portal URL) in the left pane and set the following properties in the right pane:

   • Virtual Directory: Make sure that the directory name is "ReportServer_RASREPORTING". If you used a different name for the SQL Server instance, you should see that name instead of the "RASREPORTING" part.

   • TCP port: Set the port number to 8085.

5. Click the Apply button to apply the settings.

6. Select the Web Portal URL category in the left pane and then do the following:

   • Make sure that the Virtual Directory field is set to "Reports_<InstanceName>", where "InstanceName" is the name of your SQL Server instance. The default Parallels RAS name would be "Reports_RASREPORTING".

   • Examine the URLs field. Make sure that the port number after the server name is 8085. If it's not, click the Advanced button and change the port number.

7. Verify that you can access the Reporting Services Web Portal by clicking the URL on the Web Portal URL page. This should open the SQL Server Reporting Services home page in a web browser.

8. Click Exit to close the Reporting Services Configuration Manager.

To view Parallels RAS reports, select the Reporting category in the RAS Console. The report information is displayed as follows:

• The middle pane lists the available reports. See the Predefined reports subsection below for the complete list. The blue "folders" icon (at the top of the list) groups reports by type or displays all of them as a flat list. The "refresh" icon refreshes the report list by retrieving it from the database (this can be useful when you enable/disable the reporting functionality or when you add custom reports, which may not appear in the list automatically).

• When you initially open the Reporting category, the right pane contains just the Information tab page, which informs you whether Parallels RAS Reporting is active. If it's not, you need to make sure that it is installed and enabled.

• The blue "square" icon in front of the Tasks drop-down list (upper right-hand side of the RAS Console) expands the reporting interface into full screen. The Tasks drop-down list allows you to perform the following actions: Duplicate (duplicates a report tab page), Full screen (on/off), various Close Report options, Delegate Permissions (allows you to grant permissions to view reports to other RAS administrators, such as Power and Custom administrators who don't have these rights).

To run a report, double-click it in the middle pane. The report opens in a tab page in the right pane:

• Most of the predefined reports include controls that you can interact with, such as From/To dates, Sort By, Sort Order, Chart Type, Server Name, and others depending on the report type. When you change a value in any of these controls, click the View Report button to apply the new values/options and re-run the report.

• The main report area (lower portion where the data is represented as a graph, text, or numbers) includes a menu bar with icons that allow you to change the magnification, list through report pages (if more than one is included), search for text, save a report to a file, print a report, and export it to one of the available formats (Word, Excel, PowerPoint, PDF, or a data feed).

Predefined reports

Parallels RAS Reporting includes a number of predefined reports in the following groups:

1. Users reports. This group includes reports about how end users are interacting with Parallels RAS:

   • Sessions activity for all users — shows all sessions produced by all users in the system. The report shows information about each session and includes active time, idle time, disconnected and total time. A user is identified by username and IP address. The Secure Gateway information is also included.

   • Sessions activity for user — shows all sessions produced by a single user. The report shows information about each session and includes active time, idle time, disconnected and total time.

   • Application usage for user — shows applications used by a specified user, including number of times used and total time.

   • Device usage for user — shows information about devices used by a user. The report includes information such as device vendor, device model, and total time used.

   • Operating system usage for user — shows the operating system being used by a specified user.

   • Full user information — shows detailed information about a specified user.

2. User groups reports. These reports obtain information about how groups of users are interacting with Parallels RAS:

   • Sessions activity for all groups — shows all sessions produced by all groups in the system. The report includes active, idle, and disconnected time.

   • Sessions activity for group — shows all sessions produced by a group in the system. The report shows information about each session produced by each user in the group and includes start, end, active, idle, disconnect and total time.

   • Application usage for group — shows applications used by a specified group, including number of times used and total time.

   • Device usage for group — shows information about devices used by users as members of a specified group. The report includes device vendor, model and total time used.

   • Client operating system usage for group — shows the operating system used by members of a particular group.

3. Devices reports. This group includes reports about the devices that are connecting to Parallels RAS.

   • Devices used — shows all devices using the system. The report includes a device manufacturer, model, and the number of sessions opened by the device.

   • Client operating system used — shows devices and corresponding operating systems that are using the system.

   • Parallels client version used — shows information about a device model, Parallels Client version used, and session information.

4. Servers activity reports. This group includes reports about the activity of Parallels RAS server components:

   • Sessions activity for RD session hosts — shows the session activity of users on a particular RD Session Host. Report includes start, end, active, idle and disconnect time.

   • Sessions activity for VDI provider — shows the session activity of users on a particular Provider. Report includes start, end, active, idle and disconnect time (standalone Hyper-V and VMware ESXi only).

   • Sessions activity for AVD provider — shows the session activity of users on a particular AVD provider. Report includes start, end, active, idle and disconnect time.

   • Session activity for RD Session Host Pool — shows the session activity of users of a particular RDSH host pool. Report includes start, end, active, idle and disconnect time.

   • Session activity for VDI Host Pool — shows the session activity of users of a particular VDI host pool. Report includes start, end, active, idle and disconnect time.

   • Session activity for AVD Host Pool — shows the session activity of users of a particular AVD host pool. Report includes start, end, active, idle and disconnect time.

   • Gateway tunnelled sessions — shows tunnelled session information for a specified Gateway.

5. Server health reports. This group includes reports on server CPU and RAM usage for different components of Parallels RAS.

   • RD Session hosts health — shows server CPU and RAM usage for a specified server in the Farm.

   • Providers health — shows server CPU and RAM usage for a specified provider in the Farm.

   • Remote PCs health — shows server CPU and RAM usage for a specified Remote PC in the Farm.

   • Gateways health — shows server CPU and RAM usage for a specified Gateway in the Farm.

   • Connection Brokers health — shows server CPU and RAM usage for a specified Connection Broker in the Farm.

   • Enrollment servers health — shows server CPU and RAM usage for a specified Enrollment server in the Farm.

6. Application reports. Reports related to applications.

   • Activity for all applications — shows information about applications used in the system. Report includes information such as application name, number of times used and the total usage time. When viewing this report, select "All applications" or "RAS published applications" depending on your needs. When the second option is selected, the report will not include non-published applications and duplicates.

   • Activity for application — shows usage of an application by individual users during a specified time period. The information includes start time, end time, and total time for each session. Other information, such host server name and session ID is also shown.

7. Logon duration reports. Reports that show detailed information about user logon duration. They also show information about connection duration, authentication duration, RAS policy lookup duration, host preparation duration, group policy load time, and desktop load time.

   • Logon duration for all users — shows minimum, maximum, and average logon duration for all users on every server.

   • Logon duration for user — shows minimum, maximum, and average logon duration for a specified user on every server.

   • Logon duration for RD Session Host — shows minimum, maximum, and average logon duration on a specified RD Session Host for every user.

   • Logon duration for VDI Provider — shows minimum, maximum, and average logon duration for specified Provider for every user.

   • Logon duration for AVD Provider — shows minimum, maximum, and average logon duration for specified AVD provider for every user. The report also shows this information for connection duration, authentication duration, RAS policy lookup duration, host preparation duration, group policy load time, and desktop load time.

   • Logon duration for VDI Host Pool — shows minimum, maximum, and average logon duration for specified VDI Host Pool for every user.

   • Logon duration for AVD Host Pool — shows minimum, maximum, and average logon duration for specified AVD HOst Pool for every user.

8. UX Evaluator reports. Reports related to UX Evaluator, which is the time interval measured at the client between the first step (user action) and the last step (graphical response displayed).

   • UX Evaluator for all users — shows UX Evaluator for all users on every server.

   • UX Evaluator for user — shows UX Evaluator for a specified user on every server.

   • UX Evaluator for RD Session Host — shows UX Evaluator for a specified RD Session Host for every user.

   • UX Evaluator for VDI Provider — shows UX Evaluator for a specified Provider for every user.

   • UX Evaluator for AVD Provider — shows UX Evaluator for a specified AVD provider for every user.

   • UX Evaluator for VDI Host Pool — shows UX Evaluator for a specified VDI Host Pool for every user.

   • UX Evaluator for AVD Host Pool — shows UX Evaluator for a specified AVD Host Pool for every user.

9. Transport protocol reports. Reports that show how long each transport protocol is used during sessions.

   • Transport protocol for all users — shows information about the used transport protocols for all users.

   • Transport protocol for user — shows information about the used transport protocols for a specified user.

   • Transport protocol for RD Session Hosts — shows information about the used transport protocols for a specified RD Session Host.

   • Transport protocol for VDI Provider — shows information about the used transport protocols for a specified Provider.

   • Transport protocol for AVD Provider — shows information about the used transport protocols for a specified AVD Provider.

   • Transport protocol for VDI Host Pool — shows information about the used transport protocols for a specified VDI Host Pool.

   • Transport protocol for AVD Host Pool — shows information about the used transport protocols for a specified AVD Host Pool.

10. Connection quality reports. Reports that show information about connection quality.

    • Connection quality for all users — shows information about connection quality for all users.

    • Connection quality for user — shows information about connection quality for a specified user.

    • Connection quality for RD Session Hosts — shows information about connection quality for a specified RD Session Host.

    • Connection quality for VDI Provider — shows information about connection quality for a specified Provider.

    • Connection quality for AVD Provider — shows information about connection quality for a specified AVD Provider.

    • Connection quality for VDI Host Pool — shows information about connection quality for a specified VDI Host Pool.

    • Connection quality for AVD Host Pool — shows information about connection quality for a specified AVD Host Pool.

11. Latency reports. Reports that show information about session latency.

    • Latency for all users — shows information about session latency for all users.

    • Latency for user — shows information about session latency for a specified user.

    • Latency for RD Session Hosts — shows information about session latency for a specified RD Session Host.

    • Latency for VDI Provider — shows information about session latency for a specified Provider.

    • Latency for AVD Provider — shows information about session latency for a specified AVD Provider.

    • Latency for VDI Host Pool — shows information about session latency for a specified VDI Host Pool.

    • Latency for AVD Host Pool — shows information about session latency for a specified AVD Host Pool.

12. Bandwidth availability reports. Reports that show information about bandwidth availability.

    • Bandwidth availability for all users — shows information about bandwidth availability for all users.

    • Bandwidth availability for user — shows information about bandwidth availability for a specified user.

    • Bandwidth availability for RD Session Hosts — shows information about bandwidth availability for a specified RD Session Host.

    • Bandwidth availability for VDI Provider — shows information about bandwidth availability for a specified Provider.

    • Bandwidth Availability for AVD Provider — shows information about bandwidth availability for a specified AVD Provider.

    • Bandwidth availability for VDI Host Pool — shows information about bandwidth availability for a specified VDI Host Pool.

    • Bandwidth Availability for AVD Host Pool — shows information about bandwidth availability for a specified AVD Host Pool.

13. Session disconnect reports. Reports that show the mosts frequent reasons for disconnecting and the number of reconnections.

    • Session disconnect for all users — shows top disconnect reasons and the number of reconnections for all users.

    • Session disconnect for user — shows top disconnect reasons and the number of reconnections for a specified user.

    • Session disconnect for RD Session Hosts — shows top disconnect reasons and the number of reconnections for a specified RD Session Host.

    • Session disconnect for VDI Provider — shows top disconnect reasons and the number of reconnections for a specified Provider.

    • Session disconnect for AVD Provider — shows top disconnect reasons and the number of reconnections for a specified AVD Provider.

    • Session disconnect for RD Session Hosts Pool — shows top disconnect reasons and the number of reconnections for a specified RD Session Host Pool.

    • Session disconnect for VDI Host Pool — shows top disconnect reasons and the number of reconnections for a specified Provider.

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.

Operating system requirements

Parallels RAS Reporting can be installed on a server running one of the following Windows Server versions:

• Windows Server 2022

• Windows Server 2019

• Windows Server 2016

• Windows Server 2012 R2

.NET Framework 3.5 and .NET Framework 4.5 or higher must be installed.

Microsoft SQL Server requirements

Parallels RAS Reporting can be used with the following Microsoft SQL Server versions:

• Microsoft SQL Server 2022

• Microsoft SQL Server 2019

• Microsoft SQL Server 2017

• Microsoft SQL Server 2016

Beginning with RAS 17.1, SQL Server Reporting Services (SSRS) and the SQL Server database engine can be deployed on separate hosts.

Using Microsoft SQL Server 2017 and 2019

Microsoft SQL Server 2017 and 2019 allow you to install the database engine and SQL Server Reporting Services (SSRS) on different hosts. Parallels RAS 17.1 (and newer) supports this deployment scenario and gives you the ability to use SQL Server Reporting Services and the SQL Server database engine installed on separate hosts.

Installation locations

RAS Reporting must be installed on the same server where SQL Server Reporting Services are running. Please note that if you have SSRS and the database engine installed on different hosts, RAS Reporting must be installed where the SSRS are installed.

The following table contains RAS and SQL Server version compatibility information and locations where components necessary to use RAS Reporting can be installed:

Microsoft SQL Server must be installed as a named instance (not default or unnamed instance), because an instance must have a name for RAS Reporting to work. You can specify an instance name when you install Microsoft SQL Server (or when you create a new SQL Server instance in a multi-instance scenario). For complete details, please read the Install Microsoft SQL Server section that follows this one.

Note: For Parallels RAS installations running on multiple servers, it is recommended that Microsoft SQL Server is installed on a dedicated server.

In this section:

To install Parallels RAS Reporting:

1. Log in to the server where you have Microsoft SQL Server Reporting Services installed. Make sure you use the account with administrative privileges (AD).

2. Note: As was mentioned earlier, SQL Server 2017 and newer allow you to install SQL Server database engine and SQL Server Reporting Services (SSRS) on different hosts. You need to be logged in to the server where you have SSRS installed.

3. Download the latest version of Parallels RAS Reporting from https://www.parallels.com/products/ras/download/links/.

4. Once downloaded, double-click the RASReporting-xxx.msi file to run the installation wizard.

5. Follow the onscreen instructions and proceed to the Database connection page. Specify the SQL Server database engine location:

   • Location: If the SQL Server database engine is installed on the local server (together with SSRS), select Localhost. If the SQL Server is installed on a different server, select Remote and then specify the server connection properties (see below).

   • Server: If you selected Remote, specify the FQDN or IP address of the server where you have SQL Server installed.

   • Username: Specify the username to log in to SQL Server.

   • Password: Specify the password.

6. On the same page, specify the SQL Server instance name. The default instance name is RASREPORTING. If you would like to use a different instance, you can specify it on this page. If the instance doesn't exist, you need to create it first.

7. Click Next.

8. On the Viewing Reports User page, you need to specify an Active Directory user who will be granted permissions to access the RAS reporting database. The default user is "rasreportingview" (note that the user must be created in Active Directory before it can be used here). You can specify a different Active Directory user if you wish, but you will need to change the reporting settings in the RAS Console before you can view reports (this change is described later in this chapter when the RAS reporting configuration is explained).

9. Click Next to install Parallels RAS Reporting.

Configure RAS Reporting in the RAS Console

To configure Parallels RAS Reporting:

1. Log in to the Parallels RAS Console.

2. Select the Administration category and click the Reporting tab in the right pane.

3. In the Reporting tab, select the Enable RAS Reporting option.

4. In the Server field, specify the FQDN or IP address of the server hosting your SQL Server instance. The value in the Port field is used by the service which receives data from the RAS Connection Broker. The default port is 30008.

5. Specify a user login option by selecting one of the following:

   • Prompt user for login details — If this option is selected, the Parallels RAS Console user will be prompted to enter credentials before they can run a report.

   • Use following credentials — If this option is selected, the specified username and password will be used. The default (built-in) user name is RASREPORTINGVIEW. If you specified a different user when you installed RAS Reporting, specify that user credentials here.

6. To test the database connection, click the Test connection button.

Configure advanced settings

These settings are optional, so you can configure them according to your needs.

To access advanced settings:

1. On the Administration > Reporting tab page, click the Tracking Settings button. The Advanced Setting dialog opens.

2. In the Session Information section, specify the following options:

   • Enable Tracking. Records sessions data (affects all reports except server reports).

   • Retain information for. Select for how long the information should be kept in the database.

3. In the Server Counters Information section, specify the following:

   • Enable Tracking. If selected, server counter data is recorded (affects server reports only).

   • Retain information for. Select for how long the information should be kept in the database.

   • Track CPU / Memory counter when change is more than (%). Use these two options to set the minimum CPU and Memory resource usage required to record data.

4. The Custom reports section is used to enable custom reports in the Parallels RAS Console. Select the Enable custom reports option and specify a folder name where custom reports will be stored (or use the default "Custom reports" name). Note that this is a virtual folder located on the SQL Server Reporting Services side, so you need to specify just a name (not a traditional path). You will see the folder in the Parallels RAS Console in the Reporting category together with other (predefined) folders that contain reports.

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.

This guide describes how to use Parallels® RAS Reporting. The first part of the guide describes how to install and configure Parallels RAS Reporting and then view predefined reports in the Parallels RAS Console. The second part explains how to create your own custom reports using Microsoft SQL Server Report Builder.

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.

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.

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 Create a New Blank Report and Add a Data Source sections.

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:

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.

The table below describes the RAS Reporting database schema.

To see the visual representation of the schema, please use the following link:

Database schema

SQL query example

Referring to the database schema and the SQL query above, we can see that the query does the following:

• Selects usernames from table dbo.Users and gives the "us" alias to the table.

• Since dbo.Users is not directly related to dbo.ApplicationConnections, it should first join dbo.RDSessions using matches on field SID from dbo.Users and User_fk from dbo.RDSessions

• After we have joined dbo.RDSessions (alias "rd"), which has link to dbo.ApplicationConnections, we may also join ApplicationConnections itself with alias "ac" and select application names and number of launches from it.

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.

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.

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.

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.

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.

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.