Analysis of test steps in HP ALM

Question

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.

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.