Business Views Microsoft Excel reports

You can create and modify Business View Excel reports in Microsoft Excel. This enables you to analyze your data using any of the capabilities available in Excel, and then upload the report to ALM.

Prerequisites

Make sure you prepare for the following before generating Business View Excel reports:

Supported versions of Microsoft Excel

Verify you installed a supported version of Microsoft Excel.

For details, see Support Matrix.
Install the ALM Business Views Microsoft Excel add-in

Install the ALM Business Views Microsoft Excel add-in from Marketplace.

Note:

  • The ALM Business Views Microsoft Excel add-in is available in 32-bit and 64-bit. The 32-bit version is only for use with Microsoft Office 32-bit. The 64-bit version is only for use with Microsoft Office 64-bit.

  • If you do not install the ALM Business Views Microsoft Excel add-in, you are prompted to install the add-in when creating a business view Excel report in the Analysis module.

    Installing via Marketplace allows you to install only for yourself or for all users on the machine. To install for all users, you must have administrator permissions.

Back to top

Microsoft Excel ALM tab

After you installed the add-in, the ALM tab is available in Microsoft Excel. This tab enables you to create and configure Business View Excel reports.

UI Element

Description

Opens the login screen to log in to ALM.

Logs out of ALM.

Opens the login screen to switch to another project in the same ALM session.

Opens the Add Worksheet dialog box to create a new worksheet for each selected business view.

Toggles on and off the Worksheet Configuration pane.

Automatically refreshes the active worksheet each time its configuration is changed.

Sets the maximum number of rows shown in each Business View worksheet.

Note: Set the value to zero to retrieve all rows.

Refreshes the current worksheet.

Refreshes all worksheets.

Opens the Add Worksheet dialog box to create a new worksheet for each selected business view in a new Excel workbook.

Opens a business view Excel workbook from a file or from the ALM Analysis View tree.

Saves the current Business View Excel workbook to a file or to the ALM Analysis View tree.

Opens the ALM Addin Options dialog box.

  • Enable logging. Click to create a log of all activity that occurs while creating the Excel report.

  • Log file path. The location of the log file. Click the browse button to select a path to the log file.

Opens the Application Lifecycle Management Help for the add-in.

The help opens only when you are logged in to ALM.

Back to top

Log in to ALM from Microsoft Excel

If you access Microsoft Excel directly, and not through ALM, you must log in to ALM from Microsoft Excel.

To log in to ALM from Microsoft Excel:

  1. Open Microsoft Excel.

  2. In the ALM tab, click Connect.

  3. Enter the following login information and click Connect.

    Server URL

    URL of the ALM server in the following format:

    http://<ALM server URL>:<port number>/qcbin

    User Name

    Password

    		 Your ALM username and password

    Standard Authentication

    External Authentication

    Select Standard Authentication or External Authentication:

    • Standard Authentication. Enter the ALM server URL, login name, and password. Click Authenticate.

    • External Authentication. Select one of the following:

      • For a CAC (Common Access Card) enabled environment:

        Enter the web server URL and click External Authentication. Select a certificate.

      • For a SiteMinder enabled environment:

        Enter the web server URL and select Single Sign-On. Click External Authentication. Enter user name, and password.

    To enable access to an ALM server configured with a web server that requires client certificate authentication from the add-in, perform the following steps:

    1. Locate the file ALM.Analysis.ExcelAddin.dll.config in the BV Excel add-in installation folder, by default: C:\ProgramData\MicroFocus\ALM-Client\ExcelReportAddin.
    2. Open the file, add the following line: <add key="CLIENT_CERTIFICATE_REQUIRED" value="Y"/>, and save.
    3. Restart Microsoft Excel.

     
    Proxy Settings

    To set your proxy settings, click Proxy Settings.

    By default, the connection is done via the system proxy settings. To use a different proxy server, in Proxy Option select Use a Proxy Server. Under Proxy Server, enter the proxy details and credentials.

    Domain

    Project

    		 Select the target domain and project.

Back to top

Generate a Business View Excel report

Create and configure a Business View Excel report in Microsoft Excel, upload the report to ALM, and generate the report.

To generate a Business View Excel report:

  1. Add a Business View Excel report to 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 Business View Excel Report. In the New Business View Excel Report dialog box, enter a name for the Business View Excel report.

  2. Create the Business View Excel report in Excel.

    1. In ALM's Analysis module, select the Business View Excel report. In the Configuration tab, click New Excel. Excel opens with the ALM tab on the ribbon.

    2. In the ALM tab, click Add to add business view Excel worksheets in the same Excel book, or click New Report to add business view Excel worksheets in a new Excel book.

    3. Select the business view and click OK.

      To select multiple business views, use the Shift or Ctrl keys. The new business view Excel worksheets are added and the Worksheet Configuration pane becomes active.

  3. Configure the Business View Excel report in Excel.

    Click Show Configuration to toggle on the Worksheet Configuration pane, and configure the following:

    Select the fields to be included in the report

    In the Fields tab of the Worksheet Configuration pane, select the fields to be removed from the report.

    To select multiple fields, use the Shift or Ctrl keys.
    Filter the data to be included in the report

    In the Filter tab of the Worksheet Configuration pane, select the fields to be filtered and enter the filter criteria.

    Note: You cannot filter based on memo (CLOB) fields.
    Sort the report

    In the Sort tab of the Worksheet Configuration pane, select the fields by which the report is sorted and set the sorting criteria.

    You can sort by up to five fields at a time.

    Note: You cannot sort on memo (CLOB) fields.
    Select the projects to be included in the report

    In the Advanced tab of the Worksheet Configuration pane, select Include domain and project name and select the projects to include in the report.

    Use current project only. Select if only the current project is to be included in the report.
    Edit the query

    In the Advanced tab of the Worksheet Configuration pane, click Edit Query to configure the report by directly editing the DQL query.

    If you edit the query, the other tabs of the Worksheet Configuration pane are disabled and all selection, sorting, and filtering of fields are done directly in the DQL query.

  4. Import the Business View Excel report to ALM.

    You can upload the Business View Excel report directly to ALM from Excel. In Excel, click the arrow under the Save button and select Save to ALM.

    Alternatively, you can save the report as a file in Excel and upload it later to ALM. To upload the file in ALM:

    1. In Excel, click the arrow under the Save button and select Save to file. In the search window, navigate to the desired location, enter the desired file name, and click Save.

    2. In the Configuration tab in ALM's Analysis module, click the upload button .

    3. In the search window, navigate to the desired file and click Open. The selected file name is shown in the File name field.

  5. Generate the Business View Excel report.

    Generating a Business View Excel report refreshes all the data, saves the Excel file, and opens it in Excel.

    1. In the Analysis view in ALM, click .

    2. In the Configuration tab, click Generate.

    3. In the search window, navigate to the desired location to save the report and click Save. The report is saved and opens in Excel.

Back to top