HP ALM Analysis Custom Report issue in query parameterization

[Beginner_in_QC]

Hi all,

I am trying to extract defect report in Analysis view. For that I build query and passing one parameter "where bug_detected_release = '\@defect_detected_release\@'. In the defect, this field contains value of alphanumeric. But when we run the query I am getting error as "conversion failed when converting the varchar value '\@defect_detected_release\@' to date type int.". But while creating defect, I have provided an alphanumeric value. Please clarify how to check field type or do I need to declare the parameter before writing query ..

Thanks in advance

QC thirster

[alex_k]

You can view all table names and column names of a certain project from Site Admin or by connecting to Project DB via any DB client, or you can create new Excel Report -> go to Query Builder and on the right of query builder you'll see all tables that are in ALM project.

For example if I would like to select all defects that have certain Target release I would type SQL query like this:

SELECT *
FROM BUG
where BUG.BG_TARGET_REL = '1002'

1002 - would be Target release ID.

Or if you would like to select by release name you could do something like:

SELECT
  "defect"."BG_BUG_ID" AS "id",
  "defect"."BG_TARGET_REL" AS "target_rel",
  "release"."REL_NAME" AS "name",
  "release"."REL_ID" AS "id1"
FROM
  BUG "defect" 
  INNER JOIN RELEASES "release" ON "defect"."BG_TARGET_REL" = "release"."REL_ID"
WHERE
    "release"."REL_NAME" LIKE '%REL%'

In ALM 11.50 that is coming soon there will be a Business Views feature that will help you to build custom queries for reports or graphs, and there you'll be able to select by target release names as well.

Hope that will help you out.

[padma]

Hi, In HP ALM, I am trying to generate a report from ANALYSIS view.

Can i use GROUP BY clause and  get the count of each group ?

I have a resultset for the below coumns

SELECT

CYCL_FOLD.CF_ITEM_NAME AS TEST_SET_FOLDER,
CYCLE.CY_CYCLE  as TEST_SET_NAME,
CYCLE.CY_STATUS as TestSetStatus,
CYCLE.CY_USER_14 as TEST_SET_TYPE,
TESTCYCL.TC_STATUS as TEST_STEP_STATUS,
TESTCYCL.TC_SUBTYPE_ID,
TESTCYCL.TC_TEST_ID,
TESTCYCL.TC_TESTCYCL_ID,
TESTCYCL.TC_TEST_INSTANCE,

FROM
CYCLE,
CYCL_FOLD,
TESTCYCL

WHERE
(CYCLE.CY_FOLDER_ID = A

CYCLE.CY_FOLDER_ID =B     

CYCLE.CY_FOLDER_ID = C

CYCLE.CY_FOLDER_ID = D

)

AND
CYCLE.CY_CYCLE_ID  =  TESTCYCL.TC_CYCLE_ID AND
CYCL_FOLD.CF_ITEM_ID =CYCLE.CY_FOLDER_ID

ORDER BY
CYCL_FOLD.CF_ITEM_NAME,
CYCLE.CY_CYCLE ,
TESTCYCL.TC_TEST_ID

 I want to group the result by  TESTCYCL.TC_CYCLE_ID and get the count of Each groups.

Is it possible?

Im not sure how query it