Highlighted
garzaacb Regular Contributor.
Regular Contributor.
235 views

Create a Business View that reports Only Current Run Steps

I have been trying to figure out for about a week now to create a business view that only counts the current runs steps.

Currently I Business View Graph uses the business view Run Steps.  When using the business view 'Run Steps", it includes steps from previous runs.  Is there a way to create a DQL for Run Steps that only report on the current run?

0 Likes
11 Replies
Knowledge Partner Knowledge Partner
Knowledge Partner

Re: Create a Business View that reports Only Current Run Steps

I think you might be better off starting out with a copy of the Business View called Test Instances With Linked Runs instead. You should be able to use a WHERE clause where you can use the MAX function on the run ID for each test instance.
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
0 Likes
garzaacb Regular Contributor.
Regular Contributor.

Re: Create a Business View that reports Only Current Run Steps

I can't seem to get the max function to work.  Nothing gets returned.  I am sure I am doing something wrong.  Here is my Query

Select test_instance.id,
test.name As test_instance_name,
test_instance.cycle_id,
test_set.name As test_set_name,
test_instance.status,
run.id As run_id,
run.name As run_name,
run.status As exec_status,
test.subtype_id,
run.execution_date,
run.execution_time,
run_step.user_05,
run_step.status As status1
From test_instance
Inner Join run On test_instance.id = run.testcycl_id
Inner Join test On test_instance.test_id = test.id
Left Join test_set On test_set.id = test_instance.cycle_id
Inner Join run_step On run.id = run_step.parent_id
Where run.testcycl_id = Max(run.id)

0 Likes
Knowledge Partner Knowledge Partner
Knowledge Partner

Re: Create a Business View that reports Only Current Run Steps

In the select, change 

run.id As run_id

to

max(run.id) As run_id

 

Remove the where clause and instead add

group by test_instance.id

Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
0 Likes
garzaacb Regular Contributor.
Regular Contributor.

Re: Create a Business View that reports Only Current Run Steps

Its still not working.  Nothing gets returned .  When I preview the query I get the following error.  Can you tell me what this means?

[Mercury][SQLServer JDBC Driver][SQLServer]Column 'TEST.TS_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I understand it is asking for me to include more columns in grouping.  So I add them and it is not giving me what i need when I preview the query.  Its still listing all the test instances instead of showing me the most recent ones.  Is there someithing I am missing

Here is the updated query

Select test_instance.id,
test.name As test_instance_name,
test_instance.cycle_id,
test_set.name As test_set_name,
test_instance.status,
Max(run.id) As run_id,
run.name As run_name,
run.status As exec_status,
test.subtype_id,
run.execution_date,
run.execution_time,
run_step.user_05,
run_step.status As status1
From test_instance
Inner Join run On test_instance.id = run.testcycl_id
Inner Join test On test_instance.test_id = test.id
Left Join test_set On test_set.id = test_instance.cycle_id
Inner Join run_step On run.id = run_step.parent_id
Group By test_instance.id

0 Likes
garzaacb Regular Contributor.
Regular Contributor.

Re: Create a Business View that reports Only Current Run Steps

Okay I know I am getting closer.  Here is my new query.  The problem is I need only the latest run steps for each instance.

Select run.testcycl_id As "Test Instance ID",
Max(run.id) As max_run_id,
run_step.status,
run_step.id
From run
Inner Join run_step On run.id = run_step.parent_id
Inner Join test_instance On test_instance.id = run.testcycl_id
Group By run.testcycl_id,
run_step.status,
run_step.id,
test_instance.cycle_id
Having (run.testcycl_id = 531) Or
(run.testcycl_id = 534)

Here is a snapshot of the results.  The problem is the max run id 1403 is showing up.  That is not the latest run.  How do I capture only the latest runs latest steps

clipboard_image_0.png

0 Likes
Knowledge Partner Knowledge Partner
Knowledge Partner

Re: Create a Business View that reports Only Current Run Steps

You need GROUP BY instead of HAVING.
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
0 Likes
garzaacb Regular Contributor.
Regular Contributor.

Re: Create a Business View that reports Only Current Run Steps

I have it working.  I had to do a subquery in the join for run id.  Here is my final query.  I do have a question though.  How do I show steps where there is no run yet.  Right now I have the test instance showing as null but it doesn't list steps since the run does not exist.  Do you any suggestions on how to solve this?  I want those steps to show up as no run.

Select run.testcycl_id As "Test Instance ID",
Max(run.id) As max_run_id,
run_step.status,
run_step.id,
run_step.execution_date,
run_step.execution_time,
run_step.user_05,
run_step.test_id,
test_instance.test_id As test_id1,
test.name,
test_folder.name As name1,
test_folder.parent_id
From test
Inner Join test_folder On test_folder.id = test.parent_id
Inner Join test_instance On test.id = test_instance.test_id
Inner Join run On run.id = (Select Max(run.id) From run
Where run.testcycl_id = test_instance.id)
Left Outer Join run_step On run.id = run_step.parent_id
Group By run.testcycl_id,
run_step.status,
run_step.id,
run_step.execution_date,
run_step.execution_time,
run_step.user_05,
run_step.test_id,
test_instance.test_id,
test.name,
test_folder.name,
test_folder.parent_id

clipboard_image_0.png

0 Likes
Knowledge Partner Knowledge Partner
Knowledge Partner

Re: Create a Business View that reports Only Current Run Steps

You should join the DESSTEPS table as well.
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
0 Likes
garzaacb Regular Contributor.
Regular Contributor.

Re: Create a Business View that reports Only Current Run Steps

I'm almost there.  Here is my latest query.  The records that have runs started are showing doubled.  So if there are three steps in the run it shows as 6 steps in the query.  But the test cases that have no runs show the correct number of test steps.

So my next question is how do I make the steps that have no run id have a status of no run.  Also how to I put in a todays date and time for steps that have no run yet (fake it out)

Select run.testcycl_id As "Test Instance ID",
Max(run.id) As max_run_id,
run_step.status,
run_step.id,
run_step.execution_date,
run_step.execution_time,
run_step.test_id,
test_instance.test_id As test_id1,
test.name,
test_folder.name As name1,
test_folder.parent_id,
test.id As id1,
test_instance.id As id2,
run.id As id3,
test_instance.cycle_id,
run_step.desstep_id,
design_step.id As id4
From test
Inner Join test_folder On test_folder.id = test.parent_id
Inner Join test_instance On test.id = test_instance.test_id
Left Outer Join run On run.id = (Select Max(run.id) From run
Where run.testcycl_id = test_instance.id)
Left Outer Join run_step On run.id = run_step.parent_id
Right Outer Join design_step On test.id = design_step.parent_id
Group By run.testcycl_id,
run_step.status,
run_step.id,
run_step.execution_date,
run_step.execution_time,
run_step.test_id,
test_instance.test_id,
test.name,
test_folder.name,
test_folder.parent_id,
test.id,
test_instance.id,
run.id,
test_instance.cycle_id,
run_step.desstep_id,
design_step.id

 

clipboard_image_0.png

0 Likes
NEW_ALM12 Outstanding Contributor.
Outstanding Contributor.

Re: Create a Business View that reports Only Current Run Steps

I do not have straight query for BV excel. But, if I could be a helpful, below is my query.

You may try running in regular excel report. If it satisfies, you twist to fit into BV excel. 

SELECT DISTINCT

CF_ITEM_NAME as "Test Set Folder Name",
CY_CYCLE as "Test Set Name",
RN_RUN_ID as "Run ID",
TS_NAME as "Test Case Name",
RN_STATUS as "Test Case Status",
ST_STEP_NAME as "Test Step Name",
ST_STATUS as "Test Step Status",
DS_USER_01 as "Tester Name",
RN_EXECUTION_DATE as "Date",
RN_EXECUTION_TIME as "Time"

FROM DESSTEPS,( SELECT *
FROM STEP,( SELECT *
FROM RUN,(SELECT
CF_ITEM_NAME ,CY_CYCLE ,TS_TEST_ID,TS_NAME,MAX(RN_RUN_ID) AS MAX_RUN_ID
FROM TEST,( SELECT *
FROM STEP,( SELECT  * FROM RUN,(SELECT *
FROM TESTCYCL, ( SELECT *
FROM CYCLE,(SELECT * FROM CYCL_FOLD) A
WHERE CY_FOLDER_ID=CF_ITEM_ID ) B
WHERE TC_CYCLE_ID=B.CY_CYCLE_ID) C
WHERE RN_TESTCYCL_ID=C.TC_TESTCYCL_ID) D
WHERE ST_RUN_ID=D.RN_RUN_ID ) E
WHERE TS_TEST_ID= E.TC_TEST_ID
GROUP BY CF_ITEM_NAME ,
CY_CYCLE,TS_NAME,TS_TEST_ID ) M
WHERE  RN_RUN_ID=MAX_RUN_ID ) N
WHERE ST_RUN_ID=RN_RUN_ID ) O
WHERE DS_TEST_ID=O.TS_TEST_ID
0 Likes
garzaacb Regular Contributor.
Regular Contributor.

Re: Create a Business View that reports Only Current Run Steps

I have everything working.  I decided to just fake the time.  Below is my final solution in case anyone else would like to use this.  Thank you for those who gave me clues on how to do this

Select test_instance.id,
test_instance.cycle_id,
test_set.name As test_set_name,
test.name,
ISNULL(run_step.status, 'No Run') As Status,
ISNULL(run_step.execution_date, test_set.open_date) As "Exec Date",
ISNULL(run_step.execution_time, '01:01:01') As "Exec Time",
test_folder.name As name1,
test_folder.parent_id
From test_instance
Inner Join test On test_instance.test_id = test.id
Left Join test_set On test_set.id = test_instance.cycle_id
Inner Join design_step On test.id = design_step.parent_id
Left Outer Join run On run.id = (Select Max(run.id) From run
Where test_instance.id = run.testcycl_id)
Left Outer Join run_step On run_step.desstep_id = design_step.id And
run.id = run_step.parent_id
Inner Join test_folder On test_folder.id = test.parent_id

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.