Open topic with navigation

Use > Common Features > Reports > Report Examples

Report Examples

The following use cases are provided as examples to help you create a report template with parameters, upload it to HEAT, publish it, and then run it.

ClosedUsers Select Incident Categories and View Owners by Category

This example shows how to create an incident report with category as a parameter.

ClosedStep 1: Log into the Report Server
1. From the Service Desk Console, log in as a Report Manager and open the Report Template workspace. A list of report templates appears.
2. Click New Report Template. The Microsoft SSRS application opens.
3. In the Connect to Report Server login window, enter the following:
Username Enter <userID>#<Tenant ID>.
Password Enter the application password.
4. Click OK.

The Microsoft SQL Server Report Builder appears.

ClosedStep 2: Create the Dataset for the Report
1. From the left Report Data pane, click Data Sources > SaaSAppmodel.

Create Dataset for the Report

2. Right-click SaaSAppmodel, then select Add Dataset. The Dataset Properties window appears.
3. Click Query Designer, located below the Query field.
4. In the Enter Data Source Credentials window, enter your Microsoft SSRS user name and password, then click OK.
You only have to enter your Microsoft SSRS credentials once during a session.

The Query Designer window appears.

5. Click Edit as Text.
6. Enter your query. For example, a simple query to get all the incidents by categories, showing the incident number and owner

Select incidentnumber,category,owner from incident where category in (@category)

7. Click !
In this example, @category is taken by Microsoft SSRS as a parameter. When running the query, you are prompted for the value. You can provide any value to test the query. The Category parameter is created automatically. This parameter is mapped to the dataset automatically.

The Define Query Parameters window appears.

8. Enter a value in the Parameter Value field, for example, enter category, then click OK.
9. View the report columns, then click OK. The Dataset Properties window appears.
10. Click OK to close the window. The dataset is created, as shown under Datasets.

The Category parameter is created, as shown under Parameters.

Dataset Parameters

ClosedStep 3: Create the Dataset for the Parameter
1. From the left Report Data pane, click Data Sources > SaaSAppmodel.
2. Right-click SaaSAppmodel, then select Add Dataset. The Dataset Properties window appears.
3. Click Query Designer, located below the Query field. The Query Designer window appears.
4. Click Edit as Text. Use the text-based query designer to specify a query using the query language supported by the data source, run the query, and view the results at design time.
5. Enter your query. For example, a simple query to get all the incident categories

select distinct category from incident where category !=''

Your query should always have a value after select and not just select * because doing so not only causes performance issues, but it might also create an error when upgrading if a field is deleted.
6. Click !
7. View the incident categories that are created, as queried in this example, then click OK. The Dataset Properties window appears.
8. Click OK to close the window.
9. Click OK to close the Dataset Properties window. The second dataset, for the parameter, is now created.

You have now created two datasets—one for the report and one for the parameter, as shown:

Two Datasets

ClosedStep 4: Create the Parameters
1. Under Parameters, select the parameter that you created, then right-click and select Parameter Properties. The Report Parameter Properties window appears.
2. Enter a name in the Name field or accept the default that you entered in the Parameter Value field earlier on. You cannot use any spaces in this field. This name is not seen by the user.
3. In the Prompt field, enter a prompt for the user, for example, Select a Category or leave the default value. The user sees this prompt.
4. Select Allow multiple values. This allows the user to select multiple value selections from the Category menu.

Allow Multiple Values

5. Click Available Values.
6. Select Get values from a query. The Dataset Field menu appears.
7. Select the dataset that you created from the Dataset menu field.
8. Select the category value from the Value field.
9. Select the category label from the Label field.

Category Label

10. Click OK.
11. Right-click the dataset that you created for the report (as described in Step 2: Create the Dataset for the Report), and select Dataset Properties.
12. Click Parameters and make sure that it is using the appropriate parameter.

Parameters

13. Click OK to close the Dataset Properties window.
ClosedStep 5: Design the Report
1. In the designer, right-click and select Insert. You can insert charts, graphs, tables, and so on when designing your report. In this example, the report data is displayed in a table.
2. Select Table.
3. Drag from the dataset that you created and drop the values into the header fields.

Create a Report

4. Click Run to view the report. View the incident categories in the menu and the prompt value you defined.

View a Report

5. Select the categories for which you would like to run the report or click Select All, then click View Report.

Categories for a Report

6. Make sure that the report is to your satisfaction before uploading it to HEAT.
7. Click Save As and save the report to your local machine.
ClosedStep 6: Upload and Publish the Report
1. Log in as a Report Manager.
2. Click Report Templates to open the Report Template workspace.
3. Click Upload Report.
4. In the Report Template Name field, enter a descriptive name for the report. For example, enter Incident Categories by Owners.
5. Browse to select the template you saved, then click Save.
6. Click OK to dismiss the Report Published message.
ClosedStep 7: Run the Report
1. Click Reports to open the Reports workspace.
2. Click New Report.
3. From the Report Template field, select the report that you created. In this example, select Incident Categories by Owners.
4. Enter a display name and a description.
5. Select a category to group the report by. For example, select Incident.
6. In the Publish Report to field, select the roles to which this report should be published, such as Report Manager, Service Desk Analyst, or Service Desk Manager.
7. In the Parameter Information section, select some of the incident categories or select All.

Whichever value you select will be the default value when the user runs the report. However, they can select other values from the menu and run the report again.

8. If you want users to be able to select the categories, leave the Hidden field as no.
9. Select Allow Run Now.

Running the Report

10. Click Save.
11. Select the new report to view it.
Ensure that your web browser is enabled to allow pop-ups.

Users can select multiple categories or view all categories.

Multiple Categories Report

Closed Incidents Filtered by Owner Teams and Owners Within the Teams

In this example, you create an incident report showing all the incidents filtered by Owner Teams and then Owner. The list of Owners that you see after you select the Owner Team is constrained by the Team.

Users will be able to select one or more Owner Teams, select an Owner from the team, and view the incidents where the selected employee is specified as the Owner.

Closed1. Log into the Report Server
1. Log into HEAT as the Report Manager.
2. Click the Report Template tab from the Navigator Bar. The Report Template tab appears, showing a list of Report Templates.
3. Click New Report Template. The SSRS application opens.
4. In the Connect to Report Server login window, enter the following:
Username enter <userID>#<Tenant ID>.
Password enter the application password.
5. Click OK. The Microsoft SQL Server Report Builder appears.
Closed2. Create the Dataset for the Report
1. From the left Report Data pane, click Data Sources > SaaSAppmodel.
2. Right-click SaaSAppmodel, then select Add Dataset. The Dataset Properties window appears.
3. Click Query Designer, located below the Query field.
4. In the Enter Data Source Credentials window, enter your SSRS user name and password, then click OK.
You are required to enter your SSRS credentials only once during a session.

The Query Designer window appears.

5. Click Edit as Text.
6. Enter your query. For example, a simple query to get all the incidents by Owner Team, showing the incident number and owner

select incidentnumber,ownerteam,owner from incident where owner= (@owner)

7. Click !
In this example, @owner is taken by SSRS as a parameter. When running the query, you are prompted for the value. You can provide any value to test the query. The Category parameter is created automatically. This parameter is mapped to the dataset automatically.

The Define Query Parameters window appears.

8. Enter a value in the Parameter Value field, for example, enter admin, then click OK.
9. View the report columns, then click OK. The Dataset Properties window appears.
10. Click OK to close the window.
  • The DataSet is created, as shown under Datasets.
  • The owner parameter is created, as shown under Parameters.

Owner Parameter

Closed3. Create the Datasets for the Parameter

You need to create two datasets for the parameter—the first dataset is for showing the list of Owner Teams for incidents, and the second dataset is for showing the list of owners constrained by the selected Owner Team.

1. From the left Report Data pane, click Data Sources > SaaSAppmodel.
2. Right-click SaaSAppmodel, then select Add Dataset. The Dataset Properties window appears.
3. Click Query Designer, located below the Query field. The Query Designer window appears.
4. Click Edit as Text. Use the text-based query designer to specify a query using the query language supported by the data source, run the query, and view the results at design time.
5. Enter your query. For example, a query to get all the incident Owner Teams

select distinct ownerteam from incident

Your query should always have a value after select and not just select * because doing so not only causes performance issues, but it might also create an error when upgrading if a field is deleted.
6. Click !
7. View the incident Owner Teams that are shown, as queried in this example, then click OK. The Dataset Properties window appears.
8. Click OK to close the window.
9. Click OK to close the Dataset Properties window. The first dataset for the parameter is now created.

Now, create the second dataset for the parameter.

10. From the left Report Data pane, click Data Sources > SaaSAppmodel.
11. Right-click SaaSAppmodel, then select Add Dataset. The Dataset Properties window appears.
12. Click Query Designer, located below the Query field. The Query Designer window appears.
13. Click Edit as Text.
14. Enter your query. For example, a query to get all the incident Owners from the selected Owner Teams

select distinct owner from incident where ownerteam in (@ownerteam)

15. Click !
16. In the Define Query Parameters window, enter a Parameter Value, for example, Admin, then click OK.
17. View the incident Owner that is shown, as queried in this example, then click OK. The Dataset Properties window appears.
18. Click OK to close the Dataset Properties window.

You have now created three datasets—one for the report and two for the parameters, as shown:

Three Datasets

Closed4. Create the Parameter for the Owner Teams
1. Under Parameters, select the ownerteam parameter you created, then right-click and select Parameter Properties. The Report Parameter Properties window appears.
2. Enter a name in the Name field or accept the default you entered in the Parameter Value field earlier on. You cannot use any spaces in this field. This name will not be seen by the user.
3. In the Prompt field, enter a prompt for the user, for example, Select an Owner Team or leave the default value. The user will see this prompt.
4. Select Allow multiple values. This allows the user to select multiple value selections from the Owner Team menu.
5. Click Available Values.
6. Select Get values from a query. The Dataset field menu appears.
7. Select the dataset you created from the Dataset menu field, for this example, Dataset2.
8. Select the ownerteam value from the Value field.
9. Select the ownerteam label from the Label field.
10. Click OK.
Closed5. Create the Parameter for the Owners Constrained by Owner Teams
1. Under Parameters, select the owner parameter you created, then right-click and select Parameter Properties. The Report Parameter Properties window appears.
2. Enter a name in the Name field or accept the default you entered in the Parameter Value field earlier on. You cannot use any spaces in this field. This name will not be seen by the user.
3. In the Prompt field, enter a prompt for the user, for example, Select an Owner from the selected Owner Team or leave the default value. The user will see this prompt.
4. Click Available Values.
5. Select Get values from a query. The Dataset field menu appears.
6. Select the dataset you created from the Dataset menu field, for this example, Dataset3.
7. Select the owner value from the Value field.
8. Select the owner label from the Label field.
9. Click OK.
10. Right-click dataset you created for the report (as described in Step 2: Create the Dataset for the Report), and select Dataset Properties.
11. Click Parameters and make sure that it is using the appropriate parameter.
12. Click OK to close the DataSet Properties window.
Closed6. Design the Report
1. In the designer, right-click and select Insert. You can insert charts, graphs, tables, and so on when designing your report. In this example, the report data will be displayed in a table.
2. Select Table.
3. Drag from the dataset you created and drop the values into the header fields.

Design the Report

4. Go to the Parameters folder and select ownerteam, then press Ctrl + keyboard up arrow to move ownerteam above owner.
5. Click Run to view the report. View the incident Owner Team and Owners in the menu and the prompt values you defined.

View the Report

6. Select the Owner Teams you would like to view or click Select All and then select an Owner from the selected Owner Teams, then click View Report.
7. Make sure that the report is to your satisfaction before uploading to HEAT.
8. Click Save As and save the report to your local machine.
Closed7. Upload and Publish the Report
1. Log into HEAT as the Report Manager role.
2. Click Report Templates to open the workspace.
3. Click Upload Report.
4. In the Report Template Name field, enter a descriptive name for the report, for example Incidents by Owner by Teams.
5. Browse to select the template you saved, then click Save.
6. Click OK to dismiss the Report Published message.
Closed8. Run the Report
1. Click Reports to open the workspace.
2. Click New Report.
3. From the Report Template field, select the report you created. In this example, Incident by Owner by Teams.
4. Enter a Display Name and a Description.
5. Select a Category to group the report by, for example, select Incident.
6. In the Publish Report to field, select the roles to which this report should be published, for example, Report Manager, Service Desk Anaylst, Service Desk Manager.
7. In the Parameter Information section,
  • Select an incident Owner Team you want the or select All.
  • Select an Owner.

Whichever value you select will be the default value when the user runs the report. However, they can select other values from the menu and run the report again.

8. If you want users to be able to select the categories, leave the Hidden field as No.
9. Select Allow Run Now.
10. Click Save.
11. Select the new report to view it.
Ensure your web browser is enabled to allow pop up windows.

View the New Report

Users can select multiple teams or view all the teams, and then select an owner from the teams.

ClosedUse a Field that Stores both HTML and Text

This use case describes how to create a report template that uses both HTML and text-based fields from the BO records. For example, your Email body might be using HTML and text elements, which might not render the line breaks correctly in the generated report. For your reports to render correctly, you need to add an expression to the field that uses HTML and text elements.

1. Log into the Service Desk Console as Report Manager and open the Report Templates workspace. The list of report templates appears.
2. Click Edit next to the template you want to edit. The Microsoft SQL Server Reporting Service (SSRS) launches.
3. In the Connect to Report Server login window, enter the following:
Username Enter <userID>#<Tenant ID>.
Password Enter the application password.
4. Click OK.
5. From the View menu and ensure Properties is checked. The Properties pane appears.
6. Select the form field for which you want to create the expression. For example, EmailBody.

Create Expression

7. In the Properties pane, click the drop-down in the cell next to the Markup Type field and chooseExpression. The Expression window appears.
8. Enter the following expression:

=iif(Code.checkhtml(Fields!EmailBody.Value)=true,"HTML","None")

9. Click OK.
ClosedRestrict Report Data Based on Login ID
1. From the Service Desk Console, log in as a Report Manager and open the Report Template workspace. A list of report templates appears.
2. Select Edit next to the template that you want. The Report Builder application opens.
3. In the Report Parameter Properties window, enter information under the General tab.
Closed--fields--
Field Description
Name A descriptive name for the parameter, for example, LoginID.
Prompt The HEAT expression $(CurrentLoginID())
Data type The data type. Select Text.
Select parameter visibility The visibility of the parameter. Select an option.If you select Hidden, the user does not have the option to change the parameter when running the report.

Click Help in the window for more information on how to create a parameter.

4. Click OK.

Topics

Reports

Tasks

Running a Report

Printing Reports

Creating Reports & Templates

Exporting a Report

Sending a Report by Email

Reference

Scheduling a Report

Troubleshooting Reports

Default Reports

Copyright © 2011-2014 FrontRange Solutions USA Inc. | All Rights Reserved