I've developed quite a few complex reports in HP ALM for my clients over the past years and found it was a good idea to let power users implement SQL reports.

With version 11.x HP introduced business views, which replace not only Excel reports but apparently the whole Dashboard in HP ALM.

Although currently working with version 12.x of HP ALM, I see that you can use business views as basis for Excel reports or Charts and other online reports in the Analysis view of the ALM Dashboard.

This means you should think about migrating your existing SQL reports into business views.

Differences between old SQL based reports and new DQL based business views

Business Views are implemented using an SQL flavor called Data Query Language (DQL), which provides and abstract layer between the database and your queries.

Thus business views bare the advantage that they can query any database supported by HP ALM without having to adjust each single one of them if a database is swapped from e.g. Oracle to Microsoft.

This, from my point of view is the only advantage. Having that said, I currently work with one client for almost five years. Guess how often the database has been switched. Zero!

Other than that the new Query Editor used to implement business views in HP ALM is nicer and richer in functionality than the old query editor when it comes to supporting implementation itself. It utilizes basic stuff like syntax highlighting, auto format, proper indenting and automatic translation of DQL into SQL or a graphic representation of a query like people are used to it in MS Access. All this was not really offered in the old query editor.

BUT, you are not able to use parameters provided by end users in business views, which is a disadvantage compared to e.g. the old Excel reports.

Business views unlike Dashboard reports can no longer be defined in the Dashboard, but only in a projects customizing.

Users can then utilize one or more business vies in Excel via an Add-In they have to install separately or in the old Dashboard.

Migrating existing SQL reports to HP ALM DQL

The following has to be regarded especially, when migrating from the old Excel Reports to business views.

Well, it is more or less the list of what I still have to document in this article, since it shall tell us how to migrate.

• field and entity names ...
• special stuff like escaping "time" ...
• functions ...
• comments ...
• special things like last and first of month ...
• examples for functions cause that is missing in the HP online documentation ...

HP ALM Business View History Reports

People, who visited my site searching for specific HP ALM History reports shall not be disappointed.

Below you can find several DQL queries to get the history for one field or more than one field utilizing HP ALM Business Views.

DQL for querying last history entry per record for one field

Below SQL provides a standard structure for querying audit tables in HP ALM for one field of interest.

In below example I am joining on the BUG table to get historic information for defects.

Select defect.id,
  audit_log."time",
  audit_property.name,
  audit_property.property_name,
  audit_property.old_value,
  audit_property.new_value,
  audit_log."user"
From defect,
  audit_log,
  audit_property,
  (Select audit_log.parent_id bug_id,
    Max(audit_log."time") max_time
  From audit_log,
    audit_property
  Where audit_log.id = audit_property.parent_id And (audit_log.parent_type =
    'BUG' And audit_property.name = 'field_of_interest')
  Group By audit_log.parent_id) lau
Where CHAR_TO_CHAR(defect.id) = lau.bug_id And audit_log."time" = lau.max_time
  And audit_log.parent_id = CHAR_TO_CHAR(defect.id) And audit_log.id =
  audit_property.parent_id And (audit_property.name = 'field_of_interest' And
  audit_log.parent_type = 'BUG')

Using JOIN in the Query Builder is in general possible, but you have to watch the data types.

Else you will see funny error messages like 'invalid number' when executing a query.

It is also down to data types that I have to use the function CHAR_TO_CHAR() in the where clause to define the join condition in between entity table BUG and the audit log table.

DQL for querying the history for a period in time

Most times you will want to restrict the result from your history reports to a certain period of interest.

Examples could be previous / last month, quarter or even year.

The below query will give you all data for a field of interest for the current month.

The start and the end dates of the current month are calculated.

Select defect.id,
  defect.user_18,
  defect.status,
  defect.user_14,
  defect.name,
  defect.user_32,
  defect.user_33,
  defect.audit_log."time",
  audit_property.old_value,
  audit_property.new_value,
  defect.user_44
From defect
  Inner Join audit_log On CHAR_TO_CHAR(defect.id) = audit_log.parent_id
  Inner Join audit_property On audit_log.id = audit_property.parent_id
Where defect.status In ('Status A', 'Status B') And audit_property.new_value =
  'A New Value' And (audit_log."time" >= trunc_date(currentdate(), 'mm')
  And audit_log."time" < TRUNC_DATE(DATEADD(31, TRUNC_DATE(CURRENTDATE(),
  'mm')), 'mm')) And audit_property.name = 'A Technical Field Name of Interest'

Well folks, that's about it for now.

I will continue documenting more here in the future.