Microsoft Excel reports

You can export ALM data to Microsoft Excel. This enables you to analyze your data using any of the capabilities available in Excel.

In this topic:

Excel reports overview

• By default, you cannot create Excel reports. You can view and edit existing Excel reports from previous versions of ALM.

  You can enable the ability to create Excel Reports by configuring the ENABLE_CREATE_LEGACY_EXCEL_REPORT site configuration parameter. For details, see Set configuration parameters.

• LoadRunner Enterprise: Excel reports are not available for LoadRunner Enterprise entities.

• An Excel report consists of a set of data defined by SQL queries on the project database. You can also run a Visual Basic script on the extracted data to process and analyze the data.

• You can also create a report with parameters. This enables you to create a more flexible report that you only need to create once but can be used in a number of different contexts.

Back to top

Generate an Excel report

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

1. Prerequisites: To verify supported versions of Microsoft Excel, see Support Matrix.

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

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

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

   3. Right-click the 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.

   For details, see Create an SQL query.

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.

   For details, see Create a post-processing script .

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

   To generate the Excel report, in the Configuration > Generation Settings tab, set options for running the Excel report.

   UI Element	Description
   	Generate. Generates the Excel report. If the status of the report is Not Ready, a warning displays. If the report query contains parameters, the Report Parameters dialog box opens. Type the parameter values in the Value column. The report is saved in the location you define. If you selected Launch report in Excel, Excel opens and displays the report. If not, a confirmation message displays.
   Launch report in Excel	Whether or not to instruct ALM to open the report in Excel after the report is generated. If this option is not selected, you can load the saved report in Excel at a later stage.
   Status	Informs other users whether the report is ready to be generated. The following options are available for the report status: Ready. Indicates that the report is ready to be generated. Not Ready. Indicates that the report is not ready to be generated. If a user tries to generate a report with this status, a warning message displays.
   Save report as	The location and name of the Excel file to which the data is exported. Click the browse button to select a folder.

Back to top

Create an SQL query

You create SQL queries to define the project data to include in an Excel report.

Before creating SQL queries

To ensure the integrity of the database, run only queries that extract data from the project database. Do not run queries that alter the project database by adding, modifying, or deleting database records.

ALM ensures that your queries are valid and do not alter the project database	By default, to ensure that your queries are valid and do not alter the project database, ALM checks that the following conditions are fulfilled: The query starts with the command SELECT. The query contains only one SQL statement. The query does not contain any of the following commands (except in comments): INSERT, DELETE, UPDATE, DROP, CREATE, COMMIT, ROLLBACK, ALTER, EXEC, EXECUTE, MERGE, GRANT, REVOKE, SET, INTO, or TRUNCATE.
Customize the verification	You can customize whether this verification is performed, and how it is performed. To customize the verification, set the SQL_QUERY_VALIDATION_ENABLED and SQL_QUERY_VALIDATION_BLACK_LIST parameters in Site Administration. For details on setting parameters, see Set configuration parameters.
Customize the maximum number of retrieved records	To customize the maximum number of records that can be retrieved from the database, set the REPORT_QUERY_RECORDS_LIMIT parameter in Site Administration. To customize the maximum length of time that ALM waits for an SQL query for an Excel report to be executed, set the REPORT_QUERY_TIMEOUT parameters in Site Administration. For details on setting parameters, see Set configuration parameters.

To create an SQL query:

1. In the Configuration > Query tab, click Query Builder.

   

2. In the SQL editor area, type a query.

   UI Element	Description
   	Add Query. Enables you to create a new query. A new tab is added to the query builder with the default name Query<number>. When you generate the report, the results of each query are displayed in separate Excel worksheets.
   	Delete. Deletes the selected query.
   	Rename Query. Enables you to rename the current query. Note: The Excel worksheet containing the data extracted by the query will have the same name as the query.
   	Test Query. Displays the first ten results of the query in the Query Results tab. If your query contains parameters, ALM uses the default values for the parameters.
   	Run Query. Displays results of the query in the Query Results tab. If your query contains parameters, ALM prompts you for values.
   	Database Type. Displays the type of database used for your project database. You must write the SQL query using the syntax used by the project database type.
   	Find. Searches the query for text that you entered in the find box. Click the Find button again to find the next occurrence of the text.
   <SQL editor>	Contains the SQL query that defines the data set that is extracted to the Excel report. Separate project entities with commas. Syntax exception: The @ and \ characters have a special meaning and are not read literally when running SQL queries on the project database. If you want these characters to be read literally, you should replace @ by \@ and \ by \\. Example: If you want the statement SELECT * FROM BUG WHERE BUG.BG_SUMMARY = `@parameter@' to be run literally on the ALM database, type SELECT * FROM BUG WHERE BUG.BG_SUMMARY = `\@parameter\@'.
   Copy/Paste	Copies selected text from one place in the SQL editor to another. Available from: Query editor right-click menu
   Cut/Paste	Moves selected text from one place in the SQL editor to another. Available from: Query editor right-click menu

3. In the entities pane, select the ALM entities to be added to the SQL query.

   UI Element	Description
   	Add Entity to Query. Adds the selected project entity to the SQL query. The entity is inserted at the cursor position. To add a project entity with the column alias defined by the entity title, click the Add Entity to Query arrow and select Add Entity to Query with Entity Title as Alias.
   	Entities View. Enables you to view fields and entities in the Entities pane by their name, their database column name, or both.

4. In the Query Parameters tab, define parameters in the SQL query.

   Use parameters in SQL queries to modify the results of an Excel report, based on parameter values that you enter.

   Example: Suppose you want to create a report that displays a graph of the number of requirements added to your project over a period of time. You might want to run the report several times, each time for a different period of time. You can avoid the need to create multiple reports by creating a single report with query parameters for the start date and end date of the period. Each time you generate the report, you are prompted to enter the start and end dates for the period.

   1. Add query parameters to the parameters list.

      Click New Query Parameter , and specify the following:

      Field	Description
      Parameter Name	A parameter name cannot include special characters, except the underscore character. If you rename a parameter, the change is automatically applied to the parameter wherever it is used in the SQL query.
      Default Value	The value that the parameter uses by default.
      Hide On Run	Instructs ALM to use the default value every time you generate the report. Clear this option if you want to be prompted to keep or change the default value when you generate the report.
      Global Parameter	Indicates that the parameter is available to all queries in the current Excel report.

   2. After you add parameters to the parameters list, click Add Parameter to Query to insert selected parameters into the query.

      Note: For a parameter representing a text value, you must manually add single quotes (') around the parameter in the SQL pane. For example, if the SQL pane contains the phrase WHERE BUG.BG_DETECTED_BY = @name@, you must replace the text @name@ with '@name@'.

   Caution:  

   • You cannot use parameters for SQL identifiers, such as a table name or a column name.

   • Parameter values cannot include an SQL statement.

   • If you delete a parameter that is in use in a report query, queries containing the parameter will not be valid.

Back to top

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.

Guidelines for creating post-processing scripts

Before writing a post-processing script, consider the following guidelines:

Your post-processing script must contain a sub-procedure named QC_PostProcessing.	This procedure serves as the entry point to your script. This procedure cannot return a value and cannot take any arguments.
Define the range containing the report data	Before you can work with the report data in Excel, you must first define the range containing the report data. You define this range in two steps: Define the worksheet containing the report data by creating a worksheet object containing the data. For example, if the worksheet is named Query1, you can use the statement Set MainWorksheet = ActiveWorkbook.Worksheets("Query1"). Define the range containing the data by creating a range object containing the data. For example, if you defined the worksheet object with name MainWorksheet, you can use the statement Set DataRange = MainWorksheet.UsedRange. After you define this range, you can manipulate the data contained in it using Visual Basic code.
Configure your Microsoft Excel security settings	Before you run a post-processing script, you must configure your Microsoft Excel security settings so that Excel can run the script. Office 2010: In Excel, click the Microsoft Office Button, and then click Excel Options. Select Trust Center > Trust Center Settings > Macro Settings. Select Trust access to the VBA project object model. Close Excel. Enabling macros in Excel allows access to core Microsoft Visual Basic objects, methods and properties, and represents a potential security hazard.

To create a post-processing script:

In the Configuration > Query tab, click Post-processing. User interface elements are described below:

UI Element	Description
	Generates the Excel report
	Find. Searches the post-processing script for text that you entered in the find box. Click the Find button again to find the next occurrence of the text.
Run post-processing	Instructs ALM to run the post-processing script after the data is exported to Excel. Clear this option if you do not want to run the script when you generate the report.
<script editor box>	Contains the Visual Basic script that runs in Excel after the report data is exported to Excel. Base the script on the template script already displayed in the Script Editor box.

Back to top