How to Generate an Excel Report

This task describes how to analyze data in ALM using Excel reports.

Tip: The ALM Demo Project contains sample Excel reports. You can use the SQL queries and post-processing scripts in those reports as a basis for developing your own queries and scripts.

To learn more about Excel reports, see Excel Reports Overview.

  1. Prerequisites

    To verify supported versions of Microsoft Excel, see ALM system requirements.

  2. Add an Excel report to the analysis tree

    Add an Excel report to a folder in the analysis tree.

    1. Open the Analysis View module. On the ALM sidebar, under Dashboard, select Analysis View.

    2. Add a folder to the analysis tree. Right-click a folder under the Private or Public root folder, and select New Folder.

    3. Create a new Excel report. Right-click a folder, and select New Excel Report. Enter a name for the Excel report in the New Excel Report dialog box.

  3. Create an SQL query

    Define the project data that you want to include in the report. You define this data by creating SQL queries. You can create multiple queries in an Excel report. When you generate the report, the results of each query are displayed in separate Excel worksheets.

    1. Open the Query Builder. In the Configuration tab, click the Query tab, and click Query Builder. For user interface details, see Excel Report Query Builder Dialog Box.

    2. Write an SQL query. Type a query in the SQL editor.

    3. Add ALM entities to an SQL query. Select ALM entities in the Entities pane.

    4. Define parameters in an SQL query. Use parameters in SQL queries to modify the results of an Excel report, based on parameter values that you enter. In the Query Builder dialog box, use the buttons in the Query Parameters tab.

  4. Create a post-processing script

    You can create a Visual Basic script to run after the data is exported to Excel. You use this script to manipulate the data in Excel.

    For example, you can present requirements data in Excel as a pivot table, you could perform calculations on defects data to calculate the average time an open defect takes to be fixed, or you could create a graph based on exported data.

    In the Configuration tab, click the Post-processing tab. Write a Visual Basic script. For user interface details, see Post-processing Tab.

  5. Generate an Excel report

    Generating an Excel report extracts the data you specified in the SQL queries from the project database, saves the data in an Excel workbook, and runs the post-processing script.

    1. In the Configuration tab, click the Generation Settings tab. Set options for running an Excel report. For user interface details, see Generation Settings Tab.

    2. Click Generate.