Highlighted
Absent Member.
Absent Member.
1117 views

How to access the sql query for the filter , we create in the UI?

Jump to solution

Hi all ,

Right now I need the following report: For all the valid requirements , give me the linked test cases and their status (passed , failed , not executed ) for a particular run/cycle , etc.

Would have been much easier , if I can select the filter for each column (drop-down box with the existing filters): 1st column =IDs of the valid requirements(i have such simple filter already) , 2nd column = test case IDs and/or the names of the TCs linked to those requirements (another filter but applied on the result of the first one , like a pipeline) and the last column : the status of the TCs (from the second column) related to a specific run ID...

Another useful feature would be to be able to check how the SQL query looks like for our simple filters - then we can conduct a bit easier a more complex report..

 

Thanks,

Boris,

Tags (3)
0 Likes
2 Solutions

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Boris,

I think you found the problem. The filter excludes data driven parent nodes, only the child nodes are considered. In your project the assignment is done to the dd parents, therefore the expected nodes do not show up.

You do not want to exclude the data driven parents. Regrettably, it is not possible to change this for filtering in the way it is used here. So, it seems you have to go with an advanced query for your report:

SELECT DISTINCT t.TestID_pk testdefid, t.Name TestName, t.ProjectID_fk ProjectID
FROM LQM_Tests t
INNER JOIN TM_ReqTestCoverages rtc ON t.TestID_pk = rtc.TestPlanNodeID_pk_fk OR t.ParentID_fk = rtc.TestPlanNodeID_pk_fk
WHERE rtc.ReqTreeNodeID_pk_fk IN (
SELECT ReqTreeNodeID_pk 
FROM TM_RequirementTreeNodes rtn WITH (NOLOCK) 
INNER JOIN TM_CustomPropertyValues cpv1 ON cpv1.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk 
INNER JOIN TM_CustomRequirementProperties crp1 ON cpv1.CustomPropertyID_pk_fk = crp1.CustomPropertyID_pk 
INNER JOIN TM_CustomPropertyValues cpv2 ON cpv2.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk 
INNER JOIN TM_CustomRequirementProperties crp2 ON cpv2.CustomPropertyID_pk_fk = crp2.CustomPropertyID_pk 
WHERE rtn.ProjectID_fk = ${$PROJECTID}
AND rtn.ProjectID_fk = crp1.ProjectID_fk 
AND crp1.PropertyName = 'Issue Type' 
AND LOWER(cpv1.PropertyValue) like '${reqProp_Issue Type_1|story}'
AND rtn.ProjectID_fk = crp2.ProjectID_fk 
AND crp2.PropertyName = 'Tests (Planned)' 
AND LOWER(cpv2.PropertyValue) like '${reqProp_Tests (Planned)_3|%system test%}'
)

 

Best regards,

Hubert

View solution in original post

Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Boris,

I have extended the query by the requirement name, test status and a filtering for execution plan folder. It should work now for execution plans as well as testing cycles.

Some considerations for this query:

- Only requirements which are covered by a test are included in the result.

- By a "planned" test is meant a test is assigned to a Testing Cycle or Execution Plan and the cycle or plan was already started.

- The query is based on LQM_Tests as well as on DataMart tables, therefore it could take some time (seconds - depending on your system settings) until changes are updated.

- - If a test is assigned to more than one execution plan within the filtered execution plan folder and there are different test status then you will get for every status a separate row.

 

SELECT DISTINCT rtn.PropertyName RequirementName, t.Name TestName,

CASE WHEN statusnames.StatusName IS NULL THEN 'N/A' ELSE statusnames.StatusName END TestStatus,

rtn.ReqTreeNodeID_pk requid, t.TestID_pk testdefid, t.ProjectID_fk ProjectID

FROM LQM_Tests t

INNER JOIN LQM_V_SimpleTests testDefs WITH (NOLOCK) ON t.TestID_pk = testDefs.TestID OR t.ParentID_fk = testDefs.TestID

INNER JOIN TM_ReqTestCoverages rtc ON testDefs.TestID = rtc.TestPlanNodeID_pk_fk

INNER JOIN TM_RequirementTreeNodes rtn ON rtc.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk

LEFT JOIN RV_LatestTestStatus teststatus ON t.TestID_pk = teststatus.TestID

LEFT JOIN TM_ExecTreePaths epaths ON teststatus.ExecutionPlanID = epaths.NodeID_pk_fk

LEFT JOIN TM_TestDefStatusNames statusnames ON teststatus.StatusID = statusnames.StatusID_pk

WHERE rtc.ReqTreeNodeID_pk_fk IN (

   SELECT ReqTreeNodeID_pk

   FROM TM_RequirementTreeNodes rtn WITH (NOLOCK)

   INNER JOIN TM_CustomPropertyValues cpv1 ON cpv1.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk

   INNER JOIN TM_CustomRequirementProperties crp1 ON cpv1.CustomPropertyID_pk_fk = crp1.CustomPropertyID_pk

INNER JOIN TM_CustomPropertyValues cpv2 ON cpv2.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk

   INNER JOIN TM_CustomRequirementProperties crp2 ON cpv2.CustomPropertyID_pk_fk = crp2.CustomPropertyID_pk

   WHERE rtn.ProjectID_fk = ${$PROJECTID}

   AND rtn.ProjectID_fk = crp1.ProjectID_fk

   AND crp1.PropertyName = 'Issue Type'

   AND LOWER(cpv1.PropertyValue) like '${reqProp_Issue Type|story}'

AND rtn.ProjectID_fk = crp2.ProjectID_fk

AND crp2.PropertyName = 'Tests (Planned)'

AND LOWER(cpv2.PropertyValue) like '${reqProp_Tests (Planned)|%system test%}'

)

AND (teststatus.TestID IS NULL OR epaths.ParentNodeID_pk_fk = '${execFolderId|37}')

ORDER BY RequirementName

 

As the query now gets very custom-tailored I would suggest to do further questions and changes to it via support line.

 

Best regards,

Hubert

View solution in original post

9 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert
Hi Boris,
 
I'd like to point to the nested filter feature. It can be used from filters as well as reports.
 
Here are the steps to filter for status of tests assigned to valid requirements:
 

1.
You already have a filter for valid requirements.
 
Go to the tests unit and create a new filter which has a selection criteria "Nested Requirement Filter". Select the requirement filter you already have. Save the test filter.
 

2.
Now go to the reports unit and create a new report. Choose test run as result category.
 
As selection criteria select "Nested Test Filter". And select the test filter created in the previous step.
 
If you click on Next you can choose which columns should be in the report, e.g. test id, test name, status. Save the report.
 

3.
If you want to filter by a certain run ID then edit the report again. Click on "Advanced Query".
 
Now the report is converted to SQL and you can add any WHERE condition you want. For example if you want to filter for the Exec Plan Run ID then add
WHERE r.ExecDefRunID_fk = X
 
If you want to add the Test Run ID then add
WHERE r.TestDefExecID_pk_fk = 1
 
 
The result can be found in the data tab. There it is possible to do further filtering and sorting as known from other grids. You can also export the data as .xlsx file.
 

Regards,
Hubert
0 Likes
Highlighted
Absent Member.
Absent Member.
Hi Hubert,

As always - very comprehensive answer, thanks.

The filters are working just fine , so up to the point where I have to create a report there are no issues.

But then , when creating the report with the Nested test filter - I get no data. In order to simplify it , I've created a report in the category Test , just to get a table with the test cases - I expect the same list of TCs , as showed when I use the filter in the tab Tests, but with no success.

There is no table under Data, under the properties I've tried to export as .csv , but just to see that it is empty.

If you can find the problem , just looking at the query , here it is:


WITH Implicit_TestsHierarchie(NodeID, Level, TreeOrder) AS (
SELECT NodeID_pk, 1 AS Level,
CONVERT(NVARCHAR(500), RIGHT('000' + CONVERT(NVARCHAR, OrderNumber), 4)) AS TreeOrder
FROM TM_TestPlanNodes WHERE ParentNodeID_fk IS NULL AND ProjectID_fk = 1
UNION ALL
SELECT n.NodeID_pk, Level+1,
CONVERT(NVARCHAR(500), TreeOrder + '/' + RIGHT('000' + CONVERT(NVARCHAR, n.OrderNumber), 4) ) AS TreeOrder
FROM TM_TestPlanNodes n INNER JOIN Implicit_TestsHierarchie r ON r.NodeID = n.ParentNodeID_fk
WHERE Level < 100
)
SELECT r.TestName FROM (
SELECT t.*, th.TreeOrder FROM RTM_V_Tests_NoTreeOrder t
LEFT JOIN Implicit_TestsHierarchie th ON t.TestID = th.NodeID
WHERE t.ProjectID = 1)
r INNER JOIN (SELECT testDefs.TestID as id FROM LQM_V_SimpleTests testDefs WITH (NOLOCK) JOIN TM_ReqTestCoverages rtc ON testDefs.TestID = rtc.TestPlanNodeID_pk_fk WHERE rtc.ReqTreeNodeID_pk_fk IN (SELECT DISTINCT ReqTreeNodeID_pk as id FROM TM_RequirementTreeNodes rtn INNER JOIN (SELECT ReqTreeNodeID_pk as id FROM TM_RequirementTreeNodes rtn WITH (NOLOCK) INNER JOIN TM_CustomPropertyValues cpv ON cpv.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk
INNER JOIN TM_CustomRequirementProperties crp ON cpv.CustomPropertyID_pk_fk = crp.CustomPropertyID_pk WHERE rtn.ProjectID_fk = ${$PROJECTID} AND rtn.ProjectID_fk = crp.ProjectID_fk AND crp.PropertyName = 'Issue Type'
AND LOWER(cpv.PropertyValue) like '${reqProp_Issue Type_1|story}') sub1 ON sub1.id = ReqTreeNodeID_pk INNER JOIN (SELECT ReqTreeNodeID_pk as id FROM TM_RequirementTreeNodes rtn WITH (NOLOCK) INNER JOIN TM_CustomPropertyValues cpv ON cpv.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk
INNER JOIN TM_CustomRequirementProperties crp ON cpv.CustomPropertyID_pk_fk = crp.CustomPropertyID_pk WHERE rtn.ProjectID_fk = ${$PROJECTID} AND rtn.ProjectID_fk = crp.ProjectID_fk AND crp.PropertyName = 'Tests (Planned)'
AND LOWER(cpv.PropertyValue) like '${reqProp_Tests (Planned)_3|%system test%}') sub2 ON sub2.id = ReqTreeNodeID_pk)) data ON r.TestID = data.id


I will continue testing with a simpler filters , just to be able to get a list of Test Cases.

Thanks,
Boris,
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert
Hi Boris,

I created a demo project and added to some requirements the properties
"Issue Type" with value "story"
and
"Tests (Planned)" with value "system test"

I then created in the same project a report and pasted the SQL you posted as advanced query.

The only problem I found was that hardcoded project id in "WHERE t.ProjectID = 1". I replaced this by "WHERE t.ProjectID = ${$PROJECTID}" then the report worked. It returns all the tests which are assigned to requirements having the above properties set.

Please check if project id, property names and property values in report match the actual data.

Regards,
Hubert
0 Likes
Highlighted
Absent Member.
Absent Member.
Hi Hubert,

Changing the 1 for you makes sense , as this is my project ID , but not for your demo project (I assume).
Anyway , even with changing the hard-coded value with the variable ${$PROJECTID}, it still doesn't work.


I've checked with another filter , a very simple one (pure test filter , no nested requirement filters) and the xls is generated , so in general the feature is there.
I've also changed the Result Category for this query and the report generates an xls with the requirements , which is also OK.

But when I use the test filter with nested requirement filter , there is an empty report(csv ).

There should be something really small , that I'm missing (especially when you say it works on your side) - any other ideas?

Thank you in advance.
Boris,
0 Likes
Highlighted
Absent Member.
Absent Member.
Hi Hubert,

One difference between mine and your project is probably the source of the requirements (in mine case this is a JIRA integration).

But the one , that cause the issue on my side is the fact that I have data-driven tests (parents) linked to the requirements .

When linking data-driven instances the report works.

Is it intentionally developed like this ? Normally, the parent of the data-driven tests describes the use-case or scenario ,while the instances only feed it up with data . According to me the linkage should be possible on parent level - what do you think?

BR,
Boris,
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Boris,

I think you found the problem. The filter excludes data driven parent nodes, only the child nodes are considered. In your project the assignment is done to the dd parents, therefore the expected nodes do not show up.

You do not want to exclude the data driven parents. Regrettably, it is not possible to change this for filtering in the way it is used here. So, it seems you have to go with an advanced query for your report:

SELECT DISTINCT t.TestID_pk testdefid, t.Name TestName, t.ProjectID_fk ProjectID
FROM LQM_Tests t
INNER JOIN TM_ReqTestCoverages rtc ON t.TestID_pk = rtc.TestPlanNodeID_pk_fk OR t.ParentID_fk = rtc.TestPlanNodeID_pk_fk
WHERE rtc.ReqTreeNodeID_pk_fk IN (
SELECT ReqTreeNodeID_pk 
FROM TM_RequirementTreeNodes rtn WITH (NOLOCK) 
INNER JOIN TM_CustomPropertyValues cpv1 ON cpv1.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk 
INNER JOIN TM_CustomRequirementProperties crp1 ON cpv1.CustomPropertyID_pk_fk = crp1.CustomPropertyID_pk 
INNER JOIN TM_CustomPropertyValues cpv2 ON cpv2.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk 
INNER JOIN TM_CustomRequirementProperties crp2 ON cpv2.CustomPropertyID_pk_fk = crp2.CustomPropertyID_pk 
WHERE rtn.ProjectID_fk = ${$PROJECTID}
AND rtn.ProjectID_fk = crp1.ProjectID_fk 
AND crp1.PropertyName = 'Issue Type' 
AND LOWER(cpv1.PropertyValue) like '${reqProp_Issue Type_1|story}'
AND rtn.ProjectID_fk = crp2.ProjectID_fk 
AND crp2.PropertyName = 'Tests (Planned)' 
AND LOWER(cpv2.PropertyValue) like '${reqProp_Tests (Planned)_3|%system test%}'
)

 

Best regards,

Hubert

View solution in original post

Highlighted
Absent Member.
Absent Member.
Thanks Hubert, now with this query it works.

So far , we managed to list all the Test Cases linked to the valid requirements , but there are some more columns needed (see the initial question).

Is it too much , if I ask you to extend the advanced query with the following columns:

Valid Reguirements (now we see only the test cases linked with them),

and the execution status for the TCs based on certain execution folder (for example: if the test case is not planned in any of the plannings in the folder - N/A, if it is planned but not executed "Not executed", Passed or Failed ).

I hope , this doesn't go beyond the usual support you give in the forum, but if it is - let me know , I can probably use the supportline.

Thank you one more time - your feedback is very fruitful and provided in a timely manner.
Boris
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Boris,

I have extended the query by the requirement name, test status and a filtering for execution plan folder. It should work now for execution plans as well as testing cycles.

Some considerations for this query:

- Only requirements which are covered by a test are included in the result.

- By a "planned" test is meant a test is assigned to a Testing Cycle or Execution Plan and the cycle or plan was already started.

- The query is based on LQM_Tests as well as on DataMart tables, therefore it could take some time (seconds - depending on your system settings) until changes are updated.

- - If a test is assigned to more than one execution plan within the filtered execution plan folder and there are different test status then you will get for every status a separate row.

 

SELECT DISTINCT rtn.PropertyName RequirementName, t.Name TestName,

CASE WHEN statusnames.StatusName IS NULL THEN 'N/A' ELSE statusnames.StatusName END TestStatus,

rtn.ReqTreeNodeID_pk requid, t.TestID_pk testdefid, t.ProjectID_fk ProjectID

FROM LQM_Tests t

INNER JOIN LQM_V_SimpleTests testDefs WITH (NOLOCK) ON t.TestID_pk = testDefs.TestID OR t.ParentID_fk = testDefs.TestID

INNER JOIN TM_ReqTestCoverages rtc ON testDefs.TestID = rtc.TestPlanNodeID_pk_fk

INNER JOIN TM_RequirementTreeNodes rtn ON rtc.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk

LEFT JOIN RV_LatestTestStatus teststatus ON t.TestID_pk = teststatus.TestID

LEFT JOIN TM_ExecTreePaths epaths ON teststatus.ExecutionPlanID = epaths.NodeID_pk_fk

LEFT JOIN TM_TestDefStatusNames statusnames ON teststatus.StatusID = statusnames.StatusID_pk

WHERE rtc.ReqTreeNodeID_pk_fk IN (

   SELECT ReqTreeNodeID_pk

   FROM TM_RequirementTreeNodes rtn WITH (NOLOCK)

   INNER JOIN TM_CustomPropertyValues cpv1 ON cpv1.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk

   INNER JOIN TM_CustomRequirementProperties crp1 ON cpv1.CustomPropertyID_pk_fk = crp1.CustomPropertyID_pk

INNER JOIN TM_CustomPropertyValues cpv2 ON cpv2.ReqTreeNodeID_pk_fk = rtn.ReqTreeNodeID_pk

   INNER JOIN TM_CustomRequirementProperties crp2 ON cpv2.CustomPropertyID_pk_fk = crp2.CustomPropertyID_pk

   WHERE rtn.ProjectID_fk = ${$PROJECTID}

   AND rtn.ProjectID_fk = crp1.ProjectID_fk

   AND crp1.PropertyName = 'Issue Type'

   AND LOWER(cpv1.PropertyValue) like '${reqProp_Issue Type|story}'

AND rtn.ProjectID_fk = crp2.ProjectID_fk

AND crp2.PropertyName = 'Tests (Planned)'

AND LOWER(cpv2.PropertyValue) like '${reqProp_Tests (Planned)|%system test%}'

)

AND (teststatus.TestID IS NULL OR epaths.ParentNodeID_pk_fk = '${execFolderId|37}')

ORDER BY RequirementName

 

As the query now gets very custom-tailored I would suggest to do further questions and changes to it via support line.

 

Best regards,

Hubert

View solution in original post

Highlighted
Absent Member.
Absent Member.
Thank you very much, Hubert!
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.