Using HP Application Lifecycle Management v11 to track testing, defects, etc. Within this web app they have analysis features that let you create graphs/reports for defects/tests/test instances. Does anyone know if you are able to create analysis reports for the steps within a test? For example, I want to be able to see how many steps for a test have been executed, passed, failed, etc. This could be for one test or done for a range of them.

1 Answer 1


Yes, it is possible to do this. Within the Analysis View, go to "New Excel Report". Excel reports are the most flexible feature of the Analysis View, because they allow you to write SQL queries against the ALM database to extract any detail you want, at any level of summary. You can then use Microsoft Excel itself to generate charts (if you desire) or pivot tables or pivot charts to further analyze and view the data in different levels of aggregation. Or, you can use your spreadsheet as input to a dedicated reporting tool such as Crystal Reports.

If you know absolutely nothing about SQL, you might try to take some tutorials on it at W3Schools -- once you're familiar with the basics, you can write queries against the QC/ALM database by learning the ALM database schema. Go to Help -> Documentation Library within ALM and click on "HP ALM Project Database Reference". This will describe to you which tables are available in ALM (by default; note that the ALM Administrator can add their own tables and customizations) which you can query as you desire. The documentation is reasonably good at describing the meaning of each column of each table.

As far as actual test steps, they are no different than any other object in ALM. The STEP table is the standard table that stores the list of test steps. In order to be sure that you are selecting the desired test steps for the tests you're interested in, you will need to perform a "join", or add a "where" clause to your SQL statement, in order to ensure that the column ST_TEST_ID matches the value of the TS_TEST_ID column in the TEST table.

See the little yellow key next to the "ID" columns (such as ST_TEST_ID) in the documentation? Those are either primary or foreign keys. Now that you've taken the W3schools SQL tutorial, you know fully well what a primary key is, and you should now understand how to pull data from one table where the primary or foreign key is matching in another table. This is the foundational knowledge you'll need to write good SQL queries for ALM.

If you also happen to know (or are willing to learn) Microsoft Visual Basic for Applications (VBA), you can do even more with ALM Excel Reports: the reports will automatically run a VBA macro for you when the report is generated, to automatically format, clean up, or arbitrarily process the data the report produces. This is to help you automatically prepare a report for management or your peers, even if your SQL query by itself can't produce a spreadsheet that's ready-to-go without modification. For example, you can generate Pivot Tables or Pivot Charts from VBA.

  • This is an excellent reply, thanks a lot. I am pretty handy with VBA (from my .NET background) so I am excited to dive into this. However, when I tried to start a new excel graph the link is not active. Do you know if this has to do with permissions or an upgraded version of ALM? I will have to get online with our help desk to try and figure it out.
    – Jared
    Commented Jul 19, 2012 at 14:48
  • I am not sure about whether this feature requires specific licensing, because I am not an ALM Administrator myself; I am simply a regular user of a very large ALM installation at a large enterprise. However, I have previously been a victim of having the Excel Report option grayed out. I was able to get it resolved by calling my ALM Administrator support team and they had to change permissions in the entire ALM Project. Apparently this setting is controlled at the project level. My guess is, it's either disabled by default and the Admin didn't change the default; or it costs money/licenses to.. Commented Jul 19, 2012 at 15:00
  • ...enable it and that is the reason why they don't want to turn it on. OR they think it's a security risk to allow regular ALM users to directly query the SQL, but I don't see how read-only access can be destructive, so... they have no problem enabling it here, at least. The admin's reaction was "Oh, you actually NEED that?! Nice, ok... no problem, it's on now, let me know if it works." Commented Jul 19, 2012 at 15:02
  • I found our ALM support team and submitted a request for access, I will let you know how it all turns out.
    – Jared
    Commented Jul 19, 2012 at 17:24
  • If you found my answer helpful, you could +1 it or mark it as the answer... just a thought... (welcome to SQA by the way). Commented Jul 19, 2012 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.